如何在PHP Smarty中优化这段代码？

Question

如何在PHP Smarty中优化这段代码？

现在我有一个代码让我困惑，有一个简单的代码。

$sql_set_land = "select * from set_new_land where id_rent_house =".Tools::getValue("id_rent_house");
            //print_r($sql_set_land);
            $n_land = Db::rowSQL($sql_set_land,true);
            $landTitle1 = $n_land['landTitle1'];
            $landTitle2 = $n_land['landTitle2'];
            $landBuilderNumber = $n_land['landBuilderNumber'];
            $landLandMark = $n_land['landLandMark'];
            $land_1 = $n_land['land_1'];
            $land_2 = $n_land['land_2'];
            $land_3 = $n_land['land_3'];
...
...
...
            $land_30 = $n_land['land_30'];
            $land_31 = $n_land['land_31'];
            $land_32 = $n_land['land_32'];

当我搜索需要推送值的代码时。

 $this->context->smarty->assign([
                'park_space' =>$park_space,
                'recording_data' =>$recording_data,
                'clode_number' => $clode_number,
                'ad_choose_top' => $ad_choose_top,
                'ad_choose_type' => $ad_choose_type,
                'ad_choose_payment_type' => $ad_choose_payment_type,
                'ad_choose_payment_type1' => $ad_choose_payment_type1,
                'landTitle1' => $landTitle1,
                'landTitle2' => $landTitle2,
                'landBuilderNumber' => $landBuilderNumber,
                'landLandMark' => $landLandMark,
                'land_1' => $land_1,
                'land_2' => $land_2,
                'land_3' => $land_3,
...
...
...
                'land_30' => $land_30,
                'land_31' => $land_31,
                'land_32' => $land_32,

优化如何发挥作用？我能写到数组吗？如果它可以写入数组，我该怎么办？

Answer 1

您的问题不在PHP代码中，而是在数据库的设计中。任何时候需要在列名中包含数字都表明您没有对数据进行规范化。

如果您扩展了*（这通常是一种很好的做法，以避免在对数据库进行更改时出现意外），则必须编写以下内容：

select
    landTitle1,
    landTitle2,
    landBuilderNumber,
    landLandMark,
    land_1,
    land_2,
    land_3,
    land_4,
    land_5,
    land_6,
    land_7,
    land_8,
    land_9,
    land_10,
    land_11,
    land_12,
    land_13,
    land_14,
    land_15,
    land_16,
    land_17,
    land_18,
    land_19,
    land_20,
    land_21,
    land_22,
    land_23,
    land_24,
    land_25,
    land_26,
    land_27,
    land_28,
    land_29,
    land_30,
    land_31,
    land_32
from set_new_land 
where id_rent_house = :id

对于一个正常的数据库,您可以编写如下内容：

select
    SND.landTitle1,
    SND.landTitle2,
    SND.landBuilderNumber,
    SND.landLandMark,
    L.landNumber,
    L.land
from set_new_land as SND
join lands as L
   On L.set_new_land_id = SND.set_new_land_id
where SND.id_rent_house = :id

那么在PHP中，可以使用array_column函数：

$lands = array_column($dbResults, 'land');
// or, if the land numbers are important
$lands = array_column($dbResults, 'land', 'landNumber');

但是,如果您无法修复数据,可以在PHP中将其转换为更合理的形式,使用一个从1到32的循环：

$lands = [];
for ( $landNumber=1; $landNumber<=32; $landNumber++ ) {
    $columnName = 'land_' . $landNumber;
    $lands[] = $messyDbResults[ $columnName ];
}