Lesson 3 - Form handling and printing database data into table in PHP

PHP Databases for beginners Form handling and printing database data into table in PHP

In the previous lesson, First database table and MySQL PHP drivers, we established a connection with the MySQL database and added a couple of users to it using PHP. In real life applications, we add users to databases using forms, which is what we are going to go over today along with printing users from the database into an HTML table. We'll keep things as short and simple as possible.

Form

Let's start by creating a simple HTML page with a form that will be used to add users to the database. We will modify our insert query section to make it use the values from the form. Here's what the user registration part of our code will look like:

<!DOCTYPE html>
<html lang="en">

        <head>
                <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
                <title>User sign up</title>
        </head>

        <body>

                <h1>User sign up</h1>

                <?php

                require_once('Db.php');
                Db::connect('127.0.0.1', 'web_database', 'root', '');
                if ($_POST)
                {
                        $date = date("Y-m-d H:i:s", strtotime($_POST['date_of_birth']));
                        Db::query('
                                INSERT INTO user (first_name, last_name, date_of_birth)
                                VALUES (?, ?, ?)
                        ', $_POST['first_name'], $_POST['last_name'], $date);

                        echo('<p>You have successfully signed up.</p>');
                }
                ?>

                <form method="post">
                        First name:<br />
                        <input type="text" name="first_name" /><br />
                        Last name:<br />
                        <input type="text" name="last_name" /><br />
                        Date of birth:<br />
                        <input type="text" name="date_of_birth" /><br />
                        <input type="submit" value="Sign up" />
                </form>

        </body>
</html>

The code is very short, but we will go over it regardless. The Form is simple, it has 3 fields (first name, last name, date of birth) and a submit button. Due to the fact that form does not have an action attribute, it will send the values to the page it is on.

I also added a PHP script, which connects to the database, and checks whether at least some of the form data was sent. If so, it runs the SQL query, which inserts data into the database. Make sure you keep the following 3 things in mind:

  1. We have to convert the date from the user's regional format (as he entered it, e.g. 01/15/1989) to the MySQL format (1989-01-15). The functions str_to_time() and date() do that for us.
  2. The SQL request is very similar to the one from the previous lesson. However, we no longer have to insert data to each and every column, only in 3 of them. The default value of (0) will be inserted into the number_of_articles column.
  3. We need to insert PHP variables (especially user from the $_POST array) to the query. Beware: Don't EVER insert variables directly into the query! If the user types in an SQL command instead of his name, it would become part of the query and it would be executed in your database! Always write question marks instead of query parameters, and pass said parameters in the same order later as we did with the Db::query() function! This mistake is done time and time again by beginners, putting their user data at risk!

To make it clear, let's take a look at how to NOT do it:

// THIS CODE IS VERY DANGEROUS!
Db::query('
        INSERT INTO user (first_name, last_name, date_of_birth)
        VALUES ("'. $_POST['first_name'] . '", "' . $_POST['last_name'] . '", "' . $date . '")');

The variables were inserted directly into the SQL query. In other words, if the user writes the following string in the first form field:

", "", ""); DELETE FROM user; --

The query would delete every user you have in your database, because what he wrote will be inserted directly into a query and executed as a command. We call this sort of attack an SQL Injection. Luckily, our wrapper is immune to this particular attack because of how it is set up. However, this is not a default behavior and will not protect you from other kinds of injections.

Whenever we want to insert a parameter into a query, we use a question mark and we write the parameter outside of the query! The database will insert the parameter in its place safely. Do not interrupt an SQL query string in any way, shape or form.

Just to be clear, here is the corrected version of the query shown above:

Db::query('
        INSERT INTO user (first_name, last_name, date_of_birth)
        VALUES (?, ?, ?)
', $_POST['first_name'], $_POST['last_name'], $date);

Let's test it out and add a new user:

User sign up
localhost/db_test/

User sign up
localhost/db_test/

Check the database, to make sure that it's really there:

User in MySQL database

Printing data

Now, let's print data from the database into an HTML table in our script. Insert the following PHP code at the bottom of the PHP block:

$users = Db::queryAll('
        SELECT *
        FROM user
');
echo('<h2>Users</h2><table border="1">');
foreach ($users as $user)
{
        echo('<tr><td>' . htmlspecialchars($user['first_name']));
        echo('</td><td>' . htmlspecialchars($user['last_name']));
        $date = date("m/d/Y", strtotime($user['date_of_birth']));
        echo('</td><td>' . htmlspecialchars($date));
        echo('</td><td>' . htmlspecialchars($user['articles_count']));
        echo('</td></tr>');
}
echo('</table>');

The most important thing here is calling the Db::queryAll() function. What it does is execute a database query, just like Db:query(), but it also returns all of the rows that were selected as a result of a query. We will use it for reading and use Db::query() for writing (editing, adding, deleting).

The SQL query itself is only made up of 4 words. It could be paraphrased to "Pick every column from user". The asterisk character means every single column. Due to the fact that we didn't specify which users we wanted, it will select each and every one.

As a result, we get an array of rows and store it into our $users variable. Then, we iterate over the array with using the foreach loop and echo out each row and column into the HTML table. Don't forget to use the htmlspecialchars() function, otherwise, people would be able to insert JavaScript instead of their names, which would be executed when it reached the printing phase. We call this kind of attack XSS.

Let's look at the application output:

User sign up
localhost/db_test/

Go ahead and add some more users, they will appear in the table. That is all for today's lesson. I hope that this first couple of lessons have helped break the ice through practical use of the learning material. In the next lesson, Programming of NOOCMS, we are going to work on CMS. The source code from today's lesson can be downloaded, as always, in the attachment below.


 

Download

Downloaded 59x (3.07 kB)
Application includes source codes in language PHP

 

 

Article has been written for you by David Capka
Avatar
Do you like this article?
2 votes
The author is a programmer, who likes web technologies and being the lead/chief article writer at ICT.social. He shares his knowledge with the community and is always looking to improve. He believes that anyone can do what they set their mind to.
Unicorn College The author learned IT at the Unicorn College - a prestigious college providing education on IT and economics.
Thumbnail
All articles in this section
Databases in PHP for beginners
Thumbnail
Next article
Programming of NOOCMS
Activities (5)

 

 

Comments

Avatar
John Dooe
Member
Avatar
John Dooe:8. August 6:32

just to point something out in case you get this error

"Warning: PDOStatement::e­xecute(): SQLSTATE[HY000]: General error: 1364 Field 'articles_count' doesn't have a default value"

set the default value of "articles_count" to zero, I think it's related to the strict mode in MySQL. That's how I solved the error.

Edited 8. August 6:33
Reply  +1 8. August 6:32
Life is simple, don't make it complicated.
To maintain the quality of discussion, we only allow registered members to comment. Sign in. If you're new, Sign up, it's free.

1 messages from 1 displayed.