是否有更优雅的方式来格式化PHP中的MySQL结果？

I just asked myself, after a few years of development, if there is a more elegant way of formatting MySQL results into associative arrays like the one below.

经过几年的开发,我只是问自己,如果有一种更优雅的方式将MySQL结果格式化为关联数组,如下所示。

Here some dummy code that shows how I usually do it.

这里有一些虚拟代码,显示了我通常如何做到这一点。

$sql = 'SELECT field1, field2 FROM sample_table';
$res = $db->prepare($sql)->getAll();
$formatted = array();

foreach ($res as $row) {
    $formatted[$row['field1']] = $row['field2'];
}

As I'm doing things like these super often, I asked myself if there is a more elegant or quicker way.

由于我经常做这些事情,我问自己是否有更优雅或更快捷的方式。

Thanks!

1 个解决方案

#1

You can create a class to handle the recurring tasks. The idea is to encapsulate the behaviors that you can reuse later with the minimum amount of code. Here's a basic example. Keep in mind that you may want to delegate the database stuff (connection, queries, etc.) to another class. Also keep in mind that this class is specific to an array with key-value pairs (to be use with a query with 2 columns).

您可以创建一个类来处理重复任务。我们的想法是封装您可以在以后使用最少量代码重用的行为。这是一个基本的例子。请记住,您可能希望将数据库内容(连接,查询等)委托给另一个类。另请注意,此类特定于具有键值对的数组(用于具有2列的查询)。

<?php

//Takes a two columns SQL query and format it to optain an array with key-value pair
class queryToMap{

    private $formattedArray = array();  //The formated array


    //Execute the query and format the array
    public function executeQuery($sql){

        $res = $this->dbQuery($sql);

        while($row = $res->fetch_array()){

            $this->formattedArray[$row[0]] = $row[1];

        }
    }

    //Returns the formated array
    public function getArray(){
        return $this->formattedArray;
    }

    //Execute query and return the result
    private function dbQuery($sql){
        //DB connection...
        $db = new mysqli("localhost", "bob", "123", "db");


        //if the query is based on user input, better use prepared statement 
        $res = $db->query($sql);

        return $res;
    }


}


//Use of the class
$a = new queryToMap();
$a->executeQuery("SELECT F_CUST_ID, F_CUST_NAME FROM TP_CUSTOMER");

//Output the array
var_dump($a->getArray());