PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
pedroz
Forum Commoner
Posts: 99 Joined: Thu Nov 03, 2005 6:21 am
Post
by pedroz » Sun Mar 05, 2006 3:35 pm
mysql query to know the max id in two tables
working:
Code: Select all
$m1 = $db->sql_fetchrow($db->sql_query("SELECT MAX(id) FROM users"));
$m2 = $db->sql_fetchrow($db->sql_query("SELECT MAX(id) FROM users_temp"));
$max = max($m1[0], $m2[0]);
echo $max
However...
I would like to know if there is a short query to get the $max value
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Sun Mar 05, 2006 3:40 pm
You could try:
Code: Select all
select max(max(a.id), max(b.id)) as max_from_both from table_a as a, table_b as b
(Untested though)
pedroz
Forum Commoner
Posts: 99 Joined: Thu Nov 03, 2005 6:21 am
Post
by pedroz » Sun Mar 05, 2006 3:46 pm
Nop
...
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Sun Mar 05, 2006 4:22 pm
Hmm... considering this works:
Code: Select all
select max(id) from a
union
select max(id) from b
I would have thought sticking it in a subquery would work:
Code: Select all
select max(
(select max(id) from a
union
select max(id) from b))
But I always get NULL. I bet timvw comes in any minute and regurgitates a little part of the MySQL manual in true text-book fashion
pedroz
Forum Commoner
Posts: 99 Joined: Thu Nov 03, 2005 6:21 am
Post
by pedroz » Sun Mar 05, 2006 4:26 pm
I am trying this without sucess
Code: Select all
$m2 = $db->sql_fetchrow($db->sql_query("(SELECT MAX(id) FROM users) UNION (SELECT MAX(id) FROM users_temp)"));
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Sun Mar 05, 2006 4:27 pm
Code: Select all
select
max(id) as maxval
from
a
union
select
max(id) as maxval
from
b
order by
maxval desc
limit 1;
Works.
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Sun Mar 05, 2006 4:28 pm
pedroz wrote: I am trying this without sucess
Code: Select all
$m2 = $db->sql_fetchrow($db->sql_query("(SELECT MAX(id) FROM users) UNION (SELECT MAX(id) FROM users_temp)"));
Don't start a query with syntax like a parentheses. It will fail. Union looks like I posted above
pedroz
Forum Commoner
Posts: 99 Joined: Thu Nov 03, 2005 6:21 am
Post
by pedroz » Sun Mar 05, 2006 4:48 pm
WORKING! THANKS