Select from more than one table

Pioneer5000

Was wondering is there anyway to select infomation from more than one table within the same query?

Like at the moment im going:


$query = "SELECT * FROM table1 ORDER BY date_auto";

but i was wondering since i have two tables that are set up the same way but just hold different infomation is there a way i can at the same time query them both? cause at the moment im starting a php tag then doing one query ending the php tag then stating another php tag for the next query. So pretty much i want to SELECT * FROM table1 & table2 ORDER BY date_auto";

Ashley_Sheridan

Yes, you need to use a JOIN on your tables. Depending on what you are trying to do, you will need to use a specific type of join, as there are more than one. What are you actually trying to achieve with your query?

Pioneer5000

Well i have two tables:

Table 1
Table 2

Both contain the same catergoy's within them. So the both have autoID, type, comments, date_auto but one is set up to hold infomation about apples and one is set up to hold infomation for oranges.

What i want to is display the infomation from these both tables in the same html page and for all the infomation to be ordered by date. At the moment it is kind of doing that but is first displaying all the apple infomation in date order then displaying all the orange infomation in order but i want it to be just display in date order only and for it not to matter weither is apple or ornage.

So eg.

Apple, Green, I like green apples, date is 1 Mar 2010
Oranges, unkown, i like oranges too, date is 3 Mar 2010
Apple, Red, I like Red apples, date is 10 Mar 2010

etc.

rulian

//// never mind actually just reread your post

Pioneer5000

Anyone know?

NogDog

Sort of sounds like you should have a single "fruit" table with a "type" column where you can specify whether it is an "apple" or an "orange". But if that's not possible for some unknown (to us) reason, then you might be able to do UNION in your SELECT.

(SELECT type, comments, date_auto FROM apples)
UNION
(SELECT type, comments, date_auto FROM oranges)
ORDER BY date_auto

JVassie

NogDog;10885766 wrote:
Sort of sounds like you should have a single "fruit" table with a "type" column where you can specify whether it is an "apple" or an "orange". But if that's not possible for some unknown (to us) reason, then you might be able to do UNION in your SELECT.
(SELECT type, comments, date_auto FROM apples)
UNION
(SELECT type, comments, date_auto FROM oranges)
ORDER BY date_auto

Wouldn't he need to use UNION ALL so that it selects all data, rather than getting rid of any duplicates across any of the fields?

NogDog

JVassie;10885787 wrote:
Wouldn't he need to use UNION ALL so that it selects all data, rather than getting rid of any duplicates across any of the fields?

Possibly. Since I don't know what the OP's real functional requirement is, I can't be sure if (a) duplicates should be kept or deleted, or (b) if it's even possible for there to be any duplicates; but your point is well taken and should be considered by the OP.

Pioneer5000

No there should be no duplicates as when you enter data it is date stamped and is then ordered by that so even if infomation is the same its not really a duplicate, if that makes sense.

Also what does OP stand for? Operator?

Thanks for the great help guys that worked great. But this has now brought to my attention another problem as the information im requesting from the table's are coming in date order now no matter which table its from which is what i want.... but i am displaying them in a html table and each entry that is taken from the tables are displayed in a html table row and i need each row to be color coded. So is there a way to do this? I probally need to run an if statement where it will be like:
eg.
if infomation is from apples then bgcolor = red

else bgcolor for table = orange

so how do i do this as obviously i cant just say if infomation is from apples whats the actual thing i can type to differentiate between what infomation is taken from what table?

JVassie

OP stands for Original Poster

Ok, so i would use the query that NogDog suggested, and then perhaps something like this?

<?php
//connection details
$q = "(SELECT type, comments, date_auto FROM apples) UNION (SELECT type, comments, date_auto FROM oranges) ORDER BY date_auto";
$result = mysql_query($q);
while($row = mysql_fetch_assoc($result)){
        if($row['type'] == 'Orange'){
                 ?><tr bgcolor="orange">
                       <td align="center">
                           <?php echo $row['type']; ?>
                       </td>
                       <td align="center">
                           <?php echo $row['comments']; ?>
                       </td>
                       <td align="center">
                          <?php echo $row['date_auto']; ?>
                       </td>
                   </tr>
                 <?php
             }
            else{
                    ?><tr bgcolor="red">
                       <td align="center">
                           <?php echo $row['type']; ?>
                       </td>
                       <td align="center">
                           <?php echo $row['comments']; ?>
                       </td>
                       <td align="center">
                          <?php echo $row['date_auto']; ?>
                       </td>
                   </tr>
                 <?php
    }

Try that and see how it works. 🙂

Pioneer5000

Thanks ill give that a try.

But what is mysql_fetch_asso doing?

And also is it possible to union more than two tables?

JVassie

Hmm, not sure whether you can UNION more than 2 tables, w3schools doesnt say either way:

W3schools Also see here: MySQL Manual

About the mysql_fetch_assoc function, see here:

PHP Manual

"The mysql_fetch_assoc() function returns a row from a recordset as an associative array.

This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows."