PHP - PDO - How to fetch multiple rows with only one query?

Question

First of all, I'm new to PDO. I'm working on a small project to improve myself.

I'm using a code like this to fetch single row from my database:

    $sql = "SELECT * FROM users WHERE ID = 1";
    $sql_prepare = $db -> prepare($sql);
    $result = $db -> query($sql);
    $user = $result -> fetch(PDO::FETCH_ASSOC);

and echo any result of that row like this:

    echo $user['ID'];
    echo $user['Name'];

---

I need to fetch multiple rows from my database with only using one query. I dont want to query over and over again for each row that I need.

First thing that came in my mind was trying this:

    $sql = "SELECT * FROM users WHERE ID = 1 AND ID = 4 AND ID = 17";
    $sql_prepare = $db -> prepare($sql);
    $result = $db -> query($sql);
    $user = $result -> fetch(PDO::FETCH_ASSOC);

But it didnt work as expected. I researched coding forums for couple of hours but all answers were about using fetchAll method and then outputing results by using foreach loop. I dont want to strain the code by loading whole database table. I just want to load specific rows from my database by using only one query.

So my question is:

How can I fetch multiple and specific rows from my database without using fetchAll method and with only one query?

Thanks for your answers and time in advance.

Sorry if the question was asked before.

Solution 1

You need modify query to

SELECT * FROM users WHERE ID IN(1,4,17);

and use fetchAll() method which returns all records instead of one.

If you don't want to use fetchAll(); then you need use fetch() in loop and you need still modify query.

while ($user = $result->fetch(PDO::FETCH_ASSOC)) {
  print_r($user);
}

Notice: you use prepared statements without parameters.

Solution 2

you should use it like this

$sth = $dbh->prepare('SELECT * FROM users WHERE ID IN(?,?,?)');
    if($sth->execute([1,2,3])) {
        //1,2,3 is the value to be send
        if($sth->rowCount() > 0) {
            while($result = $sth->fetchObject()) {
               print_r($result);
            }
        } else {
            echo 'there are no result';
        }
    } else {
        echo 'there error in the query';
}

there are alot of ways to do this thing but it's just the basics prepare -> execute -> fetch data

Comments

[OnrS90]

Thanks for your answer. This is exactly what I was looking for! Can you also type a simple example on prepared statements with parameters?

[OnrS90]

Thanks a lot! I will upvote your answer when I have 15 reputation. :)

[Robert]

stackoverflow.com/questions/14767530/… here is good example of using prepared statements with in clause

[OnrS90]

Thanks again! I'll check that out. :)