Dynamic Queries with PHP PDO

Question

I am trying to figure out how to convert my script history from mysql_query () to PDO. I have a form with 4 input fields that you can optionally select. This means that depending on what information you are trying to get, you can select 0, 1, 2, 3, 4 fields.

I tried to query db as follows:

$q = $db->prepare('SELECT date,
                          name,
                          action
                   FROM history
                   WHERE name = :name
                   AND action = :action');

$q->bindParam(':name', $Name, PDO::PARAM_STR, 20);
$q->bindParam(':action', $Action, $PDO::PARAM_STR, 20);
$q->execute();

But this does not work if I do not have the selected fields and you want to show the whole story.

With mysql_query (), I would just do this:

mysql_query('SELECT date,
                    name,
                    action
             FROM history
             $Name
             $Action');

This means that if there is no $ Name or $ Action, they are simply not included in the request.

Should I just copy / paste the old query into $ q = $ db-query ('')? But this type of lesion uses PDO.

Answer 1

, .

, where column = column, , where column = value.

EDIT:

, , bindParam . .

/* Start with the most general case for the sql query.
 * The where part always evaluates to true and will thus
 * always return all rows and exists only to make appending
 * further conditions easier.
 */

$q = 'SELECT date, name, action FROM history WHERE 1';

/* Prepare a params array in any way you wish. A loop might be more
 * efficient if it is possible, but since in this example you have
 * only 2 variables, it didn't seem necessary 
 */

$params = array();
if (! empty($Name)) {
    $params['name'] = $Name;
}

if (! empty($Action)) {
    $params['action'] = $Action;
}

/* When the params array is populated, complete the sql statement by
 * appending the param names joined with ANDs 
 */

foreach ($params as $key => $value) {
    $q .= sprintf(' AND `%s` = :%s', $key, $key);
}

/* When the query is complete, we can prepare it */
$stmt = $db->prepare($q);

/* Then bind the values to the prepared statement 
 */

foreach ($params as $key => $value) {
    // Using bindValue because bindParam binds a reference, which is
    // only evaluated at the point of execute
    $stmt->bindValue(':'.$key, $value);
}

/* Now we're ready to execute */
$stmt->execute();

empty , sql, .

param bindValue, , . , , .

, , ( ) params.