fat creative Posted May 11, 2008 Share Posted May 11, 2008 I have a form that is currently working, searching on one table. I got help from here and it's working GREAT. Now I'd like to join a 2nd table and I need some help with writing a script. My first table is named "phoneList" and I'd like to join it with a second table called "meaning". I will match on the field called "Lastname" and the fields I want to return are Firstname, Lastname, Email, and Phone from the phoneList table and a field called meaning from the meaning table (the thought is each last name has a different meaning). Right now, this is all test data for me to learn from. The form is at www.fatcreative.com/test.html and my current code is: # GRAB THE VARIABLES FROM THE FORM $Lastname = $_POST['Lastname']; $Firstname = $_POST['Firstname']; //this should select based on form selections $query = "SELECT * FROM phoneList WHERE ID > 0 "; if (strlen($Firstname) > 0) { $query .= "AND Firstname='$Firstname'"; } if (strlen($Lastname) > 0) { $query .= "AND Lastname='$Lastname'"; } $result = mysql_query($query) or die ("Couldnt execute query"); $numrows = mysql_num_rows($result); //count the number of rows returned if($numrows < 1 ) //if none!!! { echo ("There were no rows returned from the database"); } else //otherwise draw the table //put info on new lines echo "<table cellspacing='15'>"; echo "<tr><td colspan='3'></td></tr>"; while($row = mysql_fetch_assoc($result)) { extract($row); echo " <tr>\n <td>$Firstname</td>\n <td>$Lastname</td>\n <tr> <td>$Email</td>\n <tr> <td>$Phone</td>\n </tr>\n"; echo "<tr><td colspan='3'></td></tr>\n"; } echo "</table>\n"; Thank you so much in advance for ANY assistance! Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 11, 2008 Share Posted May 11, 2008 Well, the query is a straightfroward join: SELECT p.Firstname,p.Lastname,p.Email,p.Phone,m.meaning FROM phoneList as p,meaning as m WHERE m.Lastname=p.Lastname You'll then have to modify your output to show the new data as well. On a side note, you ought to be using mysq_real_escape_string() on your firstname and lastname fields to protect against mysql injection. Quote Link to comment Share on other sites More sharing options...
fat creative Posted May 11, 2008 Author Share Posted May 11, 2008 Thank you for that suggestion! I tried doing a join like that before I posted and I kept getting an error message. I think it's because of this statement: if (strlen($Firstname) > 0) { $query .= "AND Firstname='$Firstname'"; } I'm not sure how to append to the query with the join. I need this statement to determine if the user has actually selected something from the drop down first. If they haven't, I don't want it included in the search. Is what I'm asking even possible? It seems like anything is possible with PHP, but being so new, I just don't know. Oh, and thanks for the info on "mysql injection". I don't know what that means yet :-) but I'll do some research this afternoon! Jeanette Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2008 Share Posted May 11, 2008 You can append your AND statements, but don't forget space before AND if (strlen($Firstname) > 0) { $query .= " AND Firstname='$Firstname' "; } if (strlen($Lastname) > 0) { $query .= " AND Lastname='$Lastname' "; } Quote Link to comment Share on other sites More sharing options...
fat creative Posted May 17, 2008 Author Share Posted May 17, 2008 This worked great! Thank you so much for your help! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.