php MySQLi: MySQL Improved Extension
Blogs20142014-02-24
There are three main API options when considering connecting to a MySQL database server:
- PHP’s MySQL Extension
- PHP’s mysqli Extension
- PHP Data Objects (PDO)
Each has its own advantages and disadvantages. By default we use ‘PHP MySQL extension’. Here focus on the second - MySQLi extension: The mysqli extension is built using the PHP extension framework, its source code is located in the directory ext/mysqli.
According to wiki, MySQLi is an improved version of the older PHP MySQL driver, offering various benefits. The MySQLi extension provides various benefits with respect to its predecessor, the most prominent of which are:
- An object-oriented interface
- Support for prepared statements
- Support for multiple statements
- Support for transactions
- Enhanced debugging support
- Embedded server support
The following is a quick example from php.net/mysqli’s document page:
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") "
. $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!($res = $stmt->get_result())) {
echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;
}
var_dump($res->fetch_all());
?>