table joins

[anthony-needs-you]

Does anyone know why this table join doesnt work

 

$result = mysql_query("
SELECT t.departureDate, t.expireDate, t.airport, t.destination, t.resort, t.hotel, t.duration, t.board, t.price, t.description, t.customerRef, t.mystiqueRef, t.stars, c.departureDate, c.expireDate, c.airport, c.destination, c.resort, c.hotel, c.duration, c.board, c.price, c.description, c.customerRef, c.mystiqueRef, c.stars
FROM test AS t LEFT JOIN cyprus AS c
ON t.customerRef = c.customerRef 
WHERE c.customerRef OR t.customerRef LIKE '%$search%'") or die(mysql_error());

 

When i search one table it with this its ok

 

$result = mysql_query("SELECT departureDate, expireDate, airport, destination, resort, hotel, duration, board, price, description, customerRef, mystiqueRef, stars FROM test WHERE customerRef LIKE '%$search%'");

 

but with the join the search shows no results?

[GingerRobot]

Please define "doesn't work". What happens? Do you get an error message?

[anthony-needs-you]

no it just doesnt show any search results. When i search without the join its fine?

[anthony-needs-you]

it doesnt show an error. Does it look correct?

 

Its to be used to display results based on a reference number being searched (customerRef). Although say a do a search for a reference number I know that is in the test table it just displays nothing, same vice verser

[daalouw]

Because you're doing a LEFT JOIN, you should only search on the left table field t.customerRef.  The LEFT JOIN says that you want all rows from left table (for the predicate) and only rows from right table that match left table on customerREF.

 

Try this instead:

 

$result = mysql_query("

SELECT t.departureDate, t.expireDate, t.airport, t.destination, t.resort, t.hotel, t.duration, t.board, t.price, t.description, t.customerRef, t.mystiqueRef, t.stars, c.departureDate, c.expireDate, c.airport, c.destination, c.resort, c.hotel, c.duration, c.board, c.price, c.description, c.customerRef, c.mystiqueRef, c.stars

FROM test AS t

LEFT JOIN cyprus AS c

ON t.customerRef = c.customerRef

WHERE t.customerRef LIKE '%$search%'") or die(mysql_error());