Questions tagged [mysqli]

The mysqli PHP extension is the successor to the mysql PHP extension. It provides a low-level mapping to MySQL's C interface. The extension's principle features consist of:

• An object-oriented interface
• Support for prepared statements
• Support for multiple statements
• Support for transactions
• Enhanced debugging support
• Embedded server support

Deprecation of the mysql extension

The mysql extension was deprecated in PHP version 5.5, and removed in version 7.0. Code written for modern servers must use the mysqli or pdo extensions instead.

In addition to an object-oriented interface, most mysqli features also provide an equivalent procedural interface through functions prefixed mysqli_. However, these functions were primarily intended for users transitioning away from legacy code using the mysql extension. Code in a modern environment is expected to use object-oriented programming.

The mysqli extension's prepared statement support makes use of ? placeholders bound to variable references for input, and variable references bound to columns when fetching output rows. Please note that, in order to use some aspects of mysqli prepared statements (most notably mysqli_stmt_get_result), your installation of PHP must use the Mysql Native Driver (mysqlnd), which also provides improved performance over the older MySQL Client Library.

• PHP documentation on the MySQLi class

A simple MySQLi SELECT query example:

The following example retrieves 2 output columns from a SELECT query using both an integer and a string parameter.

// A form post has supplied the input values in:
// $_POST['fruit']
// $_POST['age']

// Enable mysqli error reporting. Errors will be reported as exceptions
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Open a new connection to the MySQL server.
$mysqli = new mysqli("host", "user", "password", "database");

// Set the correct connection charset
$mysqli->set_charset('utf8mb4');

// SQL string with input placeholders
$SQL = "SELECT firstName, email FROM users WHERE favorite_fruit = ? AND age > ?";

// Prepare the statement
$stmt = $mysqli->prepare($SQL);

// Bind parameters:
// "s" indicates a string parameter (favorite_fruit)
// "i" indicates an integer parameter (age)
$stmt->bind_param("si", $_POST['fruit'], $_POST['age']);

// Execute the statement
$stmt->execute();

// Bind result variables to fetch the columns returned:
// Supply one variable for each column. Variables are bound by reference
$stmt->bind_result($firstName, $email);

// Fetch rows:
// On each loop iteration, the variables $firstName, $email will be 
// populated with values from the currently fetched row.
while ($stmt->fetch()) {
    echo "Name: $firstName, Email: $email\n";
}

// Close the prepared statement (optional)
$stmt->close();

Related tags

• mysql
• pdo
• php