| Trying for multiple select
|
Nicox
msg:4443773 | 5:13 pm on Apr 21, 2012 (gmt 0) |
Hi :-) Im trying to make one multiple select to get 3 names of 3 different tables... so here is the code:
$q = mysql_query("(SELECT name as c FROM cats WHERE id = '$data[cat]') UNION ALL (SELECT name as y FROM country WHERE id = '$data[country]') UNION ALL (SELECT name as p FROM player WHERE id = '$data[player]');");
$mixed = mysql_fetch_array($q);
and... its work only for the first select: $mixed['c'];
The other... are missing :/
Where is the problem? :(
|
LifeinAsia
msg:4443830 | 10:39 pm on Apr 21, 2012 (gmt 0) |
To do a UNION, the field names need to be the same across all SELECTs. So it should be:
SELECT name as c
FROM cats
WHERE id = '$data[cat]'
UNION ALL
SELECT name as c
FROM country
WHERE id = '$data[country]'
UNION ALL
SELECT name as c
FROM player
WHERE id = '$data[player]'
However, it sounds like what you're really trying to do is a JOIN of 3 different tables. That will only work if there is some common field (key) between the tables you're trying to JOIN.
If there is no common field between the tables, then you need 3 distinct SELECTs:
SELECT name as c
FROM cats
WHERE id = '$data[cat]';
SELECT name as y
FROM country
WHERE id = '$data[country]';
SELECT name as p
FROM player
WHERE id = '$data[player]';
How you would parse the results depends on the language you're using on the application side (PHP, .NET, etc.). To me, it would probably just be easier to make 3 calls to the database:
$q1 = mysql_query("(SELECT name as c FROM cats WHERE id = '$data[cat]');");
$q2 = mysql_query("(SELECT name as y FROM country WHERE id = '$data[country]");
$q3 = mysql_query("(SELECT name as p FROM player WHERE id = '$data[player]')");
|
brotherhood of LAN
msg:4443831 | 10:54 pm on Apr 21, 2012 (gmt 0) |
Your code is fine, but it only fetches the 1st row where in fact your query returns 3 rows.
You can however fit the result all into one row with something like
SELECT cats.name AS col1,c.name AS col2,p.name aS col3
FROM cats
INNER JOIN country AS c ON c.id = '$data[country]'
INNER JOIN player AS p ON p.id = '$data[player]'
WHERE cats.id = '$data[cat]' |
|
I havent tested it but Im sure you get the idea.
This kind of assumes that your ID columns are unique or you could get unpredictable results, but the query returns the same data as your initial code.
|
Nicox
msg:4444051 | 12:52 am on Apr 23, 2012 (gmt 0) |
LifeinAsia, my code was that you proposed with $q1 $q2 $q3 but.... i try to make the website lighter, because some hostings make problem for a lot of mysql queries.
Thanks brotherhood of LAN, your code work perfectly! :-)
|
|
|
