php - 如何在 mysql 中通过所需的列名设置 sql 结果索引值？

Question

如何在 mysql 中根据所需的列名设置 sql 结果索引值？例如

表名:国家

+-----------+----------------+--------------------+
|   id      |     code       |   name             |
+-----------+----------------+--------------------+
|    1      |       88       |   Bangladesh       |
+-----------+----------------+--------------------+
|    2      |      966       |   Saudi Arabia     |
+-----------+----------------+--------------------+
|    3      |      967       |   Yemen            |
+-----------+----------------+--------------------+
|    4      |      963       |   Syria            |
+-----------+----------------+--------------------+
|    5      |      249       |   Sudan            |
+-----------+----------------+--------------------+

建表查询如下:

CREATE TABLE `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(20) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

往表中插入数据如下:

insert  into `country`(`id`,`code`,`name`) values (1,88,'Bangladesh'),
(2,966,'Saudi Arabia'),(3,967,'Yemen'),(4,963,'Syria'),(5,249,'Sudan');

结果会这样显示:

如果我执行下面的查询和我想要的结果如下:

select code, name from country order by code ;

期望的结果:

[88] => Array
    (
        [code] => 88,
        [name] =>  Bangladesh            
    )

[249] => Array
    (
        [code] => 249,
        [name] =>  Sudan            
    )

[963] => Array
    (
        [code] => 963,
        [name] => Syria
    )

[966] => Array
    (
        [code] => 966,
        [name] => Saudi Arabia
    )

[967] => Array
    (
        [code] => 967,
        [name] => Yemen
    )

我需要的数组键将是直接SQL查询的代码。可能吗？

Answer 1

使用 PDO 很容易做到这一点, 它的 PDOStatement::fetchAll()方法和使用 array_column() 构建所需的 key 功能，如以下步骤所述:

1. 使用 DSN、用户名和密码连接到数据库。
2. 查询数据库。
3. 操纵结果。

代码可能是这样的:

const DB_DSN = "mysql:host=mysql.hostname.com;dbname=yourdb;port=3306";
const DB_USERNAME = "username";
const DB_PASSWORD = "P4ssW0rd!";

try
{
    $db = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    $results = $db->query("SELECT code, name FROM country ORDER BY code")->fetchAll(PDO::FETCH_ASSOC);
    $results = array_column($results, null, "code");
}
catch ( PDOException $e )
{
    throw new Exception("An error occurred during the interaction with the database (" . DB_DSN . ").", 0, $e);
}

var_dump($results);

这将为您提供您想要的输出。