adding a condition to a WHERE clause based on a passed variable value mysql

Question

I am a relative novice and could use some help with this problem.

This will be used in a search filter situation. Users need to search by a value and 1 or more other values passed by the search form.

$name = $_POST['name'];
$sdate = $_POST['sdate'];
$startdate = $_POST['startdate'];
$enddate = $_POST['enddate'];
$vehicle = $_POST['vehicle'];
$triptype = $_POST['triptype'];

If any of these values are '' I do not want them in the query, If they contain a value I do want them in the query.

SELECT * FROM form_data WHERE `resp_person` = '$name', 
 IF $sdate != '' then `sdate` = '$sdate', 
 IF $startdate != '' then `sdate` = *all values between $startdate and $enddate*, 
 IF $triptype != '' then `triptype` = '$vehicle', 
 IF $vehicle != '' then `vehicle` = '$vehicle', `sdate`  
ORDER BY `sdate` DESC, `stime` DESC")

I know the code is wrong but it should give you a good idea of what I am trying to accomplish. Any guidance would be greatly appreciated.

You can test the variables in PHP whilst you build the SQL. — eggyal
– eggyal, Commented Mar 31, 2015 at 22:39
The variables are not the issue. The solution I am looking for is how to code the IF statements ( or a suitable alternative) so they work within query. — Aesa
– Aesa, Commented Apr 1, 2015 at 23:10

pala_ · Accepted Answer · 2015-04-02 02:35:35Z

1

A better way is to not use string concatenation to build the entire query, but rather use an sql library that supports prepared statements, such as PDO.

$pdo = new PDO('... connection string ...', username, password);
$where = '';
$possible_values = array('name', 'sdate', 'startdate', 'enddate', 'vehicle', 'triptype' );
$params = array();
foreach($possible_values as $val)
{
    if(isset($_POST[$val]))
    {
        $params[] = $_POST[$val];
        if($where == '')
        {
            $where = "WHERE $val = ?";
        }
        else
        {
            $where .= " AND $val = ?";
        }
    }
}
$stmt = $pdo->prepare("SELECT * FROM form_data " . $where);
$stmt->execute($params);

answered Apr 2, 2015 at 2:35

pala_

9,0101 gold badge18 silver badges32 bronze badges

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

2 Comments

Aesa Over a year ago

pala_ ... Thank you for your suggestions. I will try both and let you know how it worked. Again, I am a relative novice but I think I understand this.

pala_ Over a year ago

No problem. As a novice I really think it better for you if you focus on using prepared statements for your SQL - if you have time, look up SQL injection and why it's bad -- prepared statements are the best way to protect against these

Rick James · Accepted Answer · 2015-04-02 01:53:25Z

0

In cases like this, I prefer to build the query in pieces...

$wheres = array();   // Collect things to AND together
if ($searchterm != 'All')  $wheres[] = "subject LIKE '%searchterm'";
if (...)  $wheres[] = "...'";
...
if (count($wheres) > 0)
    $where_str = "WHERE " . implode(' AND ', $wheres);
else
    $where_str = '';

$order_str = (...) ? "ORDER BY ..." : '';

$limit_str = $limit ? "LIMIT $limit" : '';

$query = "SELECT ... FROM foo $where_str $order_str $limit_str";

Oh, and don't forget to use escape the strings on any data coming in from a form -- else a user can do nasty things to the SQL statement!

answered Apr 2, 2015 at 1:53

Rick James

144k15 gold badges144 silver badges255 bronze badges

8 Comments

pala_ Over a year ago

or better yet, convert the whole thing to pdo and use prepared statements

Rick James Over a year ago

@pala_ Can PDO insert column names?

pala_ Over a year ago

no - since the column names are well known ahead of time however, you can whitelist their allowance while building the prepared statement. there's no reason to encourage the use of string concatenation for the entire query

Rick James Over a year ago

But you can't eliminate a clause with PDO. That is, add AND sdate = '...' only if the user filled in the "sdate" prompt in the <form>.

pala_ Over a year ago

rubbish. gist.github.com/anonymous/3ebcdb4639f313e3503d - pdo, optional (safe) columns, safe parameters, prepared statement goodness.

|

Collectives™ on Stack Overflow

adding a condition to a WHERE clause based on a passed variable value mysql

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related