CountryGirl

Well, that's where I'm running into problems I think. I think some of the KeyIDs match, but I don't believe they all do. What I'm confused by is that not every table has the same primary key. Do I have to link them by a primary key or just anything that will make them pull them correct info? The company I'm working for just switched webhosts, so I am going to have to re-do everything anyways (...sigh...). And put everything into a new MySQL database on the new host. So, I may be able to get it better organized this time around. Qadoshyah

I changed the code a little, since I realized I was being a bit redundant . It is working better than it was, but it is still not returning a few names. This is what I changed it to: "SELECT asmnt_legal.Account, asmnt_legal.Legal, cmn_name.OwnersName, cmn_name.Address1, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_parcel.ParcelID FROM asmnt_legal LEFT JOIN cmn_name ON asmnt_legal.KeyID=cmn_name.KeyID LEFT JOIN asmnt_parcel ON asmnt_legal.KeyID=asmnt_parcel.KeyID WHERE asmnt_legal.Account = '{$search}' OR cmn_name.OwnersName = '{$search}' OR cmn_name.Address2 = '{$search}' OR asmnt_parcel.ParcelID = '{$search}' ORDER BY asmnt_legal.Account ASC, asmnt_legal.Legal ASC"; The table that contains the names is cmn_name and the field that holds the name is OwnersName. The structure of the 3 tables I'm using above is: asmnt_legal --------------- KeyID Account Legal cmn_name ---------------- KeyID NameID RowID OwnersName Address1 Address2 City State ZipCode asmnt_parcel ------------------ KeyID RowID Account ParcelID Township Range Section Quarter NameID OwnersName SubdivisionName LotNumber ParentAccount TotalValue TaxAreaCode LotSize LotSizeType Thanks! Qadoshyah

Cool, thanks!! I have another question: I have 4 tables joined in at the moment. They return searches accurately most of the time. But, there are certain names that they won't return anything on, when I know there is something in the database for that entry. Why is it doing this? Do I have them joined wrong somehow? Should they all be joined to the same "Index" or "Unique" spot? This is what I have right now. And like I said above, it's working for a lot of entries, but not working for some. I can pull up things totally fine with account numbers, but doing the names sometimes is where I'm running into problems. Just not returning any results. And no, I'm not getting any error message. "SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal, cmn_name.Address1, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_parcel.ParcelID FROM asmnt_ownership LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID LEFT JOIN cmn_name ON asmnt_ownership.NameID=cmn_name.NameID LEFT JOIN asmnt_parcel ON asmnt_ownership.KeyID=asmnt_parcel.KeyID WHERE asmnt_ownership.Account = '{$search}' OR asmnt_ownership.OwnersName = '{$search}' OR cmn_name.Address2 = '{$search}' OR cmn_name.OwnersName = '{$search}' OR asmnt_parcel.ParcelID = '{$search}' ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC"; You can see what I'm talking about if you want with the page I'm testing on: www.wagonerassessor.com/searchjoin.php If you type in something like "BERGEN, LARRY D" you'll return a result. But, if you type in something like, "ABSHIER, STEVE" you won't return anything. But, I know that there are records in the database. From the same table BERGEN, LARRY is from. Searching by Account number & Parcel number, I always return the accurate info. I'm just running into problems when I try searching by name, address or legal info. Thanks! Qadoshyah

Thank you! Thank you! That was it. I guess it helps to have a second set of eyes looking at it . I haven't been getting error messages, it just won't return results. But, hopefully I won't have any more snags. We'll see. Hopefully I can get the rest of the tables joined tomorrow, so ya never know . Qadoshyah

Okay, I was going good until I tried adding in a 4th table and then I can't get anything. Here's my SELECT query: SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal, cmn_name.Address1, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_parcel.ParcelID FROM asmnt_ownership LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID LEFT JOIN cmn_name ON asmnt_ownership.NameID=cmn_name.NameID LEFT JOIN asmnt_parcel ON asmnt_ownership.NameID=asmnt_asmnt_parcel.NameID WHERE asmnt_ownership.Account = '{$search}' OR asmnt_ownership.OwnersName = '{$search}' OR cmn_name.Address2 = '{$search}' OR asmnt_parcel.ParcelID = '{$search}' ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC"; See anything wrong with this? Qadoshyah

Haha, I figured it had to be something simple !! I looked over the code several times and didn't even see it, LOL. That was it and it worked. Yes, this is great. Hopefully I can Join the other tables in correctly. Qadoshyah

Thanks! That's what I was thinking, but wasn't sure. Okay, I've tried this code: "SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal FROM asmnt_ownership LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}' ORDER BY asmnt_ownership.Account, asmnt_legal.Legal"; And this code: "SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal FROM asmnt_ownership LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}' ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC"; And neither of them are working. Why is this? Qadoshyah

I'm having difficulty with the WHERE clause for some reason. I've tried putting it at a few different spots in the code and it won't work. Does this look right or not? "SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal FROM asmnt_ownership LEFT JOIN asmnt_legal WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}' ON asmnt_ownership.KeyID=asmnt_legal.KeyID ORDER BY asmnt_ownership.Account, asmnt_legal.Legal"; Qadoshyah

Okay, well, I think a LEFT JOIN will work . I am able to pull the Legal information now once I added to the SELECT statement: "SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal FROM asmnt_ownership LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID ORDER BY asmnt_ownership.Account, asmnt_legal.Legal"; Now, should I be able to do this with all tables? Continue to just LEFT JOIN all tables? And to be able to just return one search result, can I still use the same code I was using before? Like: WHERE Account = '{$search}' OR OwnersName = '{$search}'" Thanks! I think I may be getting the hang of things . Qadoshyah

I just edited this post, because I was able to make the search work. But, I am not pulling any data from the second table. This is the code I have right now: "SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName FROM asmnt_ownership LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID ORDER BY asmnt_ownership.Account, asmnt_legal.Legal"; I'm thinking a LEFT JOIN isn't going to work, unless I just have my stuff written wrong (?). When I do a search with an account number, I get ALL the account numbers (which is a crazily high amount). The colum for Legal is there (since I added it into the code), but nothing is in it. So, it's not pulling anything from the database. Thanks! Qadoshyah

I think I will try playing around with the LEFT JOIN and see if that'll work with my tables (I *think* it may). The structure of my tables . . . I'm not totally sure what info you need on that? The database type is InnoDB. Collation is latin1_swedish_ci. Table names are: appr_agland appr_commbuilding appr_commercial appr_miscimpr appr_modifiers appr_outbuilding appr_resident appr_value asmnt_accountexempt asmnt_legal asmnt_ownership asmnt_parcel asmnt_situs asmnt_taxarea cmn_name sale_parcel Qadoshyah

Okay, read through the JOINS manual and have some questions . . . posted them on the multiple tables search post. Thanks! Qadoshyah

Alright, I just read through MySQL's Manual on Joins and I am slightly confused. How do I know which Join would be best to use? An inner join, an outer join, a left join, etc? Do I need to do something like this, "t1.name = t2.name", for each table? They all have a KeyID as their Primary Key and an ApprID that is the index for each table. Any ideas as to what would be the best way to write JOINS for the 16 tables I need to join? Thanks! Qadoshyah

Alright, cool. I will read that article and then come ask any questions . Thanks so much for all your help! I'll probably get back to this on Friday, since it's Thanksgiving tomorrow . Have a great Thanksgiving! Qadoshyah

Alright, I will try out some JOINS and hopefully I'll do it right . They all have the same KeyIDs . . . is that what is necessary to associate them? Thanks! Qadoshyah

Ah, yes, that worked! That's exactly what I needed . Thanks so much! Now, I have another question ~ I have multiple tables (16 to be exact) that need to be searched from. How would I incorporate the other tables so that by just searching the one item would return all the needed results (they all relate by having the same account number). Thanks again! Qadoshyah

They need to be able to search by both account & name. I'd like it so that the database will return the exact match. Qadoshyah

Oh I didn't even realize that comma was there! Duh (on my part ). Well, I was finally able to get something to return - yay, at least I've got something ! You can see what I'm doing at www.wagonerassessor.com/search0.php. If you type in "730000012" for example, it'll return a bunch of account numbers. Now, this is where my question comes in again. How can I make it so that it will only return the one account number? Because I need to be able to have people return just one account number with the owners name, and other information. How do I make that happen? Thanks again for the help! Qadoshyah

I just messed around with it a bit. I added a search word into the LIKE clause for the Account number and it still didn't return anything. I keep just getting the "problems ...." This is what I put: $query = "SELECT Account, FROM asmnt_ownership WHERE Account LIKE '%730%'"; $result = mysql_query($query, $con); I'm at a loss as to what I am doing wrong! Qadoshyah

Alright, I see what you are saying here and I will try something a bit different. I still have one question: There are tons of different names in the OwnersName category. I can't possibly list them all. Do I have to put an exact word in the $search spot? Or is there a way to put some kind of word/clause I can put it that is like a wildcard and will return whatever the person searches? I'm probably making this more complicated than it is ! Thanks for the help! Qadoshyah

I'm slightly confused on one thing here. The LIKE clause and 'searchWord'. Do I put something specific there? For example, like "Account Number." There are so many things that I will need to have searched, how do I make it the most anonymous, so that any results will return? Does that make sense? And the WHERE clause . . . there are two column names that I need to be able to search from. Those are "Account & "OwnersName." So, I put those in the WHERE clause, right? Qadoshyah

Alright, I tried that and it still came back with problems. What can we fix now? Thanks so much for the help!! Qadoshyah

Well, I tried the above and that didn't solve it. I don't see any error message come up except for it saying "problems. . . ." which is at a certain part in my code. I'm thinking I've got something wrong in this part of the code, since that is the error message (the "else {echo problems....}") I get: while ($r = mysql_fetch_array($result)) { // Begin while $act = $r["Account"]; $name = $r["Owners Name"]; echo "<tr> <td>$act</td> <td>$name</td> </tr>"; } // end while echo "</table>"; } else { echo "problems...."; } } else { echo "Search string is empty. <br> Go back and type a string to search"; } ?> Does something look wrong there? And the full text of my code again (with the WHERE clause fixed): <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd"> <?php $dbHost = 'localhost'; $dbUser = 'wcassessor'; $dbPass = '************'; $dbDatabase = 'wcassessor'; $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()); ?> <?php if ($search) // perform search only if a string was entered. { mysql_connect($dbHost, $dbUser, $dbPass) or die ("Problem connecting to DataBase"); $query = "SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'"; $result = mysql_db_query($con, $query); if ($result) { echo "Here are the results:<br><br>"; echo "<table width=90% align=center border=1><tr> <td align=center bgcolor=#00FFFF>Account</td> <td align=center bgcolor=#00FFFF>Owners Name</td> </tr>"; while ($r = mysql_fetch_array($result)) { // Begin while $act = $r["Account"]; $name = $r["Owners Name"]; echo "<tr> <td>$act</td> <td>$name</td> </tr>"; } // end while echo "</table>"; } else { echo "problems...."; } } else { echo "Search string is empty. <br> Go back and type a string to search"; } ?> <html xmlns="http://www.w3.org/1999/xhtml"> <head><title>Searching Another Test</title> </head> <body bgcolor=#ffffff> <h1>Searching the Database</h1> <form method="post" action="search0.php"> <table width=90% align=center> <tr><td>search for:</td><td><input type=text name='search' size=60 maxlength=255></td></tr> <td></td><td><input type=submit></td></tr> </table> </form> </body> </html> Thanks! Qadoshyah

I'm trying to use the code below as a search feature for my website. But, I either get an error message or it says "problems ...." when I try to run a search on it. I think it has something to do how the connection is written in the code, but I'm not totally sure. Could any of you experts take a look at this and let me know exactly is wrong? I'm sure you guys could pick out what I'm not typing in right! I greatly appreciate the help!! <?php $dbHost = 'localhost'; $dbUser = 'wcassessor'; $dbPass = '**********'; $dbDatabase = 'wcassessor'; $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()); ?> <?php if ($search) // perform search only if a string was entered. { mysql_connect($dbHost, $dbUser, $dbPass) or die ("Problem connecting to DataBase"); $srch="%".$search."%"; $query = "SELECT * FROM asmnt_ownership WHERE Account, OwnersName"; $result = mysql_db_query($con, $query); if ($result) { echo "Here are the results:<br><br>"; echo "<table width=90% align=center border=1><tr> <td align=center bgcolor=#00FFFF>Account</td> <td align=center bgcolor=#00FFFF>Owners Name</td> </tr>"; while ($r = mysql_fetch_array($result)) { // Begin while $act = $r["Account"]; $name = $r["Owners Name"]; echo "<tr> <td>$act</td> <td>$name</td> </tr>"; } // end while echo "</table>"; } else { echo "problems...."; } } else { echo "Search string is empty. <br> Go back and type a string to search"; } ?> <html xmlns="http://www.w3.org/1999/xhtml"> <head><title>Searching Another Test</title> </head> <body bgcolor=#ffffff> <h1>Searching the Database</h1> <form method="post" action="search0.php"> <table width=90% align=center> <tr><td>search for:</td><td><input type=text name='search' size=60 maxlength=255></td></tr> <td></td><td><input type=submit></td></tr> </table> </form> </body> </html> Qadoshyah

Sure, here's all the code I'm using right now ~ Thanks so much! Qadoshyah