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
– Rudu, Commented Jan 4, 2012 at 20:23

Kris Krause · Accepted Answer · 2012-01-04 20:08:27Z

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 4, 2012 at 20:08

Kris Krause

7,3162 gold badges25 silver badges26 bronze badges

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

Comments

Explosion Pills · Accepted Answer · 2012-01-04 20:09:19Z

1

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.

answered Jan 4, 2012 at 20:09

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Comments

Rudu · Accepted Answer · 2012-01-04 20:15:16Z

1

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 ;)).

answered Jan 4, 2012 at 20:15

Rudu

15.9k4 gold badges51 silver badges66 bronze badges

Collectives™ on Stack Overflow

php combining multiple sql queries

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related