Building a Practical PHP Database Helper with MySQLi

The Problem

A dynamic website often needs to read from or change a database. Static HTML can display fixed text, but it cannot ask a database for current values, insert new records, update existing rows, or show a live table of results. PHP solves this by running on the server, connecting to MySQL, sending database commands, and then printing HTML back to the browser.

The practical workflow is simple:

Browser
  |
  v
PHP page
  |
  v
mysqli connection
  |
  v
MySQL database
  |
  v
Result returned to PHP
  |
  v
HTML printed to browser

The important part is not just making the connection. A useful PHP database script also needs to:

Keep connection details in one place.
Stop early if the database connection fails.
Send a database command through mysqli_query.
Understand the difference between a command that changes data and a command that returns rows.
Loop through returned rows one at a time.
Render database output in a readable table.
Report how many rows were affected by a change command.
Read the generated value from an AUTO_INCREMENT field when needed.

This tutorial builds those pieces step by step using the procedural mysqli_ functions.

Keep Database Credentials in One Include File

A database connection needs four values: username, password, host, and database name. The host is often localhost when the database is on the same server as the website. If the database is on another server, the host may be another address provided by the hosting environment.

Do not duplicate these values across many PHP files. If the password changes or if the database moves to another server, every duplicated copy becomes a maintenance problem. It is also a security risk because confidential values are spread across more files.

A cleaner approach is to store the values in one small PHP file and include it wherever database access is needed.

<?php
$dbUser = "app_user";
$dbPassword = "app_password";
$dbHost = "localhost";
$dbName = "club_registry";
?>

A script that needs database access can then start with:

<?php
include "db_config.php";
?>

This keeps the connection setup portable. The rest of the application can keep using the same variable names even if the underlying credentials or database name change.

Open and Close a mysqli Connection

To connect to MySQL, call mysqli_connect with the host, username, password, and database name. The returned value is a link identifier, which is then passed to other mysqli_ functions.

<?php
include "db_config.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    OR die("connection to server failed");

mysqli_close($connection);
?>

The connection variable is important. It is the handle PHP uses when sending commands to MySQL. Closing the connection is not always strictly required, but it is good practice once the script no longer needs the database.

The OR die(...) part is a defensive guard. If the connection fails, the script terminates immediately instead of continuing with invalid database state. Without that guard, the rest of the script may try to process data that does not exist.

Send a Database Command with mysqli_query

Once the connection is available, pass a command string to mysqli_query.

<?php
include "db_config.php";

$connection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName)
    OR die("connection to server failed");

$databaseCommand = "";
if (isset($_POST["command"])) $databaseCommand = trim($_POST["command"]);

$result = mysqli_query($connection, $databaseCommand);

if ($result == "") {
    print "Command failed<br>The command was: " . $databaseCommand . "<p>\n";
} else {
    print "Command succeeded<p>\n";
}

mysqli_close($connection);
?>

The returned $result has different meanings depending on the type of command:

Command type	What `$result` represents
Commands that change the database	`1` when the command succeeds, empty string when it fails
Commands that return rows	A resource object when the command succeeds, an empty string when it fails

That distinction matters. If the command only changes data, a success result tells you that MySQL accepted the command. If the command returns rows, the result must be read row by row.

Count Rows and Fields in a Returned Result

When a command returns a table of values, PHP receives a resource object. You can inspect it before rendering it.

<?php
if ($result == "") {
    print "Data lookup failed<p>\n";
} else {
    $rowCount = mysqli_num_rows($result);
    $fieldCount = mysqli_num_fields($result);

    print "Number of rows: " . $rowCount . "<br>";
    print "Number of columns: " . $fieldCount . "<p>\n";
}
?>

mysqli_num_rows gives the number of rows returned. mysqli_num_fields gives the number of columns. These are useful when building generic display helpers because the script does not need to know the table structure in advance.

Fetch Returned Rows One at a Time

A returned result should be treated like a table that can be read one row at a time. The common pattern is a while loop around mysqli_fetch_array.

<?php
while ($record = mysqli_fetch_array($result)) {
    print $record[0] . " ";
    print $record[1] . " ";
    print $record[2] . "<br>";
}
?>

Each fetched row is an array. You can read fields in two ways:

Numeric index, such as $record[0], $record[1], $record[2].
Associative index, such as $record['surname'] or $record['yearBorn'].

Numeric indexes are useful for generic loops because the code can process every column without knowing its name. Associative indexes are more readable when the script is written for a known table structure.

If you only want numeric indexes, use mysqli_fetch_row. If you only want associative indexes, use mysqli_fetch_assoc. If you want both, use mysqli_fetch_array.

Render a Result as an HTML Table

A reusable display function keeps database scripts shorter. The function below accepts a returned result and prints its rows into a simple HTML table.

<?php
function renderResultTable($result)
{
    if ($result == "") {
        print "No table data available<p>\n";
    } else {
        $fieldCount = mysqli_num_fields($result);

        print "<table border=1>\n";

        while ($record = mysqli_fetch_array($result)) {
            print "<tr>";

            for ($index = 0; $index < $fieldCount; $index++) {
                print "<td>" . $record[$index] . "</td>";
            }

            print "</tr>\n";
        }

        print "</table>\n";
    }
}
?>

This function does not need to know the table name, column names, or number of columns. It asks for the result for the number of fields and loops from zero to that count.

This pattern is especially useful for quick admin pages, learning tools, or internal utilities where you want to see the output of a database lookup without writing table-specific display code each time.

Add Column Headings with a Second Result

A table without headings is hard to read. A practical approach is to ask MySQL for the table structure separately, then use the resulting structure to print a heading row.

The display function can accept two resources:

One resource containing column information.
One resource containing the actual table data.

<?php
function renderTableWithHeadings($columnsResult, $dataResult)
{
    print "<table border=1>\n";
    print "<tr>";

    while ($column = mysqli_fetch_array($columnsResult)) {
        print "<td><b>" . $column[0] . "</b></td>";
    }

    print "</tr>\n";

    $fieldCount = mysqli_num_fields($dataResult);

    while ($record = mysqli_fetch_array($dataResult)) {
        print "<tr>";

        for ($index = 0; $index < $fieldCount; $index++) {
            print "<td>" . $record[$index] . "</td>";
        }

        print "</tr>\n";
    }

    print "</table>\n";
}
?>

The important idea is that result resources can be stored in variables and processed after the command has returned. A script can run more than one command, keep the results in separate variables, and then combine those results when building the final page.

Build a Generic Command Result Processor

A generic command processor needs to distinguish between commands that return rows and commands that only report success or failure. The practical method is to normalize the command text to lowercase and inspect the first few characters.

<?php
function processDatabaseResult($commandText, $result)
{
    $normalized = strtolower(trim($commandText));

    if ($result == "") {
        print "COMMAND FAILED<p>\n";
    } elseif (
        substr($normalized, 0, 6) == "select" ||
        substr($normalized, 0, 4) == "show" ||
        substr($normalized, 0, 8) == "describe"
    ) {
        $fieldCount = mysqli_num_fields($result);

        print "<table border=1>\n";

        while ($record = mysqli_fetch_array($result)) {
            print "<tr>";

            for ($index = 0; $index < $fieldCount; $index++) {
                print "<td>" . $record[$index] . "</td>";
            }

            print "</tr>\n";
        }

        print "</table>\n";
    } else {
        print "COMMAND SUCCEEDED<p>\n";
    }
}
?>

This function is useful because the same PHP page can handle several command styles:

A command that returns rows is displayed as a table.
A successful command that changes data prints a success message.
A failed command prints a failure message.

The keyword check is intentionally case-insensitive. A command may begin with uppercase, lowercase, or mixed case text, so converting the command to lowercase before checking it keeps the function predictable.

Use PHP Variables to Simplify Multi-Step Database Work

Not every database task has to be written as one complex command. PHP can hold intermediate values in variables between database calls. That makes some operations easier to understand.

A typical multi-step workflow looks like this:

Run a database command that returns one row.
Fetch that row into a PHP array.
Join or transform values using PHP string operations.
Build a second database command using the computed value.
Run the second command and check whether it succeeded.

<?php
$result = mysqli_query($connection, $lookupCommand);

if ($result == "") {
    print "Lookup failed<p>\n";
} else {
    $record = mysqli_fetch_array($result);
    $displayName = $record['forename'] . " " . $record['surname'];

    $changeCommand = buildChangeCommand($displayName);
    $changeResult = mysqli_query($connection, $changeCommand);

    if ($changeResult == "") {
        print "Change failed<p>\n";
    } else {
        print "Change succeeded<p>\n";
    }
}
?>

This is often easier to read than a large command that does everything at once. The trade-off is that multiple commands may not be as fast as one carefully written database command. For many small administrative tasks, readability and lower error risk are more important than maximum processing speed.

Combine Rows from Multiple Results

A PHP script can also combine data from multiple tables by running multiple read commands, storing each result separately, and fetching one row from each result inside the same loop.

<?php
$memberResult = mysqli_query($connection, $memberCommand);
$workResult = mysqli_query($connection, $workCommand);
$birthResult = mysqli_query($connection, $birthCommand);

$firstRow = 1;
$combinedCommand = startCombinedInsertCommand();

while ($member = mysqli_fetch_array($memberResult)) {
    $work = mysqli_fetch_array($workResult);
    $birth = mysqli_fetch_array($birthResult);

    $newRow = buildCombinedRow(
        $member['member_ref'],
        $member['forename'],
        $member['surname'],
        $member['sex'],
        $work['occupation'],
        $birth['city_born'],
        $birth['year_born'],
        $work['children_count']
    );

    if ($firstRow == 1) {
        $combinedCommand = $combinedCommand . $newRow;
    } else {
        $combinedCommand = $combinedCommand . "," . $newRow;
    }

    $firstRow = 0;
}

$combinedResult = mysqli_query($connection, $combinedCommand);

if ($combinedResult == 1) {
    print "Operation succeeded<p>\n";
} else {
    print "Operation failed<p>\n";
}
?>

The practical point is the loop structure. Each pass through the loop reads one row from each result. The script then has all related values available at the same time and can build one combined output operation.

This only works cleanly when the result sets are ordered consistently and contain matching records. If the rows do not align, the script may combine the wrong values.

Count Rows Changed by an Action Command

A successful change command does not always mean that a row was actually changed. A delete or update can succeed even when no row matches the condition.

Use mysqli_affected_rows after the command to find out how many rows were changed by the most recent insert, update, replace, or delete operation.

<?php
$result = mysqli_query($connection, $changeCommand);

if ($result == "") {
    print "Change failed<p>\n";
} else {
    print "Rows affected: " . mysqli_affected_rows($connection) . "<p>\n";
}
?>

This gives better feedback than a generic success message. It tells the user whether the command actually touched any rows.

Read the Generated AUTO_INCREMENT Value

When a table has an AUTO_INCREMENT field, MySQL generates the next value when a new row is inserted. PHP may need that generated value, for example, to show a reference number to the user.

Use mysqli_insert_id after the insert operation.

<?php
$result = mysqli_query($connection, $insertCommand);

if ($result == "") {
    print "Insert failed<p>\n";
} else {
    $generatedId = mysqli_insert_id($connection);
    print "Generated reference: " . $generatedId . "<p>\n";
}
?>

If the previous command did not generate an auto-increment value, the returned value is zero. If there was no connection, it returns false.

mysqli_ and mysql_ Function Names

Older PHP database code may use functions beginning with mysql_. The improved family begins with mysqli_, and the examples here use mysqli_ throughout.

For most functions, the names look very similar. However, connection and query calls are not simple one-for-one replacements in every case. When maintaining old code, check those calls carefully instead of mechanically removing or adding the letter i everywhere.

For new code in this style, use the mysqli_ functions consistently.

Common Mistakes to Watch For

Duplicating credentials in many files

Put the database username, password, host, and database name in one included PHP file. Duplicating them makes later changes harder and spreads confidential values across more scripts.

Continuing after a failed connection

If the connection fails, stop the script. A failed connection means the rest of the database work cannot be trusted.

Treating every successful command the same way

A successful data-returning command gives PHP a resource to read. A successful action command gives PHP a success value. Process them differently.

Forgetting that returned rows are read one at a time

A result resource is not printed automatically. Fetch rows in a loop with mysqli_fetch_array, mysqli_fetch_row, or mysqli_fetch_assoc.

Assuming success means rows changed

Use mysqli_affected_rows when the number of changed rows matters.

Mixing numeric and associative row access without a reason

Use numeric indexes for generic table rendering. Use associative indexes when field names make the code clearer.

Forgetting quote handling when building commands dynamically

When PHP variables are inserted into database command strings, string values need careful quoting. Keep command construction small and easy to inspect.

Checklist

Store connection settings in one included file.
Open the database connection with mysqli_connect.
Stop execution if the connection fails.
Send commands through mysqli_query.
Check whether the result is empty before processing it.
Use mysqli_num_rows and mysqli_num_fields for returned table data.
Fetch rows with mysqli_fetch_array when both numeric and associative indexes are useful.
Render returned rows with a reusable table function.
Use mysqli_affected_rows after change commands when row count matters.
Use mysqli_insert_id when an auto-generated key must be shown or reused.
Close the connection when the database work is finished.

Conclusion

A PHP database helper does not need to be complicated. The essential pattern is to centralize connection details, open a mysqli connection, send a command, inspect the result, and render returned rows carefully. Once those pieces are wrapped in small functions, PHP can manage common MySQL tasks with less repeated code and clearer error handling.