PHP从SQL多表取数据报错：未定义变量item1-3的解决

我在SQL中创建了lipstick、liner、brush、eyeshadow四张表，用PHP的Product类获取数据时遇到问题。初始代码只能获取单表数据，修改后尝试同时获取多表数据却报错：第74行未定义变量item1-3。

初始代码（仅能获取单表数据） #

<?php

// Use to fetch product data
class Product
{
    public $db = null;

    public function __construct(DBController $db)
    {
        if (!isset($db->con)) return null;
        $this->db = $db;
    }

    // fetch product data using getData Method
    public function getData($table = 'lipstick', $liner = 'liner', $brush = 'brush', $eyeshadow = 'eyeshadow'){
        $result = $this->db->con->query( "SELECT * FROM {$table}");
        $result1 = $this->db->con->query( "SELECT * FROM {$liner}");
        
        
        $result2 = $this->db->con->query("SELECT * FROM {$eyeshadow}");
        $result3 = $this->db->con->query("SELECT * FROM {$brush}");
                                                                         

        $resultArray = array();

        // fetch product data one by one
        while ($item = mysqli_fetch_array($result, MYSQLI_ASSOC)){
            $resultArray[] = $item;
        }

        return $resultArray;
    }

修改后报错的代码 #

<?php

// Use to fetch product data
class Product
{
    public $db = null;

    public function __construct(DBController $db)
    {
        if (!isset($db->con)) return null;
        $this->db = $db;
    }

    // fetch product data using getData Method
    public function getData($table = 'lipstick', $liner = 'liner', $brush = 'brush', $eyeshadow = 'eyeshadow'){
        $result = $this->db->con->query( "SELECT * FROM {$table}");
        $result1 = $this->db->con->query( "SELECT * FROM {$liner}");
        
        
        $result2 = $this->db->con->query("SELECT * FROM {$eyeshadow}");
        $result3 = $this->db->con->query("SELECT * FROM {$brush}");
                                                                         

        $resultArray = array();

        // fetch product data one by one
        while ($item = mysqli_fetch_array($result, MYSQLI_ASSOC)){
            $resultArray[] = $item;
        }

        return $resultArray;

          // fetch product data one by one
          while ($item1 = mysqli_fetch_array($result1, MYSQLI_ASSOC)){
            $resultArray[] = $item1;
        }

        return $resultArray;
          // fetch product data one by one
          while ($item2 = mysqli_fetch_array($result2, MYSQLI_ASSOC)){
            $resultArray[] = $item2;
        }

        return $resultArray;
          // fetch product data one by one
          while ($item3 = mysqli_fetch_array($result3, MYSQLI_ASSOC)){
            $resultArray[] = $item3;
        }

        return $resultArray;
    }

错误原因 #

1. 提前return导致代码不可达：第一个return $resultArray;执行后，后面的所有循环代码都不会被执行，item1、item2、item3这些变量根本没机会定义，触发未定义变量错误。
2. 代码冗余且结构混乱：重复的return语句和零散的循环写法容易引发逻辑错误。

修正方案 #

方案1：移除多余return，统一处理所有结果集 #

把所有return语句移到最后，依次处理每个查询结果，将数据合并到同一个数组中：

<?php

// Use to fetch product data
class Product
{
    public $db = null;

    public function __construct(DBController $db)
    {
        if (!isset($db->con)) return null;
        $this->db = $db;
    }

    // fetch product data using getData Method
    public function getData($table = 'lipstick', $liner = 'liner', $brush = 'brush', $eyeshadow = 'eyeshadow'){
        // 收集所有查询结果集
        $resultSets = [
            $this->db->con->query("SELECT * FROM {$table}"),
            $this->db->con->query("SELECT * FROM {$liner}"),
            $this->db->con->query("SELECT * FROM {$eyeshadow}"),
            $this->db->con->query("SELECT * FROM {$brush}")
        ];

        $resultArray = array();

        // 遍历所有结果集，合并数据
        foreach ($resultSets as $result) {
            if ($result && $result->num_rows > 0) {
                while ($item = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                    $resultArray[] = $item;
                }
            }
        }

        return $resultArray;
    }
}

方案2：用SQL UNION合并查询（推荐，提升效率） #

如果四张表的字段结构一致，直接用SQL的UNION ALL合并查询，只需要一次数据库请求，效率更高：

<?php

// Use to fetch product data
class Product
{
    public $db = null;

    public function __construct(DBController $db)
    {
        if (!isset($db->con)) return null;
        $this->db = $db;
    }

    // fetch product data using getData Method
    public function getData($table = 'lipstick', $liner = 'liner', $brush = 'brush', $eyeshadow = 'eyeshadow'){
        // 用UNION ALL合并四张表的查询
        $sql = "SELECT * FROM {$table} 
                UNION ALL 
                SELECT * FROM {$liner} 
                UNION ALL 
                SELECT * FROM {$eyeshadow} 
                UNION ALL 
                SELECT * FROM {$brush}";
        
        $result = $this->db->con->query($sql);

        $resultArray = array();

        if ($result && $result->num_rows > 0) {
            while ($item = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                $resultArray[] = $item;
            }
        }

        return $resultArray;
    }
}

注意：使用UNION ALL时，要求所有查询的字段数量、字段类型必须一致；如果字段结构不同，需要明确指定字段名，确保每个SELECT语句的字段列表匹配。

内容的提问来源于stack exchange，提问作者M Taha