Lesson 12

Capturing data

When we want to save some data our users submit via web form, most often we do it by storing those data in a database.

There are many database vendors, services and technologies. One of the most commonly used for PHP is the MySQL database. It is actually a part of a popular group of open source software running together called LAMP (Linux, Apache, MySQL, PHP).

To create your first MySQL database start Wamp and open phpMyAdmin page:

You might be prompted to enter username and password. On local PC machines username will be “root” and password leave empty. On Mac OSX – both username and password are “root”.

Next we will want to create our first database:

Click “New” and call the database “hh_db” (short for Hacking Heroes Database). Set the Collation to “utf8” or “utf8_[your-language].

Once the database is created we can add our first table. Databases can store many tables. When designing databases we want to group specific data into certain tables. For instance – we want to store personal data of all students in a table called “students”. Personal data will include:

  • first name
  • last name
  • age
  • short biography

The above elements will be our table’s columns. It is not mandatory, but it is a good practice to also have a row id column. And if we make the value of that column a unique number, and automatically increment it for each row, it will later make our data tables’ operations much easier and faster. To do that we make that column “primary”.

So, all together we will have 5 columns. Columns names shouldn’t contain spaces nor special characters:

  1. id
  2. first_name
  3. last_name
  4. age
  5. bio

Let’s add these to our table:

We need to set a specific data types for each column. id is an integer / number (INT) – and we give it length 4 – in our case will be enough (so id values can go up to 9999).

For first_name and last_name we use data type VARCHAR which is designed to store short strings (short text values) and give it a length of 50 characters.

Age is an INT again and we set it’s length to 3 – in case we would want some day to accept students that are 100+ years old.

Our bio is of type TEXT – which is designed to store large amounts of text.

For the last 2 columns we’ve ticked a box for  Nullit means that we allow for data insertion even if no data values are set for age and bio column. It will make more sense later.

Now that our database and students table have been created, let’s code some HTML form to allow users to insert some data to it:

See the Pen Insert Data by HackingHeroes.org (@hackingheroes) on CodePen.dark

The action of the form has been set to save_new_student.php

We can write our database connection in the save_new_student.php file, but chances are we might want to use the db connection later on other pages too – so let’s keep it in a standalone file and include it where we need it.

 

That’s all we need. $conn stores the status of our connection and makes all it’s properties and methods available to us.

Let’s retrieve values from our form submission:

and let’s insert them to our table:

Variable $sql stores a database command written in SQL. It’s a special language for database operations. Here we say INSERT data INTO table called students and set values for given columns with the values of POST variables from form submission.

If we’re succesfull “New record created successfully” message will be displayed.

Try submitting some data to your form

and see if it has been saved in your database:

« Lesson 11 Index Lesson 13 »