Problem with multi tables in mysql

Links for php scripts

Moderators: macek, egami, gesf

shalini
New php-forum User
New php-forum User
Posts: 1
Joined: Wed Jul 27, 2011 4:09 am
Location: india
Contact:

Problem with multi tables in mysql

Postby shalini » Wed Jul 27, 2011 4:31 am

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
New php-forum User
New php-forum User
Posts: 22
Joined: Wed May 02, 2012 5:27 am

Re: Problem with multi tables in mysql

Postby Antrikssh » Mon May 14, 2012 6:20 am

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

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Problem with multi tables in mysql

Postby Nullsig » Mon May 14, 2012 6:54 am

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'


Return to “PHP Scripts”

Who is online

Users browsing this forum: No registered users and 0 guests

cron