Having a problem with JOINS in my code - Help!

[CountryGirl]

I seem to be having a problem trying to join in multiple tables. I was using the INNER JOIN clause since yesterday and it was working great. I had several tables joined in and everything seemed fine. I tried to add in another table and change some of the way the results returned as and then it wouldn't retrieve anything. So, I started rewriting the whole code to the point where it had last worked and now it won't work at all.

 

It's working fine pulling information from ONE table, but when I try to join in any other table, it doesn't work. What am I doing wrong??? Please help!

 

Here is the code I was using with all JOINs and it was working fantastic until right now. Do you see anything in it that shouldn't be in it??

 

<?php
   
   $dbHost = '********'; 
   $dbUser = '******'; 
   $dbPass = '********';
   $dbDatabase = '*****'; 
  

$search = $_POST['search'];

if ($search) // perform search only if a string was entered.
{
   $con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());
   mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
   
   
   $query = "SELECT asmnt_parcel.Account, asmnt_parcel.OwnersName, asmnt_parcel.ParcelID, asmnt_parcel.Township, asmnt_parcel.Range, asmnt_parcel.Section, asmnt_parcel.LotSize, asmnt_parcel.LotSizeType, asmnt_parcel.TaxAreaCode, asmnt_legal.Legal, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_situs.Situs 
			 INNER JOIN asmnt_legal ON asmnt_parcel.Account=asmnt_legal.Account
			 INNER JOIN cmn_name ON asmnt_parcel.OwnersName=cmn_name.OwnersName
			 INNER JOIN asmnt_situs ON asmnt_parcel.Account=asmnt_situs.Account
   			 WHERE asmnt_parcel.Account = '{$search}' OR asmnt_parcel.OwnersName = '{$search}' OR asmnt_parcel.ParcelID = '{$search}' OR asmnt_legal.Legal = '{$search}'
             ORDER BY asmnt_parcel.Account";
   $result = mysql_query($query, $con);

   if ($result)
   {
      echo "Results:<br><br>";
      echo "<table width=90% align=center border=1><tr>
      <td align=center bgcolor=#4A6B3F>Account</td>
      <td align=center bgcolor=#4A6B3F>Owners Name</td>
      <td align=center bgcolor=#4A6B3F>Address</td>
      <td align=center bgcolor=#4A6B3F>City</td>
      <td align=center bgcolor=#4A6B3F>State</td>
      <td align=center bgcolor=#4A6B3F>Zip Code</td>
      <td align=center bgcolor=#4A6B3F>Legal</td>
      <td align=center bgcolor=#4A6B3F>Parcel ID</td>
      <td align=center bgcolor=#4A6B3F>Township</td>
      <td align=center bgcolor=#4A6B3F>Range</td>
      <td align=center bgcolor=#4A6B3F>Section</td>
      <td align=center bgcolor=#4A6B3F>Size</td>
      <td align=center bgcolor=#4A6B3F>Type</td>
      <td align=center bgcolor=#4A6B3F>School District</td>
      <td align=center bgcolor=#4A6B3F>Situs</td>
      </tr>";
   
      while ($r = mysql_fetch_array($result))
      { // Begin while
         $act = $r["Account"]; 
         $nme = $r["OwnersName"];
         $add = $r["Address2"];  
         $city = $r["City"];  
         $ste = $r["State"];  
         $zip = $r["ZipCode"]; 
         $legal = $r["Legal"];   
         $pid = $r["ParcelID"];
         $tship = $r["Township"]; 
         $rng = $r["Range"];
         $sctn = $r["Section"];   
         $size = $r["LotSize"];  
         $type = $r["LotSizeType"]; 
         $sch = $r["TaxAreaCode"];
         $sit = $r["Situs"];
         echo "<tr>
            <td>$act</td>
            <td>$nme</td>
            <td>$add</td>
            <td>$city</td>
            <td>$ste</td>
            <td>$zip</td>
            <td>$legal</td>
            <td>$pid</td>
            <td>$tship</td>
            <td>$rng</td>
            <td>$sctn</td>
            <td>$size</td>
            <td>$type</td>
            <td>$sch</td>
            <td>$sit</td>
            </tr>";
      } // end while
      
      echo "</table>";
   }
   else
   {
      echo "Sorry, please try your search again.";
   }
}
else
{
   echo "Start your search";
}
?>

 

This is what I've got with only pulling from one table right now that is working:

 

<?php
   
   $dbHost = '***********'; 
   $dbUser = '*******'; 
   $dbPass = '*********';
   $dbDatabase = '*********'; 
  

$search = $_POST['search'];

if ($search) // perform search only if a string was entered.
{
   $con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());
   mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
   
   
   $query = "SELECT OwnersName
   			 FROM asmnt_parcel
   			 WHERE asmnt_parcel.OwnersName = '{$search}' 
             ORDER BY asmnt_parcel.OwnersName ASC";
   $result = mysql_query($query, $con);

   if ($result)
   {
      echo "Results:<br><br>";
      echo "<table width=90% align=center border=1><tr>
      <td align=center bgcolor=#4A6B3F>Owners Name</td>
      </tr>";
   
      while ($r = mysql_fetch_array($result))
      { // Begin while
         $name = $r["OwnersName"];  
         echo "<tr>
            <td>$name</td>
            </tr>";
      } // end while
      
      echo "</table>";
   }
   else
   {
      echo "Sorry, please try your search again.";
   }
}
else
{
   echo "Start your search";
}
?>

 

Thanks!

Qadoshyah

[Mchl]

We need some debug information.

 

Change

 $result = mysql_query($query, $con);

to

 $result = mysql_query($query, $con) or die(mysql_error().": $query");

[CountryGirl]

I did that and then ran a search on it and this is the error code it came back with:

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN asmnt_legal ON asmnt_parcel.Account=asmnt_legal.Account INNER JO' at line 2: SELECT asmnt_parcel.Account, asmnt_parcel.OwnersName, asmnt_parcel.ParcelID, asmnt_parcel.Township, asmnt_parcel.Range, asmnt_parcel.Section, asmnt_parcel.LotSize, asmnt_parcel.LotSizeType, asmnt_parcel.TaxAreaCode, asmnt_legal.Legal, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_situs.Situs INNER JOIN asmnt_legal ON asmnt_parcel.Account=asmnt_legal.Account INNER JOIN cmn_name ON asmnt_parcel.OwnersName=cmn_name.OwnersName INNER JOIN asmnt_situs ON asmnt_parcel.Account=asmnt_situs.Account WHERE asmnt_parcel.Account = 'Carlin, Tom' OR asmnt_parcel.OwnersName = 'Carlin, Tom' OR asmnt_parcel.ParcelID = 'Carlin, Tom' OR asmnt_legal.Legal = 'Carlin, Tom' ORDER BY asmnt_parcel.Account"

 

How do I fix this?

 

Qadoshyah

[Mchl]

You need a FROM before name of the first table.

[CountryGirl]

You need a FROM before name of the first table.

 

Thank you! That's what I realized and I was just coming back to post that I added that and it is working now .

 

Qadoshyah

[Mchl]

It's good you figured it out by yourself

 

For proper error handling techniques see here: http://www.phpfreaks.com/blog/or-die-must-die

[CountryGirl]

Thank you! I will take a look at that .

 

Qadoshyah