[SOLVED] "Dynamic" MySql query based on form with optional fields

[colby07]

Hey guys, I'm new to PHP and MySql but have been attempting to improve.

I currently have an HTML form with certain inputs where some of them are optional.  Based on the results of this form, I would like to query the database.

For example, lets say I have the following variables:

$name

$address

$country

that have been posted from a form where either of them can be empty.

How could I create a php script that would return all the records in the database table if all the fields are empty and that would return the correct records any combination of inputs were given.

Thank you in advance for your time and generosity.

I came accross a forum post where the following solution was suggested:

 

$query  = "SELECT * FROM table_name WHERE 1";}

 

if ($name != "")

{$query .= " AND name = '$name'";}

if ($address !="")

{$query .= " AND addresse = '$addresse'";}

if ($country !="")

{$query .= " AND country = '$country'";}

 

but when all fields are empty all I get is a blank page instead of all the records in the table.  When I input a name, the result is correct though.  This issue has been driving me nuts...

 

Thank you in advance for your help and generosity,

 

Colby07

[Maq]

This was probably more of a PHP question because you have to manipulate the strings a bit.  You also have to check whether or not there's a condition so you know whether or not to use WHERE or AND.

 

Try something like this:

 

 

   $set = FALSE;
   $query  = "SELECT * FROM table_name";

   if (!empty($name))
   {
      $query .= " WHERE name = '$name'";
      $set = TRUE;
   }
   if (!empty($address))
   {
      $query .= ($set===TRUE ? " AND" : " WHERE") . " address = '$address'";
      $set = TRUE;
   }
   if (!empty($country))
   {
      $query .= ($set===TRUE ? " AND" : " WHERE") . " country = '$country'";
   }

[colby07]

Thank you so much for your response,

I have to leave for the day but I'll try it as soon as I get back!

 

Colby07

[colby07]

thx again for the reply Maq.

I tried running the script but when all three fields are blank, nothing is displayed on screen.  Just to test it, if I remove the if statements, all the records show up and the only field that works data is inputed is the first one.

 

Do you think there is a another way of going about solving this?

Thank you again,

 

colby07

[Maq]

OK well, that creates the string properly, I tested it myself.  Can I see some more of the relevant code, specifically the sql block.

[colby07]

<?php

 

include('db_login.php');

$connection = mysql_connect($db_host, $db_username, $db_password);

if (!$connection){

die ("Could not connect to the database: <br />". mysql_error( ));

}

 

 

 

$db_select=mysql_select_db($db_database);

if (!$db_select)

{

die ("Could not select the database: <br />". mysql_error( ));

}

 

$name = $_POST['name'];

$address = $_POST['address'];

$country = $_POST['country'];

 

$set = FALSE;

  $query  = "SELECT * FROM table_name";

 

  if (!empty($name))

  {

      $query .= " WHERE name = '$name'";

      $set = TRUE;

  }

  if (!empty($address))

  {

      $query .= ($set===TRUE ? " AND" : " WHERE") . " address = '$address'";

      $set = TRUE;

  }

  if (!empty($country))

  {

      $query .= ($set===TRUE ? " AND" : " WHERE") . " country = '$country'";

  }

 

 

$results = mysql_query($query)

or die(mysql_error());

 

while ($row = mysql_fetch_array($results)) {

extract($row);

 

//For the sake of testing the search, I only make it return the

//countries associated with the record.

echo $country;

echo "<br>";

}

 

?>

 

I've tried everything within my capabilities to get the script to display all the records when all fields are empty but just haven't been able to.

 

When I fill in the name field, it provides the records with that name.

When I fill in the name field with the address and the country, it gives me the appropriate records.

When only the address or counrty field is filled in, nothing is displayed.

It is as if the only time I get a result is when the name is filled in.

[colby07]

Hey, I just wanted to say that there was a small input value in my form and now that it's fixed, the search and query function properly!

 

THANK YOU Maq!

 

Really appreciate your help.

Problem solved

[Maq]

Hey, I just wanted to say that there was a small input value in my form and now that it's fixed, the search and query function properly!

 

THANK YOU Maq!

 

Really appreciate your help.

Problem solved

 

Good to hear, but what do you mean small input value in your form?

 

P.S. - You can mark the thread solved.  There is a SOLVED tab at the bottom of the thread.

[colby07]

Sorry I meant small input value error in the form...