close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

How to create a nested JSON from two different MySQLi query?

Thread began 4/13/2022 4:20 pm by Rokon | Last modified 4/14/2022 4:54 pm by Ray Borduin | 558 views | 11 replies |

Rokon

How to create a nested JSON from two different MySQLi query?

Hello

I was able to create a simple JSON output from the single MySQLi query as below;

<?php
die(json_encode($myQuery->Results, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
?>

but now I am in a situation where I need to get output of nested JSON and I can't figure it out where I should put the other MySQLi query so that it can have
output of nested JSON data.

Pls help me to get out from this critical situation. Thanking you.

Sign in to reply to this post

Ray BorduinWebAssist

I'm not quite sure how you want the data.

Do the two recordsets have the same columns?

You could join two recordsets into a single array and then output that as JSON like:


<?php
die(json_encode(array($myQuery->Results,$mySecondQuery->Results), JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
?>

Sign in to reply to this post
Did this help? Tips are appreciated...

Rokon

Originally Said By: Ray Borduin
  I'm not quite sure how you want the data.

Do the two recordsets have the same columns?

You could join two recordsets into a single array and then output that as JSON like:


<?php
die(json_encode(array($myQuery->Results,$mySecondQuery->Results), JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
?>  



I am attach the file here, I tried but failed and seems I could not figure it out correctly yet, Pls help me.

Attached Files
hadith-books-new.php
Sign in to reply to this post

Ray BorduinWebAssist

You already have the hadithsection table in the first query. I think you may be able to delete the second recordset and use your original recordset and just add the SectionBD column to your first recordset.

Sign in to reply to this post
Did this help? Tips are appreciated...

Rokon

Originally Said By: Ray Borduin
  You already have the hadithsection table in the first query. I think you may be able to delete the second recordset and use your original recordset and just add the SectionBD column to your first recordset.  



I tried that before but the problem is on the hadithsection table there are many row which have the same book id like a tree. Example Book is the main tree and sections are the branches.

I need to output the JSON as nested like several sections are belongs to one book ID

Sign in to reply to this post

Ray BorduinWebAssist

How are you outputting the json? That seems like something you could handle on the output page that reads in the json. What format do you want the json to have?

I think you would probably have to hand code a loop through the first recordset results so you can get the corresponding rows and then add it to the array so you can json encode it if you wanted to do it on this page.

Sign in to reply to this post
Did this help? Tips are appreciated...

Rokon

Originally Said By: Ray Borduin
  How are you outputting the json? That seems like something you could handle on the output page that reads in the json. What format do you want the json to have?

I think you would probably have to hand code a loop through the first recordset results so you can get the corresponding rows and then add it to the array so you can json encode it if you wanted to do it on this page.  



Yes I need to loop the second query result inside the JSON output of first query and in this place I become stuck. To get a view for a regular webpage it is easy for me that I bring the second query inside the 1st query repeat region and I can get the result what I want but to get a JSON output for the both query like nested I failed.

Can you pls guide me a bit here so that I can try.

Sign in to reply to this post

Ray BorduinWebAssist

php:
<?php require_once('../Connections/hadithbd.php'); ?>

<?php 
require_once('../webassist/mysqli/rsobj.php'); ?>
<?php
$hadithBooksList 
= new WA_MySQLi_RS("hadithBooksList",$hadithbd,0);
$hadithBooksList->setQuery("SELECT hadithbook.BookID, hadithbook.totalHadith, hadithbook.BookNameBD, Count(hadithsection.BookID) AS totalSection FROM hadithbook LEFT OUTER JOIN hadithsection ON hadithbook.BookID = hadithsection.BookID WHERE hadithbook.Active = 1 GROUP BY hadithsection.BookID ORDER BY hadithbook.priority ASC");
$hadithBooksList->execute();
?>
<?php
while (!$hadithBooksList->atEnd()) {
?>
<?php
$sectionList 
= new WA_MySQLi_RS("sectionList",$hadithbd,0);
$sectionList->setQuery("SELECT hadithsection.SectionBD FROM hadithsection WHERE hadithsection.BookID = ?");
$sectionList->bindParam("i""".($hadithBooksList->getColumnVal("BookID"))  ."""-1"); //WAQB_Param1
$sectionList->execute();
?>
<?php
  $hadithBooksList
->Results[$hadithBooksList->Index]["Sections"] = $sectionList->Results;
  
$hadithBooksList->moveNext();
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Generate Hadith Books JSON File</title>
</head>
    <body>
<?php
header
("Content-Type: application/json; charset=UTF-8");
    
die(
json_encode($hadithBooksList->ResultsJSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));    

?>    
</body>
</html>
Sign in to reply to this post
Did this help? Tips are appreciated...

Rokon

Originally Said By: Ray Borduin
  
php:
<?php require_once('../Connections/hadithbd.php'); ?>

<?php 
require_once('../webassist/mysqli/rsobj.php'); ?>
<?php
$hadithBooksList 
= new WA_MySQLi_RS("hadithBooksList",$hadithbd,0);
$hadithBooksList->setQuery("SELECT hadithbook.BookID, hadithbook.totalHadith, hadithbook.BookNameBD, Count(hadithsection.BookID) AS totalSection FROM hadithbook LEFT OUTER JOIN hadithsection ON hadithbook.BookID = hadithsection.BookID WHERE hadithbook.Active = 1 GROUP BY hadithsection.BookID ORDER BY hadithbook.priority ASC");
$hadithBooksList->execute();
?>
<?php
while (!$hadithBooksList->atEnd()) {
?>
<?php
$sectionList 
= new WA_MySQLi_RS("sectionList",$hadithbd,0);
$sectionList->setQuery("SELECT hadithsection.SectionBD FROM hadithsection WHERE hadithsection.BookID = ?");
$sectionList->bindParam("i""".($hadithBooksList->getColumnVal("BookID"))  ."""-1"); //WAQB_Param1
$sectionList->execute();
?>
<?php
  $hadithBooksList
->Results[$hadithBooksList->Index]["Sections"] = $sectionList->Results;
  
$hadithBooksList->moveNext();
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Generate Hadith Books JSON File</title>
</head>
    <body>
<?php
header
("Content-Type: application/json; charset=UTF-8");
    
die(
json_encode($hadithBooksList->ResultsJSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));    

?>    
</body>
</html>
  



Exactly what I was look for, Thanks a lot Ray for you great great support!

Sign in to reply to this post

Rokon

Originally Said By: Ray Borduin
  
php:
<?php require_once('../Connections/hadithbd.php'); ?>

<?php 
require_once('../webassist/mysqli/rsobj.php'); ?>
<?php
$hadithBooksList 
= new WA_MySQLi_RS("hadithBooksList",$hadithbd,0);
$hadithBooksList->setQuery("SELECT hadithbook.BookID, hadithbook.totalHadith, hadithbook.BookNameBD, Count(hadithsection.BookID) AS totalSection FROM hadithbook LEFT OUTER JOIN hadithsection ON hadithbook.BookID = hadithsection.BookID WHERE hadithbook.Active = 1 GROUP BY hadithsection.BookID ORDER BY hadithbook.priority ASC");
$hadithBooksList->execute();
?>
<?php
while (!$hadithBooksList->atEnd()) {
?>
<?php
$sectionList 
= new WA_MySQLi_RS("sectionList",$hadithbd,0);
$sectionList->setQuery("SELECT hadithsection.SectionBD FROM hadithsection WHERE hadithsection.BookID = ?");
$sectionList->bindParam("i""".($hadithBooksList->getColumnVal("BookID"))  ."""-1"); //WAQB_Param1
$sectionList->execute();
?>
<?php
  $hadithBooksList
->Results[$hadithBooksList->Index]["Sections"] = $sectionList->Results;
  
$hadithBooksList->moveNext();
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Generate Hadith Books JSON File</title>
</head>
    <body>
<?php
header
("Content-Type: application/json; charset=UTF-8");
    
die(
json_encode($hadithBooksList->ResultsJSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));    

?>    
</body>
</html>
  




Just a little more of your kind support further needed please. I am trying to just include the "chapterList" query under the "sectionList" query for one more step nested JSON. Please give me one more final push here to overcome the whole things. Thanks a lot Ray!

php:
<?php require_once('../Connections/hadithbd.php'); ?>

<?php 
require_once('../webassist/mysqli/rsobj.php'); ?>
<?php
$hadithBooksList 
= new WA_MySQLi_RS("hadithBooksList",$hadithbd,0);
$hadithBooksList->setQuery("SELECT hadithbook.BookID, hadithbook.totalHadith, hadithbook.BookNameBD, Count(hadithsection.BookID) AS totalSection FROM hadithbook LEFT OUTER JOIN hadithsection ON hadithbook.BookID = hadithsection.BookID WHERE hadithbook.Active = 1 GROUP BY hadithsection.BookID ORDER BY hadithbook.priority ASC");
$hadithBooksList->execute();
?>
<?php
while (!$hadithBooksList->atEnd()) {
?>
<?php
$sectionList 
= new WA_MySQLi_RS("sectionList",$hadithbd,0);
$sectionList->setQuery("SELECT hadithsection.SectionID, hadithsection.SectionBD FROM hadithsection WHERE hadithsection.BookID = ? ORDER BY hadithsection.SectionID ASC");
$sectionList->bindParam("i""".($hadithBooksList->getColumnVal("BookID"))  ."""-1"); //WAQB_Param1
$sectionList->execute();
?>
<?php
$chapterList 
= new WA_MySQLi_RS("chapterList",$hadithbd,0);
$chapterList->setQuery("SELECT hadithchapter.SectionID, hadithchapter.ChapterBG, hadithchapter.ChapterAR, hadithchapter.ChapterEN FROM hadithchapter WHERE hadithchapter.StatusActive = 1 AND hadithchapter.BookID = ? AND hadithchapter.SectionID = ? ORDER BY hadithchapter.chapID ASC");
$chapterList->bindParam("i""".($hadithBooksList->getColumnVal("BookID"))  ."""-1"); //WAQB_Param1
$chapterList->bindParam("i""".($sectionList->getColumnVal("SectionID"))  ."""-1"); //WAQB_Param2
$chapterList->execute();
?>
<?php
  $hadithBooksList
->Results[$hadithBooksList->Index]["Sections"] = $sectionList->Results;
  
$hadithBooksList->moveNext();
}
?>
<?php
header
("Content-Type: application/json; charset=UTF-8");

$json json_encode($hadithBooksList->ResultsJSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
    
//die(json_encode($hadithBooksList->Results, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));    
        
file_put_contents("hadith_books_section_chapter.json"$json);

?>
Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...