[PHP] How to use MySQLi

[RastaLulz]

MySQLi Info:

You must be registered for see links

Today I'll show you the basics of MySQLi, and how to use it. I'm showing you this because a lot of you still use the old mysql functions. That's bad because they really are outdated, less secure, and less efficient.

Connect to MySQL database:

<?php
$_CONFIG['mysql']['host'] = 'localhost';
$_CONFIG['mysql']['user'] = 'root';
$_CONFIG['mysql']['pass'] = 'pass';
$_CONFIG['mysql']['db']  = 'db';

$db = new mysqli($_CONFIG['mysql']['host'], $_CONFIG['mysql']['user'], $_CONFIG['mysql']['pass'], $_CONFIG['mysql']['db']);

Execute a query:

$username = 'RastaLulz';

//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");

//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

Get data:

if($getUserInfo->num_rows > 0) {
  $userInfo = $getUserInfo->fetch_object();
  echo $user."'s emails is <strong>".$userInfo->email.'</strong>!';
}else{
  echo 'Sorry, but that username could not be found.';
}

Stop SQL injections:

$pass = '123456'; $email = '[email protected]'; $id = '21';

$secure = $db->prepare('UPDATE users SET password = ?, email = ? WHERE id = ?');
$secure->bind_param('ssi', $pass, $email, $id); //ssi stands for string, string, integer - based on the question marks
$secure->execute();

If you have any questions, feel free to ask below.

 

[Markshall]

You offered to answer questions, so, what is the difference between MySQL and MySQLi? I've always wondered but been too much of a lazy prick to Google it.

 

[TheJacob]

This shows a basic use for MySQLi. Looks good, it'll probably help people out. You should write a follow up tutorial actually explaining why to use MySQLi over MySQL and PDO in PHP.

 

[RastaLulz]

You offered to answer questions, so, what is the difference between MySQL and MySQLi? I've always wondered but been too much of a lazy prick to Google it.

Well, I'm not that all good when it comes to terminology. However, I'll try my best to explain it.

MySQLi allows you to use object oriented code and is a lot more efficient (from what I heard on XenForo). But the main reason I use MySQLi is to stop SQL injections, with the simple bind_param function.

 

[TheJacob]

MySQLi allows you to use object oriented code and is a lot more efficient (from what I heard on XenForo). But the main reason I use MySQLi is to stop SQL injections, with the simple bind_param function.

You should read the documentation on PHP.net. It's like four pages or something long (not the individual function documentation). It will thoroughly go through all the benefits and disadvantages, comparing MySQLi to the traditional > 4.1.3 MySQL function integration in PHP. Then you should personally compare it to PDO by reading its documentation and user reviews (as MySQLi and PDO are practically the exact same thing although they have slight differences which depending on what your planning to do will help you choose between those two).

None the less, I am glad to see somebody has converted to smarter coding practices (if used properly)!

 

[Kryptos]

Nice tutorial, everyone should really stop using MySQL, and start using MySQLi.

I'm using MySQLi on RevCMS 3.0, and none of it is really tested but I was able to build a pretty neat class, that would make doing a query + binding the params and/or the result a 1/2 line job.

 

[brsy]

Well I have a quick question. I saw you had the following code above in your OP.

$user = 'RastaLulz';
 
//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");
 
//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

Shouldn't it be:

$username = 'RastaLulz';
 
//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");
 
//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

?? I simply changed the variable from $user to $username because in your query you are using the variable $username

I am just assuming, as I have no MySQLi knowledge but I am just wondering.

 

[Kryptos]

Well I have a quick question. I saw you had the following code above in your OP.

$user = 'RastaLulz';
 
//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");
 
//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

Shouldn't it be:

$username = 'RastaLulz';
 
//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");
 
//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

?? I simply changed the variable from $user to $username because in your query you are using the variable $username

I am just assuming, as I have no MySQLi knowledge but I am just wondering.

Yea, Rasta made a mistake. Nice eye!

 

[brsy]

Yea, Rasta made a mistake. Nice eye!

Yea, thanks. I was wondering because I was confused how his "$username" variable came along

 

[RastaLulz]

Well I have a quick question. I saw you had the following code above in your OP.

$user = 'RastaLulz';
 
//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");
 
//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

Shouldn't it be:

$username = 'RastaLulz';
 
//update info
$db->query("UPDATE users SET email = '[email protected]' WHERE name = '".$username."'");
 
//get info
$getUserInfo = $db->query("SELECT * FROM users WHERE name = '".$username."'");

?? I simply changed the variable from $user to $username because in your query you are using the variable $username

I am just assuming, as I have no MySQLi knowledge but I am just wondering.

Fixed.

 

[langer6]

Sorry for been a noob, But does mysqli have its own class then? As I have not seen one declared there.
Or does it require us to make our own?

 

[RastaLulz]

Sorry for been a noob, But does mysqli have its own class then? As I have not seen one declared there.
Or does it require us to make our own?

MySQLi is a class built into PHP.

You must be registered for see links

 

[langer6]

Ohh okay, Thank you. I think i will stay away from this in my current project and then move onto it in the future.

 

[At0m]

Nice tutorial Rasta, keep going with this.
Will help me, and a lot of people.

 

[Markshall]

Nice tutorial Rasta, I've just had a quick read over this -- I've a little better understand of the MySQLi functionality now, I may decide to use it next time I create a simple project.

 

[Adil]

Thanks Rasta, will help all of us in the future ^^
p.s(50,000 post )

 

[Jo$h]

Is MySQLi available for the procedural method, or must you use OOP?

 

[RastaLulz]

Is MySQLi available for the procedural method, or must you use OOP?

No, you don't have to use the OOP way. How ever, I don't see why you wouldn't.

Look at the manual for MySQLi, and it will show you examples for both ways:

You must be registered for see links

 

[Jo$h]

No, you don't have to use the OOP way. How ever, I don't see why you wouldn't.

Look at the manuel for MySQLi, and it will show you examples for both ways:

You must be registered for see links

I am just not as good with the OOP method, so I will be adding MySQLi Support to my second version of CreepCMS ;D. (First is in Middle stages of Development.)

 

[Livar]

Great tutorial.