MySQLi Object-Oriented Programming

PHP Modules Tutorials - Herong's Tutorial Examples

∟MySQLi Object-Oriented Programming

This section provides a tutorial example on how to use MySQLi module in project-oriented programming style.

MySQLi is designed to support both procedual and object-oriented programming (OOP) paradigms. With procedual paradigm, all functionalities are accessible by calling mysqli_* functions as shown in previous tutorials.

With object-oriented programming paradigm, MySQLi functionalities are grouped into 2 main class objects: "mysqli" and "mysqli_result".

"mysqli" class object - Represents a connection to a MySQL server. A "mysqli" object can be created by calling the "mysqli" class constructor, which is equivalent to the mysqli_connect() function.

$con = new mysqli($host, $user, $pass, $base, $port);

All functionalities that operates on a given connection are provides as instance methods or properties on the "mysqli" object. For example:

# procedual functions
  $res = mysqli_query($con, $sql);
  $error = mysqli_error($con);
  $num = mysqli_affected_rows($con);
  $id = mysqli_insert_id($con);
  mysqli_close($con);

# object-oriented methods
  $res = $con->query($sql);
  $error = $con->error;
  $num = $con->affected_rows;
  $id = $con->insert_id;
  $con->close();

"mysqli_result" class object - Represents a result set returned by a SELECT query.

$res = $con->query($sql);

All functionalities that operates on a given result set are provides as instance methods on the "mysqli_result" object. For example:

# procedual functions
  $row = mysqli_fetch_array($res);
  $num = mysqli_num_rows($res); 
  $fields = mysqli_fetch_fields($res);
  mysqli_free_result($res);

# object-oriented methods
  $row = $res->fetch_array();
  $num = $res->num_rows; 
  $fields = $res->fetch_fields();
  $res->free();

To show you how to use the MySQLi module in object-oriented programming style, I have converted the mysqli-query.php script by replacing procedual functions with object instance methods and properties:

<?php
#  mysqli-query-oop.php
#- Copyright 2009-2026 (c) HerongYang.com. All Rights Reserved.

  $con = new mysqli("127.0.0.1", "herong", "TopSecret");

  # drop an existing database 
  $res = mysqli_query_err($con, "DROP DATABASE MyBase");

  # create a new database 
  $res = mysqli_query_err($con, "CREATE DATABASE MyBase");

  # set the current database 
  $res = mysqli_query_err($con, "USE MyBase");

  # create a new table
  $res = mysqli_query_err($con, 
    "CREATE TABLE MyTable ".
    "(ID INTEGER AUTO_INCREMENT, Value INTEGER, PRIMARY KEY (ID))");

  # query the table structure 
  $res = mysqli_query_err($con, "DESC MyTable");

  # display the header row 
  $row = $res->fetch_fields();
  foreach ($row as $val) {
    print(str_pad($val->name, 10));
  }
  print("\n");

  # display result rows
  while ($row = $res->fetch_array(MYSQLI_NUM)) {
    foreach ($row as $val) {
      print(str_pad($val, 10));
    }
    print("\n");
  }
  $res->free();

  $con->close();

function mysqli_query_err($con, $sql) {
  $res = $con->query($sql);
  if (!$res) {
    $errno = $con->errno;
    $error = $con->error;
    print("Query failed: $errno - $error\n");
  }
  return $res;
}
?>

If you run the script, you will get the same output as mysqli-query.php:

herong$ php mysqli-query-oop.php 

Field     Type      Null      Key       Default   Extra     
ID        int(11)   NO        PRI                 auto_increment
Value     int(11)   YES