Storing Data

In order to read and store data we’re going to need to use some PHP and MySQL. At the point we strongly advise you take a look at the basics of PHP and SQL (SQL is what MySQL uses) at our favourite site w3schools.com

So you’re read the primer on w3schools.com? Great. Let’s move on

PHP is able to read data that has been sent to it via GET or POST. We simply say:

So we could easily print to screen whether or not the user selected yes or no for milk like this:

It’s easy enough to get the data, but we want to do something with it. When you downloaded this example you imported a script into phpMyAdmin. That script created and setup a database. At this point it’d be worth your while opening up phpMyAdmin:

  • Launch your webserver and in the control panel that opens click ‘start servers’
  • Click ‘Open start page’
  • Click ‘Tools’ and then ‘phpMyAdmin’

Click on ‘kwmcexamplesurvey’ in the left hand panel. You should see two tables ‘survey’ and ‘tea_kinds’. You can click on a table to see it’s data. phpMyAdmin allows you to easily look at data in the database via a graphic user interface i.e clicking buttons within an app. Under the hood the database runs on SQL. You can click on the SQL tab and type in SQL commands to retrieve data. For example clicking on the survey table is the equivalent of running the query ‘SELECT * FROM survey;’ in the SQL tab.

So, we can use PHP to get the POST data and we can see we have a database, but how do we get the two to talk to each other? The safest and easiest way is something called PDO – PHP Data Objects. This is an interface that’s built into PHP that lets it read and write to a database. It’s ‘safe’ because it makes it very difficult for people to try and read your database without your permission, or to insert data into your database other than what you intended.

We’ve wrapped up our database connection and all the functions needed – storing data and retrieving it in a class. You can read more about PHP classes here. Classes are essentially a nice way of keeping code contained so it’s easy to update and maintain.

Let’s crack open our PHP class and see how it’s communicating with our database. Fire up ‘classes/Connection.php’ in your text editor and we’ll see what’s going on

Firstly we declare some variables

private means the variables can not be accessed outside of the class. The majority of variables in a class are either ‘public’ or ‘private’ – the reasons for this go beyond the scope of this tutorial. We’re just going to make everything private, as it’s safest.

The first thing we setup is the connection details for the database. When you imported the surveysetup.sql file it created a database called ‘kwmcexamplesurvey’ and a user with the same name with the password of ‘kwmcexamplesurvey’. We also created a variable called ‘$con’. This will be our connection to the database that we’ll use throughout the class

Next we define this funny looking function ‘__contruct’. This is known as a constructor, it runs automatically as soon as the class is created. It’s sort of like a setup function. All this constructor does is calls the function connect. The ‘$this->’ means it’s a function within the class, specifically this classes connect function

Here the class attempts to connect to the database using the details we’ve given it. We store this connection in out ‘$con’ variable for later use. The try and catch is something built into PHP. It means try and do this, if something goes wrong ‘catch’ the error. In this case if the class can’t connect to the database it will print an error to the screen and create a connection.errors.txt file with more information in it.

This function will take three bits of data, sugars, milk and how_many from our form and insert it into the database. Notice we’re using our “$con” var we used earlier except it’s now ‘$this->con’. That’s because we’re using the connection to the database that we stored as the variable ‘$con’ in this class. The ‘this’ keyword generally means it’s something owned by it.

The database insert is built up over several statements. First the SQL itself is prepared, with placeholders for the values. These placeholders begin with a ‘:’. We then execute the SQL statement passing in the values that we want to replace the placeholders with. We return the lastInsertId – this will give us the id from the database for that insert. The whole thing is wrapped in a try and catch in case anything goes wrong.

The function saveTeaChoices works in a very similar way except this has an extra line around it:

This is a foreach loop and is used when there are multiple answers. It will go through each tea type the user ticked and insert into into the tea_kinds table.

Hopefully you can see how the PHP is basically running SQL queries on the database via the class. Whilst we’ve defined all these methods of inputing data, we haven’t actually called them yet. Open up thanks.php in your text editor

The first thing we see if this:

This tells the PHP to bring in the code for our connection classso we can use it

We then check that some data was posted to the page

PHP has a built in check ‘isset’. This will check that a variable is not empty. In this case we are checking that anything has been sent to the page via POST. As long as something has been posted to the page the code will continue

We then grab the variables we want from the form date posted to the page.

We then create an instance of the Connection class and store it in the variable ‘$dbconnection’. We then call the function enterSurvey and pass in the data from the form. This inserts the majority of the users answers into the database

Finally we check that we have some tea types posted, and if so we call the function saveTeaChoices and pass it the array of the users chosen teas

And that is how we store data. If you answer the survey again and take a look at the database in phymyAdmin just before and after you should see the data has changed. Next we’ll looking at retrieving data