PDO query depending on what inputs are completed

Question

Hello i have a form with 6 fields:

    Age1
    Age2
    Client type
    Destination
    Score1
    Score2

I want to create a query based on what fields are completed and i don't know how to create one only by creating for each posibility a query. Those form fields are colums in my database of 2 tables.

$age1=$_POST['age1'];
$age2=$_POST['age2'];
$score1=$_POST['score1'];
$score2=$_POST['score2'];
$destination=$_POST['destionation'];
$client_type=$_POST['client_type'];

if ($age1!='' and $age2!='')
{
    $age_sql=" where TIMESTAMPDIFF(year, Bday,CURDATE())>=$age1 and TIMESTAMPDIFF(year, Bday,CURDATE())<=$age2"; 
}
if ($destination!='')
{
$dest_sql='Inner Join Leg_Client_Destinatie 
                where  Leg_Client_Destinatie.Destinatie="'.$destinatie.'"
                and Leg_Client_Destinatie.ID=Persoane.ID';  
}
$stmt = $dbh->prepare("SELECT * from Persoane $dest_sql $varsta_sql");    
                    $stmt->execute(); 
                while ($row = $stmt->fetch())
                {
                }

but this is not a good solution to make for each posibility because there can be alot of combinations. Any of you have another ideea?

that's about all you can do: dynamically build up your query string based on whatever the user selected. otherwise you're stuck writing incredibly ugly/huge queries that contain the logic to handle ALL possible options combinations. — Marc B, Feb 10 '15 at 16:40
ok but check my example let's say i have destination and age completed then at the age sql i need `and` instead `where` because i use `where` on `$dest_sql` — user3463807, Feb 10 '15 at 16:43
yes. if you're building a query dynamically, then you have to make sure you build a syntactically VALID query. — Marc B, Feb 10 '15 at 16:44
You are building your SQL statements with code from the outside world. This is leaving you open to SQL injection attacks. Read http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php to find out how to make SQL queries safely. — Andy Lester, Feb 10 '15 at 16:59
i'll bind the values it's np my problem is with building the query actualy :D — user3463807, Feb 10 '15 at 17:04

score 0 · Accepted Answer · answered Feb 10 '15 at 18:09

I managed to make it working. I bet there are more elegant ways to do it for example with arrays but atm is good like this. If someone has another ideea feel free to post it. Thnx

$age1=$_POST['age1'];
$age2=$_POST['age2'];
$score1=$_POST['score1'];
$score2=$_POST['score2'];
$destination=$_POST['destionation'];
$client_type=$_POST['client_type'];

$sql=("SELECT * From Persoane $join WHERE Persoane.ID IS NOT NULL $join2");
if ($destination!='')
{
$join='Inner Join Leg_Client_Destination 
                ON  Leg_Client_Destination.Destination="'.$destination.'"
                ';  
$join2='and Leg_Client_Destination.ID=Persoane.ID';
} 
if ($age1!='' and $age2!='')
{
    $sql .= " AND TIMESTAMPDIFF(year, Bday,CURDATE())>=$age1 and TIMESTAMPDIFF(year, Bday,CURDATE())<=$age2"; 
}   
if ($score1!='' and $score2!='')
{
    $sql .= " AND score_T>=$score1 and score_T<=$score2"; 
}
if ($client_type!='')
{
    $sql .= " AND Client_Type=$client_type"; 
}       
                    $stmt=$dbh->prepare($sql);
                    $stmt->execute(); 
                while ($row = $stmt->fetch())
                {

Well you have also opened yourself up to SQL injection because you are not preparing your params. — prodigitalson, Feb 10 '15 at 18:18
i know i pased the code before binding params and this code is under login anyway :p `$stmt->bindParam(':age1', $age1);` — user3463807, Feb 10 '15 at 18:42

PDO query depending on what inputs are completed

1 Answers1