[SOLVED] Seaching between two tables

[ngfireball]

hi guys/girls, just recently started using php and getting use to it but i am having trouble with one aspect of it.

 

i want to search for a specific result between two tables from a given search.

 

i have a table that holds auctions, this will have a car id and some other information.

the next table is a car table, in this table i have all the car details and yet again the car id, so what i want to do is take all the car id's in auctions and and get the car details from the car table, next i want to filter them with my search criteria, so if i am searching for a corsa, i want to get all the car that are up for auction and are the make corsa to be listed.

 

i have had this working a year ago, but now it seems my code wont work anymore, i dont know whats happened.

 

 

thanks for any help

[ngfireball]

ok, this is my current code that i am using, however it doesnt work correctly.

 

it collects all the car id's for the auction table fine, and stores them in an array.

next i cycle through the array can get the car details to compare to the search text, and if it matches i save the car id to another array.

once this is done it should then load the array and get the car details for the last time to be displayed, however this doesnt work correctly, the filter i used doesnt filter to only show results that i want it to search for.

 

 

 

<link rel="stylesheet" type="text/css" href=".../CSS/ICA.css" />
<?php
//session_start(); //so you can use the session variable
$search = array();
$search2 = array();
$searchtext = $_GET['SearchText'];

?>

<?php
$username="root";
$password="abc123";
$database="ica";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


$query="SELECT * FROM auctions";
$result=mysql_query($query);
$num=mysql_num_rows($result);

$i=0;
while ($i < $num) {

$vehicleno=mysql_result($result,$i,"ICAVehicleRefNo");

$search[] = "$vehicleno";


$i++;
}

$p=0;
$length = count($search);

echo" numbers in auction array: $length<br>";


while($p < $length)
{
$resultarray = $search[$p];


$query2 ="SELECT * FROM cars WHERE `ICAVehicleRefNo` = '$resultarray'";

$result2 = mysql_query($query2);

$num2=mysql_num_rows($result2);

while($i2 < $num2){

$make2=mysql_result($result2,$i2,"Make");
$model2=mysql_result($result2,$i2,"Model");
$reserveprice2=mysql_result($result2,$i2,"ReservePrice");
$vehicleno2=mysql_result($result2,$i2,"ICAVehicleRefNo");

if($make2 = $searchtext){
echo"search text 1 is: $make2   $searchtext<br>";
$search2[] = "$vehicleno2";
}else{
if($model2 = $searchtext){
echo"search text 2 is: $model2     $searchtext<br>";
$search2[] = "$vehicleno2";
}else{
if($reserveprice2 = $searchtext){
echo"search text 3 is: $reserveprice2     $searchtext<br>";
$search2[] = "$vehicleno2";
}else{}}}

$i2++;
}


$p++;
}


$p2=0;
$length2 = count($search2);

echo"total number of entries in array after filter: $length<br>";

while($p2 < $length2)
{
$resultarray2 = $search2[$p2];

echo"result in array $p2 is : $search2[$p2]";

$query3 ="SELECT * FROM cars WHERE 'ICAVehicleRefNo' = '$resultarray2'";

$result3 = mysql_query($query3);


while( $row = mysql_fetch_array( $result3 ) ) { 
    $idF = $row[ 'ICAVehicleRefNo' ];
$makeF = $row[ 'Make' ];
$modelF = $row[ 'Model' ];
$resrvepriceF = $row[ 'ReservePrice' ]; 
$featuresF = $row[ 'Features' ]; 
$bodycolourF = $row[ 'BodyColour' ]; 
$DateRegisteredF = $row[ 'DateRegistered' ]; 
$carimageF = $row[ 'CarImage' ]; 
$EngineF = $row[ 'EngineCC' ]; 
$MileageF = $row[ 'Mileage' ]; 

?>

<div class="products">
<?php if($carimageF == 1){?>
<?php
  $second = $_SERVER['HTTP_HOST']."/";
$first = "http//";
$added = $first.$second."images/";
?>
<a href="images/<?php echo $idF; ?>.jpg" class="growleft"><img src="images/<?php echo $idF; ?>.jpg" width="80" height="80" alt="Click For Full Size Image" target="_blank"></a><?php //displays image based on id
}else{
echo '<img src="images/nopic.jpg" width="30" height="30">';} 
echo "</pt>";
?> 
<?php echo "<h6>$makeF</h6> <pt>";?>
    <?php echo " Model: $modelF <br>Features: $featuresF <br><br>  Reserve Price:"; ?>
    <?php echo "&pound $resrvepriceF</pt><br>"; ?> 
<?php echo "Engine Size: $EngineF <br>"; ?>
    <?php echo "Mileage: $MileageF <br>"; ?>
<?php echo "Car Color: $bodycolourF <br>"; ?>
   <br><br>
</div>

<?php
}

$p2++;
}

?> 

[GingerRobot]

it collects all the car id's for the auction table fine, and stores them in an array.

next i cycle through the array can get the car details to compare to the search text, and if it matches i save the car id to another array.

 

This isn't a particularly good way of doing things - it involves far more queries than are necessary. You can do what you want in a far simpler way using joins. You might like to try this excellent tutorial

[ngfireball]

hi thanks for the reply, i know my way is very inefficient and the hard way to do this but it shows that i coded it myself with is good, but bad when you cant get pass problems.

i am having trouble understanding how Data Joins Unions works, i get that you can used the id's to get data which are they same type, so i can search for all the corsa's in the auction table, but i dont see how you do this, how to i get all of the car data out or specific car data?

 

i also want to be able to search by diffrent criteria, so i want to search by price and make, would this be possible by using data joins unions?

 

[ngfireball]

thanks for the responce GingerRobot, the linked helped me think about my problem in a diffrent way, i still dont really get data joins unions but will study it some more, anyways i came up with a solution that worked for me, it aint the best but it does the job for my assignment.

 

thanks.