/    Sign up×
Community /Pin to ProfileBookmark

PHP, create json from inner join table

Dear All,

As I would like to create json as below format

“`
{
“data”: [
{
“title”: “After getting a COVID-19 Vaccine”,
“content”: [
{
“list”: “You may have some side effects, which are normal signs that your body is building protection.”
},
{
“list”: “Following symptoms can take place, and it usually resolves within 2 to 3 days.”
},
{
“list”: “Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache”
}
]
},
{
“title”: “After getting a COVID-19 Vaccine”,
“content”: [
{
“list”: “You may have some side effects, which are normal signs that your body is building protection.”
},
{
“list”: “Following symptoms can take place, and it usually resolves within 2 to 3 days.”
},
{
“list”: “Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache”
}
]
}
]
}
“`

The information is from mysql select command:
`SELECT (tb_helpinfo.title_eng) as title,(tb_helpdetail.content_eng) as content FROM tb_helpinfo INNER JOIN tb_helpdetail ON tb_helpinfo.id = tb_helpdetail.helpinfo_id`

![(https://pasteboard.co/K0tRyFZ.png)]

How could I make json like that from mysql information?

to post a comment
PHP

16 Comments(s)

Copy linkTweet thisAlerts:
@SempervivumMay 05.2021 — Check if this code does the job for you:
``<i>
</i>$result_of_query = [
[
"title" =&gt; "After getting a COVID-19 Vaccine",
"content" =&gt; "You may have some side effects, which are normal signs that your body is building protection.",
], [
"title" =&gt; "After getting a COVID-19 Vaccine",
"content" =&gt; "Following symptoms can take place, and it usually resolves within 2 to 3 days.",
], [
"title" =&gt; "After getting a COVID-19 Vaccine",
"content" =&gt; "Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache.",
], [
"title" =&gt; "Helpful tips",
"content" =&gt; "Drink plenty of fluids.",
], [
"title" =&gt; "Helpful tips",
"content" =&gt; "If symptoms do not get better.",
],
];
$resultarr = [];
$keys = [];
$idx = 0;
foreach ($result_of_query as $item) {
if (!isset($keys[$item['title']])) {
$keys[$item['title']] = $idx;
$key = $idx;
$resultarr[$key] = ['content' =&gt; []];
$idx++;
} else {
$key = $keys[$item['title']];
}
$resultarr[$key]['content'][] = ['list' =&gt; $item['content']];
}
echo json_encode(['data' =&gt; $resultarr], JSON_PRETTY_PRINT);<i>
</i>
``

I simuated your result set by the array $result_of_query.
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 05.2021 — @Sempervivum#1631255 Your code is looking good but it's not show title on the json.

Could you please kindly help me check the code again?

{<br/>
"data": [<br/>
{<br/>
"content": [<br/>
{<br/>
"list": "You may have some side effects, which are normal signs that your body is building protection."<br/>
},<br/>
{<br/>
"list": "Following symptoms can take place, and it usually resolves within 2 to 3 days."<br/>
},<br/>
{<br/>
"list": "Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache."<br/>
}<br/>
]<br/>
},<br/>
{<br/>
"content": [<br/>
{<br/>
"list": "Drink plenty of fluids."<br/>
},<br/>
{<br/>
"list": "If symptoms do not get better."<br/>
}<br/>
]<br/>
}<br/>
]<br/>
}
Copy linkTweet thisAlerts:
@NogDogMay 05.2021 — Depending on what DBMS you are using, you might be able to aggregate the content values into a JSON or array type, which could then simplify the PHP handling of the result.
Copy linkTweet thisAlerts:
@SempervivumMay 05.2021 — @Nitiphone#1631260 I see, forgot about that but it's easy to add:
``<i>
</i>foreach ($result_of_query as $item) {
if (!isset($keys[$item['title']])) {
$keys[$item['title']] = $idx;
$key = $idx;
$resultarr[$key] = ['title' =&gt; $item['title'], 'content' =&gt; []];
$idx++;
} else {
$key = $keys[$item['title']];
}
$resultarr[$key]['content'][] = ['list' =&gt; $item['content']];
}<i>
</i>
``
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 05.2021 — @Sempervivum#1631262 It's work now.

Should I do the same like you when I want to query and convert to json?

Thank you very much.
Copy linkTweet thisAlerts:
@SempervivumMay 05.2021 — @Nitiphone#1631263
>Should I do the same like you when I want to query and convert to json?

Don't understand this question. Instead of my array $result_of_query you have to loop through the result set of your DB query.
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 05.2021 — @Sempervivum#1631265 It's ok now.

Thank you very much for your help
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 14.2021 — @Sempervivum#1631265 How to modify to create a json like this?
``<i>
</i>{
"data": [
{
"datetime": "2021-05-12",
"history": [
{
"name": "ABC",
"feel": 1,
"cough": 0,
"fever": 1,
"headache": 1,
"tired": 0,
"appetite": 0,
},
{
"name": "DEF",
"feel": 1,
"cough": 0,
"fever": 1,
"headache": 1,
"tired": 0,
"appetite": 0,
}
]
},
{
"datetime": "2021-05-13",
"history": [
{
"name": "ABC",
"feel": 1,
"cough": 0,
"fever": 1,
"headache": 1,
"tired": 0,
"appetite": 0,
},
{
"name": "DEF",
"feel": 1,
"cough": 0,
"fever": 1,
"headache": 1,
"tired": 0,
"appetite": 0,
}
]
},
{
"datetime": "2021-05-14",
"history": [
{
"name": "ABC",
"feel": 1,
"cough": 0,
"fever": 1,
"headache": 1,
"tired": 0,
"appetite": 0,
},
{
"name": "DEF",
"feel": 1,
"cough": 0,
"fever": 1,
"headache": 1,
"tired": 0,
"appetite": 0,
}
]
}

]
}<i>
</i>
`</CODE>

But the information I need to select 2 time because I want to group by date<br/>
my PHP code like this

<CODE>
`<i>
</i>SELECT survay_date FROM tb_feedback GROUP BY CAST(survay_date AS DATE) ORDER BY survay_date DESC


$stmt-&gt;execute();
$res = $stmt-&gt;get_result();

if($res-&gt;num_rows==0){

header('Content-Type: application/json');
$json = array();
$myArray['data'] = $json;
echo json_encode($myArray);die();
}

while($row = $res-&gt;fetch_array(MYSQLI_ASSOC)) {
$myArray['data'][] = array_push($row,array("A","B"));

$statement = $conn-&gt;prepare("SELECT tb_register.firstname,tb_feedback.feel,tb_feedback.cough,tb_feedback.fever,tb_feedback.headache,tb_feedback.tired,tb_feedback.appetite,tb_feedback.swelling FROM tb_feedback INNER JOIN tb_register ON tb_register.id = tb_feedback.user_id");
//$statement-&gt;bind_param("s", $data-&gt;{'app_id'});
//$statement-&gt;bind_param("s", $row['survay_date']);

$statement-&gt;execute();
$result = $statement-&gt;get_result();

while($infor = $result-&gt;fetch_array(MYSQLI_ASSOC)) {
$infor1['history'][] = $infor;
$myArray['data'][] = $infor1;

}



}<i>
</i>
``
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 14.2021 — I am so weak about array function
Copy linkTweet thisAlerts:
@SempervivumMay 14.2021 — Single backticks (probably created by the button `&lt;/&gt;</C>) won't work reliably when posting code. You better use code tags: <C>your code here` or triple backticks.

I edited your posting accordingly.

>I am so weak about array function

Don't worry, the same applies to me when it comes to complex database queries.

>I want to group by date

This can be done easily when using PDO:

https://phpdelusions.net/pdo/fetch_modes#FETCH_GROUP
Copy linkTweet thisAlerts:
@SempervivumMay 14.2021 — I prepared same mockup data and this code seems to work fine:
``<i>
</i>$query = 'SELECT tb_feedback.
datetime,
tb_register.firstname,
tb_feedback.feel,tb_feedback.cough,tb_feedback.fever
FROM tb_feedback
INNER JOIN tb_register ON tb_register.id = tb_feedback.user_id';
$statement = $pdo-&gt;prepare($query);
$statement-&gt;execute();
$result = $statement-&gt;fetchAll(PDO::FETCH_GROUP);
// var_dump($result);
$result2 = [];
$idx = 0;
foreach ($result as $date =&gt; $history) {
$result2[$idx] = ['datetime' =&gt; $date, 'history' =&gt; []];
foreach ($history as $histitem) {
$result2[$idx]['history'][] = [
'name' =&gt; $histitem['firstname'],
'feel' =&gt; $histitem['feel'],
'cough' =&gt; $histitem['cough'],
'fever' =&gt; $histitem['fever']];
}
$idx++;
}

echo json_encode(['data' =&gt; $result2], JSON_PRETTY_PRINT);<i>
</i>
``
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 15.2021 — @Sempervivum#1631606

I modify the code but I don't know about fetch group?

Could you create this line to mysqli?

$result = $statement-&gt;fetchAll(PDO::FETCH_GROUP);

$query = 'SELECT tb_feedback.survay_date,<br/>
tb_register.firstname,<br/>
tb_feedback.feel,tb_feedback.cough,tb_feedback.fever<br/>
FROM tb_feedback INNER JOIN tb_register ON tb_register.id = tb_feedback.user_id WHERE tb_register.app_id = ?';
<br/>
<i> </i><CODE>$stmt = $conn-&gt;prepare($query);
<i> </i>$stmt-&gt;bind_param("s", $data-&gt;{'app_id'});
<i> </i>$stmt-&gt;execute();
<i> </i>
<i> </i>$result = $stmt-&gt;fetch_all(MYSQLI_ASSOC);
<i> </i>
<i> </i>$result2 = [];
<i> </i>$idx = 0;
<i> </i>
<i> </i>foreach ($result as $date =&gt; $history) {
<i> </i> $result2[$idx] = ['survay_date' =&gt; $date, 'history' =&gt; []];
<i> </i> foreach ($history as $histitem) {
<i> </i> $result2[$idx]['history'][] = [
<i> </i> 'name' =&gt; $histitem['firstname'],
<i> </i> 'feel' =&gt; $histitem['feel'],
<i> </i> 'cough' =&gt; $histitem['cough'],
<i> </i> 'fever' =&gt; $histitem['fever']];
<i> </i> }
<i> </i> $idx++;
<i> </i>}

<i> </i>echo json_encode(['data' =&gt; $result2], JSON_PRETTY_PRINT);
Copy linkTweet thisAlerts:
@SempervivumMay 15.2021 — @Nitiphone#1631623 AFAIK mysqli doesn't support the feature grouping by value. I recommend to use PDO.
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 15.2021 — @Sempervivum#1631629

Yes I use PDO but can I group it by date? as below result
<i>
</i>{
"data": [
{
"datetime": "2021-05-13",
"history": [
{
"name": "main",
"feel": "1",
"cough": "1",
"fever": "0"
},
{
"name": "tia",
"feel": "2",
"cough": "0",
"fever": "0"
}
]
},
{
"datetime": "2021-05-14",
"history": [
{
"name": "main",
"feel": "1",
"cough": "0",
"fever": "1"
},
{
"name": "main",
"feel": "1",
"cough": "1",
"fever": "0"
},
{
"name": "???????1",
"feel": "2",
"cough": "0",
"fever": "1"
}
]
},
{
"datetime": "2021-05-12",
"history": [
{
"name": "???????1",
"feel": "2",
"cough": "0",
"fever": "1"
},
{
"name": "???????3",
"feel": "1",
"cough": "0",
"fever": "0"
}
]
},
{
"datetime": "2021-05-14",
"history": [
{
"name": "znzn",
"feel": "1",
"cough": "0",
"fever": "1"
},
{
"name": "zjzj",
"feel": "1",
"cough": "0",
"fever": "1"
}
]
},
{
"datetime": "2021-05-15",
"history": [
{
"name": "tia",
"feel": "3",
"cough": "0",
"fever": "0"
},
{
"name": "test",
"feel": "3",
"cough": "0",
"fever": "0"
}
]
}
]
}
Copy linkTweet thisAlerts:
@SempervivumMay 15.2021 — I see, the reason is that your datetime includes time while mine included date only.

Extract the date like this:
<i>
</i>$query = 'SELECT DATE(tb_feedback.survay_date),
tb_register.firstname,
tb_feedback.feel,tb_feedback.cough,tb_feedback.fever
FROM tb_feedback INNER JOIN tb_register ON tb_register.id = tb_feedback.user_id
WHERE tb_register.app_id = ?';

(not tested)
Copy linkTweet thisAlerts:
@NitiphoneauthorMay 15.2021 — @Sempervivum#1631634 Thank you, I think it's because array so I focus on it.

It's work. Seem I need to learn more about array.

Thank you very much for your help
×

Success!

Help @Nitiphone spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.29,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...