Page 1 of 1

updating the last entry

Posted: Fri Apr 29, 2005 12:32 pm
by nhan
would this be correct?

Code: Select all

$query = &quote;UPDATE timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' and timeId >= (LAST_INSERT_ID())&quote; or die(&quote;Couldn't query the user-database.&quote;);
i have to update the last entry record of the table, it seems that the "timeId >= (LAST_INSERT_ID())" is wrong can anyone help me on this one....

thanks! :D

Posted: Fri Apr 29, 2005 5:02 pm
by ol4pr0
how about just

Code: Select all

$query = "UPDATE timein SET timeout='$Today',time2='$time' WHERE userName='$name' AND timeout='' AND timeId=LAST_INSERT_ID()" or die(mysql_error());

Posted: Sat May 07, 2005 2:44 am
by nhan
thanks for your reply this doesnt work...

i am trying this one :

Code: Select all

UPDATE timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' and timeId = 'MAX(ID)';
this also doesnt work...

i also tried this one :

Code: Select all

$query1 = &quote;SELECT MAX(ID) from timein where userName = '$user'&quote;;
$idmax = mysql_query($query1);
print $idmax;
$query = &quote;UPDATE MAX(timeId) timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' and timeId='$idmax'&quote;  or die(&quote;Couldn't query the user-database.&quote;);
$result = mysql_query($query);
this also doesnt work, any work around on this one... thanks so much!

Posted: Sat May 07, 2005 7:33 am
by timvw
Just read: http://dev.mysql.com/doc/mysql/en/update.html

Query would look like (untested)

Code: Select all

UPDATE 
  timein
SET 
  timeout='$today',
  time2='$time'
WHERE 
  userName = '$name' and 
  timeout = ''
ORDER BY
  timeId DESC
LIMIT 1

Posted: Sat May 07, 2005 11:43 pm
by nhan
hi tim,

the article does not explain the update with order by, i tried the code but it gives me an error :

ERROR 1064: You have an error in your SQL syntax near 'ORDER BY timeId DESC LIMIT 1' at line 1

thanks!

Posted: Mon May 09, 2005 6:53 am
by nhan
hi all,

is there any other way i can revise this code to get the maximum primary key (timeId)in the table...

Code: Select all

$query = &quote;UPDATE timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' and timeId=SELECT MAX(timeId)&quote;;
or this code

Code: Select all

UPDATE   timein SET   timeout='$today',  time2='$time'WHERE   userName = '$name' and   timeout = ''ORDER BY  timeId DESC LIMIT 1
thanks!