PDO having optional bind parameters without re-writing SQL query string

Question

I have a function which selects a list of records from a table of feedback forms. At the moment the query is based on both a course ID and user ID:

$sql = "SELECT * FROM `feedback` WHERE course_id = :course_id AND user_id = :user_id LIMIT 1";
$stmt = $this->ci->db->prepare($sql);
$result = $stmt->execute([
    "course_id" => $course_id,
    "user_id" => $user_id
        ]);
$results = $stmt->fetch(PDO::FETCH_ASSOC);

I want to make this so the user ID is optional but not sure how to write that most efficiently?

For example, I could do something like this:

if ($user_id) {
    $sql = "SELECT * FROM `feedback` WHERE course_id = :course_id AND user_id = :user_id LIMIT 1";
    $stmt = $this->ci->db->prepare($sql);
    $result = $stmt->execute([
         "course_id" => $course_id,
         "user_id" => $user_id
    ]);

 } else {

    $sql = "SELECT * FROM `feedback` WHERE course_id = :course_id LIMIT 1";
    $stmt = $this->ci->db->prepare($sql);
    $result = $stmt->execute([
         "course_id" => $course_id,
    ]);

}

$results = $stmt->fetch(PDO::FETCH_ASSOC);

But that seems very inefficient. The trouble is that if I omit "user_id" => $user_id (in the first block of code) then my $sql is invalid because the number of bound parameters won't match.

What's the best way around this?

Didn't you already ask the same question here http://stackoverflow.com/questions/40525986/writing-a-pdo-search-query-from-a-php-array — Your Common Sense, Dec 02 '16 at 10:31
Not really. That's a question about using `LIKE` and `=` conditions when looping an array. Totally different really. — Andy, Dec 02 '16 at 11:14

score 1 · Accepted Answer · answered Dec 02 '16 at 10:09

Since you're using $this, i asume you are in a class, so I created a function to wrappe the process. I'm used to play with PHP string concatenation to construct my requests when some parameters are optional. As you can add entries on the fly to the parameters array used by PDOStatment too, you can do it like this :

public function getRecords($course_id, $user_id = null) {

    // Init request string
    $request = "SELECT * 
                FROM `feedback` 
                WHERE course_id=:course_id";

    // Init $params array
    $params = array(
        ':course_id'    =>  $course_id
    );

    // If user_id is passed to the function
    // update request string & $params array
    if(null !== $user_id) {
        $request .= " AND user_id=:user_id";
        $params[':user_id'] = $user_id;
    }

    // Finish request
    $request .= " LIMIT 1";

    $stmt = $this->ci->cb->prepare($request);
    $stmt->execute($params);
    $results = $stmt->fetch(PDO::FETCH_ASSOC);
}

Yes the query is within a class function - I just omitted this for brevity. Thank you, this will work for my needs. — Andy, Dec 02 '16 at 10:11
Does it protect form MySQL injection? Shouldn't there be bindParam? And what if parameters are mixed, like some are integers, others are strings? — Coder88, Apr 01 '21 at 09:57
if you want to specify the type of the value, then yes you can use bindParam as all parameters are processed as string when you just use execute ($array_or_params). (more detailed answer here: https://stackoverflow.com/questions/12392424/pdo-bindparam-vs-execute) And the point of using PDO and a prepared query is to protect from sql injection, as whatever method you use, the values are escaped. — Dexter0015, Apr 01 '21 at 15:42

PDO having optional bind parameters without re-writing SQL query string

1 Answers1