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

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));

#2

It would seem like you'd be better served by consolidating the two queries with a JOIN at the SQL level. However, assuming the two arrays have equal length:

通过在SQL级别使用JOIN合并两个查询,您似乎可以获得更好的服务。但是,假设两个数组的长度相等:

for ($x = 0, $c = count($arr); $x < $c; $x++) {
    if (isset($arr2[$x])) {
        $arr[$x] += $arr2[$x];
    }
}

echo json_encode($arr);

Edit: you would need to change from mysql_fetch_object to mysql_fetch_assoc for this to work properly.

编辑:您需要从mysql_fetch_object更改为mysql_fetch_assoc才能使其正常工作。

#3

Why are you using 2 distinct arrays ? I would simply add the rows of the 2nd query in $arr instead of $arr2. This way, you end up with a single array containing all rows from the 2 queries.

你为什么使用2个不同的数组?我只想在$ arr而不是$ arr2中添加第二个查询的行。这样,您最终得到一个包含2个查询中所有行的数组。