asked on

two mysql queries to one json

dear experts ,

i have the following case .
i have two tables in the database .
areas and plots .. where each area could has many plots (1-M) and ofcourse in the plots table there is a foreign key that points to areas table

i want to generate a json file that shows up the all the areas and plots in a form array of array
i e : for each area display all the relevant plots

here is the structure of the two table just to give you a better vision :

areas table :

CREATE TABLE IF NOT EXISTS `Hostelz`.`areas` (
`area_id` INT(11) NOT NULL AUTO_INCREMENT,
`area_name` TEXT NULL DEFAULT NULL,
`area_number` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`area_id`),
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 10

plots table :

CREATE TABLE IF NOT EXISTS `Hostelz`.`plots` (
`plot_id` INT(11) NOT NULL AUTO_INCREMENT,
`plot_number` TEXT NULL DEFAULT NULL,
`area_id` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`plot_id`),
INDEX `area_id_f1_idx` (`area_id` ASC),
CONSTRAINT `area_id_f1`
FOREIGN KEY (`area_id`)
REFERENCES `Hostelz`.`areas` (`area_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 222
DEFAULT CHARACTER SET = utf8

and here is the sample json format i want to achieve

{
"success": [
{"plots": [{
"plotid": "289",
"plotname": "slk"
},
{
"plotid": "203",
"plotname": "wlas"
},
{
"plotid": "20",
"plotname": "nls"
}
],
"areaid": "123"},

{"plots": [{
"plotid": "123",
"plotname": "ask"
},
{
"plotid": "498",
"plotname": "vlns"
},
{
"plotid": "39",
"plotname": "lknac"
}
],
"areaid": "456"}

]

}

....... etc

hope you got me clearly ... thanks

Chris Stanyon

Probably the easiest way here is to run a query for the areas, loop through the records and create an array for each value. Inside the loop, you run a query against the plots table (using the area id in the WHERE clause) to build a new array and add that to your area array. You would then json_encode the final array for your data.

If that sounds like the right approach, give me a few minutes and I'll knock up some code

mokaid83

ASKER

yes you are right ...
the concept is right but the issue is about how to achieve it
i will be thankful . if you could provide me with the needed code to accomplish this ..

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mokaid83

ASKER

master piece of code !
Thank you a lot .. worked like a charm

but regarding $dbo = new PDO .... ( connection to db )
i already have a connect file i use in the php pages 'connect.php'

here is the format of the file i use

<?php
ini_set('default_charset','UTF-8');
$hostname = "hostname";
$username = "username";
$dbname = "db_name";
$password = "mypassword" ;
mysql_connect($hostname, $username, $password) OR DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname)or die("cannot select DB");
mysql_query("SET NAMES utf8;");
mysql_query("SET CHARACTER_SET utf8;");
?>

what can i do with your code with using the connect file i have .

mokaid83

ASKER

great solution !

mokaid83

ASKER

opps one more thing .. i would like also to add the area name ...how ?

Chris Stanyon

You're not going to be able to use my code with your connection.

Your connection file is connecting to your database using the mysql_* library. This is now deprecated in PHP so you need to switch to either PDO (as in my code) or mySQLi.

If you want to create an external connection file, then is would contain this:

<?php
try {
	//Connect to the Database
	$dbo = new PDO('mysql:host=localhost;dbname=yourDB', 'username', 'password');
	$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$dbo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
} catch (PDOException $e) {
	print "Error: " . $e->getMessage();
}
?>

Open in new window

Once you've included that file, then you would use the $pdo variable for all your database stuff.

Obviously if you have other code that is still using the old mysql library, then that will need updating as well.

Chris Stanyon

To add the area name, just add it to the array inside the foreach loop:

//add the info to the data array
$data[] = array(
	'areaid' => $row->area_id,
	'areaname' => $row->area_name,
	'plots' => $plotInfo
);

Open in new window

mokaid83

ASKER

got you ..
thanks again

Puneet Arora

How to do in case we have one more table
like Zone->Area -->Plot ..?