Jquery json array from php

Question

I am trying to reproduce the following but my using rows from the database

var allCustomers = [
 { name: 'Customer1', contacts: [
 { name: 'Bob', id: ['1'] },
 { name: 'Sue', id: ['2'] },
 { name: 'John', id: ['3'] }
 ]},
 { name: 'Customer2', contacts: [
 { name: 'Max', id: ['4'] },
 { name: 'Ross', id: ['5'] },
 { name: 'Sally', id: ['6'] }
 ]}
];

In PHP I am fetching the rows from the database, each customer has multiple contacts, which is the bit I am struggling with. Currently I am using the method below:

<script type="text/javascript">
var allCustomers = [
<?php
include('connection.php');
$stmt = $db->prepare("SELECT customer.customerID, customerName, contactID, contactName FROM customer INNER JOIN customerContact ON customer.customerID = customerContact.customerID");
if ($stmt->execute()) 
{
 while ($row = $stmt->fetch(PDO::FETCH_OBJ)) 
 {
 echo "{ name: '".$row->customerName."', contacts: [
 { name: '".$row->contactName."', id: ['".$row->contactID."'] } 
 ]},";
 } 
} 
?> 
];

But obviously this isn't a very neat way of doing this, and it only works if a customer has only one contact, otherwise it reproduces the customer and contact again.

What would you suggest to fix this, could I make use of the php decode json function or something similar?

Thanks for any suggestions :).

I am trying to produce something similar to this post, but I need the id of the contact to post back to the server.

Answer 1

Something like this:

$data = array(
 array("name" => "Customer1", "contacts" => array(
 array("name" => "Bob", "id" => 1),
 array("name" => "Sue", "id" => 2),
 array("name" => "John", "id" => 3)
 )),
 array("name" => "Customer1", "contacts" => array(
 array("name" => "Max", "id" => 4),
 array("name" => "Ross", "id" => 5),
 array("name" => "Sally", "id" => 6)
 ))
);
echo json_encode($data);

In reply to the asker's comment:

$stmt = $db->prepare("SELECT customer.customerID, customerName, contactID, contactName FROM customer INNER JOIN customerContact ON customer.customerID = customerContact.customerID");
$data = array();
if ($stmt->execute()) 
{
 while ($row = $stmt->fetch(PDO::FETCH_OBJ)) 
 {
 if (!isset($data[$row->customerId])
 {
 $data[$row->customerId] = array('name' => $row->customerName, 'contacts' => array());
 }
 $data[$row->customerId]['contacts'][] = array('name' => $row->contactName, 'id' => $row->contactId);
 } 
} 
echo json_encode(array_values($data));

• Elliott: Thanks, I did originally do it this way but am abit confused to how I would loop through the multiple contacts for each customer?
• Elliott: Thanks alot that exactly how I wanted it :) Just one more thing is it possible to not include the customerid when I echo the array back out? Thanks again.
• Imi Borbas: Updated the last row. Hope this way it'll work as you expect.

Answer 2

For create JSON with PHP, you can see this tutorial : http://www.sencha.com/learn/Tutorial:Creating_JSON_Data_in_PHP

Answer 3

Does do everything:

echo json_encode($array);