SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Apr 2005
    Location
    Birmingham UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching several tables with one query

    Hello - I'm hoping someone could help me with a query. I think it's a simple one, but I can't seem to find the answer on mysql.com

    Is it possible to re-write the the following three queries into one...
    $result_one = mysql_query("SELECT * FROM anaerobics AS an WHERE (an.reference LIKE '%$searchText%') OR (an.name LIKE '%$searchText%') OR (an.description LIKE '%$searchText%')");

    $result_two= mysql_query("SELECT * FROM cyanoacrylates AS cy WHERE (cy.reference LIKE '%$searchText%') OR (cy.name LIKE '%$searchText%') OR (cy.description LIKE '%$searchText%')");

    $result_three= mysql_query("SELECT * FROM example AS ex WHERE (ex.reference LIKE '%$searchText%') OR (ex.name LIKE '%$searchText%') OR (ex.description LIKE '%$searchText%')");


    Thank you!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, you can, but only if those three tables have exactly the same number and datatypes of columns

    otherwise, you will have to select some actual columns (rather than using the evil "select star")
    Code:
    select reference
         , name
         , description
         , 'cyanoacrylates' as source_table
      from cyanoacrylates 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'
    union all
    select reference
         , name
         , description
         , 'anaerobics' as source_table
      from anaerobics 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'
    union all
    select reference
         , name
         , description
         , 'example' as source_table
      from example 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'
    alternatively,
    Code:
    SELECT reference
         , name
         , description
         , source_table
      FROM (
           select reference
                , name
                , description
                , 'cyanoacrylates' as source_table
             from cyanoacrylates 
           union all
           select reference
                , name
                , description
                , 'anaerobics' as source_table
             from anaerobics 
           union all
           select reference
                , name
                , description
                , 'example' as source_table
             from example 
           ) as combined
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2005
    Location
    Birmingham UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick response r937.

    I'm having a problem when I'm trying to use the mysql_num_rows() PHP function. Here's the error I'm getting:
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/bondloc.co.uk/httpdocs/search_results.php on line 56


    Here's the code:
    Code:
    $result = mysql_query("select reference
         , name
         , description
         , 'cyanoacrylates' as source_table
      from cyanoacrylates 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'
    union all
    select reference
         , name
         , description
         , 'anaerobics' as source_table
      from anaerobics 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'
    union all
    select reference
         , name
         , description
         , 'example' as source_table
      from example 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'");
    
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    /* Do this */
    }
    The query seems to work when I cut the query down to just searching the one table, i.e.
    Code:
    $result = mysql_query("select reference
         , name
         , description
         , 'cyanoacrylates' as source_table
      from cyanoacrylates 
     WHERE reference LIKE '%$searchText%'
        or name LIKE '%$searchText%'
        or description LIKE '%$searchText%'");
    All the tables have exactly the same amount of columns with identical datatypes. Am I missing something simple here?

    Do the column titles that I'm not searching (i.e. the three tables all have 8 columns, but I'm only searching 3 of those columns), have to be the same across all three tables?

    Hope that makes sense. Ta
    -------------------------
    www.daveworth.co.uk

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    whenever you get the "supplied argument is not a valid MySQL result resource" message, just run the query outside of php to see what the real problem is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    in additon, if you're not checking for errors later (which you obviously aren't since you're getting that specific error) then your query commands in need to take the general form of:
    PHP Code:
    $result mysql_query('select...') or die(mysql_error()); 

  6. #6
    SitePoint Member
    Join Date
    Apr 2005
    Location
    Birmingham UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks both - Have done what you suggested (Ran the query in phpMyAdmin and used mysql_error() ) and I'm getting an identical error:

    You have an error in your SQL syntax near 'union all select reference , name , description , 'Anaerobics' as' at line 9

    Is it UNION ALL that's throwing it out?
    -------------------------
    www.daveworth.co.uk

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that error indicates that you aren't even on version 4.0 yet

    please read this thread

    you are supposed to let us know if you aren't even on 4.1, every time you start a new thread, so that people do not waste time giving you a solution that you cannot implement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Apr 2005
    Location
    Birmingham UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies, being a beginner it didn't even occur to me to check what version I was using. It seems I'm not even on version 4.0.

    Sorry for wasting your time folks.
    -------------------------
    www.daveworth.co.uk

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    hey, don't worry about it, you're new and your version is old

    be grateful it isn't the other way 'round, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Apr 2005
    Location
    Birmingham UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it not possible on the version I'm using then r937?

    I've been playing around with JOINS but not getting anywhere.

    Thanks again.
    -------------------------
    www.daveworth.co.uk

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    based on your requirements, you're not really going to be able to do this with joins. you need UNION. get your host to upgrade. not only is the version of mysql you're using outdated, it's ancient


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
  •