Hi Guys

I need help with AJAX. I want to UPDATE my DB without refreshing. I don't really know how to do this. I have found piece of code online but for some reason is not working. Another thing is : how to make run PHP code after 5 minutes. For example: someone push the button and after 5 minutes my DB will be updated. Any Ideas?

 

Not enough information to give any sort of meaningful answer.

What sort of DB?

What is being updated?

What does this button do?

Do you have access to the server?

Why the 5 minute delay?

I send you answer to your first question in pm

Edited August 28, 20214 yr by noobdesigner23

let's say I want update name, last name, and age ( just an example) so I just input new name etc and hit the submit button. other ( 5 minutes delay ) issue should work like this: I hit the button and count starts ( doesn't have to be visible) and after 5 minutes it will add something to my database. 5 minutes it's just an example
 

Edited August 28, 20214 yr by codernoob2

I’m confused are you @codernoob2 and @noobdesigner23?

anyway. It’s not possible to do this in the browser. Once a browser session is over all the variables will be destroyed. What you would need to do is save the variables in a temp table in the DB and us server tools to run a counter and the update. You need to investigate cron. This will help.

There are quite a few ways to do this, but I would be interested to know why the data can't be persisted in the DB straight away? You can store the data and not show it using a timestamp which would remove a lot of complexity. Then you could just hide any interface with JavaScript and prevent re-submissions server-side using a check on the timestamp just in case JS is disabled.

Or, you can add it to a queuing system, and have that persist the data after a period of time. Typically you would only use a queue to process intensive tasks like image processing though.

Could you explain a bit more about what you're trying to do and why it has this requirement?

On 8/28/2021 at 8:42 AM, fisicx said:

anyway. It’s not possible to do this in the browser. Once a browser session is over all the variables will be destroyed. What you would need to do is save the variables in a temp table in the DB and us server tools to run a counter and the update. You need to investigate cron. This will help.

@fisicx Bee-doo, Bee-doo, Bee-doo - https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage😉

 

On 8/31/2021 at 5:46 PM, Jack said:

There are quite a few ways to do this, but I would be interested to know why the data can't be persisted in the DB straight away? You can store the data and not show it using a timestamp which would remove a lot of complexity. Then you could just hide any interface with JavaScript and prevent re-submissions server-side using a check on the timestamp just in case JS is disabled.

Or, you can add it to a queuing system, and have that persist the data after a period of time. Typically you would only use a queue to process intensive tasks like image processing though.

Could you explain a bit more about what you're trying to do and why it has this requirement?

@Jack As an ex-DBA I'd like to say - Using date/datetime/timestamp in WHERE clause to filter any data is not a good idea; leads to performance degradation; and should be considered as anti-pattern. I wouldn't bother you with Data Normalisation on this 😉

Last but not least: What about using setTImeout(); / clearTimeout(); functions on form submission?

 

Best wishes,
Ursel

 

On 9/9/2021 at 10:20 AM, Die Stumme Ursel said:

@fisicx Bee-doo, Bee-doo, Bee-doo - https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage😉

 

@Jack As an ex-DBA I'd like to say - Using date/datetime/timestamp in WHERE clause to filter any data is not a good idea; leads to performance degradation; and should be considered as anti-pattern. I wouldn't bother you with Data Normalisation on this 😉

Last but not least: What about using setTImeout(); / clearTimeout(); functions on form submission?

 

Best wishes,
Ursel

 

This would be true if you're querying datasets that contain a large amount of data that regularly increase. Without knowing anything about the size of the data, how often writes happen, and how the database is architected, saying there's a perf issue reading a value with a WHERE clause is completely over the top. Literally every CRUD app will use WHERE to filter things like getting a user ID, and they basically never run into issues with this until they hit very high scale. The app we're talking about here might only have 10 users and barely any data, so this type of query would be nanoseconds on a modern DB server. You don't even have to filter using WHERE on the date, you can grab the last value and do the comparison server-side.

OP hasn't been very clear about what they're trying to do, but if the aim is to have the data saved, the most important thing is to persist the data as soon as possible. A user can alter the app state in so many ways in a 5 min period, that can cause data to simply never be saved, or become corrupted.

If you're suggesting using setTimeout and waiting 5 mins before saving, that's likely going to lead to a lot of state bugs that are tricky to replicate. You can save to localStorage, but if the user closes their session, there is no action that runs in the background to ever save that data in the DB. Even if the user comes back, you'll have to run something on the client to check, and reading from localStorage, running that process etc, would be considerably slower than a single DB read, since localStorage isn't very well optimised. The chances of running into inconsistent state bugs and timing issues is practically guaranteed. This could also potentially be a security issue depending on what you're saving as LocalStorage can be modified on the client without any checks.

Again, we don't know much about what OP is actually trying to do as his question is vague. Hiding the content using LocalStorage for 5 mins would be fine just to "lock" content temporarily, but if saving is involved in some way, and the data needs to be accessed again later, I would personally write instantly and worry about perf later, if it even becomes an issue.

Edited September 11, 20214 yr by Jack

On 9/11/2021 at 2:36 PM, Jack said:

This would be true if you're querying datasets that contain a large amount of data that regularly increase. Without knowing anything about the size of the data, how often writes happen, and how the database is architected, saying there's a perf issue reading a value with a WHERE clause is completely over the top. Literally every CRUD app will use WHERE to filter things like getting a user ID, and they basically never run into issues with this until they hit very high scale. The app we're talking about here might only have 10 users and barely any data, so this type of query would be nanoseconds on a modern DB server. You don't even have to filter using WHERE on the date, you can grab the last value and do the comparison server-side.

As I said above - I wouldn't bother you with Data Normalisation rules as probably it would be a complete waste of (mine) time.

Speaking about filtering data sets and using date/datetime columns being anti-pattern is not about the amount of data; execution time to select single column/row out of 10 rows total and so on. It is broken by design. If you plan and model not just your databases but rather your project(s) without security and performance in mind will lead to issues, sooner or later. Here is a brief example why date/datetime is bad. Assume we have following structure

 

CREATE TABLE `t1` (
  `idate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then we could so something like this:

MariaDB [d1]> INSERT INTO t1 (idate) VALUES (19970505);
Query OK, 1 row affected (0.00 sec)

MariaDB [d1]> INSERT INTO t1 (idate) VALUES ('19970505');
Query OK, 1 row affected (0.01 sec)

MariaDB [d1]> INSERT INTO t1 (idate) VALUES ('97-05-05');
Query OK, 1 row affected (0.00 sec)

MariaDB [d1]> INSERT INTO t1 (idate) VALUES ('1997.05.05');
Query OK, 1 row affected (0.00 sec)

MariaDB [d1]> INSERT INTO t1 (idate) VALUES ('1997 05 05');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [d1]> INSERT INTO t1 (idate) VALUES ('0000-00-00');
Query OK, 1 row affected (0.00 sec)

 

And then doing some filtering like this:

 

MariaDB [d1]> SELECT idate FROM t1 WHERE idate >= '1997-05-05';
+------------+
| idate      |
+------------+
| 1997-05-05 |
| 1997-05-05 |
| 1997-05-05 |
| 1997-05-05 |
+------------+
4 rows in set (0.00 sec)

MariaDB [d1]> SELECT idate FROM t1 WHERE idate >= 19970505;
+------------+
| idate      |
+------------+
| 1997-05-05 |
| 1997-05-05 |
| 1997-05-05 |
| 1997-05-05 |
+------------+
4 rows in set (0.00 sec)

MariaDB [d1]> SELECT MOD(idate,100) FROM t1 WHERE idate >= 19970505;
+----------------+
| MOD(idate,100) |
+----------------+
|              5 |
|              5 |
|              5 |
|              5 |
+----------------+
4 rows in set (0.00 sec)

MariaDB [d1]> SELECT idate FROM t1 WHERE idate >= '19970505';
+------------+
| idate      |
+------------+
| 1997-05-05 |
| 1997-05-05 |
| 1997-05-05 |
| 1997-05-05 |
+------------+
4 rows in set (0.00 sec)

 

It could be over the top for you but I'd rather will try to plan / design / build / whatever I need to do with security considerations and performance optimisations in mind rather than choosing easiest way.

After so many years working as a Database Administrator I don't need the actual database structure to know if their approach is good or bad. 

I am not suggesting anything. Let's make things clear - I am pointing why using DATE/DATETIME/TIMESTAMP columns to filter data is bad. And it is really bad no matter how many rows you are filtering - one or one million.
IT IS BAD as jumping head first without a parachute from a plane - either from 500 feet or 5000 feet. You'll end dead either ways.

As they have said:

On 8/26/2021 at 7:47 AM, codernoob2 said:

Another thing is : how to make run PHP code after 5 minutes. For example: someone push the button and after 5 minutes my DB will be updated. Any Ideas?

Almost exactly what setTimeout() does. Simple question - simple answer. As easy as that. It's not necessary to write a poem explaining why other people's opinion is wrong 😉

I am not saying that this approach is best or any better than yours or someone else's. It is up to OP to make a decision what to use or will they use anything suggested here at all.

It is a little bit off topic but if you allow me to ask:

@Jack Are any opinions different than Moderator's / Administrator's welcome on these forums?
Of course you could decide I am troll / spammer and edit/delete my post if it's necessary 😉

- Ursel

 

I don't think you're a troll or spammer, I was genuinely curious, since most ORM's use this exact method. You see this in ActiveRecord, Ecto, Eloquent, Prisma and many others all the time. In fact, many will write the created_at and modified_at date for you on insert or update for this exact purpose.

2 hours ago, Die Stumme Ursel said:

It could be over the top for you but I'd rather will try to plan / design / build / whatever I need to do with security considerations and performance optimisations in mind rather than choosing easiest way.

Persisting data from LocalStorage isn't secure, it can be modified by anyone from any domain. It's fine for locking content, showing and hiding UI etc, but the data should be seen as untrustworthy.

None of your examples look slow to me either. They are broken in what they return, that's true, but the timer seems to be instant, unless I'm missing something? As I said before, you don't need to filter using SQL anyway, this can be handled server-side at the application level.

2 hours ago, Die Stumme Ursel said:

Almost exactly what setTimeout() does. Simple question - simple answer. As easy as that. It's not necessary to write a poem explaining why other people's opinion is wrong

We don't know what OP is trying to do, but for others that stumble across the same thread in Google looking for an answer, it's worth explaining that this method isn't viable in some cases, since most user sessions aren't 5 mins in duration.

As i mentioned, for locking content with absolutely no data persistence, go ahead and use LocalStorage and setTimeout, it will work fine. However, if OP's goal is to require that data in some way, disabling the UI and waiting for it to be written after a period of time is significantly more error prone than disabling the UI and writing instantly.

2 hours ago, Die Stumme Ursel said:

Are any opinions different than Moderator's / Administrator's welcome on these forums?
Of course you could decide I am troll / spammer and edit/delete my post if it's necessary

No, discussions like this are worth having, and I never edit other peoples posts. You know more about databases than me as far as I can tell, which is why I asked in the first place. At the very least we have detailed the options more thoroughly as a result of this discussion anyway.

On 9/13/2021 at 7:24 PM, Jack said:

I don't think you're a troll or spammer, I was genuinely curious, since most ORM's use this exact method. You see this in ActiveRecord, Ecto, Eloquent, Prisma and many others all the time. In fact, many will write the created_at and modified_at date for you on insert or update for this exact purpose.

Ah, my next "favourite" topic - the almighty ORM (just kidding). From the OOP principles point-of-view any ORM (or similar) tool is an anti-pattern. I wouldn't start that flame now. 
In my humble opinion using ORMs is making developers bad at database design. The most common mistakes are:

• Improper relations between entities
• Lack of or dump indices
• Using ORM classes as anemic objects with data

I am not saying that ORM tools are bad at all, but let me ask you following questions:

• How many developers do you know to be using proper ERD (Entity Relationship Design) tools?
• How many of them are using Generalisation / Specialisation properly when it comes to ER modelling?
• How many of them knows how to use database indices properly?
• How many of them understands Database Normalisation in general and Normal Forms?

Having to know when each tuple was created (created_at) or modified (modified_at) doesn't add that much value to neither your product nor data. The other funny part is so called "soft delete". Having such "hidden" rows in your table(s) would lead to database fragmentation and overall performance penalties. Let's see it in action, assuming we have following structure 

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` char(40) DEFAULT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And have following considerations:

• We are using (almost) standard users table provided by Laravel framework
• We are using TIMESTAMP columns for created_at and updated_at to utilise CURRENT_TIMESTAMP predicate and no to bother with handling these columns in our code (That's the power of Eloquent ORM here)
• We are trying to put our data footprint as small as possible hence password column is CHAR(40) assuming we will have SHA1 hashes instead of plain text password

Now let's try to find most recently created user, a simple SELECT query would looks like

SELECT * FROM users ORDER BY created_at DESC LIMIT 1;

But what happen behind the scene is

MariaDB [d1]> EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Definitely RDBMS is doing a full table scan. It will be blazingly fast with 5 rows of data but what about 500K rows?
Having "Using filesort" would lead to temporary table to be created in order to perform sorting and then limitation of the result set. When working with smaller result sets such temp. tables will be created in memory - which will be fast enough. Increasing numbers of rows in the table, ie. amount of data stored in the table, would lead to temp. tables to be created on disk - which is slow and resource consuming process. Also, such temporary objects have to be freed properly adding more time to overall execution time.

Now, given the fact we are aware of our database structure and we know how indices and auto_increment works we could make following assumptions:

• All user IDs are generated automatically in (almost) sequential order
• All timestamps are generated automatically at same time as user ID
• Hence user with ID of 5 will be (or should be) created after user with ID of 4

Using different approach to fetch the most recently created user will give us

MariaDB [d1]> EXPLAIN SELECT * FROM users ORDER BY id DESC LIMIT 1;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|    1 | SIMPLE      | users | index | NULL          | PRIMARY | 4       | NULL |    1 |       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

But what if we create an index on created_at column to speed our first query, erm let see

MariaDB [d1]> ALTER TABLE users ADD INDEX created_at (created_at);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [d1]> EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 1;
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------+
|    1 | SIMPLE      | users | index | NULL          | created_at | 4       | NULL |    1 |       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------+
1 row in set (0.00 sec)

It's the same result as using our primary key. So far so good one would say. If we end adding indices to all columns in our table then we will have index trees with the same size as underlying data which will render our indices unusable. Let see what we have when it comes to data storage

[mind@hive ~]# du -h /var/lib/mysql/d1/users.*
4.0K    /var/lib/mysql/d1/users.frm
11M     /var/lib/mysql/d1/users.ibd

I am using innodb_file_per_table = 1 to optimise InnoDB storage but all in all - our table uses 11MB for storing our data (the actual size is less but that's not the point). What will be the size if we get rid of created_at index then?

MariaDB [d1]> ALTER TABLE users DROP INDEX created_at;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [d1]> ALTER TABLE users ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

[mind@hive ~]# du -h /var/lib/mysql/d1/users.*
4.0K    /var/lib/mysql/d1/users.frm
2.1M    /var/lib/mysql/d1/users.ibd

We could say the overhead of data storage was 550% having 10K rows in our table. Proving that adding indices with high cardinality is not a good idea storage- and performance-wise.

On 9/13/2021 at 7:24 PM, Jack said:

None of your examples look slow to me either. They are broken in what they return, that's true, but the timer seems to be instant, unless I'm missing something? As I said before, you don't need to filter using SQL anyway, this can be handled server-side at the application level.

My previous examples where given to prove why date / date time / time related columns are broken by design. My current examples explains why using such columns to filter data are slower. Long story short - more data to seek in (ie. bigger storage) leads to more teach to do the search. Using DATETIME instead of TIMESTAMP is even worse because it uses 8 bytes (without fractional seconds) compared to 4 bytes.

Alas, when it comes to different environments figures will be very different. Doing tests on local (isolated) environment with handful of objects (databases, tables, rows in the tables, etc) is a way different that running same queries on heavy-loaded production servers using replication for example.

To give you a more natural example - let say we have our users table presented as a notebook and having each row written on it's own page. Which one will be fast:

• Reading all pages to compare all created_at value, or
• Going to last page to read it's created_at value

This applies on scenario with index on created_at column too because it is a secondary index.
 

On 9/13/2021 at 7:24 PM, Jack said:

Persisting data from LocalStorage isn't secure, it can be modified by anyone from any domain. It's fine for locking content, showing and hiding UI etc, but the data should be seen as untrustworthy.

Every bit of data sent by user should be seen as untrustworthy indeed. I couldn't agree more.

On 9/13/2021 at 7:24 PM, Jack said:

We don't know what OP is trying to do, but for others that stumble across the same thread in Google looking for an answer, it's worth explaining that this method isn't viable in some cases, since most user sessions aren't 5 mins in duration.

As i mentioned, for locking content with absolutely no data persistence, go ahead and use LocalStorage and setTimeout, it will work fine. However, if OP's goal is to require that data in some way, disabling the UI and waiting for it to be written after a period of time is significantly more error prone than disabling the UI and writing instantly.

Disabling the UI is called Display Morphing, Representation Morphing pattern or more commonly known as in-place edit but yet could be best possible solution.

 

-Ursel

Edited September 16, 20214 yr by Die Stumme Ursel

Interesting – thanks for the detailed reply.

I semi agree with the ORM thing. I defiantly have seen slow queries from them, but for getting a product to market quickly they are often the best bet for developers that don't have a lot of DB experience, like myself, although I see how this could be problematic in the future.

I wouldn't say - ORMs are evil and they shouldn't be used as every story has two sides. Issues related to ORMs often appears because most developers doesn't know how to use them properly but rather trying to use pre-made solutions like answers from reddit, stackoverflow and similar. With propel knowledge of ANSI SQL and Relational Algebra - an ORM could become very powerful tool.

When it comes to development you could have just two of the speed, quality and security. Also almost all of widely used PHP Frameworks comes with an ORM. Most popular CMS platforms have their own database abstraction tools.

On the other hand when it comes to optimisations most people are missing these simple facts:

• Server configuration could be optimised regarding the hardware
• Database structure could be optimised regarding the queries

-Ursel