Joining tables

[fat creative]

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!

[GingerRobot]

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.

 

[fat creative]

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

[Barand]

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' ";
}

[fat creative]

This worked great! Thank you so much for your help!