PHP MySQL Database

PHP

PHP MySQL Database - Database Connectivity

SEO Description: Learn PHP MySQL database connectivity. Connect to MySQL database easily by executing queries and fetching results effectively.

Introduction

PHP and MySQL are one of the most popular combinations for building dynamic, data-driven websites. MySQL serves as the database management system (DBMS) that stores the data, while PHP acts as the server-side scripting language to connect and manipulate the data. In this tutorial, you will learn how to establish a connection to a MySQL database using PHP, execute queries, fetch results, and apply best practices for reliable and secure database operations.

Prerequisites

  • Basic knowledge of PHP programming language
  • Familiarity with MySQL and database concepts
  • Web server with PHP and MySQL installed (e.g., XAMPP, WAMP, LAMP)
  • Access to phpMyAdmin or MySQL CLI for database management

Setup Steps

1. Create a MySQL Database and Table

First, create a test database and a sample table where you will store data.

CREATE DATABASE sampleDB;

USE sampleDB;

CREATE TABLE users (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Connect PHP to MySQL Database

Use PHP's mysqli extension or PDO to connect to MySQL. Below is an example using mysqli:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sampleDB";

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

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

3. Execute Queries and Fetch Results

Once connected, you can execute SQL queries to read, insert, update, or delete data.

Insert Data Example:

<?php
// Insert a new user
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $conn->error;
}
?>

Fetch Results Example:

<?php
$sql = "SELECT id, username, email, created_at 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["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Best Practices

  • Use Prepared Statements: To prevent SQL injection, always use prepared statements when executing queries with user input.
  • Close Connections: Always close your database connection once your operations are complete.
  • Error Handling: Implement robust error handling to catch and resolve issues early.
  • Use PDO for Flexibility: Consider using PHP Data Objects (PDO) for database interaction with support for multiple databases.
  • Sanitize Inputs: Validate and sanitize all user inputs before using them in queries.

Common Mistakes to Avoid

  • Not checking if the connection to the database was successful.
  • Directly including user input in SQL statements without sanitization or prepared statements.
  • Forgetting to close the database connection, leading to resource leaks.
  • Not handling errors or exceptions, making debugging difficult.
  • Using deprecated MySQL extensions like mysql_connect() instead of mysqli or PDO.

Interview Questions

Junior-Level Questions

  • Q1: How do you establish a connection to a MySQL database in PHP?
    A1: Use the mysqli or PDO extension to connect by specifying the server, username, password, and database name.
  • Q2: What function checks if the database connection was successful in mysqli?
    A2: Using $conn->connect_error to check for connection errors.
  • Q3: How can you fetch data from a result set returned by a MySQL SELECT query?
    A3: Using fetch_assoc() or fetch_array() functions on the result object.
  • Q4: What is SQL Injection, and how do you prevent it in PHP MySQL?
    A4: SQL Injection is a security vulnerability; prevent it by using prepared statements and parameterized queries.
  • Q5: How do you insert data into a MySQL table using PHP?
    A5: Use an INSERT INTO SQL statement executed by the query() method.

Mid-Level Questions

  • Q1: Explain how prepared statements improve security in PHP MySQL interactions.
    A1: Prepared statements separate SQL logic from data, avoiding direct inclusion of user input in queries, thus preventing injection attacks.
  • Q2: Describe the difference between mysqli and PDO.
    A2: mysqli is designed specifically for MySQL; PDO supports multiple databases with a unified interface.
  • Q3: How do you handle connection failures gracefully in your PHP code?
    A3: Check the connection error and use error handling mechanisms like try-catch or die() to display or log error messages.
  • Q4: How can you retrieve the ID of the last inserted row using PHP MySQLi?
    A4: Use $conn->insert_id after executing an INSERT query.
  • Q5: What is the use of the num_rows property in MySQLi result sets?
    A5: It returns the number of rows fetched by a SELECT query.

Senior-Level Questions

  • Q1: How would you optimize PHP MySQL database interactions for large-scale applications?
    A1: Use persistent connections, prepared statements, indexing in MySQL, and caching query results to optimize performance.
  • Q2: How do you handle transactions in PHP MySQL and why are they important?
    A2: Use BEGIN TRANSACTION, COMMIT, and ROLLBACK methods to ensure data integrity during multiple dependent queries.
  • Q3: Explain how to secure sensitive data, such as passwords, when storing them in a MySQL database via PHP.
    A3: Use PHP functions like password_hash() to hash passwords and store only hashed values, never plain text.
  • Q4: What are potential drawbacks of using mysqli over PDO in complex applications?
    A4: mysqli is limited to MySQL, lacks named placeholders for prepared statements, and is less flexible compared to PDO.
  • Q5: Describe how you would implement error logging for database errors in a PHP MySQL application.
    A5: Use try-catch blocks with exceptions, log errors to files or monitoring services, and avoid exposing raw errors to end users.

Frequently Asked Questions (FAQ)

Q1: Which PHP extension should I use for MySQL?

A: Use either mysqli or PDO. PDO offers more flexibility across different databases.

Q2: How do I prevent SQL Injection vulnerabilities?

A: Always use prepared statements with parameter binding instead of directly inserting user input into SQL queries.

Q3: How do I close a MySQL connection in PHP?

A: Call $conn->close(); when you are done with database operations.

Q4: Can I use PHP to connect to databases other than MySQL?

A: Yes, using PDO you can connect to various databases such as PostgreSQL, SQLite, etc.

Q5: What is the difference between fetch_assoc() and fetch_array()?

A: fetch_assoc() returns an associative array with column names as keys, while fetch_array() returns both numeric and associative indexes.

Conclusion

By mastering PHP MySQL database connectivity, you open the door to creating powerful, dynamic web applications. This tutorial covered how to connect to MySQL using PHP's mysqli extension, execute queries, fetch data efficiently, and maintain security and best practices. Continually practicing and refining these skills will improve your development workflow and prepare you for real-world database-driven projects.