Combining mySQL query

[petenaylor]

Hi all

 

I have a issue where I need to create a mySQL query to pull four different types of result from a table and show the results.

 

mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'premium' AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");

 

This works great, but I also need it to do this:

mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'standard' AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");

 

and this:

mysql_query("SELECT * FROM `adverts` WHERE type = 'premium' AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");

 

and this:

mysql_query("SELECT * FROM `adverts` WHERE type = 'standard' AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");

 

Is it possible to combine this into one query? I need to paginate the results.

 

Many thanks

 

Pete

 

 

 

[mikosiko]

use for both tables:

SELECT * FROM `trade-adverts` WHERE type IN ('standard', 'premium') AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC

 

SELECT * FROM `adverts` WHERE type IN('standard', 'premium') AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC

 

now if you want to combine both tables in just one query you have to use a JOIN between them using the PK - FK in case both tables are related or use a UNION if they are not.