You are here

mySQL and PHP Scripting - Testing Next week..

Started this week with Task List from my menotr's Charles and David. David came up to see how we're doing with the mySql and php scripts.

Here is the list of tasks that we need to work through, preferably over the next week. Let's focus on the query until it is done and then we can move on to the php.

SQL query:

1) remove the part relating to 'water_salinity'
2) add the quality column into the list of values to return
3) add the extract year and extract month functions on mintime
4) add the extract year and extract month functions on maxtime
5) add aliases (like ' as mintime_month') for the returned values, so all of the extracted years and months aren't in columns named 'extract'
6) wrap the whole query in another query that only returns rows where the month extracted from mintime and the month extracted from maxtime match

The output from the query at the end of these steps should have columns named: year,month,quality

Steps 1 through 5 should go pretty quickly. Step 6 will require learning about subqueries.

I'm working on the ending of the Task List to my php script. After getting the json_encode to print out the data from datanearhere database. Coming along great when you don't get an errors when you execute your script. Continue working on the PHP script with Charles to execute the rest of the Task List for PHP script.


The process for getting the output of the sql query into the format that data_availability.php produces is somewhat complicated, but we can take code directly from data_availability.php to do almost all of it.

1) Put the final form of the query into the PHP code.
2) loop through the rows returned from the query, assembling an array of arrays, where the outer array is a list of years, and the inner array has a quality value for each month. The existing data_availability.php does this around line 79, search for $year_availability to find it.
   a) create an empty array $year_available before the start of the while loop that creates the $row variable
   b) create an array that maps the 3 quality levels in the database to 2, 4, and 8  (the existing program does this at line 29, search for PDX_avail to find the line, but you'll need to replace PD0, PD1, PD2 with the quality level names in the query result
   c) within the while loop, check if $year_available has a field for the year of the row  (see line 75, search for sizeof($year_avail  to find it )
   d) if it does, get the array for the year of the row from $year_available[$year] and name it $month_available (see line 77)
   e) if it does not exist, create an array called $month_available with fields named for the months and values of 0  (see line 80)
   f) use the quality as field name in $PDX_avail and 'or' it (using the | operator) with the value in $month_available for the month of the row  (see line 83)
   g) assign the result of the 'or' back into the value for the month in $month_available (also line 83)
   h) assign $month_available as the value for the year in $year_available (line 84)
   i) sort $year_available by year (using the krsort function, see line 88)
   j) create an array with 3 fields 'status','rows' and 'available' with $year_available as the value for 'available'
(see line 92)
3) return that array using json_encode
4) add in the argument handling from data_availability.php (lines 4, and 20-26)
5) optional: put the query and query processing into a function  (like the getDataAvailability function used at line 27 and defined at line 36)

Looking another way to contract a php script. Using the steps from data_availability.php that Charles gave to understanding PHP. It print out the database materials from the sql queries I created from the server. And I created a php script from the query to just print the year and month from the database. the one on the left execute my result in json encode command describing the years and month from 2016-2009. I still need to modify my code though.

Still modifying on the php script with mySQL query, deleted the undefined index error from the $request=="data_availability" and inserting more php functions() to my final__params.php.

Next Week -->Testing: to be determined next week.