MCCodes - Chapter 10: Database Interaction

MCCodes Walkthrough Tutorial

Welcome to the final chapter! In Chapter 9: Security Functions (CSRF/Password/Validation), we saw how MCCodes protects itself and its users with features like CSRF tokens, secure password handling, and CAPTCHA. Many of these security features, and indeed almost every aspect of the game, rely on storing and retrieving information. How does the game remember your username, your level, your money, your items, or your gang membership between visits? That's where Database Interaction comes in.

The Problem: Remembering Everything

Imagine playing a game where every time you log out, everything resets. Your level goes back to 1, your money disappears, and your hard-earned items vanish. That wouldn't be very fun! The game needs a persistent memory -- a place to store all the data about players, items, gangs, settings, and more, and a reliable way to access and update that information.

The Solution: MCCodes uses a MySQL database as its main memory bank. Think of the database as a huge collection of organized spreadsheets (called tables). Each table stores specific information (like a users table, an items table, an inventory table).

But how does the game code actually talk to this database? It needs a translator and a librarian. This is where the $db object comes in. It provides a standard, simplified way for all other parts of the game code to communicate with the MySQL database.

The Universal Translator: The $db Object

In almost every file that needs to read or write game data (like globals.php, inventory.php, docrime.php, etc.), you'll find a variable named $db being used. This $db variable holds a special "object" created from the database class (found in class/class_db_mysqli.php).

This $db object is usually created very early on when a page loads, typically inside globals.php (as covered in Chapter 2: Global Setup & Session Management) or sglobals.php (Chapter 8: Staff Panel & Permissions). This means that most game scripts can immediately start using $db to interact with the database.

Core Database Actions via $db

How do you actually use the $db object? There are a few key actions (called "methods") you'll see used constantly:

  1. Sending a Request ($db->query())

    • This is how you tell the database what you want it to do. You give it instructions in the SQL language.
    • SELECT: Used to retrieve data. "SELECT username, level FROM users WHERE userid = 1" means "Get the username and level from the users table for the user with ID 1."
    • UPDATE: Used to modify existing data. "UPDATE users SET money = money + 100 WHERE userid = 1" means "Add 100 to the money for user ID 1."
    • INSERT: Used to add new data. "INSERT INTO items (itmname, itmdesc) VALUES ('Potion', 'Restores HP')" means "Add a new row to the items table with this name and description."
    • DELETE: Used to remove data. "DELETE FROM inventory WHERE inv_id = 5" means "Remove the inventory entry with ID 5."
    <?php
// --- Example: Asking for user's money and level ---
// Assumes globals.php has run, creating $db and setting $userid
global $db, $userid;

$query_text = "SELECT `username`, `money`, `level`
               FROM `users`
               WHERE `userid` = {$userid}";

// Send the SQL query to the database
$result_set = $db->query($query_text);
// $result_set now holds a special reference to the results (if any)
?>

    This code snippet builds an SQL query string to select data for the current user and sends it using $db->query(). The $result_set variable doesn't contain the data itself yet, but acts as a pointer to the results held by the database.

  2. Getting Results Back ($db->fetch_row(), $db->fetch_single(), $db->num_rows())

    • After running a SELECT query with $db->query(), you need to retrieve the actual data rows.
    • $db->fetch_row($result_set): Gets the next available row from the result set as an associative array (column name => value). You usually use this inside a while loop if you expect multiple rows, or just once if you expect only one row.
    • $db->fetch_single($result_set): A shortcut to get the value of the first column from the first row of the results. Useful when you query for just one specific value (like SELECT COUNT(*) ...).
    • $db->num_rows($result_set): Tells you how many rows were returned by your SELECT query. Useful for checking if any data was found.
    <?php
// --- Example: Getting data from the previous query ---
global $db, $userid;
$query_text = "SELECT `username`, `money`, `level` FROM `users` WHERE `userid` = {$userid}";
$result_set = $db->query($query_text);

// Check if any user was found
if ($db->num_rows($result_set) > 0)
{
    // Fetch the single row of data for this user
    $user_data = $db->fetch_row($result_set);

    // Now you can use the data
    echo "Welcome back, " . $user_data['username'] . "!<br />";
    echo "You have $" . $user_data['money'] . ".<br />";
    echo "Your level is " . $user_data['level'] . ".";
}
else
{
    echo "Error: User not found!";
}

// Clean up the result set (optional but good practice)
$db->free_result($result_set);
?>

    This code first checks if the query returned any rows (num_rows). If yes, it fetches the single row (fetch_row) into the $user_data array and then accesses the specific values using the column names as keys.

  3. Making Input Safe ($db->escape())

    • Problem: What if a user tries to enter something malicious in a form, like typing ' OR '1'='1 into a username field? If you put that directly into an SQL query, it could break the query and potentially expose or damage data (this is called SQL Injection).
    • Solution: Before including any data that came from a user (or any untrusted source) in an SQL query string, you MUST sanitize it using $db->escape(). This function adds backslashes before special characters (like quotes) that could interfere with the SQL syntax.
    <?php
// --- Example: Updating user's email SAFELY ---
global $db, $userid;

// Get the new email from a form submission (potentially unsafe!)
$unsafe_email = $_POST['new_email'];

// *** Sanitize the input before using it in the query ***
$safe_email = $db->escape($unsafe_email);

// Build the query using the *sanitized* variable
$update_query = "UPDATE `users`
                 SET `email` = '{$safe_email}'
                 WHERE `userid` = {$userid}";

// Execute the safe query
$db->query($update_query);

echo "Email updated!";
?>

    Here, $db->escape() ensures that even if $unsafe_email contains tricky characters, they won't break the SQL query structure when $safe_email is inserted. Always escape external data!

  4. Convenience ($db->easy_insert())

    • Typing out full INSERT queries can be tedious. $db->easy_insert('table_name', $data_array) provides a shortcut. You give it the table name and an associative array where keys are column names and values are the data to insert. It automatically builds and runs the safe INSERT query (it escapes the values for you).
    <?php
// --- Example: Adding a log entry using easy_insert ---
global $db, $userid;

$log_data = [
    'log_userid' => $userid,
    'log_action' => 'Visited shop',
    'log_time' => time(),
    'log_ip' => $_SERVER['REMOTE_ADDR']
];

// Insert the data into the 'activity_logs' table
$db->easy_insert('activity_logs', $log_data);

echo "Activity logged.";
?>

    This is much cleaner than writing the full INSERT INTO activity_logs (log_userid, log_action, ...) VALUES (...) query manually.

  5. Cleaning Up ($db->free_result())

    • When you run a SELECT query, the database server holds onto the results until you fetch them all or tell it you're done. $db->free_result($result_set) tells the database server it can release the memory associated with that specific query result. It's good practice to call this after you're finished fetching data from a $result_set, especially if your script does many queries.

Use Case: Fetching User Data in globals.php

Let's look back at how globals.php (from Chapter 2: Global Setup & Session Management) gets your player data ($ir) after you log in. It uses the $db object:

<?php
// Inside globals.php (Simplified Concept)

global $db, $userid, $ir; // $db and $userid are already set

// 1. Construct the SQL query to get all user and userstats data
$query_text = "SELECT u.*, us.*
               FROM users u
               INNER JOIN userstats us ON u.userid = us.userid
               WHERE u.userid = {$userid}
               LIMIT 1"; // We only expect one row

// 2. Execute the query using the $db object
$result_set = $db->query($query_text);

// 3. Check if we found the user
if ($db->num_rows($result_set) > 0)
{
    // 4. Fetch the single row of data into the $ir array
    $ir = $db->fetch_row($result_set);

    // (Code here also sets correct data types for $ir fields)
}
else
{
    // Problem: User exists in session but not DB? Log out.
    session_unset();
    session_destroy();
    header("Location: login.php");
    exit;
}

// 5. Clean up the result set
$db->free_result($result_set);

// Now the rest of the script can use the $ir array (e.g., $ir['username'], $ir['level'])
?>

This clearly shows the standard pattern: build query -> $db->query() -> check rows -> $db->fetch_row() -> use data -> $db->free_result().

Under the Hood: How $db->query() Works

What actually happens when you call a method like $db->query()? You don't need to know the finest details, but here's a general idea:

  1. Request: Your PHP script (e.g., globals.php) calls $db->query("SELECT ...").
  2. Translation ($db Object): The $db object takes the SQL query string you provided.
  3. Execution (MySQLi): The $db object uses built-in PHP functions (from the MySQLi extension) to send your SQL query text over the established connection to the actual MySQL database server.
  4. Database Work (MySQL Server): The MySQL server receives the SQL text, figures out what it means, performs the requested action (like finding data in the users table), and prepares a response.
  5. Response (MySQLi): The MySQL server sends the results (or a success/failure message) back to the PHP MySQLi extension.
  6. Result Handle ($db Object): The $db object receives the response from MySQLi. If it was a SELECT, it gets a special "result resource" handle (which we stored in $result_set). If it was an UPDATE or INSERT, it might just get a true/false status. It also checks for any errors reported by the database.
  7. Return: The $db->query() method returns the result handle (or boolean status) back to your PHP script.

Sequence Diagram:

sequenceDiagram
    participant Script as PHP Script (e.g., globals.php)
    participant DB_Obj as $db Object (database class)
    participant MySQLi as PHP MySQLi Extension
    participant MySQL_Srv as MySQL Database Server

    Script->>DB_Obj: Calls query("SELECT * FROM users WHERE userid=X")
    DB_Obj->>MySQLi: Passes SQL query text
    MySQLi->>MySQL_Srv: Sends SQL query over network connection
    MySQL_Srv->>MySQL_Srv: Processes query (finds data)
    MySQL_Srv-->>MySQLi: Sends back results/status
    MySQLi-->>DB_Obj: Returns result handle or status
    DB_Obj-->>Script: Returns result handle ($result_set)

This shows how the $db object acts as an intermediary, using the PHP MySQLi extension to talk to the actual database server.

Code Dive: class/class_db_mysqli.php

Let's peek inside the database class file to see simplified versions of the methods we discussed.

<?php
// File: class/class_db_mysqli.php (Highly Simplified)

class database
{
    // Property to hold the connection resource
    public mysqli|int $connection_id;
    // Property to hold the last query result
    public mysqli_result|bool $result;

    // Method to connect (called by globals.php)
    public function connect(): false|mysqli { /* ... */ }

    // Method to send a query
    public function query($query_text): mysqli_result|bool
    {
        // Store query for debugging
        // $this->queries[] = $query_text;

        // Use PHP's mysqli_query function to send the query
        $this->result = mysqli_query($this->connection_id, $query_text);

        // Check for errors
        if ($this->result === false) {
            // Call an error handling function (error_critical)
            error_critical(/* ... error details ... */);
        }
        return $this->result; // Return the result handle
    }

    // Method to fetch one row
    public function fetch_row(mysqli_result|int $result_handle = 0): false|array|null
    {
        if (!$result_handle) { $result_handle = $this->result; }
        // Use PHP's mysqli_fetch_assoc function
        return mysqli_fetch_assoc($result_handle);
    }

    // Method to get the number of rows
    public function num_rows(mysqli_result|int $result_handle = 0): int|string
    {
        if (!$result_handle) { $result_handle = $this->result; }
        // Use PHP's mysqli_num_rows function
        return mysqli_num_rows($result_handle);
    }

    // Method to escape strings
    public function escape($text): string
    {
        // Use PHP's mysqli_real_escape_string function
        return mysqli_real_escape_string($this->connection_id, $text);
    }

    // Method for easy inserts
    public function easy_insert($table, $data_array): mysqli_result|bool
    {
        $query = "INSERT INTO `{$table}` (";
        // ... Code to build column list ...
        $query .= ') VALUES(';
        // ... Code to build value list (using $this->escape() on each value) ...
        $query .= ')';
        // Run the generated query
        return $this->query($query);
    }

    // Method to free result memory
    public function free_result(mysqli_result|int $result_handle): void
    {
        // Use PHP's mysqli_free_result function
        mysqli_free_result($result_handle);
    }

    // ... other methods like fetch_single, insert_id, disconnect ...
}
?>

This simplified view shows that the $db object mostly acts as a "wrapper" around standard PHP MySQLi functions (mysqli_query, mysqli_fetch_assoc, mysqli_real_escape_string, etc.). It organizes these functions into a convenient object and adds error handling and helper methods like easy_insert. This organization makes database code throughout the rest of the game cleaner and more consistent.

Conclusion

You've reached the end of the tutorial! In this chapter, we explored how MCCodes interacts with its MySQL database, the game's central memory bank.

Understanding how the game uses the $db object is fundamental to understanding how player progress is saved, how items are tracked, how settings are applied, and how almost every dynamic feature works.

Final Tutorial Wrap-up:

Congratulations! You've journeyed through the core concepts of MCCodes, from how pages are built (Chapter 1: Page Rendering & Structure) and how sessions manage your login (Chapter 2: Global Setup & Session Management), through key gameplay features like Crimes (Chapter 3: Crime System), Items (Chapter 4: Item Management), Attacking (Chapter 5: Attacking System), and Gangs (Chapter 6: Gang System). We also looked at background automation (Chapter 7: Cron Job System), staff administration (Chapter 8: Staff Panel & Permissions), essential security measures (Chapter 9: Security Functions (CSRF/Password/Validation)), and finally, how the game interacts with its database memory (Chapter 10: Database Interaction).

Hopefully, this tutorial has given you a solid foundation for understanding how the different parts of MCCodes work together. While we've simplified many aspects, you should now be better equipped to explore the actual code, understand its flow, and perhaps even start modifying or adding your own features! Happy coding!

Previously: Chapter 9: Security Functions (CSRF/Password/Validation)

First published April 21, 2025

Tags: MCCodes Walkthrough Tutorial