CODEWITHSUNDEEP
phppdo
K. P.
K. P.

Reputation: 561

How can I bind one value to all parameters simply in PHP PDO?

I have the following function

function searchusers($username){
    $result=$this->conn->prepare("SELECT username,
        profilimg,
        (SELECT COUNT(title) FROM animelist WHERE mystatus='Watching' AND addedby LIKE CONCAT('%','?','%')) AS watching,
        (SELECT COUNT(title) FROM animelist WHERE mystatus='Completed' AND addedby LIKE CONCAT('%','?','%')) AS completed,
        (SELECT COUNT(title) FROM animelist WHERE mystatus='On-hold' AND addedby LIKE CONCAT('%','?','%')) AS onhold,
        (SELECT COUNT(title) FROM animelist WHERE mystatus='Dropped' AND addedby LIKE CONCAT('%','?','%')) AS dropped,
        (SELECT COUNT(title) FROM animelist WHERE mystatus='Plan to watch' AND addedby LIKE CONCAT('%','?','%')) AS plantowatch,
        (SELECT COUNT(title) FROM animelist WHERE addedby LIKE CONCAT('%','?','%')) AS all,
        (SELECT COUNT(title) FROM animelist WHERE addedby LIKE CONCAT('%','?','%') AND favourite='1') AS favourites,
        (SELECT COUNT(subject) FROM comments WHERE name LIKE CONCAT('%','?','%')) AS allcomments,
        (SELECT COUNT(review) FROM reviews WHERE addedby LIKE CONCAT('%','?','%')) AS allreviews,
        (SELECT AVG(myscore) FROM animelist WHERE addedby LIKE CONCAT('%','?','%')) AS meanscore
        FROM user WHERE username LIKE CONCAT('%','?','%') LIMIT 120");
    $result->bindparam(1,$username);
    $result->bindparam(2,$username);
    $result->bindparam(3,$username);
    $result->bindparam(4,$username);
    $result->bindparam(5,$username);
    $result->bindparam(6,$username);
    $result->bindparam(7,$username);
    $result->bindparam(8,$username);
    $result->bindparam(9,$username);
    $result->bindparam(10,$username);
    $result->bindparam(11,$username);
    $result->execute();
    return $result->fetchall();
}

As you can see, I bind the same value to 11 parameters, but I do it by repeating $result->bindparam(1,$username); 11 times, which looks quite terrible.

What I'm asking is, if there's a better solution for binding one value to all parameters, instead of repeating the bindparam so many times?

I could probably solve this easier with named placeholders instead of question marks, but that's not the solution I'm looking for now.

I think there is a way to solve this with a for/foreach, but I don't know how it should look like.

Upvotes: 4

Views: 86

Answers (2)

Webber
Webber

Reputation: 5494

While the other answer is technically correct and what you're asking for; in the context of your question and the provided code it is not the best solution, as it is generally not good practice to use a loop to bind parameters.

Instead i'd recommend you simply use the same parameter and bind it once (or change your question).

To do that use named parameters: instead of ? use :parameterName

Then bindparam('parameterName', $yourVariable). (see official docs).

Perhaps you could extend your question to make the use case more clear. Until then named parameters are the best solution.

Upvotes: 2

pr1nc3
pr1nc3

Reputation: 8338

for($i=0; $i<11; $i++){
    $result->bindparam($i,$username);
}
return $result->fetchall();

Since you know that there are 11 bind params you can simple use a counter.

Upvotes: 2

Related Questions