MySQLi Tutorial

Posted on 09. Jul, 2006 by Will Fitch in MySQL, PHP

Moving from a procedural system to object-oriented can be a daunting task. One feature to assist you is the MySQLi class, which allows for an object-oriented approach to database manipulation. This tutorial gives insight into the structure and basic usage of the MySQLi class. If PDO isn’t an option for you, then try MySQLi!

MySQLi stands for MySQL Improved, and is available from MySQL versions >= 4.1.3 and must be compiled in PHP with –with-mysqli=/path/to/mysql/bin/mysql_config.

There are three hierarchical classes with MySQLi:

  1. mysqli – Represents a connection between PHP and a MySQL database
  2. mysqli_stmt – Represents a prepared statement
  3. mysqli_result – Represents the result set obtained from a query against the database

Each one of these classes represent a different situation. The mysqli class is absolutely necessary because you will be communicating only through this class. It contains the connection to the other two classes (mysqli_stmt and _result). For instance, if you were executing a SELECT statement to pull cities from the states table, you would do something like the following:

PHP
  1. <?php
  2. $mysqli = new mysqli(‘hostname’,‘username’,‘password’,‘database’);
  3. //This is where we will query the database and pull using the cities/states SELECT statement
  4. // If the result returns true
  5. if ($result = $mysqli->query("SELECT city FROM state WHERE state=’AL’")) {
  6. // So the result returned true, let’s loop and print out each city.
  7. // The number of rows returned is assigned to the property "num_rows" in the mysql_result class
  8. echo ‘For the state of AL, there are ‘.$result->num_rows.‘ cities.
  9. ‘;
  10. // The "fetch_object()" method is the equivalent of the old mysql_fetch_object() function. It allows access to the returned rows within the resouce object ($result in this case).
  11. while ($row = $result->fetch_object()) {
  12. echo ‘City Name: ‘.$row->city.‘
  13. ‘;
  14. }
  15. } else {
  16. // Notice below that the errors are still contained within the mysqli class. This means that each result will affect a single "error" property. In otherwords, if your result fails, the error returned from MySQL is assigned to the property "error".
  17. // This means the query failed
  18. echo $mysqli->error;
  19. } // end else
  20. $mysqli->close();
  21. ?>

Before we go any further, let’s look at each one of the methods and parameters of the mysqli and mysqli_result class. This tutorial will not cover the mysql_stmt class, as this will be “Part 2″, which will cover prepared statements, and will be available within the next week.

Part 3 of the mysqli tutorial will cover transactions using the InnoDB engine.

mysqli Class Methods:

  • mysqli – construct a new mysqli object
  • autocommit - turns on or off auto -commiting database modifications
  • change_user - changes the user of the specified database connection
  • character_set_name - returns the default character set for the database connection
  • close - closes a previously opened connection
  • commit - commits the current transaction
  • connect - opens a new connection to MySQL database server
  • debug - performs debugging operations
  • dump_debug_info - dumps debug information
  • get_client_info - returns client version
  • get_host_info - returns type of connection used
  • get_server_info - returns version of the MySQL server
  • get_server_version - returns version of the MySQL server
  • init - initializes mysqli object
  • info - retrieves information about the most recently executed query
  • kill - asks the server to kill a mysql thread
  • multi_query - performs multiple queries
  • more_results - check if more results exist from currently executed multi -query
  • next_result - reads next result from currently executed multi -query
  • options - set options
  • ping - pings a server connection or reconnects if there is no connection
  • prepare - prepares a SQL query
  • query - performs a query
  • real_connect - attempts to open a connection to MySQL database server
  • escape_string - escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection
  • rollback - rolls back the current transaction
  • select_db - selects the default database
  • set_charset - sets the default client character set
  • ssl_set - sets ssl parameters
  • stat - gets the current system status
  • stmt_init – initializes a statement for use with mysqli_stmt_prepare
  • store_result - transfers a resultset from last query
  • thread_safe - returns whether thread safety is given or not
  • use_result - transfers an unbuffered resultset from last query

mysqli Class properties:

  • affected_rows - gets the number of affected rows in a previous MySQL operation
  • client_info - returns the MySQL client version as a string
  • client_version - returns the MySQL client version as an integer
  • errno - returns the error code for the most recent function call
  • error - returns the error string for the most recent function call
  • field_count - returns the number of columns for the most recent query
  • host_info - returns a string representing the type of connection used
  • info - retrieves information about the most recently executed query
  • insert_id - returns the auto generated id used in the last query
  • protocol_version - returns the version of the MySQL protocol used
  • server_info - returns a string that represents the server version number
  • server_version - returns the version number of the server as an integer
  • sqlstate - returns a string containing the SQLSTATE error code for the last error
  • thread_id - returns the thread ID for the current connection
  • warning_count - returns the number of warnings generated during execution of the previous SQL statement

The best way to explain the usage of the mysqli class is to show by example. There are too many methods and properties to go over in this tutorial, so I will only give examples of some.

It’s important to note that some of these methods listed, aren’t methods at all. For instance, if you wanted to get the client library version of MySQL, you would use mysqli_get_client_version(), which returns the integer version. Here is an example:

PHP
  1. <?php
  2. printf("Client version: %d\n", mysqli_get_client_version());
  3. ?>

You are forced to use the functions since there is no need for the connection to the actual database.

You have already seen the usage of the mysql_result connection from the mysqli class, so let’s go over some more examples:

Getting some info on the result from the mysql database:

PHP
  1. <?php
  2. $mysqli = new mysqli(‘localhost’,‘db_user’,‘my_password’,‘mysql’);
  3. // Get all the MySQL users and their hosts
  4. $sql = "SELECT user, host FROM user";
  5. // If the query goes through
  6. if ($result = $mysqli->query($sql)) {
  7. // If there are some results
  8. if ($result->num_rows > 0) {
  9. // Let’s show some info
  10. echo ‘There were ‘.$result->num_rows.‘ rows returned.
  11. ‘;
  12. echo ‘We selected ‘.$result->field_count.‘ fields in our query.
  13. ‘;
  14. echo ‘Our thread ID is ‘.$mysqli->thread_id.‘
  15. ‘;
  16. // Ok, let’s show the data
  17. while ($row = $result->fetch_object()) {
  18. echo ‘Username: ‘.$row->user.‘ :: Host: ‘.$row->host.‘
  19. ‘;
  20. } // end while loop
  21. } else {
  22. echo ‘There are no results to display. Odd how we connected to this database and there are no users.’;
  23. } // end else
  24. } else {
  25. // Notice the error would be within the mysqli class, rather than mysql_result
  26. printf("There has been an error from MySQL: %s",$mysqli->error);
  27. } // end else
  28. // Close the DB connection
  29. $mysqli->close();
  30. ?>

Here we used some common methods and properties to test and display information. Next week we will go over prepared statements and how they might assist in your scripts.

If you have any questions regarding MySQLi, please feel free to put comments and I will answer them soon.

– Will

[del.icio.us] [Digg] [Google] [LinkedIn] [StumbleUpon] [Windows Live] [Yahoo!] [Email] More »

Tags: , , ,

Related Articles

26 Responses to “MySQLi Tutorial”

  1. Jay Johnston

    12. Dec, 2006

    This is a great tutorial. Thanks, Will, I find oop database work much more maintainable than the old procedural method.

  2. Rafael

    17. Jun, 2007

    Thanks for this clean and concise tutorial.

  3. Conor Mulligan

    23. Jul, 2007

    Nice one, a really useful tutorial.

  4. brent ransom

    14. Nov, 2007

    Most excellent, but where’s the mysqli_stmt tutorial? I’ll go search…

  5. chris

    13. Sep, 2008

    up until now i have been a procedural php developer, even after learning of the benifits of php5 OOP cababilities i was still apprehensive about having to learn from scratch (about OOP, MVC etc..)

    however my quest into OOP is going well and tutorials like this really help. Thanks!!

    I may have a few questions which i will post later, but i will have a play first :D

  6. Kalleguld

    19. Oct, 2008

    Great article, but I can’t help but ask about the two last parts. I can’t find them, neither under the tag MySQLi or under mysql

  7. PHPFiend

    22. Oct, 2008

    Great Article!

    I am too starting to learn about OOPHP and well its quite refreshing to think of everything in a new way….I cant wait till I can write classes, much like I do the rest of my scripts… :-)

  8. Chris

    20. Nov, 2008

    REALLY looking forward to part 2!!!

  9. Marcio

    27. Feb, 2009

    Curious: When will this: $mysqli->close(); actually close the connection? When the user closes the browser? When the php is done? If we do a refresh it will re-open the connection? It has no effect ?

    Thanks in advance,
    A newbie,
    Márcio

  10. Will Fitch

    27. Feb, 2009

    Hi Marcio,

    This should be done when the execution of your PHP script is complete. Unless you have persistent connections turned on, a new connection will be opened each time a request comes in.

    If you do not close your connection using mysqli->close(), PHP will automatically close it for you. The purpose behind using the close() method is to return resources to PHP and MySQL faster.

  11. Prem

    01. May, 2009

    Nice tutorial. Thanks for sharing.
    Its good for quick refresh of mysqli class. Very nicely presented.

  12. CBP

    08. May, 2009

    Nice write-up, but looking for part 3 re transactions, please.

  13. Petty

    15. May, 2009

    Thanks for the tutorial. Is there any one can convert this tutorial as a php function.

  14. William Rouse

    15. May, 2009

    Thanks!
    I am also looking for part 2 and 3

  15. NIkki

    09. Jul, 2009

    TY !!!!!!! =O

  16. Aaron

    06. Oct, 2009

    This looks strangely close to day 10 of the screencast “Diving Into PHP” by Jeffrey Way (www.blog.themeforest.net).

    Could be coincidence, but I would hate to see someone stealing another person’s tutorial.

  17. Will Fitch

    06. Oct, 2009

    I don’t think he stole any content. This tutorial is specific to MySQLi and it’s interaction with PHP code. Mine’s over three years old and probably out of date anyway lol.

  18. Kai

    08. Nov, 2009

    Nice mysqli tutorial, it is simple and clear

  19. john

    30. Nov, 2009

    Hi thanks friend it is really very good tutorial .

  20. scott

    11. Dec, 2009

    Thanks, this helped a ton.

  21. PHP and Mysqli Tutorial « ScottsCreations.com

    11. Dec, 2009

    [...] http://www.phpfever.com/mysqli-tutorial.html/comment-page-1#comment-9383 Socialization: [...]

  22. harikrishna polu

    02. Jan, 2010

    this is really amazing …. and also presently we are having some good concepts like prepared statements and callable statements present java JDBC. so we can achieve the java performance in php with simle coding ….
    thank you php and mysql.

  23. MrBrad

    09. Jan, 2010

    I believe you have hit the jackpot on a well-done tutorial on MySQLi – spot on.

  24. Michael

    29. Jan, 2010

    Thats cool

  25. MySQLi Tutorial | Will Fitch's Blog | Drakz Free Online Service

    15. Feb, 2010

    [...] posted here: MySQLi Tutorial | Will Fitch's Blog Share and [...]

  26. How would you find the hostname for database access with yahoo Small Business Domain hosting? | BingSite

    13. May, 2010

    [...] MySQLi Tutorial | Will Fitch's Blog [...]

Leave a Reply