MySQLi Procedural Functions

MySQL Improved Extension (MySQLi) provides a Procedural Interface as well as an Object Oriented Interface. In this chapter, we will look into some of the common MySQLi Procedural functions.

mysqli_connect()

This function is used for connecting to MySQL. Before doing any database operation, you need
to connect to MySQL. On success, this function returns a link identifier that you can use in other
MySQLi functions. On failure, it will throw an error.
Following is how a user named anuj with password anuj123 needs to connect to a database called
company_db at localhost. User robin should have privileges to access company_db.

If your MySQL port is different from the default one (3306), you need to give the port number as the fifth parameter.

mysqli_connect_error()

mysqli_connect() throws an error at failure, and mysqli_connect_error() stores the error of the
last call to mysqli_connect(). If there is no error, it returns NULL.
To try out a mysqli_connect() error, stop MySQL server and call mysqli_connect(). If you have
enabled PHP errors, you would see an error that includes information like below. mysqli_connect_-
error() would return the same message.
Can’t connect to MySQL server on ‘localhost’
In practice, it’s not good to show error messages like these to the users of your PHP application
(They may contain sensitive data, and they can look too technical).
Therefore you can use the error suspension operator in front of mysqli_connect() to stop it throwing
errors, and use mysqli_connect_error() to log the error for troubleshooting like below.

mysqli_select_db()

To change the database in use, you can use mysqli_select_db(). For example assume that user anuj
also has privileges for a database called company_new_db; then you can change the database as
below.

You will only need this function if your PHP application deals with more than one database.

mysqli_close()

You can use this function to close a MySQL connection. It returns TRUE on success and FALSE on
failure.

PHP will close open connections and release resources at the end of your PHP script. But it’s a
good practice to explicitly use mysqli_close() at the end of MySQL operations to release resources
immediately.

mysqli_query()

This is the function used for executing MySQL queries. It returns FALSE on failure. For SELECT,
SHOW, DESCRIBE, and EXPLAIN queries (where there is an output), it returns a MySQL result set
(resource) which can be used in functions like mysqli_fetch_array().
For other queries, like INSERT, UPDATE, and DELETE, it returns TRUE on success.

mysqli_fetch_array()

This function is used for reading data from a MySQL result set (returned by a mysqli_query()). It
reads and returns one row of data as an array and then moves the pointer to the next row. When
there are no more rows to return, it returns NULL. Because of this behavior, it’s often used with a
While Loop as below.

Let’s assume the following employee table is available in our company_db database.

id	first_name	last_name	age	joined_date	records
1	Anuj	Kumar	25	2017-03-17	2
2	John	Doe	27	2018-01-11	1

Below is how we would fetch ID, First Name, and Last Name from this table.

When you run it, the code above will output the following content in the web browser.
1: Anuj kumar
2: John Doe
In addition to an Indexed array, mysqli_fetch_array() also returns an Associated array where keys
are the corresponding column names of the table. So, the following code segment will produce the
same output.

You can limit which array to return as below.

PHP provides two functions that produce the same results as you would get by passing constants to
mysqli_fetch_array().

mysqli_free_result()

Immediately after using a result set, you can free the memory used for it as below.

mysqli_num_rows()

mysqli_num_rows() returns the number of rows in a result set. Using it, you can take a different
action when the result set is empty.

mysqli_affected_rows()

This function provides information on the last MySQL query executed. For INSERT, UPDATE,
REPLACE, and DELETE, it provides number of rows affected. For SELECT, it returns number of
rows in the result set as mysqli_num_rows().
For example, the following is an UPDATE query to update the last name of Taylor in employee
table. Provided that the id field is unique, we know that only one row would be affected from this
query.

mysqli_error()

If there was an error in the last MySQL query, this function will return the error. If there was no
error, it would return an empty string.

mysqli_real_escape_string()

Some characters like single quote have special meanings in SQL statements. For example, the single
quote is used for wrapping strings. So, if your SQL statement contains these special characters, you
need to escape them via mysqli_real_escape_string() before sending the query to mysqli_query().
The following call to mysqli_query() returns FALSE, since the single quote in O’Neil hasn’t been
escaped.

The following call to mysqli_query() would return a proper result set (provided that an employee
exists with last name O’Neil), since the name is first escaped via mysqli_real_escape_string().

If your SQL statements are built based on user inputs like those below, it’s always a good idea to use
this function, since user input may contain special characters.

User inputs may contain attempts for security breaches by having special characters execute
malicious actions (SQL injection). Escaping user input will reduce the risk of SQL injection.