从数组值查找数据库记录并将新键和值插入到数组中

Question

I have an array, I would like to add another value to it from a database, using the [numo] value to lookup the record, here is the current array:

Array
(
    [0] => Array
        (
            [numo] => 667820161009
        )

    [1] => Array
        (
            [numo] => 667820112001
        )

and here is what I want to achieve:

Array
(
    [0] => Array
        (
            [id] => 33
            [numo] => 667820161009
        )

    [1] => Array
        (
            [id] => 34
            [numo] => 667820112001
        )

I presume that would have to loop through the array, nesting a SELECT query and then insert a new key and value, but I have no idea where to start and have searched and searched.

Many thanks in advance for you help

Stu

Answer 1

My guess without knowing anything about your DB (or specifically the table that this data comes from) would be:

SELECT id FROM table WHERE numo = 667820112001

Some PHP to go along with this:

<?php
$array = array(
    array(
        'numo' => 667820161009
    ),
    array(
        'numo' => 667820112001
    )
);

$stmt = $pdo->prepare('SELECT id FROM table WHERE numo = :numo');
$stmt->bindColumn('id', $id);

foreach($array as &$child) {
    $stmt->execute(array(
        ':numo' => $child['numo']
    ));

    $child['id'] = ($stmt->fetch(PDO::FETCH_BOUND)) ? $id : null;

    $stmt->closeCursor();
}
?>

However you can eliminate some queries with:

<?php
$array = array(
    array(
        'numo' => 667820161009
    ),
    array(
        'numo' => 667820112001
    )
);

$numos = array()
foreach($array as $child) {
    $numos[] = $child['numo'];
}

$stmt = $pdo->prepare('SELECT id FROM table WHERE numo IN ('.implode(',', $numos).')');
$array = $pdo->fetchAll();
?>