Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2002
    Location
    Perth, Western Australia
    Posts
    227

    PHP MySQL select multiple tables & same field names

    I spent the last two days trying to select fields from multiple tables. The problem is table1 and table2 has the same field name which contains different data. I want to display the data from each.

    $results = mysql_query("SELECT * FROM lname,fname WHERE 1");
    while ($rows = mysql_fetch_object($results)) {
    echo "$rows->firstname $rows->who<br>";
    echo "$rows->lastname $rows->who";
    }

    table lname - the who field contains "lname"
    table fname - teh who field contains "fname"

    Query returns:

    John fname
    Smith fname

    Should be:

    John fname
    Smith lname

    When using the mysql query above, it returns who field data from fname table, but not lname. I've tried combinations like who.lname vice versa. Read JOINs/inner/left/right but it all gets confusing! Please help!

    table sample;

    <fname table>
    id firstname who
    1 Johh fname

    <lname table>
    id lastname who
    1 Smith lname
    <<Please RTFM for signature setup>>

  2. #2
    You actually did a join right there. It's called theta-join, opposed to ANSI join (ansi join is where you use <join type> JOIN tablename ON...).

    Anyway, if you have 2 tables with same field names, then use aliases when selecting. For example:

    select l.lname as lname1, l.fname as fname1, f.lname as lname2, f.fname fname2 from lname l, fname f where 1.

    Then when you retrieve row data within PHP, use "lname1", "fname1", "lname2", "fname2".

    Hope it helps.

  3. #3
    Join Date
    Jul 2002
    Location
    Perth, Western Australia
    Posts
    227
    Life saver! Thanks
    <<Please RTFM for signature setup>>

  4. #4
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    1,082

  5. #5
    Originally posted by error404
    Or use the much simpler UNION (a basic construct that MySQL has lacked until very recently):

    SELECT * FROM table1 UNION SELECT * FROM table2

    Though, if your tables store the same data, they should be a single table.
    Can you post PHP code for displaying results from such query? Take in consideration that both tables have the same field names.
    Also, what happens if one table differs in number of records - error is reported.

    Issung a JOIN you will get NULL values for non-matching rows (unless specified otherwise in WHERE clause) and won't have to worry about data integrity manually.

    One more thing, which is pretty known in community but it's never mentioned.
    Using SELECT * has 2 disadvantages.
    First is that it's somewhat slower (please, let's not make this high performance programming techniques).
    It's slower by a small margin, and the reason for that is that database must examine which rows it has and what are their names.

    Second disadvantage is - when someone else reads your code - that person doesn't know which fields you selected and which are redundant (and he/she has to look at the table layout or find their way trough the code that iterates trough query results).
    It's allways better to write field names in queries. It will save your time when you return to modify the code (since you won't have to look up row names in DB), and it will save time of the other programmer that will modify your code.

    Of course, this is just my point of view. I won't disagree with anyone that claims otherwise.

  6. #6
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    1,082
    A UNION is not a join. It's a simple construct that does a set union on the results of the expressions on either side of it. It consolidates two relations that have the same schema into a single relation that is returned by the DBMS. In other words, if you have two tables that have an identical schema (or any result sets, in fact), you can join them into a single one (not a relational join, mind). I don't normally use MySQL, but I just tested this and it works exactly as I expected. You'll need MySQL 4 or greater though. See the MySQL documentation at http://dev.mysql.com/doc/mysql/en/union.html . By default the union will discard duplicate rows, however if you do UNION ALL instead, it will include duplicates.

    You shouldn't have to worry about data integrity manually. This should be enforced by the DBMS, though I recognize MySQL sucks at this. The JOIN is just more confusing and probably quite a bit slower, both are valid solutions as long as they return the results you need.

    The only reason SELECT * would be any slower is because it has to return more data over the IPC channel than it would if you projected only the rows you were interested in. In any case, I agree with you, I just didn't feel like typing out all the column names just to do a simple example of a union.

    A short PHP example might look like this:
    PHP Code:
    <?php

    $r 
    mysql_query('SELECT * FROM category1 WHERE price > 10 UNION SELECT * FROM category2 WHERE price > 10');
    if (!
    $r)
       die(
    mysql_error());
    while (
    $row mysql_fetch_object($r))
       echo 
    "Product: ".$row->name." Price: ".$row->price;
    However, as I said in my previous post, if you have two tables with identical schemas, your database design is broken. Such tables should almost always be consolidated unless you have an large amount of data and are doing unpredictable queries that can't be easily indexed.

  7. #7
    Join Date
    Jul 2002
    Location
    Perth, Western Australia
    Posts
    227
    My database contained two tables, suppliers & purchasing.

    table: suppliers
    fields: id, supplier_name, special

    table: purchasing
    fields: id, date, supplier_name, cheque_number, amountinc, payment_type, gst, amountex, notes


    I have purchasing.php that contains a list of supplier purchases. I wanted to highlight rows where the supplier was marked special in suppliers table. So I had to query tables suppliers and purchasing. Then have an if statement if $rows->special = Yes, then echo the bgcolor=<insert color here>. This had to be done in a while ($rows = mysql_fetch_object($results) { loop; }.

    I've tried the aliases option with the above examples. I got it to work but it involved a lot of variable rewriting. If there a way to "wildcard aliases"? Other then issuing aliases for all fields you want aliased. This would be handy if you have a lot of fields that need to be aliased due to duplicates.

    SELECT table1.blah as blah1, table1.bleh as bleh2 etc (same for 10+ fields)
    <<Please RTFM for signature setup>>

  8. #8
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    1,082
    Ah, I assumed your tables were identical from the way you were asking. It would probably be better to have the supplier ID in the purchasing table instead of the supplier name, as foreign keys should always reference the primary key of anothre table. I hope supplier_name is unique...

    As far as I know there isn't a way to do block aliasing like that. Real RDBMSs have VIEWS which would make something like what you're wanting to do much more intuitive and easy, but MySQL doesn't have them yet. Though if you leave them unaliased, only the duplicate fields will need special treatment; the rest should automatically be given their 'simple' name.

  9. #9
    Join Date
    Jul 2002
    Location
    Perth, Western Australia
    Posts
    227
    aaarrg.. I still cannot get this to work properly. Its returning multiple rows where it only meant to return 2 rows only as there are only 2 entries. I've done up an example; hope you guys can guide me;

    table: suppliers
    fields: id, supplier_name, special

    +----+---------------+---------+
    | id | supplier_name | special |
    +----+---------------+---------+
    | 1 | Intel | yes |
    | 2 | AMD | no |
    +----+---------------+---------+

    table: purchases
    fields: id, supplier_name, item1, item2

    +----+---------------+----------------------+----------------------+
    | id | supplier_name | item1 | item2 |
    +----+---------------+----------------------+----------------------+
    | 1 | Intel | Intel Celeron 2.4GHz | Intel Celeron 2.8GHz |
    | 2 | AMD | Sempron 2200+ | Sempron 2800+ |
    +----+---------------+----------------------+----------------------+


    PHP Code:
    $results mysql_query("SELECT *,suppliers.id AS id2,suppliers.supplier_name AS supplier_name2 FROM suppliers,purchases WHERE 1");
    while (
    $rows mysql_fetch_object($results)) {
    if (
    $rows->special == yes) { $bgcolor red; }
    echo 
    '<table width="100%"  border="1" cellspacing="0" cellpadding="0">
      <tr><td bgcolor="'
    .$bgcolor.'">'.$rows->id.''.$rows->supplier_name.''.$rows->item1.''.$rows->item2.'</td></tr></table>';
    $bgcolor "";

    As you can see, Intel only has special field as yes. So it should only return the red background color in its own table. The query above returns;

    1IntelIntel Celeron 2.4GHzIntel Celeron 2.8GHz <red background>
    1IntelIntel Celeron 2.4GHzIntel Celeron 2.8GHz
    2AMDSempron 2200+Sempron 2800+ <red background>
    2AMDSempron 2200+Sempron 2800+

    If I take away * from the query, it returns nothing. Just the table and its borders. But I need to have it listing all rows in purchasing table with the appropiate suppliers with special = yes with red background. I was hoping aliasing the duplicate field names will prevent some data discrepancies. Any help much appreciated!
    <<Please RTFM for signature setup>>

  10. #10
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    1,082
    You're doing a cross-product, not a join.

    For the query, do this instead:

    SELECT * FROM suppliers, purchases WHERE suppliers.supplier_name = purchases.supplier_name

    Or

    SELECT * FROM purchases LEFT JOIN suppliers ON(suppliers.supplier_name = purchases.supplier_name)

    They both do the same thing. One or the other may be more clear to you. Why is your DB design so weird?

  11. #11
    A UNION is not a join
    I am perfectly aware of that. I just asked you these questions because you offered a solution that has nothing to do with initial question (UNION where JOIN is required for example).
    As for data integrity and MySQL - that's for another topic so I won't discuss that now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •