MySQL PHP Connection and Usage

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data.

PHP is a popular server-side scripting language, especially suited for web development.

When MySQL and PHP are used together, you can create dynamic, data-driven websites.

PHP provides multiple ways to connect to and manipulate MySQL databases. You can usemysqliextension (MySQL Improved) orPDO（PHP Data Objects）。

If you want to learn about MySQL in PHP, you can visit ourIntroduction to Using MySQL in PHP。

Connect to MySQL Database

Using the mysqli Extension

mysqli ("improved MySQL") is the recommended way to connect to MySQL in PHP. Below is a basic connection example:

Example

<?php
$servername = "localhost";  // Database server address
$username = "username";     // Database username
$password = "password";     // Database password
$dbname = "database_name";  // Database name

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

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

Using PDO (PHP Data Objects)

PDO provides a data access abstraction layer, which can work with different database systems:

Example

<?php
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Execute SQL Queries

Query Data

Querying data using mysqli:

Example

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

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

Querying data using PDO:

Example

$stmt = $conn->prepare("SELECT id, name, email FROM users");
$stmt->execute();

// Set the result set to an associative array
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach($stmt->fetchAll() as $row) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}

Insert Data

mysqli insert data example:

Example

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {
    echo "New record inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

PDO insert data example (safer way):

Example

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);

// Insert a row
$name = "John Doe";
$email = "[email protected]";
$stmt->execute();

Security Considerations

Prevent SQL Injection

SQL injection is a common security threat. To prevent SQL injection, you should:

  1. Use prepared statements (like the PDO example above)
  2. Validate and filter user input
  3. Do not directly concatenate user input into SQL queries

Other Security Practices

  • Use the principle of least privilege: database users should only have the necessary permissions
  • Encrypt sensitive data
  • Back up the database regularly
  • Disable error display in production environments

Close the Database Connection

After completing database operations, you should close the connection to release resources:

mysqli closing connection:

Example

$conn->close();

PDO closing connection:

Example

$conn = null;

Practical Application Examples

User Registration System

Example

// Connect to the database
$conn = new mysqli("localhost", "username", "password", "user_db");

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

// Handle form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $username = $_POST['username'];
    $email = $_POST['email'];
    $password = password_hash($_POST['password'], PASSWORD_DEFAULT); // Hash the password
   
    // Prepared statement to prevent SQL injection
    $stmt = $conn->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $username, $email, $password);
   
    if ($stmt->execute()) {
        echo "Registration successful!";
    } else {
        echo "Error: " . $stmt->error;
    }
   
    $stmt->close();
}

$conn->close();

User Login System

Example

session_start();
$conn = new mysqli("localhost", "username", "password", "user_db");

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

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $username = $_POST['username'];
    $password = $_POST['password'];
   
    $stmt = $conn->prepare("SELECT id, username, password FROM users WHERE username = ?");
    $stmt->bind_param("s", $username);
    $stmt->execute();
    $result = $stmt->get_result();
   
    if ($result->num_rows == 1) {
        $user = $result->fetch_assoc();
        if (password_verify($password, $user['password'])) {
            $_SESSION['user_id'] = $user['id'];
            $_SESSION['username'] = $user['username'];
            header("Location: dashboard.php");
        } else {
            echo "Invalid password";
        }
    } else {
        echo "User does not exist";
    }
   
    $stmt->close();
}

$conn->close();
Other Extensions