| View previous topic :: View next topic |
| Author |
Message |
CGDesigns
Joined: 19 Jan 2010
Posts: 2
Location: Oklahoma
|
 Posted: Tue Jan 26, 2010 8:05 pm Post subject: Having a problem with JOINS in my code - Help! |
 |
|
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 |
|
| Back to top |
|
 |
rajkumar
Joined: 13 May 2008
Posts: 141
|
| Posted: Wed Jan 27, 2010 10:07 am Post subject: |
|
|
Hi Qadoshyah,
I checked your sql query and code, I think there is a problem with sql query.
Query:
--------
$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";
This is the query which you sent.
In this query there is no "FROM <TABLE NAME>".
I think you miss this statement .
Please add this "FROM" statement and check it, you can find the solution.
I hope this is the problem with this query. If not just post it what is the correct requirement.
Thank You,
Rajkumar |
|
| Back to top |
|
|
|
|
