5. PHP and MySQL
If we want to store data (for example from web forms) we can do that in text files. And that works fine as long as it is not too much data and as long as we do not perform complicated operations or searches through this data. As soon as we do want to do that, it's smart to use a database system. The database takes care of the storage in files, you have nothing to do with that. Furthermore, the database provides a system to query the data. This is done using SQL, (Structured Query Language). SQL will be discussed in the next chapter although you will see some of it here.
MySQL is such a database system, we use this system because:
- it is open source (and therefore free)
- despite the fact that it is free, it is a very extensive and stable database server
- it is the most used database in combination with PHP
- already installed on your computer if you have XAMPP installed
A database is a system that takes care of storing, editing and querying data. And that's really all it is. There is nothing to see. If you have ever opened Microsoft Access, you know that there is something to see (tables, queries, forms, etc). Although this is often called the database, it is actually a DBMS (DataBase Management System) that you see. A DBMS is the interface for a user to the underlying database. For MySQL we also need such an interface, there are several, but again we choose the most used and most user-friendly variant that again is already installed on your computer if you have XAMP installed. This DBMS is called PhpMyAdmin.
PhpMyAdmin can be found, if it is installed, at http://localhost/phpmyadmin/. In this case the DBMS is not secured (you don't have to log in), because we only work on our own computer. In PhpMyAdmin we can edit the databases. We can create new databases, add tables, change tables, delete, query the databases and more. For more information you can check out the website of the PhpMyAdmin Project.
5.4. A database
It goes too far to go into detail here about how databases exactly work. You can follow a MySQL Administering training with us. What we do is create a database that can store the responses from our form. It will be a Database called Responses with two tables:
Note: The above image was created with MS Access because PhpMyAdmin does not have such a view. It is only meant as an illustration!
What we see is a database (the whole) called reactions. Two so-called tables called persons and reactions. Both tables on their turn have so-called attributes. Furthermore there is a one-to-many relationship between persons and reactions which means that one person can make multiple reactions. Another important aspect is that both tables have an Id attribute. This ensures that a record that will eventually be placed in the table is uniquely identifiable (it is possible that a person sends the same message twice, we want to be able to keep these apart).
You can try to create the above database yourself using PhpMyAdmin. You can also just create.txt download, copy the content to the clipboard, to http://localhost/phpmyadmin/ surf and left click on the button SQL () click. A so-called query window where you can paste the contents of the clipboard. At Go (or start depending on your version) will create the Reactions database! We will continue with this in the next chapter.