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.

FetchAll doesn't make requests to your database. It operates on the result of your query. If your query returns one result then fetchall will give you one result. If it returns 2 then so will fetchall. — Daniel Nielson
– Daniel Nielson, Commented Aug 12, 2015 at 21:05
You can also do change the query to ID = 1 OR ID = 4 OR ID = 17... — Erk
– Erk, Commented Jan 28, 2017 at 23:45

Robert · Accepted Answer · 2015-08-12 20:57:44Z

14

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.

answered Aug 12, 2015 at 20:57

Robert

20.3k6 gold badges58 silver badges85 bronze badges

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

3 Comments

OnrS90 Over a year ago

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

Robert Over a year ago

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

OnrS90 Over a year ago

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

Robert · Accepted Answer · 2015-08-12 22:29:58Z

7

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

answered Aug 12, 2015 at 22:29

Robert

2,9103 gold badges29 silver badges50 bronze badges

1 Comment

OnrS90 Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related