MySql search using PHP based on multiple variable fields

Question

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

Filed_one is slected and filed_two is not selected
Field_One is not selected and field_two is selected
Filed_one is not selected and Filed_two is not selected
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....

Rakesh Sharma · Accepted Answer · 2013-12-27 12:43:18Z

1

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

answered Dec 27, 2013 at 12:43

Rakesh Sharma

13.7k5 gold badges41 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nilesh · Accepted Answer · 2013-12-27 12:51:20Z

1

            <?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);
            ?>

answered Dec 27, 2013 at 12:51

Nilesh

4401 gold badge6 silver badges22 bronze badges

Comments

Trimantra Software Solution · Accepted Answer · 2013-12-27 12:45:33Z

0

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;

edited Dec 27, 2013 at 12:45

Trimantra Software Solution

3,3273 gold badges28 silver badges45 bronze badges

answered Dec 27, 2013 at 12:41

Jelle Ferwerda

1,2291 gold badge7 silver badges13 bronze badges

1 Comment

Gumbo Over a year ago

I have skipped that and chances are that other will skip that as well. Furthermore, your code has at least 7 syntax errors and will generate an invalid query.

Fabien MEYNARD · Accepted Answer · 2013-12-27 12:49:04Z

0

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

answered Dec 27, 2013 at 12:49

Fabien MEYNARD

5974 silver badges12 bronze badges

Collectives™ on Stack Overflow

MySql search using PHP based on multiple variable fields

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related