Results 1 to 4 of 4

Thread: search in all tables #2

  1. #1
    Join Date
    Oct 2011
    Location
    London
    Posts
    41
    Thanks
    19
    Thanked 1 Time in 1 Post

    Arrow search in all tables #2

    Hi guys if you read my other post http://www.dynamicdrive.com/forums/s...ad.php?t=67166 then you know last time i was asking about fields not tables now this time i want tables LOL

    ie
    PHP Code:
    $result mysql_query("SELECT * FROM table1,table2,table3 order by 'time'"); 
    all tables all field order by "time" field basiclly i want to search everywhere for latest inputs

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,740
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Try this:

    Code:
    $get_list = "(SELECT * FROM table1)
    UNION
    (SELECT * FROM table2)
    UNION 
    (SELECT * FROM table3)
    ORDER BY time DESC";
    $get_list_res = mysql_query($get_list,$conn);
    By placing the ORDER command after the last closing parenthesis you will order all of the tables by the time column. This is assuming that your tables all have the time column, which they should.
    Last edited by james438; 02-01-2012 at 12:24 AM. Reason: fixed an error in the code
    To choose the lesser of two evils is still to choose evil. My personal site

  3. The Following User Says Thank You to james438 For This Useful Post:

    TwitterRooms (02-01-2012)

  4. #3
    Join Date
    Oct 2011
    Location
    London
    Posts
    41
    Thanks
    19
    Thanked 1 Time in 1 Post

    Default

    I have found this only works IF the tables have the same fields how would i go about doinging it with differant fields in each table??

  5. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,740
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Sorry for the late reply. I only just checked back on this thread.

    To answer your question it should be fine. Just make sure that you use the same number of columns for each table. You may want to specify the columns like this:

    Code:
    (SELECT ID, title, review_date, va_english FROM table1)
    UNION
    (SELECT ID, title, review_date, artwork FROM table2)
    UNION 
    (SELECT ID, title, review_date, author title FROM table3)
    ORDER BY review_date DESC LIMIT 900
    In the example above the column names may be different and not exist in all of the tables, but the review_date does exist in all of the tables. If review_date did not exist in all three tables it would still sort it based on the tables that do have review_date, but the other tables may be sorted a little oddly. Try making sure that the number of columns specified for each table is the same. For now try to avoid using * to specify all columns and write out the ones you want making sure that the same number of columns is listed for each table.

    If you are still having trouble try posting the error message you are getting and the query you are using.
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

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