Optimize 2 mysql queries into one
Using php and mysql 5.x. I currently load a banner image in a certain section of my site like so:
SELECT * FROM banners WHERE section = 1 AND pageid = 2
But if no results found I run a second query:
SELECT * FROM banners WHERE section = 1 AND pageid = 0
Basically what Im doing is trying to find banner images assigned to that section for that page. If no results found then I look for any default banner images in the second query. Is there a better way where I can do this in one query?
EDIT
To clarify a little bit more. I want to check if there is any banners assigned to the page, if not then see if there is any banners assigned to 0 (Default). I dont want a mix of both either it shows all banners assigned to that page or show all banners assigned to pageid 0 and there could be a possibility of multiple rows returned not just one.
ADDITIONAL EDIT
To better explain what this is for. In an admin tool I allow someone to assign a banner image to section on the website. In the admin tool they can select the secti开发者_如何学Pythonon and the page they want the banner image to show. They can also set the default banner image(s) for that section. So if there were no banner images assigned to a section by default it will load the banner image(s) assigned to 0 for that section throughout the website. So instead of assigning a default banner image to 50 different pages they can just do it one time and it will load the default banner image or images for that section. Just trying to find a way to do this in a more optimal way, instead of 2 queries could it be done in one?
The OR
operator will make the conditional (pageid = 2 OR pageid = 0)
return true
immediately if just the first value is true
and since there is a LIMIT 1
I think it should always fetch one with pageid = 2
first since the order of pageid
is DESC
and 2 is bigger than 0.
SELECT * FROM banners WHERE section = 1 AND (pageid = 2 OR pageid = 0) ORDER BY pageid DESC LIMIT 1
EDIT: I'm not positive if the ORDER BY
is necessary, I'd love to see some comments on that
Maybe it's not the most elegant way, but you can try this:
SELECT *
FROM banners
WHERE section = 1
AND pageid = IF(
(select count(*) from banners where section = 1 and pageid = 2) = 0,
0, 2
);
精彩评论