searching multiple tables in database

[anthony-needs-you]

Hi does anyone know how to search multiple tables in a database

 

below is my code, i've tried FROM test, cyprus but it doesnt seem to search them?

 

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

 

Any help is appreciated

[Maq]

Please read about LEFT JOIN.

[ballhogjoni]

first you are doing what is called a join, and you have to reference each item you want from the database with an alias to the table like this:

 

table test would be test as t and table cyprus would be c or something like that.

 

then you would have to reference the items like this:

t.departureDate, c.expireDate, etc... the t. and the c. are telling mysql what table to find that item in.

[anthony-needs-you]

have you got any example code that i can take a look at, to try and get my head around it?

[Maq]

Look at my link, it gives you an example.

[anthony-needs-you]

ok i've tried this

 

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

 

but when i search for a reference number say 156 which is related to the test table, it shows me 102 which is related to cyprus?

[Maq]

Try this:

 

$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 t LEFT JOIN cyprus c
ON t.customerRef = c.customerRef 
WHERE c.customerRef LIKE '%$search%'");

 

 

What exactly are you searching for with this (example please):

 

'%$search%'

 

If $search = 1 it will match anything with a 1 in it.

[anthony-needs-you]

its pulling this from a search box:

 

$search=$_POST["search"];

 

I tried the above but when i entered a value it came back with no results?

 

 

[Maq]

Add:

 

LIKE '%$search%'") or die(mysql_error());

[Maq]

You may have to do:

 

FROM test AS t LEFT JOIN cyprus AS c

[anthony-needs-you]

would i also have to change these?

 

while($r=mysql_fetch_array($result))
{	 
   $airport=$r["airport"];
   $destination=$r["destination"];
   $resort=$r["resort"];
   $hotel=$r["hotel"];
   $duration=$r["duration"];
   $price=$r["price"];
   $description=$r["description"];
   $customerRef=$r["customerRef"];
   $mystiqueRef=$r["mystiqueRef"];
   $stars=$r["stars"];
   $expireDate=$r["expireDate"];
   $board=$r["board"];

[Maq]

would i also have to change these?

 

while($r=mysql_fetch_array($result))
{	 
   $airport=$r["airport"];
   $destination=$r["destination"];
   $resort=$r["resort"];
   $hotel=$r["hotel"];
   $duration=$r["duration"];
   $price=$r["price"];
   $description=$r["description"];
   $customerRef=$r["customerRef"];
   $mystiqueRef=$r["mystiqueRef"];
   $stars=$r["stars"];
   $expireDate=$r["expireDate"];
   $board=$r["board"];

 

No.

[anthony-needs-you]

its still showing no results, could it be the way its displayed?

 

echo "

<h3 class=\"hol-subhed\">$destination</h3>
<div id=\"hol-wrapper\">
        <div class=\"holiday-hedlistwrap\">
        <ul class=\"hol-hedlist\">
        <li class=\"long\">Destination</li>
	<li class=\"med\">Airport</li>
	<li class=\"long\">Resort</li>
        <li class=\"long\">Hotel</li>
	<li class=\"med\">Board</li>
	<li class=\"med\">Duration</li>
        <li class=\"med\">Depart On</li>
        <li class=\"small\">Price</li>
	</ul>
        </div>
        
        <div class=\"holiday-detailswrap\">
        <ul class=\"hol-details\">
        <li class=\"long\">$destination</li>
	<li class=\"med\">$airport</li>
	<li class=\"long\">$resort</li>
        <li class=\"long\">$hotel</li>
	<li class=\"med\">$board</li>
	<li class=\"med\">$duration</li>
        <li class=\"med\">$departureDate</li>
        <li class=\"small\">£$price</li>
	</ul>
        <div class=\"clearboth\" id=\"foo\"><!--empty--></div>
        </div>
        
        <div class=\"holiday-descwrap\">
        <ul class=\"hol-hedlist\">
        <li class=\"huge\">Description</li>
        <li class=\"med\">Rating</li>
        <li class=\"med\">Ref. No.</li>
	</ul>
        </div>
        
        <div class=\"holiday-detailswrap\">
        <ul class=\"hol-details\">
        <li class=\"huge\">$description</li>
        <li class=\"med\">

$stars</li>
        <li class=\"ref\">$customerRef</li>
	</ul>
        <div class=\"clearboth\" id=\"foo\"><!--empty--></div>
        </div>

<div class=\"holiday-descwrap\">
        <ul class=\"hol-hedlist\">
        <li class=\"huge\">Operator Ref</li>
        <li class=\"med\"></li>
        <li class=\"med\"></li>
	</ul>
        </div>
        
        <div class=\"holiday-detailswrap\">
        <ul class=\"hol-details\">
        <li class=\"huge\">$mystiqueRef</li>
        <li class=\"med\"></li>
        <li class=\"ref\"></li>
	</ul>


        <!--hol-footer-->
        <div class=\"clearboth\" id=\"foo\"><!--empty--></div>
        </div>





";
}

[Maq]

Are you sure these fields are the same in both tables?

 

t.customerRef = c.customerRef 

 

Try taking out the LIKE clause and see if you get any results.

 

Does it display any errors (with or die)?

 

Add this at the top of your script as well:

 

ini_set ("display_errors", "1");
error_reporting(E_ALL);

[anthony-needs-you]

this is the code as is

 

$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 LIKE '%$search%'") or die(mysql_error());

 

if i take out the LIKE clause, it does show a syntax error

 

Both test table and cyprus table both have customerRef, although the inputed values are different