CODEWITHSUNDEEP
phpmysql
Casper Slynge
Casper Slynge

Reputation: 344

Mysql get three sets of rows depending on field

I want three sets of rows in my query, with 100 in each. Each set with a different data value. So like this:

$result = mysql_query("SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='24' ORDER BY count DESC LIMIT 100") or die(mysql_error());
$result .= mysql_query("SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='23' ORDER BY count DESC LIMIT 100") or die(mysql_error());
$result .= mysql_query("SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='22' ORDER BY count DESC LIMIT 100") or die(mysql_error());

How can I get the result in one query?

Thanks.

Upvotes: 1

Views: 79

Answers (5)

MaX
MaX

Reputation: 1805

One way is to use UNION.

$result = mysql_query("
  (SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='24' ORDER BY count DESC LIMIT 100)
  UNION
  (SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='23' ORDER BY count DESC LIMIT 100)
  UNION
  (SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='22' ORDER BY count DESC LIMIT 100)
");

More options here.

Upvotes: 1

Mohammad Masoudian
Mohammad Masoudian

Reputation: 3491

you can use UNION to do this work

(SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='24' ORDER BY count DESC LIMIT 100) 
UNION
(SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='23' ORDER BY count DESC LIMIT 100)
UNION
(SELECT * FROM actions WHERE appid='$appid' AND email!='' AND user_name!='' AND data='22' ORDER BY count DESC LIMIT 100)

Upvotes: 1

Rahul
Rahul

Reputation: 5636

Try Union Like

SELECT * FROM tableA UNION SELECT * FROM tableB UNION SELECT * FROM tableC

If you want to allow duplicate as well then try UNION ALL like

SELECT * FROM tableA UNION ALL SELECT * FROM tableB UNION ALL SELECT * FROM tableC

Upvotes: 2

Bere
Bere

Reputation: 1747

Use union

    $final_query=$query1." union ".$query2." union ".$query3;

    and 
    mysql_query($final_query);

Upvotes: 2

Nicholas Shanks
Nicholas Shanks

Reputation: 10971

use UNION:

SELECT * FROM tableA UNION SELECT * FROM tableB UNION SELECT * FROM tableC

Upvotes: 3

Related Questions