Problem with multi tables in mysql

[shalini]

Hello everybody,

Please help me, i new to php and php-forum also.

I am working on courier script and i have three tables named as consignment, b_consignment and h_consignment.

All three tables have same column name with different data.

Now i have to search a particular consignment no. from all the tables, and want to print all information of this particular consignment no. whether it is exist in consignment, b_consignment and h_consignment.

Code for query is as follows.

if(isset($_POST['Track']))
{
$c = $_POST['consignment'];

$query = "SELECT * FROM h_consignment h, b_consignment b, consignment c WHERE (c.consignment_no='".$c."' && c.consignment = 'enable') || (b.consignment_no='".$c."' && b.consignment = 'enable') || (h.consignment_no='".$c."' && h.consignment = 'enable')";

$result = mysql_query($query) or die(mysql_error());
}

Please help me for this problem, i will very greatful

Thanks
Shalini

[Antrikssh]

Hello,

If you don't mind then can you please tell me about the primary key of all this three tables. And which table is the master table.
If you are using same attributes name in all this three table along with different data then it has no use in my point of view, only one table is sufficient for that. It is very bad practice to create more than one table with same attribute in one database.

This will increase the problem of redundancy in the future. Suppose that according to your query if two table contain same type of information. I suggest you please try to remove the redundancy as much as possible...

Regards,

Antrikssh.....

[Nullsig]

So your query is joining 3 tables together on without declaring how they are being joined together. So you are probably getting a CROSS JOIN effect on your results.

From the looks of your WHERE clause you are not assuming that any of the tables will always contain the data. As a result using something like OUTER or INNER joins will not work here.

I suggest you use UNIONS like this:

Code: Select all

SELECT *
FROM consignment c
WHERE c.consignment_no='" . mysql_real_escape_string($c) . "' && c.consignment = 'enable'
UNION
SELECT *
FROM h_consignment h
WHERE h.consignment_no='" . mysql_real_escape_string($c) . "' && h.consignment = 'enable'
UNION
SELECT *
FROM b_consignment b
WHERE b.consignment_no='" . mysql_real_escape_string($c) . "' && b.consignment = 'enable'