Making a Reusable Connection Script

Topics Covered in This PHP & MySQL Tutorial:

Error Checking, Making an Include, Sorting Results

Exercise Overview

Since database connections are fundamental to virtually every data-driven application, establishing a reusable connection pattern is essential for maintainable code. Rather than duplicating connection logic across multiple files—a practice that leads to maintenance nightmares and security vulnerabilities—we'll create a centralized database connection script that can be included wherever needed.

This approach follows the DRY (Don't Repeat Yourself) principle, a cornerstone of professional development that reduces code redundancy and makes updates more manageable. When you need to modify connection parameters or add security enhancements, you'll only need to update a single file.

If you did not complete the previous exercise, return to it before proceeding with this one, as the following steps build directly on that foundation.

1. You should still have MySQL.php open in your editor. If you've closed the file, navigate to the phpclass folder and reopen MySQL.php.

2. Locate the database connection script at the top of your page. It should read:

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

3. Select this entire line and cut it (Ctrl+X or Cmd+X).

4. Create a new file in your code editor.

5. Paste the connection code into this new file.

6. Wrap the connection code in PHP opening and closing tags as shown in bold:

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

7. Save this file as dbConnect.php in the inc folder within your phpclass directory. The inc folder is a standard convention for include files that contain reusable functionality.

8. Now we'll implement proper error handling—a critical practice that separates professional code from amateur scripts. Database connections can fail for numerous reasons: server downtime, incorrect credentials, network issues, or database corruption. Add the following error-checking code in bold:

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

   The $conn->connect_errno property returns a non-zero error number if the connection fails. When a connection error occurs, we immediately display the specific error message and terminate script execution with exit(). This prevents the application from attempting to execute database operations without a valid connection, which would cascade into additional errors and potentially expose sensitive system information.

9. Save the file to preserve your changes.

10. Return to MySQL.php to integrate our new connection include.

11. At the very top of the page, before any existing code, add the include statement shown in bold:

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

    Note that we use require_once() rather than include(). This function ensures the file is included exactly once and will halt execution if the file cannot be found—preventing silent failures that could compromise your application.

12. Save your changes and test the refactored code by navigating to:

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

13. The page should function identically to the previous version, demonstrating that our refactoring was successful. You'll use this same database connection script throughout the remaining exercises, providing a consistent and maintainable foundation for all database operations.

Sorting Results

Data presentation often requires specific ordering to be meaningful to users. Whether you're displaying customer lists, product catalogs, or user rankings, the ORDER BY clause in SQL provides powerful sorting capabilities that can dramatically improve user experience and data comprehension.

1. Return to your code editor with MySQL.php open.

2. Let's sort our user records alphabetically by last name, which creates a more professional presentation. Modify 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 file and refresh your browser:

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

   Notice how the records now appear in alphabetical order by last name, creating a more organized and user-friendly display.

4. Switch back to your code editor to explore reverse sorting.

5. To display records in descending (reverse alphabetical) order, add the DESC keyword as shown in bold:

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

   The default sort order is ASC (ascending), but explicitly specifying DESC provides clarity and prevents confusion when other developers review your code.

6. Save and refresh your browser:

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

   The records now display in reverse alphabetical order by last name, with names beginning with 'Z' appearing first.

7. For more sophisticated sorting scenarios, you can specify multiple sort criteria. This is particularly useful when dealing with duplicate values in your primary sort column. Consider a customer database where multiple people share the same last name—secondary sorting by first name creates a logical, alphabetized result. Update the $SQL string as shown in bold:

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

   MySQL processes ORDER BY clauses from left to right, so records are first sorted by lastName, then any ties are resolved by sorting those matching records by firstName.

8. Save your changes and test the multi-column sorting:

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

   You'll see records organized first by last name alphabetically, and any records sharing the same last name will be further sorted by first name. This creates the most intuitive ordering for name-based data.

9. Close all open files. You've successfully implemented both modular database connections and flexible result sorting—two fundamental skills that you'll use extensively in production applications.