PHP Fundamentals

Executing Queries with MySQLi

13 min Lesson 37 of 45

Executing SQL Queries

Once connected to the database, you can execute SQL queries to create, read, update, and delete data. MySQLi provides several methods for executing queries.

The query() Method

The simplest way to execute a query is using the query() method:

<?php
require_once 'database.php';

// Execute a simple query
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result) {
    echo "Query executed successfully";
} else {
    echo "Error: " . $conn->error;
}
?>
Warning: NEVER use query() with user input directly. This example uses hardcoded SQL. We'll learn about prepared statements in the next lesson.

Creating Tables

Let's create a users table for our examples:

<?php
require_once 'database.php';

$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

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

Inserting Data

Insert new records into the database:

<?php
require_once 'database.php';

// Hash the password
$password = password_hash("mypassword123", PASSWORD_DEFAULT);

$sql = "INSERT INTO users (username, email, password, full_name)
        VALUES ('johndoe', 'john@example.com', '$password', 'John Doe')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully<br>";
    echo "Last inserted ID: " . $conn->insert_id;
} else {
    echo "Error: " . $conn->error;
}
?>
Note: The insert_id property returns the ID of the last inserted row. Very useful when you need to reference the new record.

Selecting Data

Retrieve data from the database and process the results:

Fetching All Rows

<?php
require_once 'database.php';

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

if ($result->num_rows > 0) {
    echo "<h3>Users List</h3>";
    echo "<table border='1'>";
    echo "<tr><th>ID</th><th>Username</th><th>Email</th><th>Full Name</th></tr>";

    // Fetch each row as associative array
    while ($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['username'] . "</td>";
        echo "<td>" . $row['email'] . "</td>";
        echo "<td>" . $row['full_name'] . "</td>";
        echo "</tr>";
    }

    echo "</table>";
} else {
    echo "No users found";
}

// Free result set
$result->free();
?>

Different Fetch Methods

<?php
// 1. Fetch as associative array (key = column name)
$row = $result->fetch_assoc();
// Usage: $row['username']

// 2. Fetch as numeric array (key = column index)
$row = $result->fetch_row();
// Usage: $row[0], $row[1]

// 3. Fetch as both associative and numeric
$row = $result->fetch_array(MYSQLI_BOTH);
// Usage: $row['username'] or $row[0]

// 4. Fetch as object
$row = $result->fetch_object();
// Usage: $row->username

// 5. Fetch all rows at once
$rows = $result->fetch_all(MYSQLI_ASSOC);
// Returns 2D array of all rows
?>
Tip: fetch_assoc() is most commonly used because column names are more readable than numeric indices.

Updating Data

Modify existing records in the database:

<?php
require_once 'database.php';

$sql = "UPDATE users
        SET full_name = 'John Smith', email = 'johnsmith@example.com'
        WHERE username = 'johndoe'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully<br>";
    echo "Rows affected: " . $conn->affected_rows;
} else {
    echo "Error updating record: " . $conn->error;
}
?>
Note: The affected_rows property tells you how many rows were modified. Returns 0 if no rows matched the WHERE clause.

Deleting Data

Remove records from the database:

<?php
require_once 'database.php';

$sql = "DELETE FROM users WHERE username = 'johndoe'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully<br>";
    echo "Rows deleted: " . $conn->affected_rows;
} else {
    echo "Error deleting record: " . $conn->error;
}
?>
Warning: Always include a WHERE clause in UPDATE and DELETE statements, or you'll modify/delete ALL rows!

Counting Rows

Get the number of rows returned by a SELECT query:

<?php
require_once 'database.php';

$sql = "SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
$result = $conn->query($sql);

echo "Users registered in last 7 days: " . $result->num_rows;

$result->free();
?>

Using LIMIT and OFFSET

Implement pagination by limiting the number of results:

<?php
require_once 'database.php';

// Pagination settings
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = 10;
$offset = ($page - 1) * $per_page;

// Get total number of users
$count_sql = "SELECT COUNT(*) as total FROM users";
$count_result = $conn->query($count_sql);
$total_users = $count_result->fetch_assoc()['total'];
$total_pages = ceil($total_users / $per_page);

// Get users for current page
$sql = "SELECT * FROM users ORDER BY created_at DESC LIMIT $per_page OFFSET $offset";
$result = $conn->query($sql);

echo "<h3>Users (Page $page of $total_pages)</h3>";

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "<p>" . $row['username'] . " - " . $row['email'] . "</p>";
    }
}

// Pagination links
for ($i = 1; $i <= $total_pages; $i++) {
    if ($i === $page) {
        echo "<strong>$i</strong> ";
    } else {
        echo "<a href='?page=$i'>$i</a> ";
    }
}
?>

Checking if Record Exists

Verify if a record exists before performing operations:

<?php
require_once 'database.php';

function userExists($conn, $username) {
    $sql = "SELECT id FROM users WHERE username = '$username'";
    $result = $conn->query($sql);
    return $result->num_rows > 0;
}

if (userExists($conn, 'johndoe')) {
    echo "Username already taken";
} else {
    echo "Username available";
}
?>

Multi-Query Execution

Execute multiple queries in one call:

<?php
require_once 'database.php';

$sql = "
    INSERT INTO users (username, email, password) VALUES ('user1', 'user1@test.com', 'pass1');
    INSERT INTO users (username, email, password) VALUES ('user2', 'user2@test.com', 'pass2');
    INSERT INTO users (username, email, password) VALUES ('user3', 'user3@test.com', 'pass3');
";

if ($conn->multi_query($sql)) {
    do {
        // Store first result set
        if ($result = $conn->store_result()) {
            $result->free();
        }

        if ($conn->more_results()) {
            echo "More results coming...<br>";
        }
    } while ($conn->next_result());

    echo "All queries executed successfully";
} else {
    echo "Error: " . $conn->error;
}
?>
Warning: multi_query() is rarely needed and can be dangerous with user input. Prefer executing queries separately.

Complete CRUD Example

Here's a complete example combining all operations:

<?php
require_once 'database.php';

class UserManager {
    private $conn;

    public function __construct($connection) {
        $this->conn = $connection;
    }

    // CREATE
    public function createUser($username, $email, $password, $full_name) {
        $hashed_password = password_hash($password, PASSWORD_DEFAULT);

        $sql = "INSERT INTO users (username, email, password, full_name)
                VALUES ('$username', '$email', '$hashed_password', '$full_name')";

        if ($this->conn->query($sql)) {
            return $this->conn->insert_id;
        }
        return false;
    }

    // READ
    public function getUser($id) {
        $sql = "SELECT * FROM users WHERE id = $id";
        $result = $this->conn->query($sql);

        if ($result->num_rows > 0) {
            return $result->fetch_assoc();
        }
        return null;
    }

    public function getAllUsers() {
        $sql = "SELECT * FROM users ORDER BY created_at DESC";
        $result = $this->conn->query($sql);

        return $result->fetch_all(MYSQLI_ASSOC);
    }

    // UPDATE
    public function updateUser($id, $data) {
        $updates = [];
        foreach ($data as $key => $value) {
            $updates[] = "$key = '$value'";
        }
        $updates_str = implode(', ', $updates);

        $sql = "UPDATE users SET $updates_str WHERE id = $id";

        return $this->conn->query($sql);
    }

    // DELETE
    public function deleteUser($id) {
        $sql = "DELETE FROM users WHERE id = $id";
        return $this->conn->query($sql);
    }

    // COUNT
    public function getUserCount() {
        $sql = "SELECT COUNT(*) as total FROM users";
        $result = $this->conn->query($sql);
        $row = $result->fetch_assoc();
        return $row['total'];
    }
}

// Usage
$userManager = new UserManager($conn);

// Create user
$user_id = $userManager->createUser('alice', 'alice@example.com', 'password123', 'Alice Smith');
echo "Created user with ID: $user_id<br>";

// Get user
$user = $userManager->getUser($user_id);
echo "Username: " . $user['username'] . "<br>";

// Update user
$userManager->updateUser($user_id, ['full_name' => 'Alice Johnson']);
echo "User updated<br>";

// Get all users
$all_users = $userManager->getAllUsers();
echo "Total users: " . count($all_users) . "<br>";

// Delete user
$userManager->deleteUser($user_id);
echo "User deleted<br>";
?>
Security Alert: This example uses direct string concatenation for simplicity. NEVER do this with user input! We'll learn prepared statements in the next lesson.

Exercise: Build a Simple User Directory

  1. Create a users table with fields: id, name, email, phone, city
  2. Create an add_user.php form to add new users
  3. Create a list_users.php page to display all users in a table
  4. Add a search feature to filter users by city
  5. Add pagination to show 5 users per page
  6. Create an edit_user.php page to update user information
  7. Create a delete_user.php script to remove users
  8. Add a counter showing total number of users

Best Practices

  • Always check query results: Verify if queries executed successfully
  • Free result sets: Use $result->free() to release memory
  • Use specific column names: SELECT specific columns instead of SELECT *
  • Always include WHERE in UPDATE/DELETE: Prevent accidental mass changes
  • Close connections: Close database connections when done
  • Never trust user input: Always use prepared statements (next lesson)
  • Use transactions: For operations that must succeed or fail together

Summary

  • Use query() method to execute SQL statements
  • insert_id returns the ID of the last inserted row
  • affected_rows returns the number of rows affected by UPDATE/DELETE
  • num_rows returns the number of rows in a SELECT result
  • Use fetch_assoc() to retrieve rows as associative arrays
  • Always free result sets with free() when done
  • Never concatenate user input directly into SQL queries
Previous Lesson MySQLi Introduction
Next Lesson Prepared Statements & SQL Injection Prevention
Back to Course