Creating a Database/MySQL/SELECT

Topics covered in this PHP & MySQL tutorial:

Creating a new database, Connecting to the database, SQL basics, The SELECT statement, Display the number of rows returned, Making a reusable connection script, MySQL vs. MySQLi vs. PDO

Exercise Overview

One of the biggest reasons to use a language like PHP is to connect to a database, allowing you to get and store data for a huge variety of possible applications such as customer info, store and product info, blogs, forums, etc.

MySQL is one of the world’s most popular databases because it is fast, free, stable, and feature-rich. It works great with PHP, and like PHP, it is available on almost every single host. Because of this, PHP and MySQL almost always go hand-in-hand.

In this exercise, you’ll learn how to create a database in the phpMyAdmin control panel, as well as how to connect to a database and display some data.

Creating a New Database

Adding Some Information to the Database

Now that we have a database, we need to make a table to hold some user information. In a staggering leap of creative genius we’ll call it… users.

1. You should still be in the phpMyAdmin. Click on the phpclass_yourname link.

2. In the area that says Create table, for Name enter users and for Number of columns enter 4.

   

3. Click Go. You’ll see an empty table with four blank fields. We’re going to create fields for id, firstName, lastName, and email.

   Every new table should have an id. The id should also be a unique number—this way, it is easy to keep track of each row in the database. This unique identifier is called a Primary Key. We’ll also set something called Auto Increment (A_I) which will automatically increment the id by 1 every time a new row is made. This ensures that each id number is unique and saves us the trouble of having to increment it manually.

4. Set the following:

   Name	Type	Length/Values	Attributes	Index	A_I
   id	INT		UNSIGNED	PRIMARY	checked
   firstName	VARCHAR	255
   lastName	VARCHAR	255
   email	VARCHAR	255

   To break this down:

   • id will be the name of the identity field.
   • INT is an integer with a range from −2147483648 to 2147483647. That’s a lot of values!
   • We don’t need to set a length for this field (it defaults to 10).
   • UNSIGNED sets it so that only positive numbers are allowed. This increases the usable range for our index to 0 to 4294967295. That’s even more values! If you think your application will have more than 4 billion users you can use BIGINT instead of INT.
   • PRIMARY sets the id column to be the Primary Key for this table.
   • A_I stands for Auto Increment and increments the id by 1 every time a new row is added.
   • VARCHAR is a character data type. In our case we set the length to 255 which means we can store up to 255 characters of text.

5. In the bottom right, click Save. Your table will be created!

Adding Some Data

1. Click the users table.

2. At the top of the page, click the Insert tab.

3. In the top box enter the following:

   Name	Value
   firstName	Your First Name
   lastName	Your Last Name
   email	Your Email

   Leave id blank because it will auto increment for us.

4. In the bottom box enter the following:

   Name	Value
   firstName	Noble
   lastName	Desktop
   email	noble@nobledesktop.com

5. Click Go. (You can click either Go button.) Two new rows will be created.

Connecting to the Database

There are three main ways PHP can interact with a database: MySQL Regular, MySQL Improved (MySQLi), and PDO. The first, MySQL (regular), is the original language PHP used to interact with MySQL. However, it is no longer being developed and PHP recommends you do not use it anymore. It is important to be aware of it, as there are quite a few outdated examples online that still show it. MySQLi is the improved version of the MySQL PHP extension and it is what PHP recommends you use. It can be written in a procedural syntax like the original MySQL extension (in fact the biggest difference will be the “i” at the end of the functions), but can also be written in an object-oriented style. The last, PDO, is an extension that is not tied to MySQL specifically but rather is designed to work with any type of database (such as MS SQL or Oracle). If you plan on potentially moving your product to a different database, it is recommended you use PDO.

So which should you use? Do not use the original MySQL extension. Your choice is between MySQLi and PDO. For this book we will show you MySQLi as it is the one recommended by PHP. It is also highly unlikely that any application you develop will actually switch from MySQL to another database—in that rare case, you can learn PDO if you need it. PDO does have some other advanced features and some developers prefer it, but those advantages are beyond the scope of this book. MySQLi has the advantage of also being able to use every new feature of MySQL, whereas PDO can sometimes lag behind in support. Given that MySQLi is the official method and is easier to just learn one at a time, we will show MySQLi. Once you are very comfortable with it, you can learn PDO if you need it.

1. In your code editor, open mysql.php from the phpclass folder. Here we have a simple page started for you that has an empty table. We’re going to connect to our database and then display the users table.

   The first thing to do is establish a connection to the new database. First we create a mysqli object and save it into a variable. The mysqli() function takes a number of parameters, the four most common ones being: server name, mysql username, mysql password, and database name. Note that MAMP and XAMPP have different default passwords to connect to the MySQL database.

2. At the top of the document, add the following bold code (Make sure to add the correct line for your operating system.):

   <?php
   
      Mac: $conn = new mysqli('localhost', 'root', 'root', 'phpclass_yourname');
   
      Windows: $conn = new mysqli('localhost', 'root', '', 'phpclass_yourname');
   
   ?>
   

   The MySQL server is running on the localhost, with the username of root, the password is either root (Mac) or blank (Windows), and we are connecting to the database we just made, phpclass_yourname.

3. We need to prepare our SQL statement. SQL is the language that is used to interact with the database. It allows us to view, update, insert, and delete records among other things.

   Add the following bold code:

   <?php 
   
      $conn = new mysqli('localhost', 'root', 'root', 'phpclass_yourname');
      $sql = 'SELECT * FROM users';
   
   ?>
   

   Let’s break this down:

   • First we save the string into a variable $sql (it could be called anything).
   • The string is our SQL statement, and it says to select everything from the table users. SELECT does what it sounds like and selects, or “gets,” rows from the database.
   • The * says to get all of the columns. If you only wanted to get certain columns, such as id and email you would list them like: SELECT id, email FROM users
   • You must always tell SQL which table you want to select records from (in this case it is users).

4. The SQL is written, but it still needs to be submitted to the server and the results need to be stored in a variable. Add the following bold code:

   <?php 
   
      $conn = new mysqli('localhost', 'root', 'root', 'phpclass_yourname');
      $sql = 'SELECT * FROM users';
      $result = $conn->query($sql) or die($conn->error);
   
   ?>
   

   • This stores the result in the $result variable.
   • It submits a query to the $conn object.
   • If there is an error, you can display it with $conn->error. In this case, we say or die() which means that if there is a problem, it will stop the page processing and display an error (on a production site, you would not want to display this error, but you probably want to still die() the page).

5. Save the page and then in a browser go to:

   • Mac: localhost:8888/phpclass/mysql.php
   • Windows: localhost/phpclass/mysql.php

   You should see an empty table with some headers and hopefully no error messages.

6. OK, we’re getting pretty close. All that has to happen now is to loop through the results. Return to the code, and around line 31 find the empty <tr>. We will loop through the query results and output a new <tr> each time. Add the following bold code around the empty <tr>:

   <?php while ($row = $result->fetch_assoc()):?>
      <tr>
         <td></td>
         <td></td>
         <td></td>
         <td></td>
      </tr>
   <?php endwhile;?>
   

   This uses the alternative syntax of while statements, making it easier to mix into the HTML. It fetches the $result one row at a time. The $row is an associative array that contains the row’s information.

7. Now we can output each field’s data. Add the following bold code:

   <?php while ($row = $result->fetch_assoc()):?>
      <tr>
         <td><?php echo $row['id']; ?></td>
         <td><?php echo $row['firstName']; ?></td>
         <td><?php echo $row['lastName']; ?></td>
         <td><?php echo $row['email']; ?></td>
      </tr>
   <?php endwhile;?>
   

   The name of each field is the corresponding name of the array element.

8. Save the page and then in a browser go to:

   -Mac: localhost:8888/phpclass/mysql.php
   -Windows: localhost/phpclass/mysql.php

   You’ll see a list of the two rows of data that you input earlier!

Display the Number of Rows Returned

It would be good to display how many rows were returned. This can be useful for searches, or for logins to make sure there were results found.

1. In between the <body> and the <table> tag, add the following bold code:

   <p>
      <?php echo $result->num_rows; ?> rows found.
   </p>
   

   The magic here is in the $result->num_rows;. This, as the name suggests, gets the number of rows returned from our query.

2. Save the page and then in a browser go to:

   • Mac: localhost:8888/phpclass/mysql.php
   • Windows: localhost/phpclass/mysql.php

   You should see the number of rows found.

3. Switch back to your code editor.

4. Leave mysql.php open. We’ll continue working with it in the next exercise.

   It may seem like a lot of work, but you’ve just learned an immensely powerful tool. In the next few exercises, we’ll take it further and learn how to insert, update, and delete rows, as well as use filters to only select certain information.