Create a sql query based on the existence of variables ($_POST)

Question

As I explained in the title, I want to create a sql query on my php page, to return specific results in function of the existence of the variables. I have a form on the top of my page, with some inputs (date, name, etc...) and when I click, I refresh the page with the right results.

Currently my syntax is :

if (isset($_POST['dated']) && $_POST['dated'] != null){
    $doleances = $bdd->prepare('SELECT * FROM doleance WHERE Priorite < 5 AND Date >= ? ORDER BY ID DESC');
    $doleances->execute(array($newDate));

}
else if (isset($_POST['dated']) && $_POST['dated'] != null && isset($_POST['datef']) && $_POST['datef'] != null){
    $doleances = $bdd->prepare('SELECT * FROM doleance WHERE Priorite < 5 AND Date BETWEEN ? AND ? ORDER BY ID DESC');
    $doleances->execute(array($newDate, $newDate2));
}
else if{...}
else if{...}
...

But I think there's a much better way to do that... Thanks in advance

Take a look at the approach described here: http://stackoverflow.com/questions/17051539/mysql-where-like-not-working-with-multiple-fields-using-php-and-pdo-bind/17051834#17051834 — mzedeler, Jun 13 '13 at 12:43

Brad Christie · Accepted Answer · 2013-06-13T13:08:30.553

you could use a build-as-you-go approach:

// Create holders for the WHERE clauses and query parameters
$where = array(
  "Priorite < 5"  // this looks common across all queries?
);
$params = array();

// Now build it based on what's suppled:
if (!empty($_POST['dated'])){
  if (!empty($_POST['datef'])){
    // Add to the params list and include a WHERE condition
    $params['startdate'] = $_POST['dated'];
    $params['enddate'] = $_POST['datef'];
    $where[] = "Date BETWEEN :startdate AND :enddate";
  }
  else{
    // Add to the params list and include a WHERE condition
    $params['date'] = $_POST['dated'];
    $where[] = "Date >= :date";
  }
}
else if { ... }
else if { ... }

// Now build and execute the query based on what we compiled together
// from above.
$sql = "SELECT * FROM doleance "
     . (count($where) > 0 ? "WHERE " . implode(" AND ", $where) : "")
     . " ORDER BY ID DESC";
$doleances = $bdd->prepare($sql);
$doleances->execute($params);

I was thinking along the same lines, I'm not sure there's a better way to do it than this. Side note: the use of `isset()` with `!empty()` [is redundant](http://stackoverflow.com/questions/4559925/why-check-both-isset-and-empty), just use `!empty()` — billyonecan, Jun 13 '13 at 12:57
@billyonecan: good to know. it's been a while since I was in the PHP realm, but always nice to know a better way. Thanks! — Brad Christie, Jun 13 '13 at 13:01

score 1 · Answer 2 · answered Jun 13 '13 at 12:47

1

Start by creating an array of the possible posted variables:

$possibleArgs = array( 'dated', 'datef' );

Then iterate through each $possibleArg and check if the corresponding $_POST[possibleArg] is not empty. If it's not empty, add it to your predicate.

answered Jun 13 '13 at 12:47

Kermit

33,827
13
85
121

The only problem with this is when multiple variables need to be checked as part of a condition – billyonecan Jun 13 '13 at 12:51
@billyonecan Just build an array for that too. – Kermit Jun 13 '13 at 13:00
1

@billyonecan Who knows. I'm not going to get my pitchfork over -2 rep. – Kermit Jun 13 '13 at 13:16

Create a sql query based on the existence of variables ($_POST)

2 Answers2