[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum;
+------+----------+-------+
| id | selected | type |
+------+----------+-------+
| 1 | news | news |
| 2 | faq | faq |
| 3 | forum | forum |
+------+----------+-------+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*************************
mysql> set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from (
-> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum
-> ) Temp
-> where [EMAIL PROTECTED];
+------+----------+------+
| id | selected | type |
+------+----------+------+
| 1 | news | news |
+------+----------+------+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*************************
set @cat := 'news';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+------+----------+
| id | selected |
+------+----------+
| 1 | news |
+------+----------+
1 row in set (0.00 sec)
deallocate prepare stmt;
********* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql> execute stmt;
+------+----------+
| id | selected |
+------+----------+
| 2 | faq |
+------+----------+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
--------------------
A+
Mathias
The first method is horribly inefficient (and requires mysql 4.1+). It
reads all 3 tables, unions the resulting rows, checks for (and removes)
duplicate rows, then finally throws away roughly 2/3 of the results (the
rows from the 2 unwanted tables. Compare that to the simple query which
only addresses the 1 desired table. Mathias is aware of this, which is
why he gives the second method. It creates the simple, one-table query
using the value of $cat to choose which table.
The big problem here is that neither of these methods actually do what
you asked for. That is, neither works if $cat is not set. With both
methods, you will get no rows unless $cat is set. In fact, the second
method will give a syntax eror, as there will be no table name in the
FROM clause.
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')"
MySQL's optimizer should be smart enough to notice that if $cat is set,
2 of the 3 unioned queries will have impossible WHERE clauses, so those
queries will be skipped, making this relatively efficient.
Even though this works, though, I think it's a bit of a hack. It's
clever SQL which allows two separate queries to masquerade as one, based
on the value of $cat. I really think the simple, separate query
solution I sent in my previous message
if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
{
$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
}
else
{
$sql = " SELECT id, 'news' AS type, .... FROM news
UNION
SELECT id, 'faq' AS type, .... FROM faq
UNION
SELECT id, 'forum' AS type, .... FROM forum";
}
is better, as it is clearer what we are doing (one query if we know the
table, three queries unioned if we don't), so it should be easier to
understand and maintain down the road (by you or the next guy).
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
