Ask a Question Register Login

php - Dynamic select query according to form fields

96

I have a form with two fields. The user can fill in either one or both and the MySQL database should be queried accordingly.

Here is my php code:

$number1 = $_POST['number1'];
$number2= $_POST['number2'];

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

   if (!empty($number1 ))
   {
      $query .= " WHERE number1 = ".$number1."";
      $set = TRUE;  
   }
   if (!empty($number2))
   {
      $query .= ($set===TRUE ? " AND" : " WHERE") . " number2 = ".$number2."";
   }


$data = mysql_query($query) or die("Couldn't execute query. ". mysql_error());

The code works fine if either number1 or both of the fields are filled in. However when only the second field is filled in I get the error:

Couldn't execute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

If I echo the query it is shown correctly:

SELECT * FROM table WHERE number2 = entered value

Any help is much appreciated! Thanks!!

555

Answer

Solution:

have you forget escape values? u can use somthing like this

$fields = array();
if(!empty($_POST['number1'])) {
    $fields[] = 'number1='.mysqli_real_escape_string($_POST['number1']);
}
if(!empty($_POST['number2'])) {
    $fields[] = 'number2='.mysqli_real_escape_string($_POST['number2']);
}
$sql = "SELECT * FROM table WHERE ".implide(" AND ", $fields);
$data = mysqli_query($query) or die("Couldn't execute query. ". mysql_error());

mysql extension is deprecated, use mysqli instead

650

Answer

Solution:

try this

$number1 = $_POST['number1'];
$number2= $_POST['number2'];

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

   if (!empty($number1 ))
   {
      $query .= " WHERE number1 = ".$number1."";
      $set = TRUE;  
   }
   if (!empty($number2))
   {
      if($set==FALSE)
          $query .= " WHERE number2 = ".$number2;
      else
        $query .= " AND number2 = ".$number2;
   }
189

Answer

Solution:

I think you get the error because of the assignment. You wrote $set===TRUE, its == not === The error is here:

 if (!empty($number2))
 {
  $query .= ($set===TRUE ? " AND" : " WHERE") . " number2 = ".$number2."";
 }

People are also looking for solutions to the problem: php - Best practices for updating from mysql to PDO

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.