PHP: Join two separate mysql queries into the same json data object
I'm trying to mesh the below mysql query results into a single json object, but not quite sure how to do it properly.
我正在尝试将下面的mysql查询结果网格化为单个json对象,但不太确定如何正确地执行它。
$id = $_POST['id'];
$sql = "SELECT contracts.po_number, contracts.start_date, contracts.end_date, contracts.description, contracts.taa_required, contracts.account_overdue, jobs.id AS jobs_id, jobs.job_number, companies.id AS companies_id, companies.name AS companies_name
FROM contracts
LEFT JOIN jobs ON contracts.job_id = jobs.id
LEFT JOIN companies ON contracts.company_id = companies.id
WHERE contracts.id = '$id'
ORDER BY contracts.end_date";
$sql2 = "SELECT types_id
FROM contracts_types
WHERE contracts_id = '$id'";
//return data
$sql_result = mysql_query($sql,$connection) or die ("Fail.");
$arr = array();
while($obj = mysql_fetch_object($sql_result)) { $arr[] = $obj; }
echo json_encode($arr); //return json
//plus the selected options
$sql_result2 = mysql_query($sql2,$connection) or die ("Fail.");
$arr2 = array();
while($obj2 = mysql_fetch_object($sql_result2)) { $arr2[] = $obj2; }
echo json_encode($arr2); //return json
Here's the current result:
这是当前的结果:
[{"po_number":"test","start_date":"1261116000","end_date":"1262239200","description":"test","taa_required":"0","account_overdue":"1","jobs_id":null,"job_number":null,"companies_id":"4","companies_name":"Primacore Inc."}][{"types_id":"37"},{"types_id":"4"}]
Notice how the last section [{"types_id":"37"},{"types_id":"4"}] is placed into a separate chunk under root. I'm wanting it to be nested inside the first branch under a name like, "types".
注意最后一节[{“types_id”:“37”},{“types_id”:“4”}]如何放在root下的一个单独的块中。我希望它以名称“types”的形式嵌套在第一个分支中。
I think my question has more to do with Php array manipulation, but I'm not the best with that.
我认为我的问题更多地与Php数组操作有关,但我并不是最好的。
Thank you for any guidance.
谢谢你的任何指导。
3 个解决方案
#1
5
Combine the results into another structure before outputting as JSON. Use array_values
to convert the type IDs into an array of type IDs. Also, fix that SQL injection vulnerability. Using PDO, and assuming the error mode is set to PDO::ERRMODE_EXCEPTION:
在输出为JSON之前,将结果组合到另一个结构中。使用array_values将类型ID转换为类型ID的数组。另外,修复SQL注入漏洞。使用PDO,并假设错误模式设置为PDO :: ERRMODE_EXCEPTION:
$id = $_POST['id'];
try {
$contractQuery = $db->prepare("SELECT contracts.po_number, contracts.start_date, contracts.end_date, contracts.description, contracts.taa_required, contracts.account_overdue, jobs.id AS jobs_id, jobs.job_number, companies.id AS companies_id, companies.name AS companies_name
FROM contracts
LEFT JOIN jobs ON contracts.job_id = jobs.id
LEFT JOIN companies ON contracts.company_id = companies.id
WHERE contracts.id = ?
ORDER BY contracts.end_date");
$typesQuery = $db->prepare("SELECT types_id
FROM contracts_types
WHERE contracts_id = ?");
$contractQuery->execute(array($id));
$typesQuery->execute(array($id));
$result = array();
$result['contracts'] = $contractQuery->fetchAll(PDO::FETCH_ASSOC);
$result['types'] = array_values($typesQuery->fetchAll(PDO::FETCH_NUM));
echo json_encode($result); //return json
} catch (PDOException $exc) {
...
}
If $contractQuery
returns at most one row, change the fetch lines to:
如果$ contractQuery最多返回一行,则将获取行更改为:
$result = $contractQuery->fetch(PDO::FETCH_ASSOC);
$result['types'] = array_values($typesQuery->fetchAll(PDO::FETCH_NUM));
更多相关文章
- 本机PHP函数将授予我直接访问字符串部分而无需创建临时数组的权
- 快速使用数组的最近元素来确定新元素是否唯一
- 填充PHP数组:首先检查索引?
- 如何在php数组中获取php键值
- PHP变量类型+整型类型细节
- 将SQL查询的SELECT子句解析为PHP数组
- PHP不区分大小写数组搜索函数[duplicate]
- 如何找到数组的大小
- PHP:将simpleXML对象转换为二维数组