Managing MySQL from PHP with mysqli Queries, Results, and Reusable Helpers

Practical Introduction

PHP becomes much more useful when it can talk to a database. A static page can only show information that was written into the file in advance. A PHP script connected to MySQL can fetch current records, update table data, insert new rows, delete old records, and show the result in a web page or plain output.

The basic workflow is simple:

1. Store the database connection settings in one place.
2. Open a connection from the PHP script.
3. Build a MySQL command as a string.
4. Send the command using mysqli_query.
5. Check whether it succeeded.
6. If the command returns rows, fetch those rows one at a time.
7. Close the database connection when it is no longer needed.

This post walks through that workflow in a practical way. The examples use the mysqli_ family of PHP functions. The older mysql_ functions existed in earlier PHP codebases, but new scripts should be written with mysqli_ functions.

Context and Scope

Assume you have a MySQL database that stores member records. The table contains fields similar to this:

The goal is not to design a full application framework. The goal is to understand the direct PHP and MySQL interaction layer:

PHP script
  |
  | opens a mysqli connection
  v
MySQL server
  |
  | receives commands such as SELECT, UPDATE, INSERT, DELETE, SHOW, DESCRIBE
  v
PHP receives either:
  - success or failure
  - a result set that can be read row by row

Once you understand this flow, you can build small admin scripts, simple reporting pages, import helpers, and database maintenance tools.

Step 1: Keep Connection Settings in One Include File

A script needs four pieces of information before it can connect to MySQL:

• Database host
• Database username
• Database password
• Database name

You can write these directly inside every script, but that quickly becomes painful. If the password changes, every script must be edited. It is cleaner to put the settings in one included PHP file and reuse it wherever database access is needed.

<?php
$dbHost = "localhost";
$dbUser = "app_user";
$dbPassword = "secret_password";
$dbName = "club_database";

Save this as something like db_settings.php. Then include it from scripts that need database access:

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Could not connect to the database server");

// Database work goes here.

mysqli_close($connection);

The variable returned by mysqli_connect is the connection handle. The examples below call it $connection. You pass this value to other mysqli_ functions when sending commands or checking operation details.

Closing the connection is not always strictly required, but it is good practice. It makes the script's lifecycle clearer: connect, work, close.

Step 2: Send a MySQL Command with mysqli_query

A MySQL command is usually stored in a PHP string before being sent to the server. The common pattern looks like this:

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$query = "SELECT * FROM members ORDER BY member_id";
$result = mysqli_query($connection, $query);

if (!$result) {
    print "The command failed. Command was: " . $query . "\n";
} else {
    print "The command succeeded.\n";
}

mysqli_close($connection);

The meaning of $result depends on the type of command:

This distinction matters. You do not process an UPDATE result in the same way as a SELECT result. An update tells you whether the command succeeded. A selection gives you rows that you still need to fetch.

Step 3: Count Rows and Columns in a Result

When a command returns rows, PHP receives a result set. A result set is like a table returned from MySQL, but you normally read it one row at a time.

For SELECT, SHOW, and DESCRIBE style commands, two useful functions are:

• mysqli_num_rows($result): returns how many rows are in the result
• mysqli_num_fields($result): returns how many columns are in the result

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$query = "SELECT member_id, first_name, last_name, birth_year FROM members ORDER BY member_id";
$result = mysqli_query($connection, $query);

if (!$result) {
    print "SELECT failed. Command was: " . $query . "\n";
} else {
    $rowCount = mysqli_num_rows($result);
    $columnCount = mysqli_num_fields($result);

    print "Rows returned: " . $rowCount . "\n";
    print "Columns returned: " . $columnCount . "\n";
}

mysqli_close($connection);

Counting rows and columns is useful when you want to display a result generically, especially when the selected fields may change from one command to another.

Step 4: Fetch Rows One at a Time

A result set is processed using a loop. The function mysqli_fetch_array returns the next row from the result. When there are no more rows, the loop stops.

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$query = "SELECT member_id, first_name, last_name, job_title FROM members ORDER BY member_id";
$result = mysqli_query($connection, $query);

if (!$result) {
    print "SELECT failed. Command was: " . $query . "\n";
} else {
    while ($row = mysqli_fetch_array($result)) {
        print $row["member_id"] . " ";
        print $row["first_name"] . " ";
        print $row["last_name"] . " ";
        print $row["job_title"] . "\n";
    }
}

mysqli_close($connection);

Each $row behaves like an array. With mysqli_fetch_array, you can read values in two ways:

<?php
print $row[0];
print $row["member_id"];

The numeric index depends on the order of the fields returned by the query. The associative index uses the field name. Associative names are often easier to read, especially when a script handles many columns.

PHP also provides narrower fetch functions:

If you only need field names, mysqli_fetch_assoc is clearer. If you are writing a generic display loop that prints every column, numeric indexes are convenient.

Step 5: Display Any Result Set with a Reusable Function

Displaying query results is a repeated task. Instead of writing the same loop every time, create a helper function that accepts a result set and prints all rows.

The following function prints rows in a simple pipe-separated format. In a real web page you might output a table, but this plain format keeps the example focused on PHP and MySQL rather than markup.

<?php
function printResultRows($result)
{
    if (!$result) {
        print "The command failed.\n";
        return;
    }

    $columnCount = mysqli_num_fields($result);

    while ($row = mysqli_fetch_array($result)) {
        $values = array();

        for ($column = 0; $column < $columnCount; $column++) {
            $values[] = $row[$column];
        }

        print implode(" | ", $values) . "\n";
    }
}

Now the script that runs the query becomes smaller:

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$query = "SELECT first_name, last_name, child_count, member_id FROM members ORDER BY member_id";
$result = mysqli_query($connection, $query);

printResultRows($result);

mysqli_close($connection);

function printResultRows($result)
{
    if (!$result) {
        print "The command failed.\n";
        return;
    }

    $columnCount = mysqli_num_fields($result);

    while ($row = mysqli_fetch_array($result)) {
        $values = array();

        for ($column = 0; $column < $columnCount; $column++) {
            $values[] = $row[$column];
        }

        print implode(" | ", $values) . "\n";
    }
}

This style is useful because it does not care how many fields were selected. The query can return two columns or eight columns, and the loop still works.

Step 6: Handle SELECT, SHOW, DESCRIBE, and Update Commands Differently

A more flexible helper can inspect the command string and decide how to process the result.

The key idea is:

• If the command failed, print a failure message.
• If the command begins with SELECT, SHOW, or DESCRIBE, print returned rows.
• Otherwise, treat it as a command that succeeded without returning a result table.

Because MySQL keywords may be written in different cases, convert the command to lowercase before checking the first few characters.

<?php
function processDatabaseResult($query, $result)
{
    $normalizedQuery = strtolower(trim($query));

    if (!$result) {
        print "COMMAND FAILED\n";
        return;
    }

    $isSelect = substr($normalizedQuery, 0, 6) == "select";
    $isShow = substr($normalizedQuery, 0, 4) == "show";
    $isDescribe = substr($normalizedQuery, 0, 8) == "describe";

    if ($isSelect || $isShow || $isDescribe) {
        printResultRows($result);
        return;
    }

    print "COMMAND SUCCEEDED\n";
}

function printResultRows($result)
{
    $columnCount = mysqli_num_fields($result);

    while ($row = mysqli_fetch_array($result)) {
        $values = array();

        for ($column = 0; $column < $columnCount; $column++) {
            $values[] = $row[$column];
        }

        print implode(" | ", $values) . "\n";
    }
}

Here is a complete example using the helper:

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$query = "SELECT first_name, last_name, gender, member_id FROM members ORDER BY last_name, first_name";
$result = mysqli_query($connection, $query);

processDatabaseResult($query, $result);

mysqli_close($connection);

This is the beginning of a reusable database command processor. It can handle commands that return data and commands that only report success or failure.

Step 7: Build a Query from Values Found by Another Query

Sometimes a single complex MySQL command can do the whole job. Other times, it is easier to use PHP as the glue between two simpler commands.

For example, suppose you want to find one member born in Paris, combine their first and last name in PHP, and then store that full name in another table.

The workflow is:

1. Run a SELECT query to fetch the first and last name.
2. Fetch the returned row in PHP.
3. Join the two fields using PHP string concatenation.
4. Build an UPDATE command using the computed value.
5. Run the UPDATE command.

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$findQuery = "SELECT first_name, last_name FROM members WHERE birth_city = 'Paris' ORDER BY member_id LIMIT 1";
$findResult = mysqli_query($connection, $findQuery);

if (!$findResult) {
    print "Name lookup failed.\n";
} else {
    $member = mysqli_fetch_array($findResult);
    $displayName = $member["first_name"] . " " . $member["last_name"];

    $updateQuery = "UPDATE member_summary SET display_name = '" . $displayName . "'";
    $updateResult = mysqli_query($connection, $updateQuery);

    if (!$updateResult) {
        print "Update failed. Command was: " . $updateQuery . "\n";
    } else {
        print "Update succeeded.\n";
    }
}

mysqli_close($connection);

This example shows why combining PHP and MySQL is practical. PHP can store intermediate values, join strings, choose which command to run next, and print a helpful message when something fails.

When building query strings manually, be very careful with quote characters. String field values inside the MySQL command need quotes around them. Numeric values usually do not. The quote marks are part of the database command, while PHP also uses quote marks to define strings. Mixing the two is a common source of mistakes.

Step 8: Combine Data from Multiple Tables

A PHP script can also combine data from several tables. Assume three tables contain different pieces of information about the same members:

Each table uses the same member reference value, but the reference field has a different name in each table. The goal is to create one consolidated table called member_export.

A practical PHP approach is:

1. Run one SELECT command for each source table.
2. Sort each result by its reference field so matching rows appear in the same order.
3. In one loop, fetch one row from each result set.
4. Build one row of insert values from the three fetched rows.
5. Append that row to a larger insert command.
6. Run the insert command once after the loop.

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$identityQuery = "SELECT * FROM member_identity ORDER BY member_id";
$familyQuery = "SELECT * FROM member_family ORDER BY member_ref";
$birthQuery = "SELECT * FROM member_birth ORDER BY ref_value";

$identityResult = mysqli_query($connection, $identityQuery);
$familyResult = mysqli_query($connection, $familyQuery);
$birthResult = mysqli_query($connection, $birthQuery);

$insertQuery = "INSERT INTO member_export VALUES ";
$isFirstRow = true;

while ($identity = mysqli_fetch_array($identityResult)) {
    $family = mysqli_fetch_array($familyResult);
    $birth = mysqli_fetch_array($birthResult);

    $newRow = "(" .
        $identity["member_id"] . "," .
        "'" . $identity["first_name"] . "'," .
        "'" . $identity["last_name"] . "'," .
        "'" . $identity["gender"] . "'," .
        "'" . $family["job_title"] . "'," .
        "'" . $birth["birth_city"] . "'," .
        $birth["birth_year"] . "," .
        $family["child_count"] .
    ")";

    if ($isFirstRow) {
        $insertQuery .= $newRow;
        $isFirstRow = false;
    } else {
        $insertQuery .= "," . $newRow;
    }
}

$insertResult = mysqli_query($connection, $insertQuery);

if ($insertResult) {
    print "Consolidation succeeded.\n";
} else {
    print "Consolidation failed.\n";
}

mysqli_close($connection);

The $isFirstRow flag avoids placing a comma before the first row. Every later row is prefixed with a comma so the generated command has a valid list of values.

This technique is useful when PHP needs to perform small transformations between database reads and writes. The tradeoff is that the script assumes the three result sets line up correctly. That assumption only holds if the tables contain the same entities and are ordered by matching reference values.

Step 9: Build a Small Command Processing Tool

A useful learning tool is a PHP script that accepts a single database command, runs it, and displays either:

• A failure message
• A success message
• Returned rows for SELECT, SHOW, or DESCRIBE

A second optional input can name a table to display after the command runs. This makes it easy to run an update and immediately inspect the table.

The processing side can look like this:

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$query = trim($_POST["query_text"]);
$query = str_replace("\\", "", $query);

if ($query != "") {
    $result = mysqli_query($connection, $query);
    processDatabaseResult($query, $result);
    print "\n";
}

$tableName = trim($_POST["table_name"]);

if ($tableName != "") {
    print "Table: " . $tableName . "\n";

    $columnsQuery = "SHOW COLUMNS FROM " . $tableName;
    $rowsQuery = "SELECT * FROM " . $tableName;

    $columnsResult = mysqli_query($connection, $columnsQuery);
    $rowsResult = mysqli_query($connection, $rowsQuery);

    printTableWithHeadings($columnsResult, $rowsResult);
}

mysqli_close($connection);

The function below reads the column names first, then reads the actual table data:

<?php
function printTableWithHeadings($columnsResult, $rowsResult)
{
    $headings = array();

    while ($column = mysqli_fetch_array($columnsResult)) {
        $headings[] = $column[0];
    }

    print implode(" | ", $headings) . "\n";

    $columnCount = mysqli_num_fields($rowsResult);

    while ($row = mysqli_fetch_array($rowsResult)) {
        $values = array();

        for ($column = 0; $column < $columnCount; $column++) {
            $values[] = $row[$column];
        }

        print implode(" | ", $values) . "\n";
    }
}

This kind of tool is helpful while learning and while experimenting in a controlled environment. It should not be exposed to untrusted users, because it runs whatever command is submitted.

Step 10: Find How Many Rows Were Changed

Some MySQL commands can succeed even when they do not actually change any rows.

For example, a delete command that targets records with an ID larger than any existing ID may still be a valid command. It succeeds, but removes nothing.

For INSERT, UPDATE, REPLACE, and DELETE, use mysqli_affected_rows after the command to find how many rows were changed by the most recent operation.

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$deleteQuery = "DELETE FROM members WHERE birth_year < 1980";
$deleteResult = mysqli_query($connection, $deleteQuery);

if (!$deleteResult) {
    print "Delete failed.\n";
} else {
    $changedRows = mysqli_affected_rows($connection);
    print "Rows changed: " . $changedRows . "\n";
}

mysqli_close($connection);

This is more informative than checking only success or failure. A command can be valid and successful while still affecting zero rows.

Step 11: Read the ID Generated by an AUTO_INCREMENT Field

A table may contain an auto-increment field. MySQL assigns a new sequential value when a row is inserted. This is commonly used for primary keys or reference numbers.

After an insert command creates a new auto-increment value, PHP can read that generated value with mysqli_insert_id.

<?php
include "db_settings.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    or die("Database connection failed");

$insertQuery = "INSERT INTO member_requests VALUES ('', 'Nora', 'Mills', 'pending')";
$insertResult = mysqli_query($connection, $insertQuery);

if (!$insertResult) {
    print "Insert failed.\n";
} else {
    $newReference = mysqli_insert_id($connection);
    print "New reference number: " . $newReference . "\n";
}

mysqli_close($connection);

If the previous command did not create an auto-increment value, the returned value is zero. This function is useful when the script needs to show a generated reference number to the user or use it in a later command.

Common Mistakes to Watch For

Mistake 1: Treating Every mysqli_query Result the Same Way

Do not try to fetch rows from every command result. Only commands such as SELECT, SHOW, and DESCRIBE return a result set that can be read row by row. Commands such as UPDATE and DELETE need success checks and sometimes mysqli_affected_rows.

Mistake 2: Forgetting to Check Failure

A command can fail because of a misspelled table name, a wrong field name, a connection issue, or invalid command text. Always check the result before trying to process it.

Mistake 3: Losing Track of Quote Characters

Dynamic query construction often mixes PHP strings with database string values. A missing quote or an extra quote can break the command. Build query strings carefully and print the command during debugging when needed.

Mistake 4: Assuming a Successful DELETE Removed Rows

A delete command can succeed but remove zero rows. Use mysqli_affected_rows when the number of changed rows matters.

Mistake 5: Assuming Three Result Sets Match Without Checking

When combining multiple tables by fetching one row from each result set, the script assumes the rows appear in matching order. That is only safe if all result sets contain the same entities and are ordered by corresponding key fields.

Mistake 6: Exposing a Command Runner Too Widely

A visual command processor is useful for learning and private administration, but it runs submitted database commands. Keep that kind of script restricted and do not treat it like a normal public page.

Practical Checklist

Use this checklist when writing a PHP script that manages a MySQL database:

• Put database host, username, password, and database name in one include file.
• Open the database connection before running commands.
• Stop the script if the connection cannot be made.
• Store each MySQL command in a clearly named PHP variable.
• Send the command with mysqli_query.
• Check whether the command failed before processing the result.
• For returned rows, use mysqli_num_fields and mysqli_fetch_array or a related fetch function.
• Use helper functions for repeated result display logic.
• Use mysqli_affected_rows after insert, update, replace, or delete commands when the number of changed rows matters.
• Use mysqli_insert_id after inserting into a table with an auto-increment field when the generated value is needed.
• Close the connection when the script has finished its database work.
• Prefer mysqli_ functions over old mysql_ functions.

Conclusion

Managing MySQL from PHP follows a repeatable pattern: connect, build a command, send it, inspect the result, fetch rows when needed, and close the connection. The most important distinction is whether a command returns a row set or only reports success or failure.

Once that distinction is clear, you can create reusable helpers for displaying result sets, build scripts that combine multiple database operations, track how many rows were changed, and retrieve generated auto-increment IDs. These small building blocks are enough to create practical database administration scripts and simple data-driven PHP pages.