CODEWITHSUNDEEP
phpmysqlsqlarrayspdo
Chris Camacho
Chris Camacho

Reputation: 1174

php (PDO) simple way to turn rows in lookup table into simple array

given a very simple table structure thus:

mysql> describe songpart;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | MUL | NULL    | auto_increment |
| partName | text    | NO   |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

which results in an array like this in php (when queried)

Array ( [0] => Array ( [id] => 1 [0] => 1 [partName] => Lead Guitar [1] => Lead Guitar ) 
        [1] => Array ( [id] => 2 [0] => 2 [partName] => Bass Guitar [1] => Bass Guitar ) 
        [2] => Array ( [id] => 3 [0] => 3 [partName] => Drums [1] => Drums ) 
        [3] => Array ( [id] => 4 [0] => 4 [partName] => Keyboard [1] => Keyboard ) )

Am I missing some simple trick to turn this into a simple array with id as the key like so:

Array ( [1] => Lead Guitar
        [2] => Bass Guitar
        [3] => Drums
        [4] => Keyboard )

or is it possible to get PDO to deliver an array like this?

TiA

Upvotes: 2

Views: 289

Answers (3)

Elwinar
Elwinar

Reputation: 9519

You can simply use the PDO::FETCH_KEY_PAIR is you have only 2 columns in your result.

You can also use the PDO::FETCH_GROUP and PDO::FETCH_ASSOC together if you have more. Example :

$result = $db->query('select * from channels')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

This will yield an array indexed with the first column containing at each index an array of the result for this key. You can fix this by using array_map('reset', $result) to get your goal.

Example :

$result = array_map('reset', $db->query('select * from channels')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC));

Upvotes: 5

fortune
fortune

Reputation: 3382

Try this: 

$records = $pdo->query('SELECT id, partName FROM myTable');
$records->setFetchMode(PDO::FETCH_KEY_PAIR);
print_r($records->fetchAll());

Upvotes: 3

Ranjith
Ranjith

Reputation: 2819

For that you need to set only your desired fields in iteration.

$part_name = [];

$records = $pdo->query('SELECT * FROM your_table');
foreach ($records as $row) {
    $part_name[$row['id']] = $row['partName'];
}

Upvotes: 0

Related Questions