LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 10-16-2015, 05:03 PM   #1
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Rep: Reputation: 11
Question Help with php, JSON and array that has arrays


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

Last edited by Basher52; 10-16-2015 at 05:23 PM.
 
Old 10-16-2015, 05:28 PM   #2
Smokey_justme
Member
 
Registered: Oct 2009
Distribution: Slackware
Posts: 534

Rep: Reputation: 203Reputation: 203Reputation: 203
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); 
 
Old 10-16-2015, 07:53 PM   #3
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Original Poster
Rep: Reputation: 11
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)
 
Old 10-16-2015, 08:03 PM   #4
Smokey_justme
Member
 
Registered: Oct 2009
Distribution: Slackware
Posts: 534

Rep: Reputation: 203Reputation: 203Reputation: 203
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?
 
Old 10-16-2015, 08:24 PM   #5
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Original Poster
Rep: Reputation: 11
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.

Last edited by Basher52; 10-16-2015 at 08:25 PM. Reason: a PS. created
 
Old 10-16-2015, 08:39 PM   #6
Smokey_justme
Member
 
Registered: Oct 2009
Distribution: Slackware
Posts: 534

Rep: Reputation: 203Reputation: 203Reputation: 203
Maybe try this?

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

 
Old 10-16-2015, 09:03 PM   #7
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Original Poster
Rep: Reputation: 11
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
 
Old 10-16-2015, 09:07 PM   #8
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Original Poster
Rep: Reputation: 11
*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 )
 
Old 10-17-2015, 12:59 PM   #9
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Original Poster
Rep: Reputation: 11
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

Last edited by Basher52; 10-17-2015 at 03:29 PM.
 
Old 10-23-2015, 02:21 PM   #10
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: CentOS, Arch, Mint, Zorin, Slax
Posts: 334

Original Poster
Rep: Reputation: 11
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine two Bash arrays into a new associative array bits45 Programming 7 08-26-2014 01:27 PM
[SOLVED] Pointer to an array of arrays errigour Programming 1 05-31-2014 04:40 PM
[SOLVED] Arithmetic operation between 2 Arrays to generate a new Array astalavista2000 Programming 14 10-19-2013 09:44 PM
PHP: Converting a PHP array into an HTML array koosha Programming 4 08-11-2009 08:47 AM
using arrays and functions and trying to initialize a point in the array mshinska Programming 1 11-11-2005 02:21 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 02:07 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration