Drop down list & search logic

[fat creative]

Newbie to PHP.  I've set up a form and database to search on.  Right now I'm just using a first name field and a last name field so I can understand how this works.  If I make a selection in the first name and last name fields, my search returns matching records.  However, if someone does not choose a last name and only chooses a first name, I'm not sure how to make that search work.  I've added an option choice in my form that says No Selection, but of course there is nothing in my table that says No Selection. When I set up the "real" page, my search fields will be six drop down fields.  This is my code:

 

$query = "SELECT * FROM phoneList";

$result = mysql_query($query)

    or die ("Couldnt execute query");

 

//this will list what customer entered/selelcted on form

  echo "You made the following selections <br>";

  foreach ($_POST as $field => $value)

  {

    echo "$field = $value<br>";

  }

 

# GRAB THE VARIABLES FROM THE FORM

$Lastname = $_POST['Lastname'];

$Firstname = $_POST['Firstname'];

 

//this should select based on form selections

$query = "SELECT * FROM phoneList

              WHERE Lastname=\"{$_POST['Lastname']}\" AND Firstname=\"{$_POST['Firstname']}\" ";

$result = mysql_query($query)

    or die ("Couldnt execute 2nd query");

 

//put info on new lines

  echo "<table cellspacing='15'>";

  echo "<tr><td colspan='3'><hr /></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'><hr /></td></tr>\n";

  }

  echo "</table>\n";

   

There may be extraneous code here, I've been searching for scripts and cutting and pasting til I got something to work.

 

 

I am also stuck at getting a message to display if there are no search records that match the chosen criteria.

 

Any help would be so very appreciated!!!! I gone through several books, php for the absolute beginner, php for dummies, etc, but nothing seems to be doing exactly what I need and I can't seem to figure out how to change it.

 

Thank you in advance.

 

oh the form is at www.fatcreative.com/test.html

 

 

[paul2463]

change this bit

# GRAB THE VARIABLES FROM THE FORM
$Lastname = $_POST['Lastname'];
$Firstname = $_POST['Firstname'];

//this should select based on form selections
$query = "SELECT * FROM phoneList
              WHERE Lastname=\"{$_POST['Lastname']}\" AND Firstname=\"{$_POST['Firstname']}\" ";
$result = mysql_query($query)
     or die ("Couldnt execute 2nd query");
   
//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";

 

to this

 

<?php
# GRAB THE VARIABLES FROM THE FORM
$Lastname = $_POST['Lastname'];
$Firstname = $_POST['Firstname'];

//this should select based on form selections 
//You already had the variables above so need to reuse the $_POST variables in the query
$query = "SELECT * FROM phoneList
              WHERE Lastname='$Lastname' AND Firstname='$Firstname'";
$result = mysql_query($query) or die ("Couldnt execute 2nd 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";
}
?>

[fat creative]

Worked!  It now tells me if no records match the search. Thank you.

 

If anyone has thoughts on what to do if I don't make a selection from the drop down list, I would be the happiest girl in the world!!

 

Jeanette

[DarkWater]

//this should select based on form selections

$query = "SELECT * FROM phoneList

              WHERE Firstname='$Firstname";

if (strlen($Lastname) > 0) {

  $query .= "AND Lastname='$Lastname'";

}

$result = mysql_query($query)

    or die ("Couldnt execute 2nd query");

[fat creative]

Thank you very much!  I think I understand how to modify this for the six fields.  I'm going to play and may post more questions later :-)

[DarkWater]

//this should select based on form selections

$query = "SELECT * FROM phoneList

              WHERE Firstname='$Firstname'";

if (strlen($Lastname) > 0) {

  $query .= "AND Lastname='$Lastname'";

}

$result = mysql_query($query)

    or die ("Couldnt execute 2nd query");

 

 

Sorry, there was a typo. =)

[fat creative]

Well THANK YOU for re-posting.  I was working on the first solution and just couldn't get it to work!

 

Everything is working exactly as I hoped, so ... I appreciate the help!

[DarkWater]

No problem. =P

 

(Off-topic: I'm going to Atlanta in like a month and a half for a conference.  Is it nice? xD)