Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
nhan
Forum Commoner
Posts: 95 Joined: Sun Feb 27, 2005 8:26 pm
Contact:
Post
by nhan » Sat May 07, 2005 4:12 am
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 = "e;SELECT MAX(timeId) from timein where userName = '$user'"e;;
$result = mysql_query($query);
$idmax = $result;
print $idmax;
$query = "e;UPDATE timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' and timeId = '$idmax'"e; or die("e;Couldn't query the user-database."e;);
$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.
anjanesh
DevNet Resident
Posts: 1679 Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India
Post
by anjanesh » Sat May 07, 2005 11:02 am
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.");
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Sat May 07, 2005 11:56 am
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.
nhan
Forum Commoner
Posts: 95 Joined: Sun Feb 27, 2005 8:26 pm
Contact:
Post
by nhan » Sat May 07, 2005 11:22 pm
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...
anjanesh
DevNet Resident
Posts: 1679 Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India
Post
by anjanesh » Sun May 08, 2005 12:19 am
$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.");
nhan
Forum Commoner
Posts: 95 Joined: Sun Feb 27, 2005 8:26 pm
Contact:
Post
by nhan » Sun May 08, 2005 1:01 am
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 = "e;UPDATE timein SET timeout='$Today',time2='$time' WHERE userName = '$name' and timeout = '' ORDER BY timeId DESC LIMIT 1"e; or die("e;Couldn't query the user-database."e;);
the order by code works with SELECT but in update it doesnt..
anjanesh
DevNet Resident
Posts: 1679 Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India
Post
by anjanesh » Sun May 08, 2005 1:16 am
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/>';
nhan
Forum Commoner
Posts: 95 Joined: Sun Feb 27, 2005 8:26 pm
Contact:
Post
by nhan » Sun May 08, 2005 1:33 am
output was :
Array ( [MaxTimeId] => )
anjanesh
DevNet Resident
Posts: 1679 Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India
Post
by anjanesh » Sun May 08, 2005 2:27 am
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
nhan
Forum Commoner
Posts: 95 Joined: Sun Feb 27, 2005 8:26 pm
Contact:
Post
by nhan » Sun May 08, 2005 2:38 am
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!