PHP for Beginners Part 3

We already have a data source from PHP for Beginners Part 1 and PHP for Beginners Part 2 that makes our application dynamic. It reads and displays pet data, which happens to be stored in a file called pets.json. If we change something in this file, the site updates automatically. For a more complex site, however, we'll need to read and write a lot of data. So we're going to dive into the world of databases now and learn how to manage more data. This first tutorial covers database basics and how to connect and communicate with a MySQL database.

Additional resources

Guide: PHP Fundamentals

In this tutorial, now that we've connected to our database, we're going to learn the basics of SQL queries. You'll learn how to create databases, the tables that live in them, and how to do a basic SELECT query to retrieve information.

In this tutorial you'll get familiar with the INSERT and SELECT SQL commands to let you add and retrieve data from your database.

We're going to bring things together in this tutorial by finishing the basic queries of UPDATE and DELET, along with SHOW. Then we'll wrap things up by taking a look at the most popular MySQL GUI, phpMyAdmin.

Now that we understand the basics of a MySQL database and using SQL queries to talk with it, in this tutorial we're going to see how to talk to our database from inside our PHP code. You can download the sample code below.

To talk to the database, we first open a connection using a class called PDO. This returns an object, which we set to the $pdo variable. In this tutorial we're going to talk a little more about what this means and how we use them. We'll also take a quick look at how this "new" object-oriented way of doing things is different from the old, deprecated functions.

To make it easier to control your app, configuration, like your database username and password, is usually isolated into its own file. In this tutorial we'll create a new file called config.php and see how to simplify our database information.

As we add more pets to the database, our query is going to return a lot of results. In this tutorial we'll take a look at how to control the number of results we get back from our query using LIMIT. We'll also end up introducing a security hole in our code, which we're going to correct later in this series.

In this tutorial we're going to add our $limit variable as an optional argument to our get_pets() function. We're using this variable in our if statement, and we're going to look at different ways to supply "true" and "false" using data other than booleans. This allows you to pass in a value and still evaluate true/false. PHP has some conventions for what is true or not, and it's a good idea to understand what these are.

In this tutorial we're going to create pages for each of our pets by using a query parameter. This is a bit of code that is added to the end of a URL to pass information to PHP. They look something like ?id=23. On the PHP side of things we'll use the $_GET superglobal variable to pull that data into our code.

Our last big piece to the puzzle is to create a function to query the data for just one pet. In the process of doing this, we're going to get a lesson in function scope, where each function is its own little universe and you only have access to the arguments passed in and any variables you create in that function. This can trip you up when you need to access that data from another function. In this tutorial, we'll get that all sorted out and finish up our get_pet() function.

Both get_pets() and get_pet() contain an SQL query where one part of it is a variable. Whenever you have this situation, you’re opening yourself up for an SQL injection attack. In this tutorial we'll see how this works by exploiting the security hole, and then fixing it up with prepared statements. Prepared statements let us build a query where the variable parts are kept separate from the rest of the query. This will conclude our work for this section of the site and managing databases.