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

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 stackoverflow.com/questions/40525986/… — Your Common Sense
– Your Common Sense, Commented Dec 2, 2016 at 10:31
Not really. That's a question about using LIKE and = conditions when looping an array. Totally different really. — Andy
– Andy, Commented Dec 2, 2016 at 11:14

Dexter0015 · Accepted Answer · 2016-12-02 10:09:00Z

1

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);
}

answered Dec 2, 2016 at 10:09

Dexter0015

1,0299 silver badges14 bronze badges

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

3 Comments

Andy Over a year ago

Yes the query is within a class function - I just omitted this for brevity. Thank you, this will work for my needs.

Coder88 Over a year ago

Does it protect form MySQL injection? Shouldn't there be bindParam? And what if parameters are mixed, like some are integers, others are strings?

Dexter0015 Over a year ago

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: 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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related