MySQL/PHP:以键=>返回两列作为关联数组.价值对 [英] MySQL/PHP: return two columns as an associative array as key => value pairs

问题描述

我很抱歉是否曾经有人问过这个问题，或者甚至不可能做到这一点，但这是我希望从使用MySQL的特定表中获得的东西.

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.

说，我有下表:

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

我希望能够从 MySQL 获取两个列 property 和 value 作为关联数组，因此最终结果将是是:

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:

我已经知道如何从通用的 SQL 语句返回结果后使用PHP进行此操作，该语句将返回每一行的列为 key =>.值对.我只是想知道是否有可能直接从 MySQL 的每一行返回一列的值作为键，并将第二个值作为键的值返回以直接在 PHP .

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.

推荐答案

没有开箱即用的获取模式可以为您提供这样的索引数组(它怎么知道哪个是关键?如果还有更多，该怎么办?2列?).

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?).

您可以使用 PDO ::FETCH_NUM 和 array_map() ，以第一列为键，第二列为值，构建一个新数组:

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);

侧注::您似乎正进入Entity-Attribute-Value反模式.将会给您带来麻烦，因此请考虑重新设计架构.看看这些资源以了解更多信息:

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:

• https://www.slideshare.net/billkarwin/extensible-data-modeling
• https://martinfowler.com/bliki/UserDefinedField.html
• http://karwin.blogspot.cl/2009/05/eav-fail.html

这篇关于MySQL/PHP:以键=>返回两列作为关联数组.价值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！