Variable sql query depending on number of search parameter

Question

I need to do a sql query in php for search some entries (so using WHERE). But the field used to search could be of variable number.

I have a page with a search form, with 4 Field. It sends via POST the fields to a search.php that make a query:

$gomme_sql = $data->query("SELECT * FROM table WHERE parameter1 = '$_POST['name1']' AND parameter2 = '$_POST['name2']' ORDER BY id ASC");

But I don't know which field are filled. So, if I don't enter anything in field1 from the search form, I shouldn't have parameter1 = '$_POST['name1']' in the WHERE query.

Have you any idea how to obtain this?

Thank you

score 0 · Accepted Answer · edited May 23 '17 at 12:13

0

You can check the post data before appending that clause to the query in a way like this:

edit: adding additional check:

$sql="select something from someTable ";
if(!empty($_POST['name1']) || !empty($_POST['name2'])) // add as many as you like
{
$sql.=" where ";
    if(!empty($_POST['name1']))
    {
        $sql.="parameter1= $_POST['name1']";
    }
// etc etc...
}
$sql.=" ORDER BY id ASC";

and so on.

Having said that, please, please use prepared statements with this sort of input from the user. This is SUPER open to sql injection. Please do read this: How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:13

Community

1
1

answered Sep 29 '14 at 11:48

Fluffeh

33,228
16
67
80

Ok, thank you very much! I'd like also, if no field is filled, that the page shows ALL the entries of the table. With that structure, in that case i have a WHERE without any argument passed... – Matteo Sep 29 '14 at 11:56

score 0 · Answer 2 · answered Sep 29 '14 at 12:18

You can write generic sql select function like this , if you need more complex SQL just modify it.

    <?php


     function sqlSelect($table, $sel, $wh = '', $groupby = '', $order = '', $add = '') {

            $tb = $table;
            if (is_array($table)) {
                $tb = implode(',', $table);
            }
            if ($wh) {
                if (is_array($wh)) {
                    $w = array();
                    foreach ($wh as $k => $v) {
                        $v = mysqli_real_escape_string($v);
                        if (is_null($v))
                            $w [] = "$k=null ";
                        else
                            $w [] = "$k ='$v'";
                    }
                    $wh = 'where ' . implode(' and ', $w);
                }else {
                    $wh = "where $wh";
                }
            }
            if ($groupby)
                $groupby = "group by $groupby";
            if ($order)
                $order = "order by $order";
            $sql = "select $sel from $tb $wh $groupby $order $add ";
            return $sql;
        }
    //set _GET as this is console test    
    $_GET['name1']='Bob';
    $where = array(
        'name1'=>$_GET['name1']
    );
        echo sqlSelect('sometable' , '*' , $where) ."\n";
        // select * from sometable where name1 ='Bob'    

//or some complex stuff
        echo sqlSelect('persons', "age,status" , array('name'=>'Maria' , 'likes'=>'PHP') , null,  'age' , 'limit 20');
       //select age,status from persons where name ='Maria' and likes ='PHP'  order by age limit 20

Variable sql query depending on number of search parameter

2 Answers2