MySQL Queries in PHP: MySQLi, SQL and Error Handling

Run queries with strict MySQLi error handling

For a fixed query with no user-supplied values, query() is fine. For variable values, use a prepared statement. During development, exceptions make failures easier to diagnose; production pages should log technical details rather than print database errors to visitors.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli('localhost', 'db_user', 'db_password', 'database_name');
$connection->set_charset('utf8mb4');

try {
    $connection->query("UPDATE student SET class = 'Five' WHERE class = 'Four'");
    echo 'Records updated: ' . $connection->affected_rows;
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    echo 'The database operation could not be completed.';
}
?>

How to write SQL using PHP to handle the data in MySQL database? In any database driven script we have to update, add, modify, data in the tables. By using PHP we can do all this using different functions available in PHP. We will start with very basic function, which will execute any query written in sql and can be applied to MySQL database.

SQL

Structured Query Language or popularly known as SQL is an universal language to handle database. An introduction and different types of sql command like select, insert, update etc you will get in the sql section of this site. There are some advance SQL commands like left join, linking of tables etc to study. If you are not comfortable with SQL any time you can refer the materials in sql section.

There are three steps involved in this process.

Connection to database
Build the query and execute
Display the data

1.Connecting to database

To manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records.

One is using Portable Data Object ( PDO )
Second one is MySQLI ( MysQL Improved )

2.Executing Query using PHP Functions & SQL

Once the MySQLi connection is established, the $connection->query() method executes a fixed SQL statement. For values from forms or URLs, use a prepared statement instead.

Let us first write the query and store in a variable. We will write a query to create table.

$query = "CREATE TABLE student (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(10) NOT NULL,
    mark INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB";

The SQL CREATE TABLE statement is stored in $query and passed to a parameter to the function like below.

$rt = $connection->query($query); // For this fixed CREATE TABLE statement.

For a successful CREATE TABLE command, query() returns true. When strict MySQLi error reporting is enabled (as in the complete example above), SQL errors throw mysqli_sql_exception instead of being returned as false. Use try/catch to handle failures. If you need a refresher on branching, see PHP if/else.

try {
    $connection->query($query);
    echo 'Command completed successfully.';
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    echo 'Unable to complete the database operation.';
}

Log technical details server-side inside the catch block. Do not display raw SQL errors, usernames, credentials or internal table names to website visitors. The short line below belongs inside catch (mysqli_sql_exception $e).

error_log($e->getMessage()); // Inside catch (mysqli_sql_exception $e).

During development, inspect the server error log. In production, show the visitor a generic message while keeping database details private. See MySQLi query examples for the related API. The SQL error reference provides additional historical background; do not copy removed functions from legacy examples.

The complete code is available below.

$query = "UPDATE student SET class = 'Five' WHERE class = 'Four'";
try {
    $connection->query($query); // Fixed SQL only; prepare queries with user input.
    echo 'Records updated: ' . $connection->affected_rows;
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    echo 'Unable to update records.';
}

These shorter fragments assume the $connection object is created as shown in the complete example above; see the MySQLi connection lesson.

3. Display Data

We may be collecting some data or trying for some updating or changes in database. The response from the database has to be displayed to the user.
Displaying Data from MySQL table
PHP MySQL Collecting single record from MySQL PHP Code generator for PDO & mysqli

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

vexatiousjones	04-02-2016
Im getting a No Database Selected error when using this code. Im using PDO connection string linked aboe. Any Suggestions?

smo1234	06-02-2016
YOu need to connect to database and select database. Read the connection string link available here.

Ann Ekpe	26-03-2017
Good day . please I need your assistance.I am currently designing a webpage with members signing in and having a dashboard.I want to be able to send data from the database to each particular member , so anytime they login to the website they can view the data... Urgent HELP

smo1234	26-03-2017
You can store the message in a table and display inside login area, that is when member login is completed this message can be displayed. The message can be managed by adding or updating new message etc. Knowledge of any scripting language and Database handling is required.

PHP MySQL query with error printing

Run queries with strict MySQLi error handling

SQL

1.Connecting to database

2.Executing Query using PHP Functions & SQL

3. Display Data

Subhendu Mohapatra