programming MySQLi for Beginners

[PaulSonny]

Introductions

Most of the sites you visit these days have some sort of database storage behind them. Most sites that use PHP will opt to use a MySQL database. You are able to interact with your MySQL database using the MySQLi class.

MySQLi will only work with a MySQL database, whereas if you were to use PDO, it will work with various different database systems. MySQLi can be coded via objected oriented or procedural. Today I am going to be looking at the object orientated approach.

PHP MySQLi Class

Connecting

Connecting to a MySQL database is done by instantiating a new instance of the MySQLi class.

$db = new mysqli('localhost', 'username', 'password', 'paulsonny-demo');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

The database name parameter is optional and can be left out. Please note, if you do leave the database name parameter empty then you will need to prefix all of your tables in your queries with the database name.

Querying

Let's assume we have a users table in our database and we wish to retrieve them all. 

$sql = "SELECT * FROM `users`";

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

The above will give you a $result variable that contains a mysqli_result object. Once we have the mysqli_result object we are able to loop through the results, perhaps displaying them to the user and then freeing up the result.

Query Results

If you wanted to loop through the results and show them to the user with each row being on a new line we'd do something like the following:

while($row = $result->fetch_assoc()){
    echo $row['username'] . '<br />';
}

# of Returned Rows

Each mysqli_result object that is returned from the database as a variable which is called num_rows. This can be accessed by doing the following:

<?php
echo 'Total results: ' . $result->num_rows;
?>

# of Affected Rows

When you are running an UPDATE statement, you may sometimes wish to know how many rows have been updated. The mysqli object has a variable called a affected_rows which can be accessed as follows:

<?php
echo 'Total rows updated: ' . $db->affected_rows;
?>

Free the Result

Once you have finished with your result set, it is recommend that you free the result. This would be placed after our looping over the records.

$result->free();

This will free up some system resources, and is a good practice to get in the habit of doing.

Escaping characters

When inserting data into a database, it is recommended to escape it first, so that single quotes get preceded be a backslash. This will mean that any quotes won't break out of any that you use in your SQL. You should look to use the below method:

$db->real_escape_string('This is an unescaped "string"');

However, because this is a commonly used function, there is an alias function that you can use which is shorter and less to type:

$db->escape_string('This is an unescape "string"');

This string would now be safer to insert into your database through a query.

Close Database Connection

Don't forget, when you've finished with your database to make sure that you close the connection:

$db->close();

PHP MySQL Class

If you are using the functions mysql_connect() or mysql_query() then I would recommend swapping to the above. Any functions prefixed with mysql_ are actively discouraged by PHP themselves.

In my next MySQLi Tutorial I will cover Prepared Statements. 

This tutorial and more like it can be found on my company website: P S Web Solutions Ltd.

Edited April 13, 2017 by PaulSonny

[Digital]

Awesome topic, and useful for new users, since most games will require the use of database.

[Callum]

I need to learn PHP... damn! Good tutorial! @PaulSonny

[Digital]

12 minutes ago, Callum said:

I need to learn PHP... damn! Good tutorial! @PaulSonny

Wait until he gets into Prepared Statements  

[PaulSonny]

1 hour ago, Callum said:

I need to learn PHP... damn! Good tutorial! @PaulSonny

After the next 2 instalments I'll rustle up a basic to-do app that shows add, edit/update and delete to a basic which basically forms the basis of any form/user input.