PHP & Mysql Query for JSON Output

Question

I have a table in a MySQL database that I have tried to create to be flexible in terms of the information it can store. The idea is that lots of test information gets put into this table, and then I can query the table and output into PHP to undertaken analysis on the data.

The table schema is as follows; idraw_data (auto increment) entry_date (datetime) SensorID (int) test_ID (int) test_value (double)

I use two other tables as well which link into SensorID and Test_ID. One lists the location and information about the specific sensor (e.g. who it is assigned to), and the other table includes test information like what the test is measuring, units, upper and lower limits and so on.

So i have a join statement working which lets me get the data in the form of the date, desk, test name and value filtered by mon-fri (9am - 5pm)which is the query below;

SELECT raw_data.entry_date, SensorID.Desk, tests.testname, raw_data.test_value FROM raw_data INNER JOIN sensorID ON raw_data.sensorID = SensorID.Sensor INNER JOIN tests ON raw_data.test_id = tests.testid WHERE WEEKDAY(raw_data.entry_date) BETWEEN 0 AND 4 AND HOUR(raw_data.entry_date) BETWEEN 9 AND 17;

I can use a PHP query and get the database output, and then use json_encode to put this into a JSON string to then feed into a graphing package amcharts but where I am struggling is how I can format the data to be more usable.

Is someone able to show me how I can format the mysql output to subsequently be able to get a json schema similar that provides the date & time, then the measurement type and value dynamically,

e.g. date, {location; locationname, [testa:testaname_value, testbname;testbvalue}} or is there a better way to output this for ease of graphing?

Many thanks!

phirschybar · Accepted Answer · 2018-02-15 14:55:10Z

1

What I usually do in this case is take the raw data from MySQL and loop over it, constructing a new array which can then be converted to JSON and used for graphing.

For example:

$db_data = RAW_DATA_FROM_MYSQL_ARRAY;
$data_for_graph = [];

foreach ($db_data as $item){

  $data_for_graph[$item['entry_date']] = [
    'location' => $item['location_name'],
    'testa' => $item['testaname_value'],
    'results' => [
      'test_a' => $item['test_a_val'],
      'test_b' => $item['test_b_val'],
    ],
    // etc... construct this as you like..
  ];
}

$data_for_graph_json = json_encode($data_for_graph);

// now do stuff with the graphs

Hope this helps!

UPDATE

Added a nested array structure as-per OP request.

edited Feb 15, 2018 at 14:55

answered Feb 12, 2018 at 3:33

phirschybar

8,62912 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1829564 Over a year ago

Thanks Phirschybar, I tried using the below as an example foreach ($emparray as $item){ $data_for_graph[$item['entry_date']] = [ 'location' => $item['location'] = [ $item['test'] => $item['testresult'], // etc... construct this as you like.. ]]; } but not having much luck, i get {"2017-11-15 09:00:00":{"location":{"Outdoor Humidity":"36"}}, how do i make the location an array after time and include the location value, and then create a sub array of outdoor humidity? thanks heaps

phirschybar Over a year ago

@user1829564 - my example was just to get you started. I am not sure of the exact format required for your case and for your specific charting library. the array can be constructed in any way.

user1829564 Over a year ago

Thanks for your help, from the looks of it that is an associative array?, what i was curious about is how you have an array within an array and get that from Row based information (or maybe you cant), for example I have a single row that has a date, a location, a test and a test value, but ideally would like to break that down so that one date has many locations, that have many tests that each have one result. For example Date A ---> Location A (Test A;Result A, Test B;Result B, ) -- > Location B (Test A;ResultA,TestB;ResultB) Date B --> (Location A (TestA;ResultA); Location B (.... Thanks :)

phirschybar Over a year ago

@user1829564 - I added a nested array structure to my example. So you can see that you can construct the array however you wish. Converting to JSON should work as expected. Hope this helps.

user1829564 Over a year ago

Thanks heaps everyone for the valuable responses, most appreciated!

Collectives™ on Stack Overflow

PHP & Mysql Query for JSON Output

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related