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??
Code:
<?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:
Code:
<?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";
}
?>
It's probably simpler to just run more than one select statement.
You're searching the asmnt_parcel and asmnt_legal tables. Search one table, then the next if you don't find anything. Once you have results, select the remaining data from the other tables.
Instead of JOIN, you could also use FROM -
SELECT a.*, b.*, c.* FROM asmnt1 AS a ... WHERE a.id=id
Linear Mode
