Making a Reusable Connection Script

Topics covered in this PHP & MySQL tutorial:

Error checking, Making an include, Sorting results

Exercise Overview

Because we will be connecting to the same database for the rest of the book, it would make sense to save the connection script into another page. That way, we can just include it at the top of any page that needs to connect to the database—no need to retype it every time.

If you did not do the previous exercise, do it before starting this one.

1. You should still be in mysql.php. If you closed the file, reopen mysql.php from the phpclass folder.

2. At the top of the page, select the connection script that reads:

   $conn = new mysqli('localhost', 'root', 'root', 'phpclass_yourname');
   

3. Cut it.

4. Create a new file.

5. Paste in the connection code.

6. Wrap it in php tags as shown in bold:

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

7. Save the file as dbConnect.php into the inc folder in the phpclass folder.

8. Let’s add a bit of error-checking to make the script more robust. We’ll check to see if there is a connection error. If there is one, display the error and time out. Add the bold code:

   $conn = new mysqli('localhost', 'root', 'root', 'phpclass_yourname');
   
   if ($conn->connect_errno) {
      echo "Connection Failed: " . $conn->connect_error;
      exit;
   }
   

   $conn->connect_errno returns the error number of our connection if there is one. If there is an error we should exit() out of the page because the rest of our page will likely fail to work without a database connection. We also echo out the specific error.

9. Save the page.

10. Last we need to include this script in our page. Switch back to mysql.php.

11. At the top of the page above the other code, add the following bold code:

    <?php 
    
       require_once('inc/dbConnect.php');
       $sql = 'SELECT * FROM users';
       $result = $conn->query($sql) or die($conn->error);
    

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

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

13. It should work just like it did before. You’ll use this same script to connect to the database in later exercises.

Sorting Results

Sometimes when writing a query, you’ll want to output the results in a certain order. Luckily this is a fairly easy process.

1. Switch back to your code editor.

2. First let’s output the records and order them by lastName. Edit the $sql string as shown in bold:

   require_once('inc/dbConnect.php');
   $sql = 'SELECT * FROM users ORDER BY lastName';
   $result = $conn->query($sql) or die($conn->error);
   

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

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

   The records will now display in alphabetical order by last name.

4. Switch back to your code editor.

5. What if we wanted them in reverse order? Edit the $sql string as shown in bold:

   $sql = 'SELECT * FROM users ORDER BY lastName DESC';
   

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

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

   The records will be in reverse alphabetical order by last name.

7. You can also combine ORDER BY statements so that it will order by multiple items. For example, say you wanted to order by last name, and then by first name. This way if you had multiple people with the same last name, they would then be ordered by their first names. Edit the $sql string as shown in bold:

   $sql = 'SELECT * FROM users ORDER BY lastName, firstName';
   

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

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

   The records will be in alphabetical order by last name, and then if you have any records with the same last name, you’ll see them ordered by first name as well.

9. Close any open files. We’re done for now.