How to use mysqli in php

Mysqli short for MySQL improved is an extension used for talking to a mysql database. If you want to dig in to mysqli you can use the online book at php.net as a reference. Might be a little intimidating for beginners but nevertheless contains all the information you need to know about mysqli.

This article serves as an introduction to mysqli. I’m going try my best to make this a lot more easier to learn. Whether you have already dealt with mysql database before or just a total beginner in connecting to databases.

 

Requirements

 

Connecting using mysqli

Make sure that you have the same or higher version of php, mysql, and apache as mine before proceeding.

First, you need to create a new mysqli object which takes up 4 arguments. The host, user, password and the database that you’re trying to connect with.  Also check for connection errors, then immediately terminate the current script if there are errors. This is to make sure that your program won’t consume any more resources while trying to execute the other script below it.

//host, user, password, database

$db = new Mysqli("localhost", "root", "", "orgbpls");

 

//check for connection errors

if ($db->connect_errno) {
    die('Connect Error: ' . $db->connect_errno);
}

 

Insert records

First, the very basic inserting new rows on a table. Just use the object that we created earlier to call up the query() function in this case our mysqli object is called $db.
The query() function takes up an sql query as its argument. It returns 1 if the query that you supplied executed successfully and 0 if its not.

$create_user = $db->query("INSERT INTO sys_users

SET UserID='yayquery'");

 

Update records

Nothing new here, we just supplied an update query as the argument of the query() function. What were doing here is that were changing whatever the current password of the user with the user id of yayquery into the hashed version of 1234.

$hashed_pw = md5('1234');

$update_user = $db->query("UPDATE sys_user 
SET strPassword='$hashed_pw' WHERE UserID='yayquery'");

 

Delete

This might probably getting boring now. But you also use the query() function to delete rows.

$delete_user = $db->query("DELETE FROM sys_users 
WHERE UserID='yayquery'");

 

Fetch a single record

When fetching records from the database, you need to make use of the fetch_object() together with the query() function. First you perform a select query, assign it to a variable in this case the variable is called $user.
And then use that variable to call the fetch_object() function. Assign the result to another variable, in this case the variable $u. Finally use it to get the value of the fields that you specified in your query, in this case were using it to output the user id.

$user = $db->query("SELECT UserID, intDepartment, intRole 
FROM sys_users WHERE UserID='tcollector'");

$u = $user->fetch_object();
echo $u->UserID;

 

Fetch multiple records

The idea is the same with fetching a single record all you have to do is to loop through the results using while loop.

$results = $db->query("SELECT * FROM sys_users");

while($row = $results->fetch_object()){
	echo $row->UserID.'<br/>';
}

 

Conclusion

That’s it for this tutorial, you’ve learned the basics of using mysqli in php. You’ve learned how to perform basic database operations.

One thought on “How to use mysqli in php

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s