Using PHP & MySQLi With A MySQL Database Part 1
Today I thought I would go back to basics & cover how to connect to a MySQi database with PHP and the MySQLi extention. For those familiar with PHP & MySQL this will be nothing new, but for those just venturing into PHP it will definitely come in handy.
MySQLi is the new method by which you can connect to a MySQL database. MySQLi is actually an extension & you must make sure it is compiled with your PHP install. If you aren’t sure if you have MySQLi then either use phpinfo() to check, or ask your host.
MySQLi can be used either as separate functions (procedural method) , as was used previously, or as an object (object oriented method). In this tutorial I am going to concentrate on the object method as it is easier & also helps provide a nice easy introduction to the use of objects within PHP.
Setting Up The Connection
To set up a connection to a MySQL database using MySQLi is very easy. You just instantiate the MySQLi class as follows:
|
1 |
$mysqli = new mysqli('host', 'username', 'password', 'database'); |
It is that simple. There are two more parameters which are optional. Those are the port to connect to and the socket to use, these are generally set in the php.ini and do not need to be changed.
While that is the general way to set up a MySQLi connection there is another way. You can instantiate the MySQLi class & then connect using the connect() method. Here is an example:
|
1 2 |
$mysqli = new mysqli(); $mysqli->connect('host', 'username', 'password', 'database'); |
You can use the connect() method to switch database connections & use the select_db() method to change databases. select_db() is used in the same way, the difference being it only takes one parameter. The database name.
Collecting Data
Now that you have an active connection you are probably eager to get some data. Well here is how to do it:
|
1 2 3 4 5 6 7 8 |
//Set up you database connection as in the previous section $query = "SELECT title, author FROM books ORDER BY title"; $result = $mysqli->query($query); while(list($title, $author) = $result->fetch_row()) { printf("<strong>%s</strong> by %s", $title, $author); } |
The unrendered HTML output would look like this:
|
1 2 3 |
<strong>Harry Potter & The Half-blood Prince</strong> by J.K Rowling <strong>Interview With A Vampire</strong> by Anne Rice ... |
If you’ve used the old method (pre MySQLi) to connect and collect data from a MySQL database you can probably see how similar they are.
Freeing Memory & Closing The Connection
Finally there is freeing memory used by the result & closing the connection. It’s very important to free up the resources used by the query & close the connection after you have finished using them.
|
1 2 3 4 5 |
//After all other MySQLi connection code... //Free result, deletes the query results, make sure you have finished using them. $result->free(); //Closes MySQL connection. $mysqli->close(); |
That’s all there is to it.
I hope this tutorial has been useful. If you have any questions, problems, or suggestions let me know in the comments or hit me up via Twitter, my username is VeneficusUnus.
24 Comments
John
Hi Paul, im just starting to learn php and mysql, is there any reason that i shouldn’t use mysqli_real_connect ? I recently asking about this elsewhere, when i look at mysqli_connect at the php manual its listed within the deprecated and alias list.
So thats why im thinking its better to use mysqli_real_connect, can you advise on this please?
Paul Robinson
Mysqli_connect is a function based alias to the mysqli object construct.
In my opinion it is better to use the OOP version of mysqli simply for it’s neatness and eas of use. That way you don’t have to worry about passing along a handle to each function either.
I hope that helps. Let me know if you have any other questions.
John
Thanks for replying so quickly Paul and for explaining.
Since im just starting to learn PHP, although im aware that i will need to understand procedural anyway, do you think that its better that i go in the direction of OOP more than anything? I have been reading about OOP and how it makes things easier, how parts of the code can be reused and other benefits.
Paul Robinson
With regards to mysqli you are probably better off learning the OOP version. With PHP in general you’ll need to know them both as PHP 5 becomes the standard & PHP 6 draws nearer.
There is no harm in learning both, but usage wise I would suggest sticking to OOP when using Mysqli just for it’s ease of use if nothing else. It also helps to teach the basics of OOP if you don’t already know how it works.
John
Thanks very much Paul for your advice, its very helpful.
Paul Robinson
No problem, glad I was able to help out. 😉
Peggy
“To set up a connection to a MySQL database using MySQLi is very easy. You just instantiate the MySQLi class as follows”
Your directions sound very easy, however you did not say how or where to do it. I am a newbie and trying to set up php and Mysql for Drupal. The drupal is requiring a mysqli. My database is set up in mysql but I am not sure how to get it connected with sqli. Any help is appreciated.
Paul Robinson
That’s because this is how to programatically connect to a MySQL database using MySQLi.
MySQLi is not actually a type of database it is a improved version of the old connection method used by programmers to get PHP and MySQL to work together.
If Drupal says it requires MySQLi it means it requires a PHP install with the MySQLi driver installed. Normally most PHP installs of version 5 or higher have it.
I’ve installed Drupal once or twice so if you get stuck please feel free to email me via the contact form & I’ll try and help you out.
peggy
Thank you for your quick response. After many hours of looking on the drupal.org site, I think I figured the issue. I do have the Mysqli extension installed with php. And of course it is on a WIMP server which drupal is finiky about as it is. I will message you if my fix does not work. Again thank you for your help.
Paul Robinson
Yeah if it’s possible for you to change to WAMP instead I would advise it. If not good luck. 😉
Mishael
Hey Paul,
How do I use
__construct()to connect to database, then use__destruct()to close out the connection?Thanks!
Paul Robinson
Hi Mishael,
It depends on what you mean by
__construct()&__destruct()do you mean in a custom object or when extending or using the MySQLi’s object?If you mean in a custom object then you’d just write your MySQLi connection code & close connection code into the objects respective
__construct()&__destruct()functions.If you mean when extending MySQLi then there is a small bit about it on the PHP documentation page in the examples. Let me know if you have any questions about it.
john
Hi Paul, I just installed php and mySQL, everything were working fine for me. But when i used mysqli in my php source code, and opened the page in my web browser, nothing appeared.
It looks like mysqli is not enabled with php, can you help me please?
Paul Robinson
Hi John,
it all depends on how you installed PHP & MySQL. MySQLi is an extension for PHP and comes with most PHP distro’s I’ve used.
Did you use something like XAMPP or WAMPServer to install them?
You can always install it manually though. On Linux it’s best to do it from your Distro package manager. On Windows you will need to find the php_mysqli.dll which you should be able to find on MySQL’s official website, or you can always check the ‘extensions’ folder inside your PHP install. Once you have it place it inside that ‘extensions’ folder I just mentioned (in your PHP install folder). Now open up your php.ini file (also in your PHP install folder), and search for ‘;extension=php_mysqli.dll’. If it is there, remove the ‘;’ from the front. If it isn’t type exactly that without the semi colon. Then search for ‘extension_dir’ and make sure the path is the same as where the ‘php_mysqli.dll’ file is kept.
Hopefully that will help you. 🙂
John
Hi Paul, Thank you very much for your reply.
But i already tried this method but it didn’t work for me.
i installed php 5.1.4 on my windows vista machine by unzipping a file, mySQL 5.0.22 and apache 2.0.58 .
php_mysqli.dll is in the c:\php\ext , so i changed the extension_dir value to c:\php\ext and i added in the extensions part extension=php_mysqli.dll in a new line but it didn’t work for me…
i have tried so many possibilities, all of them failed…
Paul Robinson
This might be a stupid question, but have you restarted Apache? Sometimes after installing an extension I have to restart Apache for it to be recognized. Also the best way to make sure it isn’t being read & that your PHP extension path is correct is to make a php file with:
inside & visit it, if you haven’t already.
John
Hi Paul, PHP where working fine ( until using mysqli ), i already tested the phpinfo() function it works just fine.But there is no mysqli extension in the output of phpinfo().
I already restarted apache after each modification in the php.ini file many times…
Thank you for your reply.
Paul Robinson
Is your PHP extension directory & PHP.ini file path what you expect them to be in the phpinfo().
I ask as the problem sounds like one I had with a client where PHP just wouldn’t read the extension & it was because the PHP.ini file it was reading wasn’t the one it should have been.
John
Hi John,
I have only one php.ini file and everything is fine and as expected except for the mysqli extension in the phpinfo().
I Don’t want to bother you more, thank you for your help and support.