处理之前的数据
头和行在一起显示
// 执行SQL查询后的原始数据（假设查询返回3条记录）
$rawData = [['wip_entity_name' => 'JOB001','primary_item' => 'ITEM001','primary_name' => '主产品1','primary_desc' => '主产品描述1','start_quantity' => 100,'quantity_completed' => 50,'segment1' => 'COMP001','item_name' => '组件1','item_desc' => '组件1描述','operation_seq_num' => 10,'required_quantity' => 2],['wip_entity_name' => 'JOB001','primary_item' => 'ITEM001','primary_name' => '主产品1','primary_desc' => '主产品描述1','start_quantity' => 100,'quantity_completed' => 50,'segment1' => 'COMP002','item_name' => '组件2','item_desc' => '组件2描述','operation_seq_num' => 20,'required_quantity' => 1],['wip_entity_name' => 'JOB002','primary_item' => 'ITEM002','primary_name' => '主产品2','primary_desc' => '主产品描述2','start_quantity' => 200,'quantity_completed' => 80,'segment1' => 'COMP003','item_name' => '组件3','item_desc' => '组件3描述','operation_seq_num' => 30,'required_quantity' => 5]
];// 原始数据对应的JSON表示
/*
[{"wip_entity_name": "JOB001","primary_item": "ITEM001","primary_name": "主产品1","primary_desc": "主产品描述1","start_quantity": 100,"quantity_completed": 50,"segment1": "COMP001","item_name": "组件1","item_desc": "组件1描述","operation_seq_num": 10,"required_quantity": 2},{"wip_entity_name": "JOB001","primary_item": "ITEM001","primary_name": "主产品1","primary_desc": "主产品描述1","start_quantity": 100,"quantity_completed": 50,"segment1": "COMP002","item_name": "组件2","item_desc": "组件2描述","operation_seq_num": 20,"required_quantity": 1},{"wip_entity_name": "JOB002","primary_item": "ITEM002","primary_name": "主产品2","primary_desc": "主产品描述2","start_quantity": 200,"quantity_completed": 80,"segment1": "COMP003","item_name": "组件3","item_desc": "组件3描述","operation_seq_num": 30,"required_quantity": 5}
]
*/处理之后的数据
将同样的头规整到一起显示
{"status": "success","message": "数据获取成功","data": [{"wip_entity_name": "JOB001","primary_item": "ITEM001","primary_name": "主产品1","primary_desc": "主产品描述1","start_quantity": 100,"quantity_completed": 50,"lines": [{"segment1": "COMP001","item_name": "组件1","item_desc": "组件1描述","operation_seq_num": 10,"required_quantity": 2},{"segment1": "COMP002","item_name": "组件2","item_desc": "组件2描述","operation_seq_num": 20,"required_quantity": 1}]},{"wip_entity_name": "JOB002","primary_item": "ITEM002","primary_name": "主产品2","primary_desc": "主产品描述2","start_quantity": 200,"quantity_completed": 80,"lines": [{"segment1": "COMP003","item_name": "组件3","item_desc": "组件3描述","operation_seq_num": 30,"required_quantity": 5}]}]
}代码
<?php
// 这里只写数据库部分-查出全部的数据头和行数据
$sql = "SELECT a.wip_entity_name,a.primary_item,c.item_name AS primary_name,c.item_desc AS primary_desc,a.start_quantity,a.quantity_completed,d.segment1,e.item_name,e.item_desc,d.operation_seq_num,d.required_quantity FROM wip_jobs_all aJOIN sf_item_no c ON a.primary_item = c.item_noJOIN wip_material_requierments d ON a.wip_entity_name = d.wip_entity_nameJOIN sf_item_no e ON d.segment1 = e.item_noWHERE 1=1";
// 执行查询
$result = mysqli_query($conn, $sql);
//数据库查询错误提示
if (!$result) {// 查询失败处理$response = ['status' => 'error','message' => '查询失败: ' . mysqli_error($conn),'data' => []];echo json_encode($response);exit;
}
// 处理查询结果
//定义一个空数组
$groupedData = [];
while ($row = mysqli_fetch_assoc($result)) {//以wip_entity_name作为唯一的键名$wipEntity = $row['wip_entity_name'];// 如果该工单尚未初始化，创建头部信息if (!isset($groupedData[$wipEntity])) {$groupedData[$wipEntity] = ['wip_entity_name' => $row['wip_entity_name'],'primary_item' => $row['primary_item'],'primary_name' => $row['primary_name'],'primary_desc' => $row['primary_desc'],'start_quantity' => $row['start_quantity'],'quantity_completed' => $row['quantity_completed'],'lines' => []];}// 添加行项目数据$groupedData[$wipEntity]['lines'][] = ['segment1' => $row['segment1'],'item_name' => $row['item_name'],'item_desc' => $row['item_desc'],'operation_seq_num' => $row['operation_seq_num'],'required_quantity' => $row['required_quantity']];
}// 释放结果集
mysqli_free_result($result);// 将关联数组转换为索引数组（兼容所有PHP版本）
$finalData = array_values($groupedData);// 构建响应
$response = ['status' => 'success','message' => '数据获取成功','data' => $finalData
];// 设置JSON头并输出响应
header('Content-Type: application/json; charset=utf-8');
echo json_encode($response, JSON_UNESCAPED_UNICODE);// 关闭数据库连接
mysqli_close($conn);
?>