开发者

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜