Array and Sub Array from PHP

[jrahma]

Hi,

I have the following SQL:

SELECT customers_address.customer_address_id, address_category.address_category_name, customers_address.address_name,
address_details.address_details_id,
address_details.address_details_text
FROM customers_address
JOIN address_category ON address_category.address_category_id = customers_address.address_category_id
LEFT JOIN address_details ON address_details.customer_address_id = customers_address.customer_address_id
WHERE customer_id = param_customer;

I want to show this output in JSON:

CustomerAddress:
{
customer_address_id
address_category_name
address_name
}
Details:
{
address_details_id
address_details_text
}

my current php is:

$mysql_query = $mysql_connection->prepare('CALL sp_populate_customer_addresses(:param_customer)');
$mysql_query->bindParam(':param_customer', $customer_id, PDO::PARAM_STR);
$mysql_query->execute();

if ($mysql_query->rowCount() <= 0) { echo "false"; }
else
{
  $jsonData = array();

  while($mysql_row = $mysql_query->fetch())
  {
      $jsonData[] = $mysql_row;
  }

echo json_encode($jsonData);
}

Thanks,
Jassim

[laserlight]

Your proposed JSON output looks invalid though: it looks like you have an object consisting of two name/value pairs, but the name/value pairs are not enclosed in a pair of braces to denote an object, and then the names are not quoted as they should be to be strings. Then, each of the values looks like an array, but instead of the brackets used to enclose the values of an array, you used braces, and furthermore missed out the commas that separate the values. Perhaps you had this in mind instead:

{
    "CustomerAddress": [customer_address_id,
                        address_category_name,
                        address_name],
    "Details": [address_details_id,
                address_details_text]
}

Furthermore, my guess is that this is an example of what a single row in the database result set would correspond to in JSON, i.e., you would have an array of these objects. If so, it seems that you want:

$jsonData = array();

while ($mysql_row = $mysql_query->fetch())
{
    $jsonData[] = array(
        'CustomerAddress' => array($mysql_row['customer_address_id'], etc),
        'Details' => array($mysql_row['address_details_id'], etc)
    );
}

echo json_encode($jsonData);

[jrahma]

{
"CustomerAddress": [customer_address_id,
address_category_name,
address_name],
"Details": [address_details_id,
address_details_text]
}

yes this is the format I am trying to get

but where is the fields:

• customer_address_id

• address_category_name

• address_name

in your code?

[laserlight]

jrahma;11062465 wrote:

yes this is the format I am trying to get

but where is the fields:

• customer_address_id

• address_category_name

• address_name

in your code?

You should read my example code again, and note that "etc" refers to code that you should fill in yourself. I mean, the text "customer_address_id" is right in my code snippet. Basically, an associative array in PHP corresponds to an object in JSON; a numerically indexed array in PHP corresponds to an array in JSON. So if you want the corresponding JSON output, construct the corresponding PHP structures.

[jrahma]

sorry I missed it.

I tried your code with little modification to show the Details under CustomerAdress but I am getting ugly result like this:

"CustomerAddress": {
"0": "customer_address_id:101",
"1": "address_category_name😮ther",
"2": "address_name: Nasser Vocational Training Center, Jao",
"Details": ["address_details_id:", "address_details_text:ADD-101-1"]
}

you can see that there 0, 1 and 2 where I just need the "title": "value", e.g. "customer_address_id": "101"

also the adress_details_id and adress_details_text is mixed

Here is my PHP:

$jsonData = array();

while ($mysql_row = $mysql_query->fetch())
{
    $jsonData[] = array(
        'CustomerAddress' => 
        array("customer_address_id:" . $mysql_row['customer_address_id'], "address_category_name:" . $mysql_row['address_category_name'], "address_name:" . $mysql_row['address_name'],
        'Details' => 
        array('address_details_id:' . $mysql_row['address_details_id'], 'address_details_text:' . $mysql_row['address_details_text'])
        )
    );
}

[laserlight]

jrahma wrote:

you can see that there 0, 1 and 2 where I just need the "title": "value", e.g. "customer_address_id": "101"

That's because you have wrong nesting. Look at your code, properly formatted:

while ($mysql_row = $mysql_query->fetch())
{
    $jsonData[] = array(
        'CustomerAddress' => array(
            "customer_address_id:" . $mysql_row['customer_address_id'],
            "address_category_name:" . $mysql_row['address_category_name'],
            "address_name:" . $mysql_row['address_name'],
            'Details' => array(
                'address_details_id:' . $mysql_row['address_details_id'],
                'address_details_text:' . $mysql_row['address_details_text']
            )
        )
    );
}

As you can see, you have an associative array with a single element, the key 'CustomerAddress' referring to the value that is itself an associative array consisting of three elements followed by the key 'Details' referring to another array. What you want is an associative array with two elements, one with the key 'CustomerAddress' and the other with the key 'Details'.

By the way, if yoou actually want "title": "value", e.g. "customer_address_id": "101", then the inner arrays should themselves be associative arrays, not single string values concatenated from the database result values.

[jrahma]

i changed the code to avoid an array in another array as you mentioned and I got the correct format

{
"CustomerAddress": {
"customer_address_id": "101",
"address_category_name": "Other",
"address_name": "Nasser Vocational Training Center, Jao",
"Details": {
"address_details_id": "1",
"address_details_text": "ADD-101-1"
}
}
}, {
"CustomerAddress": {
"customer_address_id": "101",
"address_category_name": "Other",
"address_name": "Nasser Vocational Training Center, Jao",
"Details": {
"address_details_id": "2",
"address_details_text": "ADD-101-2"
}
}
}

but there is one more problem, each CustomerAddress will have one or more Details as you can see in the attached screenshot.. so CustomerAddress 101 should have 4 Details for example. but the result of my code showing every Details separatly with the CustomerAddress

This is the updated code:

while ($mysql_row = $mysql_query->fetch())
{
  $jsonData[] = array(
      'CustomerAddress' => 
      array('customer_address_id' => $mysql_row['customer_address_id'],
      'address_category_name' => $mysql_row['address_category_name'],
      'address_name' => $mysql_row['address_name'],
      'Details' => array(
      'address_details_id' => $mysql_row['address_details_id'],
      'address_details_text' => $mysql_row['address_details_text'])
      )
  );
}

Thanks
Jassim

[laserlight]

If each customer can have multiple sets of details, then your JSON string surely would have a slightly different format: how do you intend to represent the multiple details? In a JSON array of objects, each of which corresponding to one set of details?

This probably also means that your initial approach of appending as you iterate through the database result set would need to be tweaked, e.g., you may need to check the existing array to see if the current customer exists, and if it does, you add the details instead of adding a customer.

[jrahma]

it's like this as an example.. If you see the first customer has two details for this address while the second customer has only one.. so it depends how many but it should list all Details related to the CustomerAddress

{
"CustomerAddress": {
"customer_address_id": "101",
"address_category_name": "Other",
"address_name": "Nasser Vocational Training Center, Jao",
"Details": {
"address_details_id": "1",
"address_details_text": "ADD-101-1"
},
{
"address_details_id": "2",
"address_details_text": "ADD-101-1"
}
}
}, {
"CustomerAddress": {
"customer_address_id": "101",
"address_category_name": "Other",
"address_name": "Nasser Vocational Training Center, Jao",
"Details": {
"address_details_id": "7",
"address_details_text": "ADD-101-2"
}
}
}

[Weedpacket]

You mean (see the FAQs about using [noparse]

[/noparse] tags to format code):
[code]
{
	"CustomerAddress": {
		"customer_address_id": "101",
		"address_category_name": "Other",
		"address_name": "Nasser Vocational Training Center, Jao",
		"Details": {
			"address_details_id": "1",
			"address_details_text": "ADD-101-1"
		},
		{
			"address_details_id": "2",
			"address_details_text": "ADD-101-1"
		}
	}
},
{
	"CustomerAddress": {
		"customer_address_id": "101",
		"address_category_name": "Other",
		"address_name": "Nasser Vocational Training Center, Jao",
		"Details": {
			"address_details_id": "7",
			"address_details_text": "ADD-101-2"
		}
	}
}

That's not valid JSON any more, either, since the second [font=monospace]{"address_details_id": "2", "address_details_text": "ADD-101-1"}[/font] doesn't have a key. Like laserlight said, if you want an arbitrary number of such pairs you'll need to list them in an array and make that array the value of the "CustomerAddress" field.

[jrahma]

sorry I didn't understand what you mean exactly... but I want the same structure in json so every CustomerAddress will have one or more Details.. This is what I want PHP to output for me..

How can I do that please..

[laserlight]

jrahma wrote:

sorry I didn't understand what you mean exactly...

Your example JSON output is invalid. That's what it means. It is not valid JSON, i.e., it cannot possibly be the return value of a correctly written json_encode function, so what you are asking for, as-is, is impossible, unless there is a serious bug in PHP's json_encode function implementation, or you don't use json_encode and instead manually construct an invalid JSON output.

The syntax of JSON is succinctly described at json.org. I suggest that you go over that carefully before you construct further JSON examples.

jrahma wrote:

but I want the same structure in json so every CustomerAddress will have one or more Details.. This is what I want PHP to output for me..

I already suggested how you might do that in post #8.

[jrahma]

Sorry this is the valid json but I didn't get what you mean in #8 if you kindly explain it more because I never done the PHO->to->JSOM before.

Thanks..

{
   "CustomerAddress":[
      {
         "customer_address_id":"101",
         "address_category_name":"Other",
         "address_name":"Nasser Vocational Training Center, Jao",
         "Details":[
            {
               "address_details_id":"1",
               "address_details_text":"ADD-101-1"
            },
            {
               "address_details_id":"2",
               "address_details_text":"ADD-101-1"
            }
         ]
      },
      {
         "customer_address_id":"109",
         "address_category_name":"Other",
         "address_name":"Nasser Vocational Training Center, Jao",
         "Details":[
            {
               "address_details_id":"7",
               "address_details_text":"ADD-101-2"
            }
         ]
      }
   ]
}

[laserlight]

jrahma wrote:

Sorry this is the valid json but I didn't get what you mean in #8 if you kindly explain it more because I never done the PHO->to->JSOM before.

My suggestion in post #8 is all about PHP, not JSON. You have a PHP associative array, with each element of the associative array containing a PHP numeric array. As you iterate over the database result set, you want to either insert another element into the associative array (a new customer address) or insert another element into the appropriate numeric array (details for the customer address). How are you going to do this?

[jrahma]

laserlight;11062507 wrote:

My suggestion in post #8 is all about PHP, not JSON. You have a PHP associative array, with each element of the associative array containing a PHP numeric array. As you iterate over the database result set, you want to either insert another element into the associative array (a new customer address) or insert another element into the appropriate numeric array (details for the customer address). How are you going to do this?

can I have a sample code please.. it seems complicated for me for first time 🙁 huhuhu

Thanks..