Data is probably the cornerstone of most web applications. Sure, your application has to be pretty, fast, error-free, and so on, but if something is essential to users, it is what data you can manage for them. From this, we can extract that managing data is one of the most important things you have to consider when designing your application.
Managing data implies not only storing read-only files and reading them when needed, as we were doing so far, but also adding, fetching, updating, and removing individual pieces of information. For this, we need a tool that categorizes our data and makes these tasks easier for us, and this is when databases come into play.
In this chapter, you will learn about:
- Schemas and tables
- Manipulating and querying data
- Using PDO to connect your database with PHP
- Indexing your data
- Constructing complex queries in joining tables
Databases are tools to manage data. The basic functions of a database are inserting, searching, updating, and deleting data, even though most database systems do more than this. Databases are classified into two different categories depending on how they store data: relational and nonrelational databases.
Relational databases structure data in a very detailed way, forcing the user to use a defined format and allowing the creation of connections—that is, relations—between different pieces of information. Nonrelational databases are systems that store data in a more relaxed way, as though there were no apparent structure. Even though with these very vague definitions you could assume that everybody would like to use relational databases, both systems are very useful; it just depends on how you want to use them.
In this book, we will focus on relational databases as they are widely used in small web applications, in which there are not huge amounts of data. The reason is that usually the application contains data that is interrelated; for example, our application could store sales, which are composed of customers and books.
MySQL has been the favorite choice of PHP developers for quite a long time. It is a relational database system that uses SQL as the language to communicate with the system. SQL is used in quite a few other systems, which makes things easier in case you need to switch databases or just need to understand an application with a different database than the one you are used to. The rest of the chapter will be focused on MySQL, but it will be helpful for you even if you choose a different SQL system.
In order to use MySQL, you need to install two applications: the server and the client. You might remember server-client applications from Chapter 2, Web Applications with PHP. The MySQL server is a program that listens for instructions or queries from clients, executes them, and returns a result. You need to start the server in order to access the database; take a look at Chapter 1, Setting Up the Environment, on how to do this. The client is an application that allows you to construct instructions and send them to the server, and it is the one that you will use.
Note
GUI versus command line
The Graphical User Interface (GUI) is very common when using a database. It helps you in constructing instructions, and you can even manage data without them using just visual tables. On the other hand, command-line clients force you to write all the commands by hand, but they are lighter than GUIs, faster to start, and force you to remember how to write SQL, which you need when you write your applications in PHP. Also, in general, almost any machine with a database will have a MySQL client but might not have a graphical application.
You can choose the one that you are more comfortable with as you will usually work with your own machine. However, keep in mind that a basic knowledge of the command line will save your life on several occasions.
In order to connect the client with a server, you need to provide some information on where to connect and the credentials for the user to use. If you do not customize your MySQL installation, you should at least have a root user with no password, which is the one we will use. You could think that this seems to be a horrible security hole, and it might be so, but you should not be able to connect using this user if you do not connect from the same machine on which the server is. The most common arguments that you can use to provide information when starting the client are:
-u <name>: This specifies the user—in our case,root.-p<password>: Without a space, this specifies the password. As we do not have a password for our user, we do not need to provide this.-h <host>: This specifies where to connect. By default, the client connects to the same machine. As this is our case, there is no need to specify any. If you had to, you could specify either an IP address or a hostname.<schema name>: This specifies the name of the schema to use. We will explain in a bit what this means.
With these rules, you should be able to connect to your database with the mysql -u root command. You should get an output very similar to the following one:
$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
The terminal will show you the version of the server and some useful information about how to use the client. From now on, the command line will start with mysql> instead of your normal prompt, showing you that you are using the MySQL client. In order to execute queries, just type the query, end it with a semicolon, and press Enter. The client will send the query to the server and will show the result of it. To exit the client, you can either type q and press Enter or press Ctrl + D, even though this last option will depend on your operating system.