MySQL/PHP: return two columns as an associative array as key => value pairs [duplicate]

Question

I apologize if this might have been asked before, or if this isn't even possible, but here is what I am hoping to get from a particular table using MySQL.

Say, I have the following table:

+-------------+------------------+
| property    | value            |
+-------------+------------------+
| style       | unisex           |
| color       | blue             |
| type        | small            |
+-------------+------------------+

I would like to be able to fetch the two columns property and value as an associative array from MySQL, so the end result would be:

array(
    'style' => 'unisex',
    'color' => 'blue',
    'type'  => 'small',
);

Just to provide context so people don't misinterpret what I'm asking:

I already know how I can do this using PHP once I have the result back from a generic SQL statement that would return each row's columns as key => value pairs. I was just wondering if it was possible to return one column's value as a key and the second value as the value to the key for each row from MySQL directly to continue with in PHP.

You can certainly write code that will translate a result set like that. — Sammitch
– Sammitch, Commented Oct 18, 2017 at 17:23
I know how to write code in PHP to accomplish that once I have the result from the MySQL query. I was just wondering if it was possible to do it from SQL directly. — Kamran Khan
– Kamran Khan, Commented Oct 18, 2017 at 17:32

ishegg · Accepted Answer · 2017-10-18 17:30:25Z

There is no out of the box fetch mode that will give you an array indexed like that (how would it know which is the key? what if there's more than 2 columns?).

You can use PDO::FETCH_NUM and array_map() to build a new array, using the first column as key and the second as value:

<?php
$query = $db->prepare("SELECT property, value FROM table");
$query->execute();
$row = $query->fetchAll(PDO::FETCH_NUM);
$rowFormatted = array_map(function ($el) {
    return [$el[0] => $el[1]];
}, $row);
var_dump($rowFormatted);

Sidenote: it looks like you're heading into an Entity-Attribute-Value antipattern. This will bring you problems along the road, so consider redesigning your schema. Take a look at this resources to learn more:

NightOwlPrgmr · Accepted Answer · 2020-06-24 14:02:43Z

1

Old question, but thought I'd at least provide a MySQL only solution since that is what was asked for:

$stmt = $db->prepare('SELECT `property`, `value` FROM `table` WHERE 1');
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

$results will be formatted as you're expecting - an array with property as the key and value as the value.

Resource: https://www.php.net/manual/en/pdo.constants.php

edited Jun 24, 2020 at 14:02

answered Jun 24, 2020 at 13:40

NightOwlPrgmr

1,3743 gold badges22 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL/PHP: return two columns as an associative array as key => value pairs [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related