Michael Stassen wrote:
[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
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
