php combining multiple sql queries

Question

I have a section of my page that refreshes every 10 seconds to check if the user has any mails or alerts.

I have 2 queries straight after one another.

$sql_result7 = mysql_query("SELECT date FROM alerts WHERE to_id='$id' AND date > '$lastcheck'", $db);
$alerts = mysql_num_rows($sql_result7);
$rs7 = mysql_fetch_array($sql_result7); 
$last_alert = $rs7[date];

$sql_result7 = mysql_query("SELECT date FROM mobmail WHERE to_id='$id' AND read = '0'", $db);
$mails = mysql_num_rows($sql_result7);
$rs7 = mysql_fetch_array($sql_result7);
$last_mail_alert = $rs7[date];

Is it possible to save trips to the database by combining this into one query? Is there any need to?

These SQL statements may actually fail since you're not providing an `ORDER BY` clause, by default perhaps you're getting them back in ascending order (newest date at the end) but there's no guarantees. — Rudu, Jan 04 '12 at 20:23

score 1 · Answer 1 · answered Jan 04 '12 at 20:08

1

Why just use a union?

SELECT date
FROM alerts where...
UNION
SELECT date
FROM mobmail where...

If you do want duplicates... utilize UNION ALL

answered Jan 04 '12 at 20:08

Kris Krause

7,304
2
23
26

score 1 · Answer 2 · answered Jan 04 '12 at 20:09

You can do it using a UNION .. basically put the second query after the first with UNION in between.

It doesn't seem like you can JOIN on anything here, except for to_id, but you don't really get any benefit out of that. This would almost be a cross join, which would be unnecessarily inefficient.

score 1 · Answer 3 · answered Jan 04 '12 at 20:15

Why not just use a union, and the MAX aggregate command...

SELECT max(date) lastdate,'alert' dtype FROM alerts WHERE .. GROUP BY to_id
UNION
SELECT max(date) lastdate,'mail' dtype FROM mobmail WHERE ... GROUP BY to_id

You'd now only need a single SQL query and decreased PHP side processing:

$sql_result7=mysql_query(/* as above with your params*/);
$res=mysql_fetch_array($sql_result7);
$last_alert=$last_mail=null;
foreach ($res as $row) {
   if ($row['dtype']=="alert") {
      $last_alert=$row['lastdate'];
   } elseif ($row['dtype']=="mail") {
      $last_mail=$row['lastdate'];
   }
}

... Or something to that effect (I can't easily test ;)).

php combining multiple sql queries

3 Answers3