CODEWITHSUNDEEP
phpmysqllogic
Jestin
Jestin

Reputation: 111

MySql search using PHP based on multiple variable fields


I am trying to search in MySql db based on user input something like below.
User may/not select from the below fields

<select name="field_one" id="f1">
<option value="AA">AA</option> 
<option value="BB">BB</option>
<option value="CC">CC</option>
</select>



<select name="field_two" id="f2">
<option value="11">11</option> 
<option value="22">22</option>
<option value="33">33</option>
</select>

if User selects only 'field_one', then mysql_query should filter only based on 'field_one'. In this case there can be four combination

  1. Filed_one is slected and filed_two is not selected
  2. Field_One is not selected and field_two is selected
  3. Filed_one is not selected and Filed_two is not selected
  4. Field_one is selected and Field_Two is selected

What is the best and efficient methode to make this search?

I tried with 'case .... break;' and 'if', but when the first condition is met, code stops and does not check the next conditions
Can any one give me a clue?
Thanks in advance for the help....

Upvotes: 0

Views: 1165

Answers (4)

Nilesh
Nilesh

Reputation: 442

            <?php
            $sql = "SELECT * FROM table_name WHERE 1";

            if(isset($_POST)){
                if(isset($_POST['field_one'])){
                    $sql.= 'AND  field_one'= $_POST['field_one'];
                }
                if(isset($_POST['field_two'])){
                    $sql.= 'AND field_two'= $_POST['field_two'];
                }
            }

            mysql_query($sql);
            ?>

Upvotes: 1

Fabien MEYNARD
Fabien MEYNARD

Reputation: 597

You can also use PDO & param binding to avoid SQL Injection : http://www.php.net/manual/fr/pdostatement.bindparam.php

Upvotes: 0

Jelle Ferwerda
Jelle Ferwerda

Reputation: 1229

Example, not tested and needs lots of variable processing against SQL injection

$where = "";
$bits = array();
$bitset=false;

if(isset($_POST['field_one') && strlen($_POST['field_one')) > 0)
  {
  $bitset = true;
  $bits[] = " field1 = $_POST['field_one')"
  }

if(isset($_POST['field_two') && strlen($_POST['field_two')) > 0)
  {
  $bitset = true;
  $bits[] = " field2 = $_POST['field_two')"
  }

if($bitset)
  {
  $where = implode(", " $bits);
  }

$sql = "select * from table " . $where;

Upvotes: 0

Rakesh Sharma
Rakesh Sharma

Reputation: 13728

try this:-

$query = "select * from table_name where 1 ";
            if(!empty($_POST['field1']) ) {
             $query .= " AND field1 like '".trim($_POST['field1'])."'";
            }
            if(!empty($_POST['field2'])) {
             $query .= " AND field2 like '".trim($_POST['field2'])."'";
            }
            // and so on
            $result = mysql_query($query);

please use escape string also http://php.net/mysql_real_escape_string

Upvotes: 1

Related Questions