Building a Database Using CREATE

To execute an SQL statement, we can select the SQL tab in the phpMyAdmin control panel (Figure 6-3). Once selected, a text field will be displayed to enter in any valid SQL commands. To execute the SQL, click the Go button beneath the text field.

An image of the P h p my admin application shows the server S Q L query to create a database.

We can also create a database by selecting the Databases tab.

An image of the P h p my admin application shows the server databases table to create a database.

Just enter the database name and click CREATE. We could also select the type of collation to use with the database. However, the default is acceptable.

Secure Programming

When creating columns within databases, special care and consideration should be given to the data types, constraints, and default values. A secure database will restrict values entered that do not meet these parameters. If the program using the database is dependent on a value existing within a field, default values should be provided. Remember, the program might reject an invalid value before attempting to update the table. It might be acceptable to use the default value when this occurs. However, there may be cases in which the value is required in order for enough information to exist within the record created. In this case, the database column should be set to require the information. If it is not provided, the database management system will reject the attempt to enter the data.

Notice the format of the CREATE statement. It is very vital that the proper parentheses and commas are included between each statement. The last line does not require a comma. Once we have entered the SQL into the text field, we can click Go to execute it. This will create the new table.

An image of the P h p my admin application showing the database S Q L to create a table Poll.

We can also create a table using the default window after selecting the database from the left-hand column. Once the table name is entered, we must also select the number of columns to create. After clicking the button, the next screen will display boxes to enter in the attributes for each column. The information entered is the same as what was shown in the preceding program code.

Let’s explore the new table we created by selecting the poll table from the panel at the left side of phpMyAdmin. Next, we will select the Structure tab from the menu.

An image of the P h p my admin application showing the Structure head comes under the poll table.

The poll table has four attributes or columns: poll_id, poll_question, yes, and no. Each attribute has a type (data type). The fields of a table can only hold data of the correct type. For example, we can only store integers in poll_id, yes, and no as indicated by the int type. The number 11 is the default size of the integer. This can be adjusted with SQL code or by making changes when clicking the Change keyword to the right of the column. We will use the default settings. The type setting for poll_question restricts entries to text only. Finally, the yes and no attributes are created with a default value of 0. None of the other attributes has default values.

The INSERT Statement

This SQL statement will insert a new row of data into the table called poll. It will declare a value for the poll_question column or attribute. More specifically, the poll_question column will get a value of Is PHP hard fun? Remember how the poll table has a total of four attributes or columns? The remaining columns poll_id, yes, and no will simply be created with default values. poll_id will get a value of 1, while yes and no will both get a value of 0.

We can execute the SQL statement in the same manner as we did when creating the table. First, we select the playground database in the phpMyAdmin control panel. Then, we click the SQL tab and enter the preceding SQL statement. Finally, we click Go to execute the entered SQL statement. The system will then indicate that one row has been inserted.

Alternatively, we can select the database from the control panel. Then click insert in the menu provided. Text fields will be presented to allow us to enter data. We can then enter the “Is PHP hard fun?” statement in the pool_question text box. Then click the “Go” button at the bottom of the screen. This will generate an SQL statement similar to the one presented earlier. The system will also include the default values not previously shown. To execute the generated statement, click the “Go” button at the bottom of the screen.

As shown, we can specify the attribute (such as ‘yes’) in the first set of parentheses and then its related setting (‘0’) in the second set of parentheses. The positions must be the same. ‘yes’ is in the third position, and its value is also in the third position. For any attribute listed, there must be an associated value. Remember that any attributes not listed will be given a default value if one was defined in the table. Notice that in this example, poll_id is set to NULL. This setting is required for any auto numerated attributes. The NULL setting (remember, we set the restrictions to not allow NULL) will be rejected which prompts the system to generate a new number by incrementing the previous number. There are other formats that can be used to insert data. However, we will use this format because we can see clearly which value is associated with which attribute.

The SELECT Statement

We can then paste this statement into the SQL text box, as previously mentioned, to produce the required results.

An image of P h p my admin application showing S Q L Query results for the Poll table.

There is one row of data in the table. It has a poll_id of 1 and a poll_question. The yes and no columns are 1 and 0, respectively.

Secure Programming

While this format is certainly quick and easy to create, it can cause possible vulnerabilities. Remember that a temporary table will be created with all attributes returned by the SELECT statement. This table might be accessible to hackers. Only expose those columns that are necessary for the completion of the task at hand. We can use the * symbol for quickly testing that the data was stored. But we don’t want to use it in the final product.

It is not much to look at in its present state, but this is all the data required to have a site poll displayed on a website. The website will display the poll question. Site visitors can then post their responses through an HTML form. Possible options would be yes or no. All responses from site visitors will then be stored in the yes or no fields. So, with a tiny bit of math, we could calculate the relative responses and display a message such as the following: 79% of all site visitors think PHP is hard and fun to learn.

Exercise: Using phpMyAdmin, execute code to accomplish the following. You can accomplish this task without coding, but this exercise is designed to help you practice your SQL coding skills. Start by creating a database named Students. Within that database, create a student table. Within the table, create the following attributes (fields): StudentID, Name, Address, City, State, Zip code, and Major. Populate at least three rows of information. Now display the information using the SELECT statement.

The UPDATE Statement

We can execute this statement in the same matter as previously discussed.

An image of the P h p my admin application showing S Q L Query from the browse page under Poll table.

We can see that the yes property of the first row of data in the poll is incremented to 1. If we run the same SQL statement again, yes will be incremented to a value of 2.

Note how the WHERE clause limits which rows will be affected by the update. Only the row with a poll_id of 1 will be affected. Any other rows in the table will not be updated, because of the WHERE clause.

Secure Programming

An UPDATE statement without a WHERE clause would update the yes attribute of all rows in the poll table. In this case, there is just one row, so the WHERE clause isn’t absolutely necessary. However, most tables you will work with will have much more than just one row, so it’s vital that the row(s) be explicitly declared. Otherwise, attributes might be updated that should not have, which will corrupt the data in the database.

Now that we have gained some basic SQL knowledge, it is time to code for our database-driven site poll.

Separating Concerns with MVC

The model-view-controller (MVC) design pattern is a common approach to organizing scripts consistently. Using a consistent approach to organizing your scripts can help you develop and debug faster and more efficiently.

The MVC framework is built upon the basic principles as defined by model-view-controller. Once you are familiar with the basic concepts, there are many MVC frameworks available to logically organize your PHP projects. Some of these include CodeIgniter, CakePHP, and Yii. These frameworks will aid in designing and developing more complex web applications.

At its most basic, MVC separates coding concerns into, you guessed it, three categories: models, views, and controllers. A model is a piece of code that represents data. Your models should also hold most logic involved in the system you’re building. A view is a piece of code that shows information visually. The information to be displayed by the view is received from a model. A controller is a piece of code that retrieves input from users and sends commands to relevant model(s). In short, MVC separates user interactions from visual representation from system logic and data. This provides the ability to divide large projects in which an HTML expert can create the views, a database expert can create the models, and a PHP programmer can create the controllers. We can also include multiple views which allow the code to be used both on the Web and in applications, without any major changes.

We have already seen examples of separating code into model, view, and controller. Remember how we made a template for HTML pages? We worked with a view that held a bare-bones HTML page skeleton. You can find it in the gallery we started building in Chapter 4. The view is located in ch4/templates/page.php.

In the same project, we created a model related to that view: the ch4/classes/Page_Data.class.php, which declares a number of methods and properties related to the content of the HTML pages.

The model and view were hooked up through a controller. In ch4/index.php, we assigned values to the model and made the model available to the view. It displayed a well-formed HTML5 page. Thus, index.php was our controller.

In this book, the aim is to use a simple implementation of MVC. Most other MVC implementations you will come across are likely to be much more elaborate. You can easily find many MVC examples that are not meant for beginning programmers. Once you understand the basic MVC principles and have gained some experience working with those principles in a simple context, you will find it much easier to understand the more elaborate implementations.

Exercise: Now that we have discovered that we have used MVC for our previous projects, return to the last project in Chapter 4. Change the folder names to indicate the model, view, and controller for the project. Make the necessary coding changes (it should not be very much) to reference the proper files within the new folders.

Planning the Logic

Let’s keep the poll simple. We will create an index.php to output a valid HTML5 page that will show the poll. The index will be a front controller.

A front controller is a design pattern very often seen in MVC web applications. A front controller is a single “entrance door” to a web application. We have already used a front controller in previous projects. Remember how index.php has been the only script loaded directly in your browser? It’s a front controller.

Note

The front controller design pattern is well documented online. You could start your own research at http://en.wikipedia.org/wiki/Front_Controller_pattern .

As in the previous projects, index.php will output a valid HTML5 page, and it will load the poll controller. The poll controller should return the poll as HTML, so it can be displayed on index.php. Note how every one view has its own model and its own controller (Figure 6-7).

A flowchart of the distribution of responsibilities starts from the front(page) controller to Models-Views-Controllers.

Notice the poll model, the poll controller, and the poll view. These three work together to display a functional poll. We can also see that the page has its own model, view, and controller. The front controller is the page controller.

Creating the Poll Project

Let’s organize our project logically using the basic MVC structure. We will create a new folder, ch6, in our executable folder (in XAMPP it’s the htdocs folder) and another folder, poll, inside this folder. We will also create three folders inside of the poll folder: models, views, and controllers. We can now copy the Privatepage.php program from the template folder in ch4 to the views folder under poll (rename it page.php). We can also copy the Private_Page_Data.class.php program from the ch4 classes folder into the models folder (rename it Page_Data.class.php). Of course, we could also download the files for this chapter from the publisher’s website.

This index program is just a slight modification from the original privateindex program seen previously. Some content has been removed, and the folder names and the values for title and content have changed! Let’s test it.

A image of admin webpage having main heading as, everything works so far exclamation mark and subheading saying , and the content goes here.

Our view has now changed. We are now looking at this code with a model-view-controller perspective. We can see how the created HTML page is a combination of a view merged with a model. We can see how the front controller hooks up the model and the view and outputs a well-formed HTML5 page to the browser for the user to see.

Making a Poll Controller

This format should look familiar, as it is very similar to the previous index examples in this textbook.

A image of admin webpage saying, Poll will show here soon.

Now our front controller and poll controller are working together to create a dynamic web page for our poll! Let’s work on our poll model next.

Making a Poll Model

The Poll class defines a blueprint for all Poll objects. The Poll class includes a method, getPollData(), which returns $this. What does the return accomplish? The simple answer is that it returns a pointer (address in memory) to the object for whatever code calls this method. What object, there is not one in this program? Before this method is called, an instance of this class (which we will see soon) must be created. Once it is created, then the getPollData() method can be called. At that point, an object with the contents of this class will exist in its own space in memory. The return statement passes a reference (pointer) to the object to the code that has called the function. This will allow the calling code to directly access and use the object and its contents.

The $pollData object has properties and get methods for poll_question, yes, and no. The $pollData object represents all the content required to show a poll. In other words, the $pollData models poll data.

Making a Poll View

This view uses the get methods to retrieve the results of the poll (currently defaulting to zero values) and places them into an unordered list.

Hooking Up Poll View with Poll Model

With a preliminary poll model and poll view created, we can update the poll controller to hook up model and view and, finally, show something in the browser.

<?php

//complete code listing for controllers/poll.php

include_once "models/Poll.class.php";

$poll = new Poll();

$pollData = $poll->getPollData();

include_once "views/poll-html.php";

?>

Listing 6-6

newpoll.php

The Poll class model is included into the controller, and an instance ($poll) is created. The getPollData method is called which creates the reference to the $poll object named $pollData. We say that $pollData is now a pointer which points to the object. We can now access anything in the object using $pollData. Finally, the poll-html.php view is attached which displays the poll results. That’s it! We have an MVC poll.

An application output (newpollindex.php) shows poll results, 0 for YES and NO.

Perhaps you’re dying to ask a question such as, Why should I create three different files to show a simple <ul> element? It would be a completely justified question to ask. If all you wanted was to show a <ul> element with a few hard-coded values, the MVC approach would be complete overkill. The best approach would probably be to write a short HTML program.

The point here is to introduce the MVC design pattern with a very simple example, so there is no overly complex code hiding the basic principles of MVC from your eyes. With the MVC approach, you are perfectly set up for creating a database-driven web application. The MVC architecture is hardly necessary for something this simple, but it can really solve some challenges you will come across with projects of greater complexity, such as the blogging system we will start making in the next chapter.

MVC encapsulates views from models and from controllers. That means we can change a view without changing anything else. Imagine we didn’t want a <ul> element for the poll. We could simply change the HTML tags used in views/poll-html.php and trust the rest of the code to run correctly. We can easily change a view without changing anything else.

Similarly, we could change the content and still trust our code to run as expected. It would be a simple task to set the no property to 9. We would only need to change a tiny bit of code. This poll application is built with self-contained elements.

Connecting to MySQL/MariaDB from PHP

The MVC architecture will make it a fairly straightforward task to connect the database and use database-driven data for the poll. Once we have established such a connection, we can retrieve data from our database and view it with HTML. That is the essence of an MVC database-driven website.

Sharing the Database Connection with the Poll Model

The constructor now accepts the pointer to the database connection object and places it in $db. Next, we will call the Poll class’s constructor and pass the PDO object pointer as an argument. Take a pause to reflect. Where in our code would we load the poll model?

There are only a couple of minor changes. The database.php program has been included and the controller has been updated to the newest version. If we execute this index file, we will see no changes in the output. However, we have connected to the database and passed the pointer to the data object. We are getting close to a complete program!

The variable $db is declared in newestpollindex.php. It is available in newestpoll.php because it is included in newestpollindex.php. Including a file is a lot like copying all the code from one file and pasting it into another file. As a consequence, all variables declared in any including file can be available in other included files, if those files are included afterward.

Retrieving Data with a PDOStatement

Inside the database, we have a poll table with a poll_question and values for its yes and no attributes. We are ready to retrieve data from the database.

The object is returned to the calling program (finalpoll.php).

The index file (controller) was also only slightly modified to call the new version of the poll controller. If we execute the finalpollindex.php program in our browser, we will discover the same output format as Figure 6-12. However, the one big difference is now the data is being pulled from the database. A true database-driven website!

Showing a Poll Form

A form was created to obtain the customer’s opinion. Once the customer responds by clicking the submit button, the response is passed, via post, to the index (controller).

Updating a Database Table According to Form Input

There is one final step required to complete the site poll example. We need to retrieve any user input submitted through the form and update the poll table with the received input. If a site visitor submits a no, we will increment the value of the no attribute in the poll database table.

The new method updatePoll() is similar to the getPollData method. However, if the user submitted a yes, we format an SQL string that can update the yes attribute in the poll table. If the user submitted a no, we format a different SQL string to update the no attribute. Notice that the yes or no values are incremented by one to add the user’s choice into the proper location in the database table. Whichever SQL string is created, it is stored in a variable called $updateSQL. Once the $updateSQL string is created, we again use the PDO method prepare() to convert the SQL string to a PDOStatement object, which is stored in the variable $updateStatement. We then call execute() to update the poll table.

Secure Programming

We should always be aware that a hacker can attempt to use SQL Injection to corrupt our tables and databases. SQL Injection is an attempt to insert SQL statements into a program’s use of SQL to insert or update data in the database. In future chapters, we will use prepared statements to create our SQL commands, which will reduce the possibility of successful injection. We will also validate the data before making any updates to the database. In the previous example, we did not use any information gathered from the user to format the SQL statement. Thus, the SQL statement created was secure and resistant to SQL Injection.

Responding to User Input

Controllers are responsible for dealing with user interactions . So, the poll controller is the right place to intercept user input from the poll form. There are a couple of things to add to the script.

<?php

//complete code listing for controllers/poll.php

include_once "models/FinalPoll.class.php";

$poll = new Poll( $db );

//check if form was submitted

$isPollSubmitted = isset( $_POST['user-input'] );

//if it was just submitted...

if ( $isPollSubmitted ) {

    //get input received from form

    $input = $_POST['user-input'];

    //...update model

    $poll->updatePoll( $input );

}

$pollData = $poll->getPollData();

include_once "views/completepoll-html.php";

?>

Listing 6-17

completepoll.php

//check if form was submitted

$isPollSubmitted = isset( $_POST['user-input'] );

//if it was just submitted...

if ( $isPollSubmitted ) {

We will use the isset method to determine if the user has chosen to give their opinion in the survey. If they have, we will update the results.

    //get input received from form

    $input = $_POST['user-input'];

    //...update model

    $poll->updatePoll( $input );

}

If they did, we will retrieve the value selected and place it into $input. Then we will pass that value to the updatePoll method, which will make the changes to the database table.

$pollData = $poll->getPollData();

include_once "views/completepoll-html.php";

Whether or not the user provides their opinion, the program will get the current poll results and display them by calling the HTML page.

Note

A minor change has occurred to the index (controller) program to call completepoll.php. You can view this change in the files downloaded from the publisher’s website.

To see the results, run completepollindex.php in the browser.

An application output (completepollindex.php) shows P H P and Poll results: 2 said YES, and 0 said NO.

We now have a completely functional poll site!

There is one weakness to this final version. Can you see it? Once the user has entered their opinion, the program allows them to enter it again. How can this be corrected? We can change the output to only display the question the first time the page is shown. Once the opinion has been given, then we can display the page that does not ask the question again, but does display the results. We can change the results to use finalpoll-html.php which was created earlier. This will require just a change to the include statement discussed earlier.

Exercise: Adjust the final poll project to accept more than one input from the user. Adjust the controller, model, and view to request, store, and display this information.