Jump to content
Sign in to follow this  
PaulSonny

MySQLi for Beginners

Recommended Posts

LV 8

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.


View full guide

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



×
×
  • Create New...

Important Information

By using this site, you agree to our Guidelines, Terms of Use, and Privacy Policy.