MySQL - PHP Syntax

Modern PHP and MySQL Integration

PHP is a dominant language for web development, and its integration with MySQL is fundamental to building dynamic, data-driven web applications. This tutorial covers the modern standards and best practices for connecting PHP to a MySQL database, focusing on security, reliability, and maintainability.

Modern PHP Database Connectivity: PDO vs. MySQLi

PHP provides two primary extensions for connecting to MySQL: MySQLi (MySQL Improved) and PDO (PHP Data Objects). While both are robust and support modern MySQL features, PDO is generally recommended for new projects.

Feature	PDO (PHP Data Objects)	MySQLi (MySQL Improved)
Database Support	Supports 12 different database drivers (MySQL, PostgreSQL, SQLite, etc.).	Supports MySQL only.
API Style	Strictly Object-Oriented.	Supports both Object-Oriented and Procedural styles.
Named Parameters	Supported (e.g., :name). This can improve readability.	Not supported. Only positional ? placeholders.
Portability	High. Code is easily portable to other databases with minimal changes.	Low. Code is tied to MySQL.

Recommendation: Use PDO for new applications. Its portability, consistent API, and support for named parameters make it a more flexible and maintainable choice in the long run. We will use PDO for the examples in this tutorial.

Setting Up Your Development Environment

Manually installing PHP, MySQL, and a web server is complex and error-prone. Modern developers use integrated software stacks or containerization for a streamlined setup.

Recommended Tools

• XAMPP (for Windows, macOS, Linux): A popular, free, and easy-to-install Apache distribution containing PHP, MariaDB (a compatible fork of MySQL), and Perl.
• WAMP (for Windows) / MAMP (for macOS): Similar all-in-one packages that provide a complete web development environment.
• Docker: A containerization platform that allows you to run PHP and MySQL in isolated, reproducible environments. This is the industry standard for professional development and deployment.

These tools come with the PDO and MySQLi extensions enabled by default, so no manual configuration of php.ini is usually required.

Core PDO Concepts and Methods

Interacting with a database via PDO involves a few key steps and objects:

• new PDO(...): Establishes a connection to the database. Connection details are passed in a special string called a DSN (Data Source Name).
• PDO::prepare(): Prepares an SQL statement for execution. This is the first step in preventing SQL injection. It returns a PDOStatement object.
• PDOStatement::execute(): Executes the prepared statement. You can pass an array of parameters to safely substitute the placeholders (? or :name) in your SQL.
• PDOStatement::fetchAll(): Fetches all remaining rows from a result set and returns them as an array of arrays.
• PDOStatement::fetch(): Fetches the next row from a result set.
• PDOStatement::rowCount(): Returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement.

Connecting and Querying with PDO: A Secure Example

The following example demonstrates the complete, secure workflow: connecting, preparing a query, executing it with parameters, fetching results, and handling errors.

<?php
// 1. Database Connection Parameters
$host = 'localhost'; // or 127.0.0.1
$db   = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

// 2. Set up the DSN (Data Source Name)
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

// 3. Set PDO options for error handling and fetching
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on error
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,      // Fetch as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                 // Use real prepared statements
];

// 4. Establish the connection within a try-catch block
try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected to database successfully!\n<br/>";
} catch (\PDOException $e) {
    // In a real app, you would log this error, not display it to the user
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 5. Prepare and Execute a SELECT Query with a WHERE clause
$sql = 'SELECT id, name, salary FROM customers WHERE salary > ?';
$minimumSalary = 5000.00;

$stmt = $pdo->prepare($sql);
$stmt->execute([$minimumSalary]);

// 6. Fetch the results
$highEarners = $stmt->fetchAll();

// 7. Process and display the results
if ($highEarners) {
    echo "Customers with salary greater than {$minimumSalary}:\n<br/>";
    foreach ($highEarners as $customer) {
        echo "ID: " . htmlspecialchars($customer['id'])
           . ", Name: " . htmlspecialchars($customer['name'])
           . ", Salary: " . htmlspecialchars($customer['salary']) . "<br/>";
    }
} else {
    echo "No customers found with a salary greater than {$minimumSalary}.\n";
}

// 8. The connection is automatically closed when the script ends.
?>