Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql - php - json

Posted on 2014-02-19
16
Medium Priority
?
578 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
[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
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 59

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 59

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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 44

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 111

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 59

Accepted Solution

by:
Julian Hansen earned 2000 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 111

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
 
LVL 59

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 111

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 59

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 111

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 59

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 59

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

715 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