HOw do I select mutliple tables in single query??

Discussion in 'MySQL' started by triogrid, Jun 26, 2010.

0
  1. #1
    How do I select multiple tables in single query with search word??

    means 

    $result = $db->sql_query("SELECT * FROM data1, data2, data3 WHERE name= '$name'");
    PHP:
    But above code doesn't work..any Ideas??
     
    triogrid, Jun 26, 2010 IP
  2. Layoutzzz

    Layoutzzz Greenhorn

    Messages:
    78
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    18
    #2
    if you have field "name" in all tables and want to search by this field in all tables 
    
$result = $db->sql_query("SELECT * FROM `data1`, `data2`, `data3` WHERE data1.`name`= '".$name."' OR data2.`name`= '".$name."' OR data3.`name`= '".$name."'");
    PHP:
     
    Layoutzzz, Jun 27, 2010 IP
  3. bvsonline

    bvsonline Peon

    Messages:
    83
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    or else you should use union or join to combine the select queries.
     
    bvsonline, Jun 28, 2010 IP
  4. Michellu

    Michellu Peon

    Messages:
    30
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    This won't do you much good:
    $result = $db->sql_query("SELECT * FROM data1, data2, data3 WHERE name= '$name'");
    PHP:
    When you select data from multiple tables without using specific conditions to pair them the results are the Cartesian product of them all. If you use 3 tables each of them having a 1000 rows you will have 1,000,000,000. Your machine will probably stop responding for a while after running such a query.

    This query:

    $result = $db->sql_query("SELECT * FROM `data1`, `data2`, `data3` WHERE data1.`name`= '".$name."' OR data2.`name`= '".$name."' OR data3.`name`= '".$name."'");
    PHP:
    won't help you much either. It gives the Cartesian product of the rows containing $name info. It will give you a headache to use the info.

    So you need conditions to pair them or use union (depending on the info those tables contain and the way you need it returned). When using union make sure you select the same number of fields from each of the tables and the same name/aliases.

    Give us more info about your tables so we can help you further.
     
    Michellu, Jun 28, 2010 IP