4

I'm trying to mesh the below mysql query results into a single json object, but not quite sure how to do it properly.

$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".

I think my question has more to do with Php array manipulation, but I'm not the best with that.

Thank you for any guidance.

Improve this question
1
  • Is the first query ($sql) guaranteed to return at most a single row? Commented Apr 19, 2010 at 18:44

3 Answers 3

Reset to default
6

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:

$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:

    $result = $contractQuery->fetch(PDO::FETCH_ASSOC);
    $result['types'] = array_values($typesQuery->fetchAll(PDO::FETCH_NUM));
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Note that if $contractQuery is guaranteed to return at most one row (contracts.id, jobs.id and companies.id are all unique (which seems likely) and there's a 1-1 relationship between contracts & jobs and contracts & companies), it would make more sense to put the result of $typesQuery as an element of the contracts result rather than a separate element.
Ok I finally got this to work... missing a parenthesis at the end of the second sql statement. But damn, this is bad ass, never learned how to use the PDO queries before, now I think I'll use this from now on. Thank you!
@outis: you're right, I need to put it as an element of contracts
@Dan: typo is now fixed. Prepared statements are a huge win, both in terms of security and efficiency. Read php.net/manual/en/pdo.prepared-statements.php for the details. I should mention that mysqli also supports prepared statements, but PDO is much nicer to deal with. You can even loop over results in a foreach loop: $query->execute(...); foreach ($query as $row) {...} because PDOStatement (php.net/PDOStatement) implements Traversable (php.net/Traversable).
Ahh this is very pro. Thank you so much for the extra fetch line example, I was contemplating how to format the json in php correctly. Now I get to go change all my db queries to the new shinyness!
1

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:

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

ok I did that, and it looks like this: [{"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"}] There can be a variable number of types_id values included (they're checkboxes), so is this the best way to organize the data? Seems to me like it should be more like {"types_id":["2","4","67","43"]} or similar so that it doesn't repeat the key name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.