CountryGirl Posted January 26, 2010 Share Posted January 26, 2010 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 Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/ Share on other sites More sharing options...
Mchl Posted January 26, 2010 Share Posted January 26, 2010 We need some debug information. Change $result = mysql_query($query, $con); to $result = mysql_query($query, $con) or die(mysql_error().": $query"); Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/#findComment-1002044 Share on other sites More sharing options...
CountryGirl Posted January 26, 2010 Author Share Posted January 26, 2010 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 Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/#findComment-1002049 Share on other sites More sharing options...
Mchl Posted January 26, 2010 Share Posted January 26, 2010 You need a FROM before name of the first table. Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/#findComment-1002050 Share on other sites More sharing options...
CountryGirl Posted January 26, 2010 Author Share Posted January 26, 2010 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 Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/#findComment-1002053 Share on other sites More sharing options...
Mchl Posted January 26, 2010 Share Posted January 26, 2010 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 Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/#findComment-1002059 Share on other sites More sharing options...
CountryGirl Posted January 26, 2010 Author Share Posted January 26, 2010 Thank you! I will take a look at that . Qadoshyah Link to comment https://forums.phpfreaks.com/topic/189902-having-a-problem-with-joins-in-my-code-help/#findComment-1002068 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.