-2

Code

$minus = mysql_query("SELECT * FROM dtr");
while($minusdata = mysql_fetch_array($minus, MYSQL_ASSOC))
{
    $time = mysql_fetch_assoc(mysql_query("SELECT time_to_sec(timediff(time_out,time_in)) as x from dtr where dtr_id = dtr_id"));
    $t = $time['x'];
    mysql_query("UPDATE dtr SET diff = '$t' WHERE dtr_id = dtr_id AND time_out != '00:00:00'");
}

I know it should work. Select the timediff of 2 columns in each row then update it to a diff column in that row. But what is happening is only the timediff of the first row is being updated to all diff columns instead of the timediff in each respective row.

Example: the timediff of the first row is 5 so 5 is the data being updated to all diff columns in all rows.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
Dev
  • 1,592
  • 2
  • 22
  • 45
  • You shouldn't use the mysql_* family function. https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Michas May 08 '15 at 03:02
  • This isn't going to go live so I'll just use mysql. – Dev May 08 '15 at 03:05
  • 3
    Can't you just do `update dtr set diff = time_to_sec(timediff(time_out,time_in))`??? – Niet the Dark Absol May 08 '15 at 03:13
  • Woahh. It worked!. Never thought you can do an operation after the `=`. I thought it have'd to be selected first then updated. Thanks. – Dev May 08 '15 at 03:20

1 Answers1

0

The WHERE statement that you are using in the UPDATE is dtr_id = dtr_id . That will always be true.

So, the $time SELECT statement is selecting the first row where dtr_id is dtr_id, which is the first row that it comes to.( It would be true of every row). Then it is updating every row with that information where dtr_id = dtr_id, which is every row again.

You will likely need to adjust the SELECT and UPDATE within the loop to something using "WHERE dtr_id = $minusdata['dtr_id']". That way you will only select and update the row that you are currently on in the while loop.

bwann
  • 1
  • 1