如何获取SQL数据并格式化为Grid.js表格所需的二维数组

需求说明 #

我需要从SQL数据库获取数据，生成Grid.js表格要求的嵌套二维数组格式，但当前编写的PHP mysqli代码无法正常运行，求正确的实现方案。

目标数据格式 #

[
    ["Marketing Directortt", "Meta4Systems", "Vinninga, Sweden", "$250 - $800", "0-5 year", "Full Time"],
    ["UI/UX designer", "Zoetic Fashion", "Cullera, Spain", "$400+", "0-2 year", "Part Time"],
    ["Web Designer", "Force Medicines", "Ugashik, US", "$412 - $241 ", "3+ year", "Freelancer"],
    ["Full Stack Engineer", "Syntyce Solutions", "Zuweihir, UAE", "$650 - $900", "0-1+ year", "Full Time"],
    ["Assistant / Store Keeper", "Moetic Fashion", "Limestone, US", "$340 - $800", "0-3 year", "Intership"],
    ["Project Manager", "Themesbrand", "California, US", "$400 - $600", "3+ year", "Part Time"],
    ["Education Training", "Micro Design", "Germany", "$750 - $940", "1.5+ year", "Freelancer"],
    ["Graphic Designer", "Digitech Galaxy", "Mughairah, UAE", "$160 - $230", "2-3+ year", "Full Time"],
    ["React Developer", "iTest Factory", "KhabÄkhib, UAE", "$90 - $160", "5+ year", "Intership"],
    ["Executive, HR Operations", "Micro Design", "Texanna, US", "$50 - $120", "1-5 year", "Part Time"],
    ["Project Manager", "Meta4Systems", "Limestone, US", "$210 - $300", "0-2+ year", "Freelancer"],
    ["Full Stack Engineer", "Force Medicines", "Ugashik, US", "$120 - $180", "2-5 year", "Part Time"],
    ["Full Stack Engineer", "Digitech Galaxy", "Maidaq, UAE", "$900 - $1020", "3-5 year", "Full Time"],
    ["Marketing Director", "Zoetic Fashion", "Quesada, US", "$600 - $870", "0-5 year", "Freelancer"]
]

当前尝试的PHP代码 #

$resultset=mysqli_query($conn, "SELECT * FROM `campgain` ORDER BY id DESC");         

while( $record = mysqli_fetch_assoc($resultset) ) {
    
    $data.='['.$record[row1].','.$record[row2].','.$record[row3].','.$record[row4].'],';
}

$data="[$data]";

echo $data;

问题分析 #

原代码存在以下几个导致格式错误的问题：

• 字段名未加引号：$record[row1]会被PHP当作常量解析，正确写法应为$record['row1']
• 未给字符串添加双引号：目标格式中每个元素都是带双引号的字符串，手动拼接时缺少引号会导致JSON格式无效
• 循环结束后末尾多一个逗号：最后一条记录后会残留逗号，导致整个数组格式错误
• 手动拼接字符串容易出现格式疏漏，不如用PHP内置的JSON生成函数可靠

正确实现方案 #

使用PHP数组存储数据，最后通过json_encode生成标准的JSON格式，代码如下：

// 初始化空数组
$data = [];

$resultset = mysqli_query($conn, "SELECT row1, row2, row3, row4, row5, row6 FROM `campgain` ORDER BY id DESC");         

while ($record = mysqli_fetch_assoc($resultset)) {
    // 将每条记录的字段按顺序存入子数组
    $data[] = [
        $record['row1'],
        $record['row2'],
        $record['row3'],
        $record['row4'],
        $record['row5'], // 根据目标格式补充对应字段
        $record['row6']
    ];
}

// 输出标准JSON，JSON_UNESCAPED_UNICODE确保中文等非ASCII字符正常显示
echo json_encode($data, JSON_UNESCAPED_UNICODE);

代码说明 #

1. 先创建空数组$data用于存储所有行数据
2. SQL查询时建议明确指定需要的字段（而非SELECT *），避免无关数据干扰
3. 循环读取每条记录，将需要的字段按目标格式的顺序存入子数组，再添加到$data中
4. 使用json_encode将PHP数组转换为标准JSON字符串，自动处理引号、逗号等格式问题，确保输出符合Grid.js要求的嵌套二维数组格式

内容的提问来源于stack exchange，提问作者Sukrat Chakravarti