• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1651
  • Last Modified:

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
0
mokaid83
Asked:
mokaid83
  • 5
  • 4
1 Solution
 
Chris StanyonCommented:
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
0
 
mokaid83Author Commented:
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 ..
0
 
Chris StanyonCommented:
Here you go:

<?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);

	//Set up the queries
	$areaSQL = "SELECT area_id, area_name, area_number FROM q_28365664_areas";
	$plotSQL = "SELECT plot_id as plotid, plot_number as plotnumber FROM q_28365664_plots WHERE area_id = :areaID";
	
	//Prepare the Plots query (using named parameters)
	$plotData = $dbo->prepare($plotSQL);
	
	//prepare some storage for the data
	$data = array();
	
	//loop through the area records
	foreach ($dbo->query($areaSQL) as $row):
		//get the plots for this area
		$plotData->execute(array('areaID' => $row->area_id));
		$plotInfo = $plotData->fetchAll(PDO::FETCH_ASSOC);

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

	//output the whole lot as JSON
	echo json_encode(array('success' => $data));

} catch (PDOException $e) {
    print "Error: " . $e->getMessage();
}
?>

Open in new window

Have a read through the code and ask if don't get something :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mokaid83Author Commented:
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 .
0
 
mokaid83Author Commented:
great solution !
0
 
mokaid83Author Commented:
opps one more thing .. i would like also to add the area name ...how ?
0
 
Chris StanyonCommented:
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.
0
 
Chris StanyonCommented:
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

0
 
mokaid83Author Commented:
got you ..
thanks again
0
 
Puneet AroraCommented:
How to do in case we have one more table
like Zone->Area -->Plot ..?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now