Using PHP what is the best way to get multiple rows in one MySQL query?

Question

Which is the best way to Get multiple rows in one MySQL query.

I have an array of IDs:

$id_array = array('34','341','342','334','344','354','3234','33234','3234','3234');

I would like to get the title associated with those id's from my mysql database.

I have two approaches:

1) example:

foreach($id_array as $id){
    $query = mysqli_query($con, "SELECT title FROM content WHERE id = '$id'");
    $id_db = mysqli_fetch_row($query);
    echo $id_db['title'];
}

2) example:

$query = mysqli_query($con, "SELECT title FROM content WHERE id = '$id_array[1]' AND id = '$id_array[2]' AND id = '$id_array[3]' AND 'id = $id_array[4]' AND id = '$id_array[5]'");

while($result = mysqli_fetch_assoc($query)){
    echo $result['title'];
}

I am working on high load site and would like to use the best solution. The above code isn't 100% complete, it is just a raw implementation of the idea. The array elements can be from 1 to 1k in count.

second example is not possible via `AND`, use `OR` instead or `IN()` — Royal Bg, Apr 22 '14 at 11:30
Your 2nd example would not work - because you use `AND` instead of `OR`. But what about bind parameters? [Read more](http://www.php.net/manual/en/pdostatement.bindparam.php) — Jurik, Apr 22 '14 at 11:31

score 2 · Accepted Answer · answered Apr 22 '14 at 11:30

2

What about this solution ?

$ids = implode(',',array_map('intval',$id_array));
$query = mysqli_query($con, "SELECT title FROM content WHERE id IN ($ids)");
// ..

answered Apr 22 '14 at 11:30

Abdou Tahiri

4,338
5
25
38

score 1 · Answer 2 · answered Apr 22 '14 at 11:35

1

Use mysql in array method man.

answered Apr 22 '14 at 11:35

jack

473
5
21

score 0 · Answer 3 · answered Apr 22 '14 at 11:33

If you want get titles for ids that are in array:

$db = new mysqli('server', 'user', 'password', 'db');
$stmt = $db->prepare('select title form content where id = ?');
$stmt->bind_param('i', $i);
$stmt->bind_result($title);
$titles = array();
foreach($id_array as $a) {
  $i = $a;
  $stmt->execute();
  array_push($titles, $title);
}

Using PHP what is the best way to get multiple rows in one MySQL query?

3 Answers3