CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
jonmrich
jonmrich

Reputation: 4333

Pass parameter with a space in prepared statement

The prepared statement that gets generated dynamically from my PHP (as an example) looks like this:

SELECT COUNT(exuid) as result_count FROM full_db3 WHERE `Age Range` = :Age Range

Age Range is one of my column names.

The problem here is that having the space in between "Age" and "Range" in my parameter, but I'm not sure how to handle this. The query is generated dynamically like so (only relevant code shown):

$all_attributes = $_POST['attris'];
    $sql = "SELECT COUNT(exuid) as result_count FROM {$table}";
    $any_condition = false;
    foreach($all_attributes as $key=>$val) {

       if (!empty($val) && in_array($key,$validKeys)) {
         if ($any_condition) {
           $sql .= ' AND `'.$key.'` = :'.$key;
         } else {
           $sql .= ' WHERE `'.$key.'` = :'.$key;
           $any_condition = true;
         }
       }
    }




 $stmt = $dbh->prepare($sql);

    foreach($all_attributes as $key=>$val) {

   if (!empty($val)  && in_array($key,$validKeys)) {
     $stmt ->bindValue(':'.$key, $val, PDO::PARAM_STR);
   }
}

$stmt->execute();

If I change my column name in the DB to Age_Range everything works perfectly fine. For a number of reasons, I'd like to be able to exclude that underscore, as I display my column names in a select and all the underscores look terrible.

Upvotes: 2

Views: 1781

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

Using my idea, and copying syntax from u_mulder's comment, this should work?

$all_attributes = $_POST['attris'];
$sql = "SELECT COUNT(exuid) as result_count FROM {$table}";
$any_condition = false;
foreach($all_attributes as $key=>$val) {

   if (!empty($val) && in_array($key,$validKeys)) {
     if ($any_condition) {
       $sql .= ' AND `'.$key.'` = :'.str_replace(' ', '_', $key);
     } else {
       $sql .= ' WHERE `'.$key.'` = :'.str_replace(' ', '_', $key);
       $any_condition = true;
     }
   }
}

It leaves the field names as is, and only changes the parameter names.

But this

$stmt ->bindValue(':'.$key, $val, PDO::PARAM_STR)

will probably need changed to this as well

$stmt ->bindValue(':'.str_replace(' ', '_', $key), $val, PDO::PARAM_STR)

Upvotes: 4

Related Questions