Page 1 of 1

solved - update query

Posted: Sat May 07, 2005 4:12 am
by nhan
i cant seem to get this right, i just have to edit the max time id so that the entries would not duplicate... heres my code:

Code: Select all

$query = &quote;SELECT MAX(timeId) from timein where userName = '$user'&quote;;
$result = mysql_query($query);
$idmax = $result;
print $idmax;
$query = &quote;UPDATE 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);
i know the code is wrong, hope anyone can help, thanks!

Posted: Sat May 07, 2005 11:02 am
by anjanesh

Code: Select all

$query = "SELECT MAX(timeId) AS MaxTimeId FROM timein WHERE userName = '$user'";
$result = mysql_query($query) or die("Couldn't query the user-database.");
$row = mysql_fetch_assoc($result) or die("Couldn't fetch row.");
$idmax = $row['MaxTimeId'];
print $idmax;
$query = "
UPDATE timein 
SET timeout='$Today',time2='$time' 
WHERE userName = '$name' AND timeout = '' AND timeId = '$idmax'
" ;
$result = mysql_query($query) or die("Couldn't query the user-database.");

Posted: Sat May 07, 2005 11:56 am
by John Cartwright
change all instances of

Code: Select all

$result = mysql_query($query) or die("Couldn't query the user-database.");
to

Code: Select all

$result = mysql_query($query) or die(mysql_error());
This will tell us exactly what is going wrong when your query fails.

Posted: Sat May 07, 2005 11:22 pm
by nhan
hi anjanesh thanks for the reply... the result of idmax displays #1, i think it only displays the number of rows affected not the exact value of that row...

:(

Posted: Sun May 08, 2005 12:19 am
by anjanesh
$row['MaxTimeId'] should contain the value - the MAX value

Code: Select all

$query = "SELECT MAX(timeId) AS MaxTimeId FROM timein WHERE userName = '$user'";
$result = mysql_query($query) or die("Couldn't query the user-database.");
$row = mysql_fetch_assoc($result) or die("Couldn't fetch row.");
$idmax = $row['MaxTimeId'];
print_r($row);echo '<br/>';
print $idmax ;echo '<br/>';
$query = "
UPDATE timein 
SET timeout='$Today',time2='$time' 
WHERE userName = '$name' AND timeout = '' AND timeId = '$idmax'
" ;
$result = mysql_query($query) or die("Couldn't query the user-database.");

Posted: Sun May 08, 2005 1:01 am
by nhan
this displays:

Array ( [MAX(timeId)] => )

1

:(

I am trying this one but this doesnt work, maybe theres an error in the code...

Code: Select all

$query = &quote;UPDATE timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' ORDER BY timeId DESC LIMIT 1&quote;  or die(&quote;Couldn't query the user-database.&quote;);
the order by code works with SELECT but in update it doesnt..

Posted: Sun May 08, 2005 1:16 am
by anjanesh
Strange. Array should not contain MAX(timeId). It should have MaxTimeId.
Do have mysql_fetch_assoc and not mysql_fetch_row ?

Whats the output of this alone ?

Code: Select all

$query = "SELECT MAX(timeId) AS MaxTimeId FROM timein WHERE userName = '$user'";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result) or die(mysql_error());
print_r($row);
echo '<br/>';

Posted: Sun May 08, 2005 1:33 am
by nhan
output was :

Array ( [MaxTimeId] => )

Posted: Sun May 08, 2005 2:27 am
by anjanesh
So your SQL statement has given 0 results - the php code has no problem.
Query SELECT MAX(timeId) AS MaxTimeId FROM timein WHERE userName = '$user' in phpMyAdmin or mysql console - replace $user with the one you want.
Whats the result ?
Get phpmyadmin

solved

Posted: Sun May 08, 2005 2:38 am
by nhan
the code is correct when i tried it on mysql.. it displays :
+-----------+
| MaxTimeId |
+-----------+
| 23 |
+-----------+
1 row in set (0.00 sec)

bu i dont know why the update query does not work.... the MaxTimeId value is not passed to the update query...

ok, i think i have to upgrade first to version 4.XX... some of my codes have conflicts due to version... thanks so much for all your help!