Michael Stassen wrote:
Sebastian wrote:
Michael Stassen wrote:
<snip>
Now, I never said this couldn't be done in SQL. Assuming $cat is
already set, the statement in $sql below should do what you asked for:
$sql = "SELECT id, 'news' AS type, .... FROM news
WHERE ($cat = '' OR $cat = 'news')
UNION
SELECT id, 'faq' AS type, .... FROM faq
WHERE ($cat = '' OR $cat = 'faq')
UNION
SELECT id, 'forum' AS type, .... FROM forum
WHERE ($cat = '' OR $cat = 'forum')"
<snip>
Michael
you just gave me an idea.. maybe this is better:
SET @cat = '$cat';
SELECT id, 'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
UNION
SELECT id, 'faq' AS type,
WHERE [expression...] AND (@cat = 'faq' OR @cat = '')
UNION
[SELECT ........]
works regardless if $cat is set or not.
That was the point. The query I gave, quoted above, works whether
$cat is set or not (though I forgot to quote $cat in the comparisons
-- sorry). No need to modify it to use a mysql user variable, either.
Taking into account the news that your real query is more complicated,
this would look something like:
$sql = "SELECT id, 'news' AS type, ..other_columns.. FROM news
..JOINs to other tables..
WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'news')
UNION
SELECT id, 'faq' AS type, ..other_columns.. FROM faq
..JOINs to other tables..
WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'faq')
UNION
SELECT id, 'forum' AS type, ..other_columns.. FROM forum
..JOINs to other tables..
WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'forum')";
Michael
Ah.. OK. I'll give that a shot instead..
Also, now that i am doing some thinking on optimizing... should the $cat
clause be present first in the where clause? since it decides if it
should get results or not. for example:
WHERE MATCH(...) AGAINST(... IN BOOLEAN MODE) AND ('$cat' = 'news' OR
@cat = '')
or:
WHERE ('$cat' = 'news' OR @cat = '') AND MATCH(...) AGAINST(... IN
BOOLEAN MODE)
does it matter in terms of performance?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
