Results 1 to 30 of 30
  1. #1

    Question Loading from two tables + mySQL + PHP

    Hello folks,

    I need to get data from two tables, once called "downloads", the other, "dvds". The fields are exactly the same, it's only the table names that differ. But I want to list all the contents from both. Is it possible to do this with one mysql query, so that they're all listed in order by date?

    Thanks!
    Jon
    Coding my way to oblivion.

  2. #2
    Join Date
    Mar 2006
    Posts
    965
    Absolutely.

    Code:
    mysql_query("SELECT do.*, dv.* FROM (downloads do, dvds dv) ORDER BY date");

  3. #3
    Query posted above won't work because of 2 reasons:

    1) Tables have columns that have the same name. Query above doesn't take that into account, meaning you'll lose results

    2) ORDER BY clause will cause MySQL to report an error, as MySQL doesn't know which table to refer to in search for column named "date".

    It'd be better if you posted your table schema before you use a query that does nothing.
    Dyslexics Have More Fnu

  4. #4
    Join Date
    Mar 2006
    Posts
    965
    Simple mistake I found:

    PHP Code:
    mysql_query("SELECT do.*, dv.* FROM (downloads do, dvds dv) ORDER BY date"); 
    for:

    PHP Code:
    mysql_query("SELECT do.*, dv.* FROM (downloads do, dvds dv) WHERE do.date = dv.date ORDER BY do.date"); 
    This will fix the above.

  5. #5
    But I'm not trying to match both tables, just load them both. So the dates wouldn't be matching.
    Coding my way to oblivion.

  6. #6
    Join Date
    Sep 2004
    Posts
    105
    Don't know if you need to do this with a derived table like this, but try
    Code:
    select * from (select * from downloads UNION select * from dvds) x order by `date`

  7. #7
    Join Date
    Mar 2006
    Posts
    965
    mysql_query("SELECT do.*, dv.* FROM (downloads do, dvds dv)" );
    This command would suffice if you're simply looking to load those two tables together.

  8. #8
    Join Date
    Dec 2005
    Posts
    326
    try:

    PHP Code:
    $query mysql_query("SELECT * FROM dvd, downloads ORDER by date"); 
    I haven't tested this query.... so if there is an error sorry, but it should work?

  9. #9
    Join Date
    Sep 2004
    Posts
    105
    Quote Originally Posted by stuffradio
    try:

    PHP Code:
    $query mysql_query("SELECT * FROM dvd, downloads ORDER by date"); 
    I haven't tested this query.... so if there is an error sorry, but it should work?
    This query will return every combination of rows from dvd and rows from downloads. So if there are 25 rows in dvd and 45 rows in downloads, this query will return 1,125 rows. If you're expecting it to return 70 rows, use the UNION query I suggested above.

  10. #10
    This is a great example of poor database design. If the tables are the same, use only one table and another column to designate the category the entry belongs to.
    The union solution posted by jimpoz is the right one.
    Dyslexics Have More Fnu

  11. #11
    There are only limited situations when both tables have to be called. By splitting them into 2 tables, I can cut their loading time in half.

    I will try the UNION method now..
    Coding my way to oblivion.

  12. #12
    What does X refer to?

    PHP Code:
    SELECT FROM (SELECT FROM sc_downloads UNION SELECT FROM sc_deliverablesx WHERE 
    Coding my way to oblivion.

  13. #13
    My host, mediatemple.net, only has MySQL 3.23.58 installed. So union is out of the question until they upgrade, which, based on their support, could be years
    Coding my way to oblivion.

  14. #14
    Quote Originally Posted by jon31
    There are only limited situations when both tables have to be called. By splitting them into 2 tables, I can cut their loading time in half.
    Sorry, but that's not true. Because of the usage of indexes, databases are rather fast in searching and retrieving data. You should never trade flexibility for negligible gain in performance.
    Dyslexics Have More Fnu

  15. #15
    Quote Originally Posted by maxymizer
    Sorry, but that's not true. Because of the usage of indexes, databases are rather fast in searching and retrieving data. You should never trade flexibility for negligible gain in performance.
    Fair enough. But at the time, the request was to keep the two tables separate, so that's what I did. We aren't in the position to re-design the table structure to merge the two at the moment, that's why I'm looking for a solution to my issue
    Coding my way to oblivion.

  16. #16
    So no one knows how to do this without having to resort to just loading two queries and saving it into an array?
    Coding my way to oblivion.

  17. #17
    Join Date
    Sep 2004
    Posts
    105
    I was thinking of putting in "you need MySQL 4+ to use UNION" but decided against it thinking it wouldn't be an issue. Oh well. That said, though, your options are somewhat limited. You could use SELECT INTO and INSERT INTO to copy the contents of the two tables into a new table; other than that without UNION support this isn't that easy to do.

  18. #18
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Yep, straight from the manual:
    Prior to MySQL 4.0, you can achieve the same effect by using a TEMPORARY table and separate SELECT statements. This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order.

    CREATE TEMPORARY TABLE tmp
    SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1';
    INSERT INTO tmp
    SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';
    SELECT * from tmp;
    DROP TABLE tmp;

    This method of solving the problem is in effect a UNION of two queries.
    You'll need to adapt the select statements for your case.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  19. #19
    Thanks jimpoz, that's what I thought. Maybe I can get my webhost to upgrade mySQL, but not likely.
    Coding my way to oblivion.

  20. #20
    Quote Originally Posted by foobic
    Yep, straight from the manual:


    You'll need to adapt the select statements for your case.
    What kind of strain would this put on the server/database? This query will only be called maybe a few times a month, so this temporary table idea might be the way to go.
    Coding my way to oblivion.

  21. #21
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Depends on the size of the tables I guess. If you're concerned about performance then do as suggested earlier - merge all the data into a single table and index it properly.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  22. #22
    Quote Originally Posted by foobic
    Yep, straight from the manual:


    You'll need to adapt the select statements for your case.
    Here's what I came up with, for testing purposes, which selects from only the first table, but gives me an error:

    PHP Code:
    CREATE TEMPORARY TABLE tmp 
    SELECT field1
    field2field3 
    FROM downloads WHERE field1 
    0;
    INSERT INTO tmp 
    SELECT field1
    field2field3 
    FROM downloads WHERE field2 
    0;
    SELECT FROM tmp;
    DROP TABLE tmp
    I'm getting the error ... "not a valid MySQL result resource"
    Coding my way to oblivion.

  23. #23
    Hmm.. well it works if I run it from within phpMyAdmin. And I tested making a regular table, rather then a temp one, and it inserts all the data. So that works. It must just be the way I'm calling it from PHP.

    PHP Code:
    $result mysql_query("QUERY");
    while (
    $myrow mysql_fetch_array($result)) {
        
    $id $myrow["field"];

    Do I need to use a different method?
    Coding my way to oblivion.

  24. #24
    You didn't pass the query to mysql_query();
    Dyslexics Have More Fnu

  25. #25
    I passed it at the $result = mysql_query("QUERY");
    Coding my way to oblivion.

  26. #26
    You have to paste what you pasted in phpmyadmin. You expect php to read your mind?

    mysql_query("your_query_goes_here"); <- you can't just put "QUERY", it passes string QUERY to mysql and that does nothing.
    Dyslexics Have More Fnu

  27. #27
    Oh, sorry, I thought you saw the post above that lists my query:

    PHP Code:
    CREATE TEMPORARY TABLE tmp 
    SELECT field1
    field2field3 
    FROM downloads WHERE field1 
    0;
    INSERT INTO tmp 
    SELECT field1
    field2field3 
    FROM dvds WHERE field2 
    0;
    SELECT FROM tmp;
    DROP TABLE tmp
    Coding my way to oblivion.

  28. #28
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    You need to use a separate mysql_query() for each of the 4 queries.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  29. #29
    Hmm.. I was wondering that, and that makes sense. I'll give that a try.
    Coding my way to oblivion.

  30. #30
    Ah, now it works wonders. Thanks for all the help fellas!
    Coding my way to oblivion.

Posting Permissions

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