Member Avatar for francis123

Hello! everyone out there!
I am having a table that looks like this in phpmyadmin

.......................................................................................................
. id . fname . lname . user_id . trackdate . country .
.......................................................................................................
. 1 . Jonh . Mark . 10 . 21 . USA .
. 2 . Frank . Yank . 12 . 12 . Canada .
. 3 . Biola . Bud . 13 . 30 . Gahna .
. 4 . James . Game . 15 . 24 . Nigeria .
. 5 . Jamba . David . 16 . 9 . England .
. 6 . Luke . Sola . 17 . 1 . Japan .
. 7 . Foster . Fraka . 18 . 28 . USA .
. 8 . Grace . Jack . 19 . 14 . USA .
. 9 . Kate . Bala . 20 . 18 . Nigeria .
. 10 . Maze . Good . 21 . 6 . USA .
........................................................................................................


I want all the records in 'trackdate' culumn to be incremented by 1 anytime the page that holds the query runs.

example: 21 becomes 22, 12 becomes 13, 30 becomes 31, and so on in that same culumn.

can i get a mysql query that can do this, any help will be appreciated

Thanks

sorry just cant fix the table very well, hope u understand

  • 2 Contributors
  • 5 Replies
  • 111 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by francis123

Dani AI

Generated

A quick reality check and a few practical fixes tied to the thread so far.

— the intended operation (add 1 to every trackdate) is normally done entirely in SQL and is efficient. Contrary to the comment in the thread, MySQL evaluates expressions per row when executing an UPDATE (see the manual). The symptom you described — one value (2) becoming 3 and then every row becoming 3 — almost always means the SQL you actually executed used a literal or a PHP variable instead of the column expression, or the final query was built incorrectly before being sent to MySQL.

Troubleshooting checklist

  • Print or log the exact query string right before execution to see whether you interpolated a PHP variable into the statement.
  • Run the query directly in phpMyAdmin or the MySQL client to compare results.
  • Verify trackdate is a numeric column (use SHOW COLUMNS or check table schema) so arithmetic behaves as expected.
  • If you must iterate in PHP, use prepared statements and bind parameters to avoid accidental global updates and SQL injection.

A safer PHP pattern (modern extension) is: select the rows you need, prepare one UPDATE with placeholders, then loop and execute with bound values. That keeps each update targeted and avoids constructing raw SQL strings. Use PDO or mysqli (not the deprecated mysql_* functions).

Operational notes

  • Don’t run this increment on every page view if the intent is a daily increment; use a scheduled job (cron) or guard logic so the update runs once per period.
  • Back up the table before bulk updates and, for very large tables, update in batches or during low-traffic windows to reduce locking and replication lag.

References: MySQL UPDATE semantics (official manual) — https://dev.mysql.com/doc/refman/8.0/en/update.html
PHP PDO prepared statements — https://www.php.net/manual/en/pdo.prepared-statements.php

Recommended Answers

All 5 Replies

Member Avatar for somedude3488

Your query would look like this:

UPDATE `table` SET `trackdate` = `trackdate` + 1
Member Avatar for francis123

Your query would look like this:

UPDATE `table` SET `trackdate` = `trackdate` + 1

I have tried something, like that, and all i get is that all the records incremented to same numbers,
Example: where i have 2 becomes 3, when this happen all the records on that column become updated to 3 as well,
As in 3, 3, 3, 3, was given to all the other records in that column.

Member Avatar for somedude3488

Yeah, that makes sense. I didn't think of that because I forgot mysql will not go through each row individually and get the current value and add one to it.

You will need to loop through the records and update them individually. I can't think of a better way.

$query = mysql_query("SELECT `id`,`trackdate` FROM `table`");
while( list( $id,$trackdate ) = mysql_fetch_row( $query ) ) {
  $trackdate = $trackdate + 1; //or $trackdate++;
  mysql_query("UPDATE `table` SET `trackdate` = '{$trackdate}' WHERE `id` = {$id}");
}
Member Avatar for francis123

Yeah, thanks for that you are the only one who have given me a different approach to this this query.
I will try that, and if its works, i will let you know, but then! if you have more better way than this, pls do not esitate to let me know!
Thanks.

Member Avatar for francis123

Yeah, that makes sense. I didn't think of that because I forgot mysql will not go through each row individually and get the current value and add one to it.

You will need to loop through the records and update them individually. I can't think of a better way.

$query = mysql_query("SELECT `id`,`trackdate` FROM `table`");
while( list( $id,$trackdate ) = mysql_fetch_row( $query ) ) {
  $trackdate = $trackdate + 1; //or $trackdate++;
  mysql_query("UPDATE `table` SET `trackdate` = '{$trackdate}' WHERE `id` = {$id}");
}

Yeah, thanks for that you are the only one who have given me a different approach to this this query.
I will try that, and if its works, i will let you know, but then! if you have more better way than this, pls do not esitate to let me know!
Thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.