Combining results from two tables

guitarlass · #1 (**permalink**) 08-23-10, 07:18

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...

pootle flump · #2 (**permalink**) 08-23-10, 07:33

Can you provide some sample data for both tables and what you want the result to be given a particular search please?

guitarlass · #3 (**permalink**) 08-23-10, 07:43

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.

pootle flump · #4 (**permalink**) 08-23-10, 08:04

What is your UNION query?

guitarlass · #5 (**permalink**) 08-23-10, 08:13

This is my sql statement;
$query="SELECT * FROM tblproducts WHERE product_name LIKE '$name'
UNION
SELECT * FROM tblaccessory WHERE accessory_name LIKE '$name'";

pootle flump · #6 (**permalink**) 08-23-10, 08:18

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.

guitarlass · #7 (**permalink**) 08-23-10, 09:47

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?

pootle flump · #8 (**permalink**) 08-23-10, 09:53

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.

guitarlass · #9 (**permalink**) 08-23-10, 10:59

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?

pootle flump · #10 (**permalink**) 08-23-10, 11:20

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.

guitarlass · #11 (**permalink**) 08-23-10, 11:30

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?