MySQLi Procedural Functions

MySQL Improved Extension(MySQLi) provides a Procedural Interface as well as an Object Oriented Interface. In this article 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 robin with password robin123 need to connect to a database called company_db at localhost. User robin should have privileges to access company_db.

1.$link = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

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

1.$link = mysqli_connect('localhost', 'robin', 'robin123', 'company_db', '3800');

mysqli_connect_error()

mysqli_connect() throws an error at failure and mysqli_connect_error() stores the error in 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 to mysqli_connect(). If you have enabled PHP errors, you would see an error that includes information like below. mysqli_connect_error() would returns 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 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.

01.<?php

02. 

03.$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

04. 

05.if (mysqli_connect_error()) {

06.$logMessage = 'MySQL Error: ' . mysqli_connect_error();

07.// Call your logger here.

08.die('Could not connect to the database');

09.}

10. 

11.// Rest of the code goes here

12. 

13.?>

mysqli_select_db()

To change the database in use, you can use mysqli_select_db(). For an example think that user robin also has privileges for a database called company_new_db then you can change the database like below.

1.$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

2. 

3.// Operations on 'company_db'

4. 

5.mysqli_select_db($link, 'company_new_db');

6. 

7.// Operations on 'company_new_db'

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.

1.$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

2. 

3.// MySQL operations goes here.

4. 

5.mysqli_close($link);

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 all other queries like INSERT, UPDATE and DELETE, it returns TRUE on success.

01.$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

02. 

03.$query = "SELECT * FROM employee";

04. 

05.if (mysqli_query($link, $query)) {

06.// Iterate and display result

07.} else {

08.// Show error

09.}

10. 

11.mysqli_close($link);

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 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.

1.while ($row = mysqli_fetch_array($result)) {

2./* Till there is data, $row will be an array.

3.* At the end, $row becomes NULL ending the loop.

4.*/

5.}

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

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

01.<?php

02. 

03.$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

04. 

05.$query = "SELECT `id`, `first_name`, `last_name` FROM `employee`";

06. 

07.$result = mysqli_query($link, $query);

08. 

09.while ($row = mysqli_fetch_array($result)) {

10.echo $row[0] . ': ' . $row[1] . ' ' . $row[2];

11.echo '<br />';

12.}

13. 

14.mysqli_free_result($result);

15. 

16.mysqli_close($link);

17. 

18.?>

When you run, above code will output following content in the web browser.

1: Robin Jackman
2: Taylor Edward
In addition to an Indexed array, mysqli_fetch_array() also returns an Associated array where keys are corresponding column names of the table. So, following code segment will produce same output.

1.while ($row = mysqli_fetch_array($result)) {

2.echo $row['id'] . ': ' . $row['first_name'] . ' ' . $row['last_name'];

3.echo '<br />';

4.}

You can limit which array to return as below.

1.// Returns only an Indexed array

2.mysqli_fetch_array($result, MYSQLI_NUM);

3. 

4.// Returns only an Associated array

5.mysqli_fetch_array($result, MYSQLI_ASSOC);

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

1.// Same as mysqli_fetch_array($result, MYSQLI_NUM)

2.mysqli_fetch_row($result);

3. 

4.// Same as mysqli_fetch_array($result, MYSQLI_ASSOC)

5.mysqli_fetch_assoc($result);

mysqli_free_result()

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

1.mysqli_free_result($result);

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.

1.if (mysqli_num_rows($result) > 0) {

2.// Proceed with the $result

3.} else {

4.// Show an error message

5.}

mysqli_affected_rows()

This function provides information on 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 an example, following is an UPDATE query to update the last name of Taylor in employee table. Provided that id field is unique we know that only one row should be affected from this query.

01.$query = "UPDATE `employee` SET `last_name` = 'Adams' WHERE `id` = 2";

02. 

03.mysqli_query($link, $query);

04. 

05.if (mysqli_affected_rows($link) == 1) {

06.// Rest of the code

07.} else {

08.// Show an error message

09.}

mysqli_error()

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

1.if (!mysqli_query($link, $query)) {

2.$logMessage = 'MySQL Error: ' . mysqli_error($link);

3.// Call your logger here.

4.die('There was an error in the query');

5.}

mysqli_real_escape_string()

Some characters like single quote has special meaning in SQL statements. For an example, 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().

Following call to mysqli_query() returns FALSE since the single quote in O’Neil hasn’t been escaped.

1.$query = "SELECT `id` FROM `employee` WHERE `last_name` = 'O'Neil'";

2.mysqli_query($link, $query);

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().

1.$name = mysqli_real_escape_string($link, "O'Neil");

2.$query = "SELECT `id` FROM `employee` WHERE `last_name` = '$name'";

3.mysqli_query($link, $query);

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

1.$lastName = mysqli_real_escape_string($link, $_POST['lastName']);

2.$query = "SELECT `id` FROM `employee` WHERE `last_name` = '$lastName'";

3.mysqli_query($link, $query);

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