Retrieving Data

As well as using our database class to insert data into the database, we can also use it to get data out. At the heart of this are our database queries. Try running this in the SQL panel of phpMyAdmin.

This grabs up the count and percentage of each answer from the tea_kinds table. The ‘GROUP BY’ statement will return a row for each unique type of tea.

Once we’ve got the data we want in PHP we need a way of visualising it. There are lots of interesting ways of animating data of the web and the majority of them use javascript. The issue we have is that javascript needs a way of reading this data we’ve now got in the PHP. What we can do is this:

  • Work out our SQL query needed
  • Use PDO to get the data from the database into PHP
  • Output the data in a way that is easy for javascript to read.

Outputting the data in a way that is easy for javascript to read means converting the data in PHP to JSON (javascript object notation). Whist that may sound rather complicated, there are built in functions to help us with all of this.

In out Connection.php class we can see the function getTeas selects the data from the db in a very similar way to the storing data methods we looked at in the previous page

SQL statements are prepared and then executed. Once we have that data we store it in an array that we deliberately structure so that it will be easy to read when converted into JSON.

We then convert this array into JSON using json_encode

For each answer/set of data we want we create a url. Look in the data folder and you will find teatypes.php. Try browsing to this page in your web browser – http://localhost:8888/kwmcexamplesurvey/data/teatype.php. You should see the JSON output.

The teatype.php file includes the class, calls the function to grab the data and then spits it out onto the page

With the data in this format it’s relatively easy for the javascript to parse through it and to allow us to manipulate it in interesting ways. We’re going to animate the data here in visualising data