PHP SQL in 1 query

Question

Excuse me, for simple guestion. I am new in Sql. So, I have a coouple of sql query, I would like to write this sql in 1 Query!

$sql1=select 'max(id) as max1 FROM table where status_id=1 LIMIT 1';
$sql2=select 'max(id) as max2 FROM table where status_id=2 LIMIT 1';
$sql3=select 'max(id) as max3 FROM table where status_id=3 LIMIT 1';
$sql4=select 'max(id) as max4 FROM table where status_id=4 LIMIT 1';

$query1=mysql_query($sql1);
$fetch1=mysql_fetch_array($query1);
$query2=mysql_query($sql2);
$fetch2=mysql_fetch_array($query2);
$query3=mysql_query($sql3);
$fetch3=mysql_fetch_array($query3);
$query4=mysql_query($sql4);
$fetch4=mysql_fetch_array($query4);

echo 'Max ID numbers are:'.$fetch1['max1'].'; '.$fetch2['max2'].'; '.$fetch3['max3'].'; '.$fetch4['max4'].'';

How can I use write these 4 sql in one query and fetch suitable ID? I tried MYSQL UNION, was'nt any result.

sidenote: stop using deprecated mysql_* functions. use MySQLi or PDO instead. — Raptor
– Raptor, Commented Sep 11, 2013 at 7:50

Sirko · Accepted Answer · 2013-09-11 07:50:20Z

5

Use this query

SELECT `status_id`, 
       max(`id`) as `max` 
FROM table 
GROUP BY `status_id`

This will give you all pairs of status_id and the respective max(id) per status_id. You can fetch the result then in a usual (while) loop.

^{Standard disclaimer: mysql_ functions are deprecated. Use PDO or mysqli instead.}

answered Sep 11, 2013 at 7:50

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xdazz · Accepted Answer · 2013-09-11 07:51:06Z

1

Use group by:

select status_id, max(id) as maxValue FROM `table` 
where status_id in (1,2,3,4) group by status_id

answered Sep 11, 2013 at 7:51

xdazz

161k38 gold badges255 silver badges278 bronze badges

1 Comment

Mark Over a year ago

This should be the answer. I wonder where the upvotes from the other came from.

Collectives™ on Stack Overflow

PHP SQL in 1 query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related