merge two mysql queries

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

Post Reply
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

merge two mysql queries

Post by JKM »

Code: Select all

$query1	= mysql_query("SELECT * FROM `db`.`table` WHERE active=0");
$query2 = mysql_query("SELECT * FROM `db`.`table` WHERE active=1");
$query	= // merge $query1 and $query2;
$rows	= mysql_num_rows($query);
while($fetch = mysql_fetch_object($query)) {
	//
}
Is this possible?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: merge two mysql queries

Post by John Cartwright »

If you active column is only 0/1, then you could simply omit the WHERE clause alltogether. Otherwise, just combine the WHERE clause conditions.

Code: Select all

$query1 = mysql_query("SELECT * FROM `db`.`table` WHERE active=0 OR active =1");
If you truly wanted to "merge" the two queries, alas unions.

Code: Select all

$query1 = mysql_query("
   (
      SELECT * FROM `db`.`table` WHERE active=0
   ) UNION (
      SELECT * FROM `db`.`table` WHERE active=1
   )
");
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: merge two mysql queries

Post by Eran »

Side note - those queries are exactly the same (OR is in fact a UNION operation)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: merge two mysql queries

Post by John Cartwright »

Eran wrote:Side note - those queries are exactly the same (OR is in fact a UNION operation)
Are they executed the exact same?

Is the query basically rewritten to express the query using WHERE logic? Or are they executed seperately -- then -- combined?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: merge two mysql queries

Post by Eran »

The query is executed as a UNION as far as I understand it. The query plan should be identical. The only difference is that a UNION clause gives you additional keywords to use (UNION ALL, UNION DISTINCT) that create small variations of the basic UNION (OR)
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: merge two mysql queries

Post by JKM »

The thing is that I want to have the first row where deleted=0 and the following rows ordered by deleted DESC.
Post Reply