Database API

by Gisle Hannemyr

This chapter ....

Introduction
Final word

Introduction

The MySQLi (MySQL improved) extension to PHP allows you to access the functionality provided by SQL servers MySQL and MariaDB..

It first appeared in MySQL version 4.1.13.

The MySQLi extension features a dual interface: It supports the procedural and object-oriented (OO) programming paradigm.

Users migrating from the old mysql extension may prefer the procedural interface. The procedural interface is similar to that of the old MySQL extension. In many cases, the function names differ only by prefix. Some MySQLi functions take a connection handle as their first argument, whereas matching functions in the old MySQL interface take it as an optional last argument.

Functions

Below are usage notes for some much used functions.

The plan is to provide examples and notes for both paradigms. Curently, it is very incomplete.

Connection

Set up a persistent connection to the database.

mysqli_connect()

Procedural:

$conn = mysqli_connect("example.com", "user", "password", "database");
$result = mysqli_query($conn, "SELECT * FROM table");
$row = mysqli_fetch_row($result);
echo $row[0];

OO:

$conn = new mysqli("example.com", "user", "password", "database");
$result = $conn->query("SELECT * FROM table");
$row = $result->fetch_row();
echo $row[0];

Fetching results from SELECT

Below are som econstruct to fetch result from SELECT queries using LEFT JOIN.

mysqli_fetch_assoc()

If the data are fetched as an associtative array, use aliases to distinguish between columns with the same names:

$sql =
"SELECT t1.column AS column1, t2.column AS column2
  FROM table1 AS t1
  LEFT JOIN table2 AS t2
  ON t1.column = t2.column;"

$result = mysqli_query($conn, $sql);

// Iterate over associative array
while ($row = mysqli_fetch_assoc($res)) {
  printf ("col1: %s, col2: (%s)" . PHP_EOL, $row['column1'], $row['column2']);
} # while

mysqli_fetch_row()

If the data are fetched as an enumerated array, refer to them by number. No aliases are needed.

$sql =
"SELECT t1.column, t2.column
  FROM table1 AS t1
  LEFT JOIN table2 AS t2
  ON t1.column = t2.column;"

$result = mysqli_query($conn, $sql);

// Iterate over enumerated array
while ($row = mysqli_fetch_arow($res)) {
  printf ("col1: %s, col2: (%s)" . PHP_EOL, $row[0], $row[1]);
} # while

Final word

[TBA]

Last update: 2021-03-11 [gh].