homepage Welcome to WebmasterWorld Guest from 54.196.168.78
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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! :-)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved