MySQL Integration - database connection, table creation with constraints, CRUD operations, joins, subqueries, querying MySQL with PHP

1. Database Connection with PHP

To interact with a MySQL database using PHP, the first step is to establish a connection. PHP offers several ways to do this, with the most common and recommended method being the MySQLi (MySQL Improved) extension. Another option is PDO (PHP Data Objects), which provides a database-agnostic interface. We will focus on MySQLi for its direct compatibility and common usage with MySQL.

The MySQLi extension provides both procedural and object-oriented interfaces. The object-oriented approach is generally preferred for its cleaner syntax and better error handling.

Object-Oriented MySQLi Connection

To connect using the object-oriented approach, you create a new `mysqli` object, passing the server name, username, password, and database name as arguments.

<?php
$servername = "localhost"; // Or your database server IP/hostname
$username = "your_db_username";
$password = "your_db_password";
$dbname = "your_database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
  

In this code:

• `$servername`: This is typically "localhost" if your database is on the same server as your web server.
• `$username`: The username you use to access your MySQL database.
• `$password`: The password for the specified username.
• `$dbname`: The name of the database you want to connect to.

The `$conn->connect_error` property checks if there was an error during the connection attempt. If an error occurs, the script terminates using `die()` and displays the error message. If successful, it prints "Connected successfully". It's crucial to handle connection errors to prevent exposing sensitive information or encountering unexpected behavior.

Procedural MySQLi Connection

The procedural approach uses functions like `mysqli_connect()`.

<?php
$servername = "localhost";
$username = "your_db_username";
$password = "your_db_password";
$dbname = "your_database_name";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
  

Both methods achieve the same result. The object-oriented style is generally favored for its consistency with other object-oriented PHP features. Always ensure your database credentials are secure and not hardcoded directly in production code; use environment variables or configuration files.

2. Table Creation with Constraints

Once connected, you can create tables in your MySQL database using SQL commands executed via PHP. A well-designed database table includes constraints to ensure data integrity.

Basic Table Creation

Let's create a simple `users` table with an `id`, `name`, and `email`.

<?php
// Assuming $conn is your established MySQLi connection object

$sql = "CREATE TABLE users (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  email VARCHAR(50) UNIQUE,
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
  echo "Table 'users' created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}

$conn->close();
?>
  

Understanding Constraints

In the example above, we used several constraints:

• `INT(6)`: Defines the `id` column as an integer with a display width of 6 digits.
• `UNSIGNED`: Ensures the integer values are non-negative.
• `AUTO_INCREMENT`: Automatically assigns a unique sequential number to each new record.
• `PRIMARY KEY`: Uniquely identifies each row in the table. A table can have only one primary key.
• `VARCHAR(30)`: Defines the `name` column as a variable-length string up to 30 characters.
• `NOT NULL`: Ensures that the `name` column cannot have a NULL value.
• `VARCHAR(50) UNIQUE`: Defines the `email` column as a variable-length string up to 50 characters. The `UNIQUE` constraint ensures that all email addresses in this column must be different.
• `TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`: This automatically sets the `reg_date` to the current date and time when a record is created and updates it whenever the record is modified.

Other Common Constraints

• FOREIGN KEY: Links data in one table to data in another table, enforcing referential integrity. For example, an `orders` table might have a `customer_id` that refers to the `id` in the `customers` table.
• CHECK: Ensures that all values in a column satisfy a specific condition (e.g., `age >= 18`).
• DEFAULT: Sets a default value for a column if no value is specified during insertion.

Proper use of constraints is vital for maintaining data accuracy and consistency across your database.

3. CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations performed on data in a database. PHP, combined with MySQLi, allows you to perform these operations efficiently.

Create (INSERT)

To add new data to a table.

<?php
// Assuming $conn is your established MySQLi connection object

$name = "John Doe";
$email = "john.doe@example.com";

// Use prepared statements to prevent SQL injection
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email); // "ss" means both parameters are strings

if ($stmt->execute()) {
  echo "New record created successfully";
} else {
  echo "Error: " . $stmt->error;
}
$stmt->close();
?>
  

Security Note: Always use prepared statements with placeholders (`?`) for inserting data. This is a crucial security measure against SQL injection attacks. `bind_param()` associates the variables with the placeholders, and `"ss"` indicates that both parameters are strings.

Read (SELECT)

To retrieve data from a table.

<?php
// Assuming $conn is your established MySQLi connection object

$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // Output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
  }
} else {
  echo "0 results";
}
$result->free(); // Free result set
$conn->close();
?>
  

`$conn->query($sql)` executes the SQL query. `$result->num_rows` checks if any rows were returned. `fetch_assoc()` retrieves each row as an associative array.

Update (UPDATE)

To modify existing data in a table.

<?php
// Assuming $conn is your established MySQLi connection object

$id = 1; // ID of the record to update
$new_email = "john.doe.updated@example.com";

$stmt = $conn->prepare("UPDATE users SET email = ? WHERE id = ?");
// "si" means first parameter is string, second is integer
$stmt->bind_param("si", $new_email, $id);

if ($stmt->execute()) {
  echo $stmt->affected_rows . " record(s) updated successfully";
} else {
  echo "Error updating record: " . $stmt->error;
}
$stmt->close();
?>
  

Delete (DELETE)

To remove data from a table.

<?php
// Assuming $conn is your established MySQLi connection object

$id = 1; // ID of the record to delete

$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id); // "i" means the parameter is an integer

if ($stmt->execute()) {
  echo $stmt->affected_rows . " record(s) deleted successfully";
} else {
  echo "Error deleting record: " . $stmt->error;
}
$stmt->close();
?>
  

4. Joins in MySQL

Joins are used to combine rows from two or more tables based on a related column between them. This is essential for querying data that spans across multiple tables.

Types of Joins

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the left side.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either the left or right table. (Note: MySQL does not directly support FULL OUTER JOIN, but it can be simulated using LEFT JOIN and RIGHT JOIN with a UNION).

Example: INNER JOIN

Let's assume we have two tables: `customers` and `orders`.

`customers` table:

customer_id	name
1	Alice
2	Bob

`orders` table:

order_id	customer_id	order_date
101	1	2023-10-26
102	1	2023-10-27
103	3	2023-10-27

To get a list of customers and their orders:

<?php
// Assuming $conn is your established MySQLi connection object

$sql = "SELECT
          c.name,
          o.order_id,
          o.order_date
        FROM
          customers c
        INNER JOIN
          orders o ON c.customer_id = o.customer_id";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo "Customer: " . $row["name"]. " - Order ID: " . $row["order_id"]. " - Date: " . $row["order_date"]. "<br>";
  }
} else {
  echo "No orders found for any customer.";
}
$result->free();
?>
  

This query will return rows where `customer_id` matches in both tables. In this case, it will show Alice's orders. Bob and Order ID 103 (which has no matching customer) will not appear.

Example: LEFT JOIN

To list all customers, and their orders if they have any:

<?php
// Assuming $conn is your established MySQLi connection object

$sql = "SELECT
          c.name,
          o.order_id,
          o.order_date
        FROM
          customers c
        LEFT JOIN
          orders o ON c.customer_id = o.customer_id";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo "Customer: " . $row["name"]. " - Order ID: " . ($row["order_id"] ? $row["order_id"] : 'No Orders') . "<br>";
  }
} else {
  echo "No customers found.";
}
$result->free();
?>
  

This query will list Alice and her orders, Bob and 'No Orders', because all customers from the left table (`customers`) are included.

5. Subqueries in MySQL

A subquery, also known as a nested query or inner query, is a query within another SQL query. It's used when you need to filter or retrieve data based on the result of another query.

Types of Subqueries

• Scalar Subquery: Returns a single value (one row, one column).
• Row Subquery: Returns a single row with multiple columns.
• Table Subquery: Returns multiple rows and multiple columns.
• Correlated Subquery: A subquery that depends on the outer query for its values. It is executed once for each row processed by the outer query.

Example: Using a Subquery with WHERE Clause

Find customers who have placed orders. This can be done with a join, but also with a subquery.

<?php
// Assuming $conn is your established MySQLi connection object

$sql = "SELECT name
        FROM customers
        WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders)";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo "Customer with orders: " . $row["name"] . "<br>";
  }
} else {
  echo "No customers found with orders.";
}
$result->free();
?>
  

The subquery `(SELECT DISTINCT customer_id FROM orders)` first finds all unique `customer_id`s from the `orders` table. The outer query then selects names from the `customers` table where their `customer_id` is present in the list returned by the subquery.

Example: Subquery in FROM Clause (Derived Table)

Calculate the average number of orders per customer.

<?php
// Assuming $conn is your established MySQLi connection object

$sql = "SELECT AVG(order_count) AS average_orders
        FROM (
          SELECT COUNT(order_id) AS order_count
          FROM orders
          GROUP BY customer_id
        ) AS customer_orders";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  $row = $result->fetch_assoc();
  echo "Average orders per customer: " . round($row["average_orders"], 2);
} else {
  echo "No orders found to calculate average.";
}
$result->free();
?>
  

In this case, the subquery `(SELECT COUNT(order_id) AS order_count FROM orders GROUP BY customer_id)` acts as a temporary table (derived table) named `customer_orders`. It counts the orders for each customer. The outer query then calculates the average of these counts.

6. Querying MySQL with PHP

This section synthesizes the previous points, demonstrating how to use PHP to execute various SQL queries against a MySQL database. The key is to use the MySQLi extension, handle connections, use prepared statements for security, and process the results appropriately.

Common Scenario: Displaying Data from a Search Query

Let's create a PHP script that searches for users by name and displays the results.

<?php
// --- Database Connection (as shown before) ---
$servername = "localhost";
$username = "your_db_username";
$password = "your_db_password";
$dbname = "your_database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// --- End Connection ---

$search_term = "";
if (isset($_GET['search'])) {
    $search_term = $_GET['search'];
}

echo "<h2>Search Results for: " . htmlspecialchars($search_term) . "</h2>";

// Use prepared statement for security
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE name LIKE ?");
$searchTermParam = "%" . $search_term . "%"; // Wildcard for LIKE
$stmt->bind_param("s", $searchTermParam);
$stmt->execute();
$result = $stmt->get_result(); // Get result set from prepared statement

if ($result->num_rows > 0) {
  echo "<table>";
  echo "<thead><tr><th>ID</th><th>Name</th><th>Email</th></tr></thead>";
  echo "<tbody>";
  while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row["id"] . "</td>";
    // Use htmlspecialchars to prevent XSS attacks when displaying user data
    echo "<td>" . htmlspecialchars($row["name"]) . "</td>";
    echo "<td>" . htmlspecialchars($row["email"]) . "</td>";
    echo "</tr>";
  }
  echo "</tbody></table>";
} else {
  echo "<p>No users found matching your search criteria.</p>";
}

$stmt->close();
$conn->close();
?>
  

In this example:

• We retrieve a search term from the `$_GET` superglobal array.
• A prepared statement is used to safely query the `users` table using the `LIKE` operator with wildcards (`%`).
• `$stmt->get_result()` is used after `execute()` to fetch the result set from the prepared statement.
• The results are iterated and displayed in an HTML table.
• `htmlspecialchars()` is used when outputting data to the browser to prevent Cross-Site Scripting (XSS) vulnerabilities.