PHP: accessing mysqli result by record id

Question

i'm having an array which contains record ids as follows:

Array
(
    [0] => 113
    [1] => 43
    [2] => 64
)

so for achieving the corresponding records, i'd have to run 3 queries:

select * from mytable where id=113
select * from mytable where id=43
select * from mytable where id=64

my question: wouldn't it be possible executing just ONE query on the whole table then directly access the mysqli result like an associative array by passing the ID?

something like $record = $res['id'][113];?

thanks in advance

Don't you know "WHERE IN" Sql command ? `select * from mytable where id in (113, 43, 64) ` — Vincent Decaux, May 27 '14 at 16:42
oh great .. thanks :) nevertheless, i'm having several arrays so i'd need to run a query for each array. — Fuxi, May 27 '14 at 16:46

score 2 · Answer 1 · answered May 27 '14 at 16:45

2

You need the IN clause

SELECT * FROM mytable WHERE id IN ( 113,43,64 );

answered May 27 '14 at 16:45

score 0 · Answer 2 · edited May 23 '17 at 10:26

Custom function indexme takes an array of arrays (numeric or associative) and by default gets the first element value of each sub-array and makes it the associative index in a return array. Optionally, a column name can be passed as the second parameter to designate which column value to use for the index.

$array = array(array('id' => 12, 'name' => 'Joe'), array('id' => 9, 'name' => 'Jane'));

$array_keyed = indexme($array);
// > array(12 => array('id' => 12, 'name' => 'Joe'), 9 => array('id' => 9, 'name' => 'Jane'));

print $array_keyed[12]['name'];
// > Joe

function indexme($arr, $key = '') { // <- custom function indexme
  $return_arr = array();
  if ( '' == $key ) {
    $keys = array_keys($arr[0]);
    $key = $keys[0];
  }
  foreach ( $arr as $value ) {
    $return_arr[$value[$key]] =  $value;
  }
  return $return_arr;
}

Pass the mysqli response to this function to make an ID indexed array:

$results_keyed = indexme($result->fetch_assoc(), 'id');

Check out the accepted answer on this page MySQL Prepared statements with a variable size variable list for a nice solution to the WHERE IN technique.

PHP: accessing mysqli result by record id

2 Answers2