generate json data with mysql help

3xtr3m3d
3xtr3m3d used Ask the Experts™
on
Hi

This is the sample of data type i need to generate with 2 mysql queries 

$obj = array(
				"attr" => array(
								"id" => 1,
								"rel" => "drive"					
							 ),
				"data" => "My Documents",
	
		"children" => array(
		array(
				"attr" => array(
								"id" => 2,
								"rel" => "file"
							),
				"data" => "file1.doc",
				"state" => ""
			 )
	)	
);
		
echo json_encode($obj);

Open in new window


this block should generate with

				"attr" => array(
								"id" => 1,
								"rel" => "drive"					
							 ),
				"data" => "My Documents",

Open in new window


SELECT id,rel,data FROM tbl_drives ORDER BY id

Open in new window


below block should generate with

		"children" => array(
		array(
				"attr" => array(
								"id" => 2,
								"rel" => "file"
							),
				"data" => "file1.doc",
				"state" => ""
			 )

Open in new window


SELECT id,rel,data FROM tbl_files WHERE parent_id = '$id' ORDER BY id

Open in new window



Any idea how to loop two queries to generate above json data?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Commented:
This is the sql statement that should do the trick. Use the outcome to create your array.

select `tbl_drives`.`id` AS `did`,
`tbl_drives`.`rel` AS `drel`,
`tbl_drives`.`data` AS `ddata`,
tbl_files.id AS `fid`,
tbl_files.rel as `frel`,
tbl_files.data as `fdata`
from (`tbl_drives` join `tbl_files`
on((`tbl_files`.`parent_id` = `tbl_drives`.`id`)))
3xtr3m3d

Author

Commented:
Thanks for the reply thats really helpful

problem is only loop

		array(
				"attr" => array(
								"id" => 2,
								"rel" => "file"
							),
				"data" => "file1.doc",
				"state" => ""
			 )

Open in new window


CvD
Commented:
I'm assuming you know how to query the database, in that case, something like the following might be a solution:

for (var $i = 0;  $i < maxIds-1; $i++) {
  var $qry = "select `tbl_drives`.`id` AS `did`, " .
"`tbl_drives`.`rel` AS `drel`," .
"`tbl_drives`.`data` AS `ddata`," .
"`tbl_files`.`id` AS `fid`," .
"`tbl_files`.`rel` AS `frel`," .
"`tbl_files`.`data` AS `fdata` " .
"from (`tbl_drives` join `tbl_files` on((`tbl_files`.`parent_id` = `tbl_drives`.`id`)))" .
"where tbl_drives.id = " + $i;

/*
Send the query to the database and loop trhough the resulting records to produce the array you
want.
*/

}

3xtr3m3d

Author

Commented:
still couldn't find a way to loop this array 

		array(
				"attr" => array(
								"id" => 2,
								"rel" => "file"
							),
				"data" => "file1.doc",
				"state" => ""
			 )

Open in new window


seems like putting above inside a while loop is impossible
CvD

Commented:
Try someting like below:

function loopArray($arr) {
  foreach ($arr as $key => $val) {
    if (is_array($val) loopArray($val);
    else {
      //do something
    }
  }
}

USAGE:
$obj = array("attr" => array( "id" => 2,"rel" => "file"),"data" => "file1.doc","state" => "");
loopArray($obj);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial