solved - update query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

solved - update query

Post 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!
Last edited by nhan on Sun May 08, 2005 2:38 am, edited 1 time in total.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.");
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

Post 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...

:(
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.");
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

Post 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..
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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/>';
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

Post by nhan »

output was :

Array ( [MaxTimeId] => )
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

solved

Post 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!
Post Reply