[SOLVED] Help with php, JSON and array that has arrays

Basher52 · 10-16-2015, 05:03 PM

Hi

I've been trying a long time now to create a JSON output that has the "correct" layout of what I'm looking for but 'nada' so far

The tables I get the data from is from MySQL, or better yet MariaDB :P
It's the typical Head and Rows situation. I got an Order table and an OrderRows table and it's no problem joining these in SQL but the list from it is as you know, ALL header data for ALL the rows are shown in the result.

I copied this example from a page I saw some days ago, the code for it won't work but the output it perfect

(I think)
This is an example of a JSON output and it's this layout of it I want to create (or hope it is):

Code:

{
  "photoOfDayList": [
    {
      "photoOfDay": {
        "photoTitle": "Title",
        "photoId": id,
        "dateTime": "date",
        "thumbImg_Phone": "$url/photo.jpg",
        "thumbImg_Tablet": "$url/photo.jpg",
        "img_Phone": "$url/photo.jpg",
        "img_Tablet": "$url/photo.jpg"
      },
      {
        "photoTitle": "Title",
        "photoId": id,
        "dateTime": "date",
        "thumbImg_Phone": "$url/photo.jpg",
        "thumbImg_Tablet": "$url/photo.jpg",
        "img_Phone": "$url/photo.jpg",
        "img_Tablet": "$url/photo.jpg"
      },
      .
      .
      .
      .
      {
        "photoTitle": "Title",
        "photoId": id,
        "dateTime": "date",
        "thumbImg_Phone": "$url/photo.jpg",
        "thumbImg_Tablet": "$url/photo.jpg",
        "img_Phone": "$url/photo.jpg",
        "img_Tablet": "$url/photo.jpg"
      }
    }
  ]

I need the layout of the data for an Android App I'm trying to create that'll show the header data 'once' and ALL it's rows in a ListView with a custom adapter.

My question now is, how do I create this in php, cos with pure SQL I can't fix it.
This is the latest code I tried:

Code:

$charset="SET NAMES 'utf-8'";
setlocale(LC_ALL, 'sv_SE.UTF8');
$result=$mysqli->query(@charset);
$sql="SELECT * FROM ec_order, ec_orderrows WHERE ec_order.Id = ec_orderrows.OrderId";
$result=$mysqli->query($sql);
while ($rows=mysqli_fetch_assoc($result)) {
$rows['ArticleName']));
$output[] = array('X' => array('BillingAddress' => $rows['BillingAddress'], array('ArticleNo' => $rows['ArticleNo'], 'Quantity' => $rows['Quantity'])));
}

/*
$output[]= array(
        $rows['DeliveryAddress'],
        array(mb_convert_encoding($rows['ArticleName'],'UTF-8', 'UTF-8')),
        array($rows['Quantity']),
        array($rows['ArticleNo']),
        );
}*/
header("Content-type: application/json; charset=utf-8");
print(json_encode($output));
$mysqli->close();

and with this I get this output:

Code:

[{"X":{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-001","Quantity":"5"}}},{"X":{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-002","Quantity":"10"}}},{"X":{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-005","Quantity":"20"}}}]

and as you see this is NOT the correct output.
The 'X' is there just to see what I get and where.
What I need is to get ONE!! 'BillingAddress' and it's value with ALL it's rows and as you see, this output is exactly the same as the SQL output.

PS.
As you might see, I'm from Sweden and we got some 3 extra special letters that you US/UK/AUS/etc people don't and as you also might see I got some code in there trying to fix the JSON_ENCODE-error(warning) I get for one of these characters, so if you can help out with that too would be perfect

I sure can tell you I tried lots of the google-result I found but 'nada' AGAIN :P

//Thanks In Advance

Smokey_justme · 10-16-2015, 05:28 PM

Well, you are the one putting the X there every single time..

Replace the while part of your code with the one bellow.. Is this what you want?

PHP Code:



while ($rows=mysqli_fetch_assoc($result)) {
$output[] = array('BillingAddress' => $rows['BillingAddress'], array('ArticleNo' => $rows['ArticleNo'], 'Quantity' => $rows['Quantity']));
}
$output = array('X' => $output);

Basher52 · 10-16-2015, 07:53 PM

Thx mokey_justme but this seems to gimme the same result:
This is what I get from it:

Code:

[{"X":{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-001","Quantity":"5"}}},{"X":{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-002","Quantity":"10"}}},{"X":{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-005","Quantity":"20"}}}]

and with your code I've replaced this(You'll see in the remarked what I did)

Code:

//while ($rows=mysqli_fetch_assoc($result)) {
////$arr['OrderHeader'][] = array('OrderRows' => array('rader' => $rows['ArticleNo'], 'namn' => $rows['ArticleName']));
//$output[] = array('X' => array('BillingAddress' => $rows['BillingAddress'], array('ArticleNo' => $rows['ArticleNo'], 'Quantity' => $rows['Quantity'])));
//}
while ($rows=mysqli_fetch_assoc($result)) {
$output[] = array('BillingAddress' => $rows['BillingAddress'], array('ArticleNo' => $rows['ArticleNo'], 'Quantity' => $rows['Quantity']));
}
$output = array('X' => $output);

Another question that comes to mind is, did I not explain what I really want enough?

On my phone I want to switch between several "customers"(the head) and for each of those, the several items they bought (the rows)

Smokey_justme · 10-16-2015, 08:03 PM

Hmm,
Sorry but there must be some mistake since in my code "X" is not repeating and can not give exactly the same result, thus the output should be something like this:

Code:

{"X": [{"BillingAddress":"VI betalar","0":{"ArticleNo":"200-001","Quantity":"5"}} ... ]}

Pretty much as you wanted it, as I undertand it.. Are you sure you're editing the file on the test server?

Basher52 · 10-16-2015, 08:24 PM

I might have written 'stuff' in here wrong, I don't know, but what I need is:
ONE!! 'head'(="BillingAddress") and several 'rows' of 'ArticleNo'.

These things, 'BillingAddress', 'ArticleNo' are example column from each table.
'BillingAddress' is just ONE column from the 'HEAD' and 'ArticleNo' is just ONE column from the 'ROWS' of the previous 'HEAD'.

PS. the 'X' I just put in there 'cos I saw it in an example but it didn't make it work.

Smokey_justme · 10-16-2015, 08:39 PM

Maybe try this?

PHP Code:



while ($rows=mysqli_fetch_assoc($result)) {
$output[$rows['BillingAddress']][] =array(array('ArticleNo' => $rows['ArticleNo'], 'Quantity' => $rows['Quantity']));
}

Basher52 · 10-16-2015, 09:03 PM

Just tried this and THIS is promising

Seems the result is what I want too

(well I hope so, this all depends on that my thoughts about how the data into the Android App is supposed to be is correct)

I'm gonna try this with more "heads" and "rows" per 'head' and if that'll work.............. You just won a toaster :P

Basher52 · 10-16-2015, 09:07 PM

*and later I gotta READ your post and try to understand it, but that'll be tomorrow or such.
NOW, I gotta be free from "work"

(although I don't consider computer work WORK :P )

Basher52 · 10-17-2015, 12:59 PM

This is the output when I test the code

Code:

{"adr xxxx":[[{"ArticleNo":"200-001","Quantity":"5"}],[{"ArticleNo":"200-002","Quantity":"10"}],[{"ArticleNo":"200-005","Quantity":"20"}]]}

@Smokey_justme, how can I show the column names for the "head data" as the row's data shows also how do I add other columns I need from the head data?
something like this: (if I haven't missed some parenthesis or such...). I've kept trying for hours and read lots of stuff with no luck

Code:

{"BillingAddress":"yada yada","DeliveryAddress":"yada yada","OrderDate":"2015-10-01":[[{"ArticleNo":"200-001","Quantity":"5"}],[{"ArticleNo":"200-002","Quantity":"10"}],[{"ArticleNo":"200-005","Quantity":"20"}]]}

When googling the only thing I find is converting a one table query into JSON, but when the heck is that ever used, kinda.
I can't understand why I can't find any result on a mysql one-to-many into JSON, weird

Basher52 · 10-23-2015, 02:21 PM

After more intense search and testing all I found I got it working.
Posting it here for others to see.

Code:

<?php

$mysqli=new mysqli("localhost","root","***","**");
if(mysqli_connect_errno()){
printf("Connection failed: %s\n", mysqli_connect_error());
exit();
}
$sqlhead="SELECT Id, DeliveryAddress, BillingAddress, OrderDate, CustomerReference, CustomerPhoneNo, Weight, FreightCost FROM ec_order";
$resultparent=$mysqli->query($sqlhead);
$result=$mysqli->query($sqlhead);
$parent = array();

while($rowparent = mysqli_fetch_array($resultparent))
{

        $parent[$rowparent['Id']]["Order"][] = array("DeliveryAddress"=>$rowparent['DeliveryAddress'],
                "BillingAddress"=>$rowparent['BillingAddress'],
                "OrderDate"=>$rowparent['OrderDate'],
                "CustomerReference"=>$rowparent['CustomerReference'],
                "CustomerPhoneNo"=>$rowparent['CustomerPhoneNo'],
                "Weight"=>$rowparent['Weight'],
                "FreightCost"=>$rowparent['FreightCost']);

        $sqlchild="SELECT Id, ArticleName, Quantity, PricePerUnit FROM ec_orderrows WHERE OrderId = $rowparent[Id] ORDER BY Id ASC";

        $resultchild=$mysqli->query($sqlchild);
        while($rowchild = mysqli_fetch_array($resultchild))
        {
                $parent[$rowparent['Id']]["OrderRow"][] = array("Id"=>$rowchild['Id'],
                        "ArticleName"=>$rowchild['ArticleName'],
                        "Quantity"=>$rowchild['Quantity'],
                        "PricePerUnit"=>$rowchild['PricePerUnit']);
        }
}
echo json_encode($parent);
$mysqli->close();

?>

The important thing here seems to be this:

Code:

$parent[$rowparent['Id']]["Order"][] = array....

I removed

Code:

$rowparent['Id']

because I didn't want that in the output but if I do the result will be that all heads comes first and then all rows.
This is probably due to the fact that each row can't be bound to its head data. (any other point on this is welcome)

When I first read about JSON on a page somewhere it made me think it could create a hierarchy of heads/rows automatically but I was mistaken when reading the "correct" info

If someone knows if it IS possible to remove the above shown

Code:

$rowparent['Id']

then let me know

I might be back later with other stuff related to this though

Thanks to Smokey_justme for putting me on the right track, well kinda