Solved

mysql - php - json

Posted on 2014-02-19
16
523 Views
Last Modified: 2014-02-20
dear experts ,

i have the following schema in the database :


Tables A,B,C,D,E

1- Table B has foreign key References  A
2 - Table C has foreign Key References  B
3- Table D has foreign Key References C
4- Table E has foreign Key References  A

the main objective is to generate  json output

the following code only covers the first point, by means i am able to generate a json output ( for each id in A has many in B )
but how to modify the code to get the final desired result .





	$dbo = new PDO('mysql:host='anydb';dbname=anydata', 'nodb', 'db123!');
	$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$dbo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
    $dbo->exec("set names utf8");
	$areaSQL = "SELECT area_id, area_name, area_number FROM areas";
	$plotSQL = "SELECT plot_id as plotid, plot_number as plotnumber FROM plots WHERE area_id = :areaID";

	$plotData = $dbo->prepare($plotSQL);
	$data = array();
	
	foreach ($dbo->query($areaSQL) as $row):
		$plotData->execute(array('areaID' => $row->area_id ));
		$plotInfo = $plotData->fetchAll(PDO::FETCH_ASSOC);

		$data[] = array(
			'areaid' => $row->area_id,
			'area_name'=> $row->area_name,
			'plots' => $plotInfo
		);
	endforeach;
	echo json_encode(array('success' => $data));

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

Open in new window

0
Comment
Question by:mokaid83
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39869866
What is the final desired result?

Sounds like you want a join

SELECT * FROM A 
  LEFT JOIN B on A.id = B.id 
  LEFT JOIN C on B.bid = C.bid
  LEFT JOIN D on C.cid = D.cid
  LEFT JOIN E on A.eid = E.eid

Open in new window

Without seeing your full schema I have had to put placeholders for the join fields.
0
 

Author Comment

by:mokaid83
ID: 39869920
it  is not about the query itself but it is about to generate the final output in json format
if you check the submitted code . you would figure out what i am looking for
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39870014
In your question you state
the main objective is to generate  json output

But you do not say anything about what the actual desired result is. I am assuming you want your table information output in JSON format - but how do you want that data to look? Based on the information supplied a JOIN is the correct way to get all the data from all 4 tables - how you then represent that data is up to you - how are you going to be using it?

json_encode will generate the JSON for you - all you need to do is get the data into an array or object as you need it.

Based on the code supplied you can retrieve the data you want with the following query
$query = "SELECT a.area_id, a.area_name, a.area_number, p.plot_id as plotid, p.plot_number as plotnumber FROM area a LEFT JOIN plots p ON a.area_id = p.area_id";

Open in new window


You can then do a fetchAll on this query and json_encode the result - but again it comes down to what is going to consume the json string - what is it expecting to find in there in terms of data structure.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39870015
You haven't told us what the final desired output is. The code above (which looks familiar :)) will execute a couple of queries and build an array, That array is then json encoded.

Without knowing what your final output should be, we can't show you how to build the array.
0
 

Author Comment

by:mokaid83
ID: 39870076
this is the final result i am looking for
one json file to be the result of all the queries
{
  "success": [
    {
      "areaid": "1",
      "area_name": "ABC",
      "plots": [
        {
          "plotid": "101",
          "plotnumber": "572",
          "centers": [
            {
              "centerid": "1",
              "centername": "xyz",
              "employess": [
                {
                  "firstname": "alex",
                  "lastname": "mark"
                },
                {
                  "firstname": "mike",
                  "lastname": "anna"
                },
                {
                  "firstname": "raul",
                  "lastname": "patrick"
                }
              ]
            },
            {
              "centerid": "2",
              "centername": "mno"
            }
          ]
        }
      ],
      "nationalities": [
        {
          "nationalityid": "1",
          "country": "USA"
        },
        {
          "nationalityid": "2",
          "country": "UK"
        },
        {
          "nationalityid": "3",
          "country": "CAN"
        }
      ]
    },
    {
      "areaid": "1",
      "area_name": "ABC",
      "plots": [
        {
          "plotid": "101",
          "plotnumber": "572",
          "centers": [
            {
              "centerid": "1",
              "centername": "xyz",
              "employess": [
                {
                  "firstname": "alex",
                  "lastname": "mark"
                },
                {
                  "firstname": "mike",
                  "lastname": "anna"
                },
                {
                  "firstname": "raul",
                  "lastname": "patrick"
                }
              ]
            },
            {
              "centerid": "2",
              "centername": "mno"
            }
          ]
        }
      ],
      "nationalities": [
        {
          "nationalityid": "1",
          "country": "USA"
        },
        {
          "nationalityid": "2",
          "country": "UK"
        },
        {
          "nationalityid": "3",
          "country": "CAN"
        }
      ]
    },
    {
      "areaid": "1",
      "area_name": "ABC",
      "plots": [
        {
          "plotid": "101",
          "plotnumber": "572",
          "centers": [
            {
              "centerid": "1",
              "centername": "xyz",
              "employess": [
                {
                  "firstname": "alex",
                  "lastname": "mark"
                },
                {
                  "firstname": "mike",
                  "lastname": "anna"
                },
                {
                  "firstname": "raul",
                  "lastname": "patrick"
                }
              ]
            },
            {
              "centerid": "2",
              "centername": "mno"
            }
          ]
        }
      ],
      "nationalities": [
        {
          "nationalityid": "1",
          "country": "USA"
        },
        {
          "nationalityid": "2",
          "country": "UK"
        },
        {
          "nationalityid": "3",
          "country": "CAN"
        }
      ]
    },
    {
      "areaid": "1",
      "area_name": "ABC",
      "plots": [
        {
          "plotid": "101",
          "plotnumber": "572",
          "centers": [
            {
              "centerid": "1",
              "centername": "xyz",
              "employess": [
                {
                  "firstname": "alex",
                  "lastname": "mark"
                },
                {
                  "firstname": "mike",
                  "lastname": "anna"
                },
                {
                  "firstname": "raul",
                  "lastname": "patrick"
                }
              ]
            },
            {
              "centerid": "2",
              "centername": "mno"
            }
          ]
        }
      ],
      "nationalities": [
        {
          "nationalityid": "1",
          "country": "USA"
        },
        {
          "nationalityid": "2",
          "country": "UK"
        },
        {
          "nationalityid": "3",
          "country": "CAN"
        }
      ]
    }
  ]
}

Open in new window




copy and paste the code at (http://www.jsoneditoronline.org/)
to get a better view of the wanted format

if this is not clear enough, i could post the schema of the tables .


note:
------
Area Table = Table A
Plots = Table B
Centers = Table C
Employees = Table D
Nationalities = Table E
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39870235
This is just a sidebar note, but please see line 4 in the original code snippet, then read this article paying particular attention to the part captioned Character Sets in MySQL

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 39871679
There is still some ambiguity in your post - the list of objects in the JSON structure are all identical - I am assuming that each instance of area / plot will be different each with their own.

Also without seeing your actual data and table defs it is difficult to come up with an exact working solution - however this should be most of the way there. It works off the test data recreated from the data in the JSON dump
<?php
error_reporting(E_ALL);

$dbo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'password');

// BUILD A QUERY TO RETURN ALL THE REQUIRED DATA IN A ROW. 

$query = "SELECT a.areaid, a.area_name, p.plotid, p.plotnumber,c.centerid, c.centername, e.firstname, e.lastname, n.nationalityid, n.country FROM area a 
  LEFT JOIN nationality n on n.areaid = a.areaid
  LEFT JOIN plot p ON a.areaid = p.areaid
  LEFT JOIN center c ON p.plotid = c.plotid
  LEFT JOIN employee e ON e.centerid = c.centerid
ORDER BY a.areaid, n.nationalityid, p.plotid, c.centerid, e.lastname, e.firstname";
  
$data = array();

// LOOP THROUGH EACH ROW AND SUCCESSIVELY TEST EACH LEVEL TO SEE IF WE HAVE ALREADY CAPTURED THAT 
// LEVEL AND IF NOT ADDING IT

foreach ($dbo->query($query) as $row) {

  // GET THE LAST AREA ITEM

  $area = end($data);
  
  // EITHER THIS IS THE FIRST ONE OR IT IS A NEW ONE OR IT IS ALREADY IN THE LIST
  // IF IN THE LIST WE IGNORE AREA ON THIS ITERATION. IF NOT WE ADD IT
  // WHEN WE ARE DONE THE $area VARIABLE WILL CONTAIN OUR CURRENT AREA OJBECT

  if (empty($area) || $area->areaid != $row['areaid']) {

    // LIST EMPTY OR AREA NOT IN LIST. CREATE NEW OBJECT FOR AREA
    // AND POPUPLATE IT

    $area = new stdClass; 
    $area->areaid = $row['areaid'];
    $area->area_name = $row['area_name'];
    $area->plots = array();
    $area->nationalities = array();
    $data[] = $area;
  }
  
  // WHEN WE GET HERE $area SHOULD CONTAIN THE AREA OBJECT FOR THE ROW WE ARE BUSY WITH
  // SO NOW WE CHECK THE PLOTS. THE PROCESS IS THE SAME AS ABOVE. IF IT DOES NOT EXIST THEN
  // CREATE IT .. SAME PROCESS AS ABOVE

  $plot = end($area->plots);
  if (empty($plot) || $plot->plotid != $row['plotid']) {
    $plot = new stdClass;
    $plot->plotid = $row['plotid'];
    $plot->plotnumber = $row['plotnumber'];
    $plot->centers = array();
    $area->plots[] = $plot;
  }
  
  // WHEN WE GET HERE $plot SHOULD CONTAIN THE PLOT OBJECT FOR THE ROW WE ARE BUSY WITH
  // SO NOW WE CHECK THE CENTERS ... AND SAME AGAIN
  
  $center = end($plot->centers);
  if (empty($center) || $center->centerid != $row['centerid']) {
    $center = new stdClass;
    $center->centerid = $row['centerid'];
    $center->centername = $row['centername'];
    $plot->centers[] = $center;
  }

  // WHEN WE GET HERE WE NEED TO CHECK IF THE EMPLOYEE FIELD IS NOT BLANK - SAMPLE DATA
  // INDICATES THAT EMPLOYEES ARE OPTIONAL SO ONLY ADD AN EMPLOYEE SECTION IF THERE IS DATA

  if (!empty($row['firstname'])) {
    if (empty($center->employees)) {
      $center->employees = array();
    }
    $employee = end($center->employees);
    if (empty($employee) || ($employee->firstname != $row['firstname'] && $employee->lastname != $row['lastname'])) {
      $employee = new stdClass;
      $employee->firstname = $row['firstname'];
      $employee->lastname = $row['lastname'];
      $center->employees[] = $employee;
    }
  }
  
  // NATIONALITY COULD BE PROCESSED DIRECTLY AFTER AREA - PUT HERE TO KEEP IT CONSISTENT
  // WITH THE OUPUT ORDER IN THE JSON SAMPLE DATA.

  $nationality = end($area->nationalities);
  if (empty($nationality) || $nationality->nationalityid != $row['nationalityid']) {
    $nationality = new stdClass;
    $nationality->nationalityid = $row['nationalityid'];
    $nationality->country = $row['country'];
    $area->nationalities[] = $nationality;
  }
}
// DONE PROCESSING ROWS SO NOW ADD THE $data ARRAY TO A PARENT ARRAY WITH THE success  KEY 

$success = array (
  'success' => $data
);

// ... AND DUMP IT

echo json_encode($success);
?>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39871723
@julianH: I believe that you may simplify things a bit by just fetching the rows as objects, instead of fetching arrays and then copying the elements into the properties of StdClass objects.  Check the ideas here:
http://php.net/pdo.query
http://php.net/manual/en/pdostatement.fetchobject.php
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39871828
Much of a muchness really and comes down to personal preference. I have as yet been unable to find a conclusive argument either way.

So it basically comes down to - do you prefer $row->areaid or $row['areaid'] ?

Difference is 2 extra characters typing and nothing else really.

Depending on who you read objects are slower than arrays or there is no significant difference.

Makes no difference to the solution, speed of execution or resource utilisation so up to the implementor.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39871872
Difference is 2 extra characters typing and nothing else really.
Erm? Suit yourself.  Here are some of the reasons that an object may be preferable.

1. When you fetch the row of data in the form of an array, then you have to copy elements of the array into an object, you could save yourself the extra typing by just fetching the row of data in the form of an object.  The class that defines the object need not have a constructor, but can have other methods that simplify the row-by-row processing.

2. It's only two characters of extra typing, but that's two more chances to make a parse error.  Oh, and there are all those extra statements needed to copy the data from the array into the object properties.

3. You can use the PHP object notation directly in the double-quoted string and the HEREDOC template without encapsulation; you must encapsulate the quoted array key notation.  That's another thing to remember (or forget, with another round of correcting parse errors).

4. In an object-oriented design, the OOP variable notation just feels better to me.

You're right that there is no measurable performance difference; I've tested it and found nothing to the left of the fourth decimal fraction of a second.  That's why I choose the OOP notation.  And you're right, it's up to the programmer to use whatever he feels comfortable with.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39871917
you could save yourself the extra typing by just fetching the row of data in the form of an object.

Only when the fetched object is identical to the object you want to copy to - not the case here. A fetched row contains hierarchical data - i.e. parent data is replicated for each row - the resulting structure comprises arrays of objects that are sub sets fo the row fetched. Hence in this case no benefit.

2. Refer 1 - the copying (in this case) is necessary irrespective of method used. I don't mind typing the extra chars and in fact my editor will quickly highlight an unclosed bracket or string - not so with a badly formed object reference.  The following is a valid statement

$x-b


3. Not using strings here so again not relevant.

4. Personal preference.

The solution was tailored to the specifics of the output requirement. To use objects because in general it might have advantages is not really a good reason - again preference but no measurable benefit.

So comes down to what you are comfortable with
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39872063
@julianH:  
Only when the fetched object is identical to the object you want to copy to - not the case here.
Except that statement is factually inaccurate.  If the fetched object is a subset or superset of the object you want, you're on firm ground and do not need to do any unnecessary copying.  Honestly, I was just trying to suggest something that might be simpler for you and our author to understand.  I think I get where you're coming from, and from where I sit it's hardly worth arguing.  Do what you feel safe with.  Please don't confuse the author of this question with a sidebar argument or with factual inaccuracies, such as saying you're not using strings (all data returned from queries is returned in string format, no matter how it may be encapsulated).  

Let's let EE try to get him an answer that tests out correctly on his test data set.  I don't really care what you want to use, and I can see there is no point in trying to show you a better way.  Choose anything that you're comfortable with.

I'm out.  Good luck, @mokaid83.
0
 

Author Comment

by:mokaid83
ID: 39872566
Fatal error: Cannot use object of type stdClass as array
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39872724
You need to give more information than that?

Where are you getting this error?
0
 

Author Comment

by:mokaid83
ID: 39874687
thanks julian ..
i fixed the error ..
i had to replace all the [ ] with->
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39874844
You ae welcome - thanks for the points.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…

743 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

9 Experts available now in Live!

Get 1:1 Help Now