I'm creating a WP plugin that will store settings in a custom table and retrieve them based on page ID or category ID, and if it's within a time frame. The date selection bit is working fine; the problem persists when the date selection part of the statement is removed.
The problem is that each select...or statement works on it's own but when I connect them using brackets and an OR selector it only pulls category. This is identical code I'm using, with the date stuff (that works) removed for clarity:
$sql = "SELECT * FROM {$tablename}
WHERE (
( start_id = {$postid} OR ( start_id < {$postid} AND stop_id >= {$postid} ) AND scope = 'post' )
OR ( start_id = {$categoryid} OR ( start_id < {$categoryid} AND stop_id >= {$categoryid} ) AND scope = 'category' )
)
AND ( ...date selector... )";
When I use this code it only pulls the category entry, even if there is a post entry that matches the criteria (that it should grab first). If I remove the category selector, it pulls the post entry just fine. There is no interference among start/stop_IDs from different rows; already checked that.
The basic table structure is this...note that some fields are null because the user will be able to specify either a range or a single page or category ID.
ID start_id stop_id scope storedsettings
1 40 45 post ...
2 50 NULL category ...
3 67 70 category ...
4 71 NULL post ...
Thank you very much for any insight into this problem!
EDIT to address some comments: (1) @JESUS: The insertion is very specific and won't allow duplicates based on ID, so it's always pulling one row; (2) @IVANIVAN: What you mean is operator precedence? OR is factored before AND, so I group the AND and then use that as the other side of the OR. Or am I misunderstanding your answer? Thx!