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

Your query would look like this:

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

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.

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.

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.