Solved

two mysql queries to one json

Posted on 2014-02-15
10
1,369 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
  • 5
  • 4
10 Comments
 
LVL 42

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 42

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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

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

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 42

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now