Solved

two mysql queries to one json

Posted on 2014-02-15
10
1,508 Views
Last Modified: 2015-09-14
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
Comment
Question by:mokaid83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39861347
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
 

Author Comment

by:mokaid83
ID: 39861353
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
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 400 total points
ID: 39861380
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:mokaid83
ID: 39861402
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
 

Author Closing Comment

by:mokaid83
ID: 39861403
great solution !
0
 

Author Comment

by:mokaid83
ID: 39861409
opps one more thing .. i would like also to add the area name ...how ?
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39861414
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39861417
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
 

Author Comment

by:mokaid83
ID: 39861434
got you ..
thanks again
0
 

Expert Comment

by:Puneet Arora
ID: 40976368
How to do in case we have one more table
like Zone->Area -->Plot ..?
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question