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

Database connectivity represents the cornerstone of modern web development. PHP's robust database integration capabilities enable you to build sophisticated applications that manage customer relationships, e-commerce platforms, content management systems, user authentication systems, and countless other data-driven solutions that power today's digital economy.

MySQL remains the world's most widely adopted open-source database management system, powering everything from startup MVPs to enterprise-scale applications. Its combination of performance, reliability, and cost-effectiveness—coupled with seamless PHP integration—makes it the de facto standard for web development. This ubiquity means you'll find MySQL support on virtually every hosting platform, making your applications highly portable and deployable.

In this comprehensive exercise, you'll master the fundamentals of database creation using phpMyAdmin's intuitive interface, establish secure database connections, and implement data retrieval patterns that form the foundation of professional web applications. These skills will serve as building blocks for more advanced database operations you'll encounter in production environments.

Creating a New Database

Adding Some Information to the Database

With your database established, the next step involves creating a table structure to organize your data effectively. We'll create a users table—a fundamental component in most web applications that handles user management, authentication, and profile information.

1. Ensure you're still in phpMyAdmin and click on the phpclass_yourname database link to select it as your active database.

2. Locate the Create table section. Enter users for the table Name and 4 for Number of columns. This creates a table optimized for basic user information storage.

   

3. Click Go to proceed to the table structure definition interface. You'll see four empty field configurations where we'll define columns for id, firstName, lastName, and email.

   Every professional database table requires an id field serving as the Primary Key—a unique identifier that ensures each record can be distinctly referenced and retrieved. We'll implement Auto Increment functionality, which automatically generates sequential unique numbers for each new record, eliminating manual ID management and preventing duplicate key errors.

4. Configure the table structure with the following specifications:

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

   Understanding these configuration choices:

   • The id field uses INT data type, providing integer values ranging from −2,147,483,648 to 2,147,483,647—more than sufficient for most applications.
   • Length defaults to 10 digits for INT fields, providing ample capacity without specifying custom length values.
   • UNSIGNED restricts values to positive numbers only, effectively doubling the useful range to 0-4,294,967,295. For applications expecting extreme scale, consider BIGINT for virtually unlimited capacity.
   • PRIMARY designates this field as the table's primary key, ensuring uniqueness and optimal query performance through automatic indexing.
   • A_I (Auto Increment) automatically generates sequential ID values for new records, streamlining data insertion processes.
   • VARCHAR(255) accommodates variable-length text up to 255 characters, ideal for names and email addresses while maintaining storage efficiency.

5. Click Save in the bottom right corner to create your table structure. MySQL will generate the table with your specified configuration.

Adding Some Data

Now we'll populate your newly created table with sample data that will demonstrate the database connection and retrieval functionality in subsequent steps.

1. Click on the users table in the left sidebar to select it as your active table.

2. Navigate to the Insert tab at the top of the interface to access the data entry form.

3. In the first row of input fields, enter your personal information:

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

   Leave the id field empty—the auto-increment functionality will automatically assign a unique identifier.

4. In the second row of input fields, add this sample data:

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

5. Click Go (either button works) to insert both records simultaneously. You'll see confirmation that two new rows have been successfully created.

Connecting to the Database

Understanding PHP's database connectivity options is crucial for making informed architectural decisions. Let's examine the three primary approaches and why modern development standards have evolved.

PHP offers three distinct methods for database interaction: MySQL (Original), MySQLi (MySQL Improved), and PDO (PHP Data Objects). The original MySQL extension, while historically significant, has been deprecated and officially removed from PHP 7.0+. You'll still encounter legacy code using this approach, but it poses security vulnerabilities and lacks modern features—avoid it entirely in new projects.

MySQLi represents the evolution of MySQL connectivity, offering both procedural and object-oriented programming paradigms. It provides enhanced security through prepared statements, improved debugging capabilities, and support for advanced MySQL features like transactions and enhanced prepared statements. MySQLi remains MySQL-specific but leverages every feature the database offers.

PDO takes a database-agnostic approach, providing a consistent interface across multiple database systems including PostgreSQL, SQLite, Oracle, and SQL Server. While this flexibility is valuable for applications that might migrate between database platforms, the reality is that such migrations are rare in practice. PDO can sometimes lag behind in supporting the newest MySQL-specific features due to its generalized approach.

For this tutorial, we'll focus on MySQLi using object-oriented syntax. This choice aligns with PHP's official recommendations and provides direct access to MySQL's full feature set. Once you master MySQLi, transitioning to PDO becomes straightforward if your project requirements demand database flexibility.

1. Open MySQL.php from the phpclass folder in your code editor. This file contains a basic HTML structure with an empty table that we'll populate with database content.

   Database connections require four essential parameters: server hostname, MySQL username, MySQL password, and target database name. Note that MAMP and XAMPP use different default authentication credentials for local development environments.

2. Add the following code at the top of the document, selecting the appropriate line for your development environment:

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

   This instantiates a new MySQLi connection object with environment-specific credentials: localhost as the server (your local machine), root as the username (default administrator account), password set to root (Mac) or empty string (Windows), and your custom database name as the target database.

3. Next, we'll construct our SQL query using industry-standard SQL syntax. Add the following code:

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

   Breaking down this SQL statement:

   • We store the query string in the $SQL variable for clarity and reusability.
   • SELECT initiates a data retrieval operation from the database.
   • The * wildcard character selects all available columns. In production code, explicitly listing required columns (e.g., SELECT id, email FROM users) improves performance and security.
   • FROM users specifies the target table for the query operation.

4. Now we'll execute the query and handle potential errors gracefully. Add this code:

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

   • The $result variable stores the query result set for subsequent processing.
   • $conn->query($SQL) executes the SQL statement against the established database connection.
   • or die($conn->error) implements basic error handling: if the query fails, execution halts and displays the specific database error. In production environments, implement more sophisticated error logging and user-friendly error messages instead of exposing technical details.

5. Test your connection by saving the file and navigating to it in your browser:

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

   You should see an empty table structure without error messages, indicating successful database connectivity.

6. Now we'll implement the data display logic. Locate the empty <tr> element around line 31 and wrap it with this PHP loop:

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

   This implements PHP's alternative syntax for control structures, which integrates cleanly with HTML markup. The fetch_assoc() method retrieves each database row as an associative array, with column names serving as array keys.

7. Complete the implementation by outputting the actual data in each table cell:

   <?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;?>

   Each $row['fieldname'] reference accesses the corresponding database column value for the current row iteration.

8. Save your changes and refresh the browser page. You should now see both data records displayed in a properly formatted table, demonstrating successful database integration.

Display the Number of Rows Returned

Displaying result counts enhances user experience and provides valuable feedback for search operations, pagination systems, and data validation processes. This functionality is particularly important for user interfaces where people need immediate feedback about query results.

1. Insert the following code between the <body> opening tag and the <table> element:

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

   The $result->num_rows property returns an integer representing the total number of records returned by your SELECT query, providing immediate feedback about the result set size.

2. Save the file and refresh your browser:

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

   The page now displays the count of returned records above the data table.

3. Return to your code editor and keep MySQL.php open for continued development in subsequent exercises.

4. You've successfully implemented a complete database connection and data retrieval system—a fundamental skill that powers countless web applications. The patterns you've learned here scale from simple personal projects to enterprise-level systems processing millions of records.

   In upcoming exercises, we'll expand these capabilities to include data insertion, updates, deletions, and advanced filtering techniques that enable sophisticated data management functionality.