MYSQL Join problem

[paulkenn]

I need some help here, I have a search based upon 5 fields across 2 tables

Client ID (contract.client_id and client.client_id)
Last Name (client.clientl)
Contract Number (contract.contractnum)
Style (contract.style)
Designer Initials (contract.desinit)

If I use this php code

$query = "SELECT * FROM client,contract WHERE client.client_id = contract.client_id";
$result = mysql_query($query) or die("Try again");

it correctly joins the two tables and displays all of the results, however, I cannot figure out how to only display results based on the keyword search. If I search only by Last Name it returns all of the records in the contract table and the same for the other 4 fields.

Where do I go next ?...

here is what I have so far, but I know it is not correct:

$query = "SELECT FROM client,contract WHERE client.client_id = contract.client_id";
/$first_one = 1;
if($client_id != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query client_id LIKE '%$client_id%'";
}
else {
$query = "$query AND client_id LIKE '%$client_id%'";
}
}
if($clientl != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query clientl LIKE '%$clientl%'";
}
else {
$query = "$query AND clientl LIKE '%$clientl%'";
}
}
if($contract_num != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query contract_num LIKE '%$contract_num%'";
}
else {
$query = "$query AND contract_num LIKE '%$contract_num%'";
}
}
if($style != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query style LIKE '%$style%'";
}
else {
$query = "$query AND style LIKE '%$style%'";
}
}
if($desinit != "") {
if($first_one == 1) {
$query = "$query WHERE";
$first_one = 0;
$query = "$query desinit LIKE '%$desinit%'";
}
else {
$query = "$query AND desinit LIKE '%$desinit%'";
}
}
*/
$result = mysql_query($query) or die("Try again");

[Roger_Ramjet]

You need to read up on the JOIN SYNTAX , specifically:

"INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table). "

Rewrite your query with a 'join condition', ie ON or USING clause, and you will get the results you want.

[paulkenn]

Thanks! That was a step in the right direction, here is the new code:

$query = "SELECT client.client_id, client.clientl, contract.contract_num, contract.style, contract.desinit FROM client RIGHT JOIN contract USING (client_id)";

all of the serach fields return the correct results with 1 exception, the client ID search field, it returns query failed.

Any idea what I'm missing in the code above?

[Roger_Ramjet]

Since client_id is used to join the 2 tables it should not be used in the WHERE clause - that's what the manual sez anyway.

If you do need to do so then you should use an ON clause not a USING clause

$query = "SELECT client.client_id, client.clientl, contract.contract_num, contract.style, contract.desinit FROM client RIGHT JOIN contract ON client.client_id=contract.client_id WHERE client.client_id=123";