Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2010
    Posts
    8

    Combining results from two tables

    i'm developing a website using php and mysql. there i have 2 tables, tblproduct and tblaccessory. I created a search engine. currently only capable of finding items in tblproduct table. I want to take results from both tables, so i can display accessories as well. It is too late to refine my database now. is there a way i can achieve this? i tried using UNION but, it generates undefined variable errors in php, if the results from one table is unavailable. Can i use LEFT JOIN? but, there is no table to take as the LEFT side table.
    I really appreciate an answer for this... Thank you...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you provide some sample data for both tables and what you want the result to be given a particular search please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2010
    Posts
    8

    sample

    thanx for your reply..
    my tblproduct table consists of these fields.
    product_id,product_name,brand,image,thumbnail,unit _price,description,unit_in_stock,sub_category_id
    product_id is an auto generated id. and sub_category_id is a foreign key.
    tblaccessory consists of;
    accessory_id,accessory_name,image,thumbnail,descri ption,brand,price,units_in_stock
    accessory_id is also auto generated.
    image and thumbnails are stings with paths.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is your UNION query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    8
    This is my sql statement;
    $query="SELECT * FROM tblproducts WHERE product_name LIKE '$name'
    UNION
    SELECT * FROM tblaccessory WHERE accessory_name LIKE '$name'";

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.
    1) SELECT * is considered bad practice - you should explicitly name your columns. This is general good advice.
    2) However, for your current query it is more than good practice - it is necessary. A union effectively "merges" to result sets. To merge them they must be what is known as "UNION COMPATIBLE". This means that there must be the same number of columns, and these columns should be of a data type that is compatible with the respective column it is merged with (based on ordinal position, so the first column from each query must be of compatible data type, the second column from each query must be of compatible data type etc.).

    Try this:
    Code:
    $query="SELECT product_id, product_name 
    FROM tblproducts 
    WHERE product_name LIKE '$name'
    UNION
    SELECT accessory_id, accessory_name 
    FROM tblaccessory 
    WHERE accessory_name LIKE '$name'";
    If it works then just add in the other columns you need.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2010
    Posts
    8
    hey, i have missed something. there is a field called supplier_id after accessory_id in tblaccessory table. so does it mean i can't use union?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No, you can use UNION you just must have the same columns in each of the two queries you UNION.
    Based on my post #6 write a new valid statement and test it. If, after that, there are columns in one table that are not in the other and you need them in then we can add them.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2010
    Posts
    8
    hey, I did it!
    It generates no errors..
    but there is a problem...
    the code behave as there isn't any results from tblaccessory at all.. This is my code its, php, i hope you know php.(just a test code)
    also i realize that in my previous post i have wrong typed the use of LIKE key word( must be '%$name%')


    $name='a';

    $sql="SELECT product_id, product_name
    FROM tblproduct WHERE product_name LIKE '%$name%'
    UNION
    SELECT accessory_id, accessory_name
    FROM tblaccessory WHERE accessory_name LIKE '%$name%'";

    $query=mysql_query($sql,$connection)
    or die("SQL Error".mysql_error());


    $nor=mysql_num_rows($query);

    if($nor>0)
    {
    ?>
    <table width="80%" border="1" align="center">

    <tr>
    <th>ID</th>
    <th>Name</th>
    </tr>
    <?php
    while($rec=mysql_fetch_assoc($query))
    {
    echo("<tr>");
    if(is_null($rec['accessory_id']))
    {
    echo("<td>P".$rec['product_id']."</td>");
    echo("<td>".$rec['product_name']."</td>");
    }
    else if(is_null($rec['product_id']))
    {
    echo("<td>A".$rec['accessory_id']."</td>");
    echo("<td>".$rec['accessory_name']."</td>");
    }

    echo("</tr>");
    }
    ?>
    </table>
    <?php }?>

    here i have used "P" and "A" to recognize whether the item is a product or an accessory. Resulting table always displays the IDs with a "P" even though its a record from tblaccessory. How can I correct this?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know php, but I can make out what you are doing.

    It would be easiest to debug all this using your standard database query tool. Only once you truly understand what is going on should you start trying to put this inline in your client code.

    The first query in a UNION statement determines the column name. It is fixed - accessory_name IS NOT a column name of that query, irrespective of which table the row comes from.

    Try this:
    Code:
    $sql="SELECT 'tblproduct' AS source_table, product_id AS item_id, product_name AS item_name
    FROM tblproduct WHERE product_name LIKE '%$name%'
    UNION
    SELECT 'tblaccessory' AS source_table, accessory_id, accessory_name 
    FROM tblaccessory WHERE accessory_name LIKE '%$name%'";
    You can check the source_table column to see where the rows come from.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2010
    Posts
    8
    Thanx...
    Well, results are still same... What I actually need to do is to separately identify whether the output is an accessory_id or a product_id. Is there a way to do that?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Read my query again. That information is in the first column.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Aug 2010
    Posts
    8
    Now I understand your query. Got what you meant by 'source_table', I wasn't really paying my attention to it. But It really works now...!!! Thanksssssssss loadssss!!
    U rock!!!

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Indeed I do
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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