MENU


PHP

PHP talks to MySQL through the mysqli extension ("mysqli" stands for MySQL Improved). Every mysqli function exists in two equivalent forms: a procedural form, and an object-oriented form built on the mysqli and mysqli_result classes. Note that the equality comparison operator in SQL is =, not PHP's ==.

Example: Procedural Style

The example below connects with mysqli_connect(), defines two tables (see Table Definitions and Data Types), populates them (see Data Manipulation), and then retrieves data with a variety of SELECT forms – ordering, set union, inner join, grouped join with HAVING, right join, and a join with USING (see Data Retrieval). A small helper function, tabulate(), prints any result set as an HTML table using mysqli_fetch_fields() for the column names and mysqli_fetch_row() to walk the rows.


ch07-php-procedural.php:
<!DOCTYPE html><html><head>
<style type="text/css">
   table, td {border: 1px solid;}
</style></head><body>
<?php

// ****** 1. Database Connection
$link = mysqli_connect("localhost", "root", "password", "testDB");
if (mysqli_connect_errno()) {
   echo "Connection failed: " . mysqli_connect_error();
   exit();
}

// ****** 2. Table Definition
mysqli_query($link, "DROP TABLE IF EXISTS tbl1, tbl2");

mysqli_query($link, "
CREATE TABLE IF NOT EXISTS tbl1 (
   a TINYINT (5) ZEROFILL,
   b INT UNSIGNED AUTO_INCREMENT UNIQUE,
   c DECIMAL(5,2) PRIMARY KEY,
   d DATETIME DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP,
   e BINARY(3) NOT NULL,
   z TEXT)
   AUTO_INCREMENT 5,
   CHARACTER SET 'latin1'
");
echo mysqli_error($link); // reports any error

mysqli_query($link, "
ALTER TABLE tbl1
   ADD f VARCHAR(5) AFTER e,
   CHANGE z g TEXT
");

mysqli_query($link, "
CREATE TABLE IF NOT EXISTS tbl2 (
   a TINYINT (5) ZEROFILL,
   x BLOB)
");
echo mysqli_error($link); // reports any error

// ****** 3. Data Management
mysqli_query($link, "
INSERT INTO tbl1 VALUES
(1,10,1.23,'2015-06-25 19:30:00','abc','aaaaa','Hello World'),
(2,99,1.1,'2016-04-23 13:30:00','def','bbb','Testing 123')
");

mysqli_query($link, "
REPLACE tbl1 SET
a=3,c=9.9,e='mno',g='Good day'
");

mysqli_query($link, "
UPDATE tbl1 SET
c=8.8 WHERE a=3
");

mysqli_query($link, "
INSERT INTO tbl2 VALUES
(4,'PHP'),
(5,'SQL')
");

// ****** 4. Data Retrieval
$result = mysqli_query($link, "
SELECT * FROM tbl1 ORDER BY a ASC,b DESC");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl2");
tabulate($result);

$result = mysqli_query($link, "
(SELECT a AS c1,g c2 FROM tbl1) UNION
(SELECT * FROM tbl2) ORDER BY c1 DESC");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl1 JOIN tbl2 ON tbl1.a <= 3");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl1 JOIN tbl2 GROUP BY tbl1.a, tbl1.b HAVING SUM(tbl1.a)>3");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl1 T1 RIGHT JOIN tbl2 T2 ON T2.a <= 4");
tabulate($result);

mysqli_query($link, "UPDATE tbl2 SET a=3 WHERE a=4");
$result = mysqli_query($link, "
SELECT * FROM tbl1 JOIN tbl2 USING (a)");
tabulate($result);

// ****** A useful, general-purpose result printer
function tabulate($result){
   $fInfo = mysqli_fetch_fields($result);
   echo "<table><tr>";
   foreach($fInfo as $col){
      echo "<td>".$col->name."</td>";
   }
   while ($row = mysqli_fetch_row($result)){
      echo "</tr><tr>";
      foreach ($row as $val){
         echo "<td>".$val."</td>";
      }
   }
   echo "</tr></table><br/>";
}

?>
</body>
</html>

Example: Object-Oriented Style

The same operations can be written against the mysqli object instead of the procedural functions: a new connection is created with new mysqli(...), and every subsequent call is a method on that object ($S->query(...)) or on the object returned by a query ($result->fetch_row()). The object-oriented example below also demonstrates the HANDLER statement for direct table access (HANDLER tbl OPEN / READ FIRST / READ NEXT / CLOSE), and multi_query(), which runs several semicolon-separated statements in one call – the result sets are then walked one at a time with store_result() and next_result().


ch07-php-oo.php:
<!DOCTYPE html><html><head>
<style type="text/css">
   table, td {border: 1px solid;}
</style></head><body>
<?php
// ****** 1. Database Connection
$S = new mysqli("localhost", "root", "password", "testDB");
if ($S->connect_errno) {
   echo "Connect failed: " . $S->connect_error;
   exit();
}

// ****** 2. Table Definition & Data Management
$S->query("
DROP TABLE IF EXISTS tbl, tmp");

$S->query("
CREATE TABLE tbl (
   a INT,
   b VARCHAR(20))");

$S->query("
CREATE TABLE tmp LIKE tbl");

$S->query("
INSERT INTO tbl VALUES
   (1,'Hello World'),
   (2,'Testing 123'),
   (3,'Good Day!'),
   (4,'How Are You?'),
   (5,'Good Luck!')");

$S->query("
INSERT INTO tmp
SELECT * FROM tbl");

echo $S->error."<br/>";

// ****** 3. Data Retrieval by HANDLER
$S->query("HANDLER tbl OPEN");
tabulate($S->query(
               "HANDLER tbl READ FIRST WHERE a>=2 LIMIT 2"));
tabulate($S->query(
               "HANDLER tbl READ NEXT WHERE a>=2 LIMIT 2"));
$S->query("HANDLER tbl CLOSE");

// ****** 4. Data Retrieval by multi_query()
$S->multi_query("
SELECT * FROM tbl WHERE a>1 AND a<5;
SELECT * FROM tbl WHERE a>2 AND a<4;");
do{
   tabulate($S->store_result());
} while ($S->next_result());

// ****** A useful, general-purpose result printer
function tabulate($result){
   $fInfo = $result->fetch_fields();
   echo "<table><tr>";
   foreach($fInfo as $col){
      echo "<td>".$col->name."</td>";
   }
   while ($row = $result->fetch_row()){
      echo "</tr><tr>";
      foreach ($row as $val){
         echo "<td>".$val."</td>";
      }
   }
   echo "</tr></table><br/>";
}
?>
</body>
</html>

HANDLER tbl OPEN, then READ FIRST WHERE a>=2 LIMIT 2 (table scanned in insertion order):
ab
2Testing 123
3Good Day!
READ NEXT WHERE a>=2 LIMIT 2 (continues from the previous cursor position):
ab
4How Are You?
5Good Luck!
multi_query() first result set, SELECT * FROM tbl WHERE a>1 AND a ab 2Testing 123 3Good Day! 4How Are You? multi_query() second result set, SELECT * FROM tbl WHERE a>2 AND a ab 3Good Day!

Conventions of Functions

In the reference sections below, each function is documented in its object-oriented form, as a method of the mysqli class. With a few exceptions, the procedural form of a given function is obtained by prepending mysqli_ to the method name and adding the connection link as the first parameter. For example:
query($s [,$i=MYSQLI_STORE_RESULT])

Object-oriented form, called as $link->query(...).

mysqli_query($l, $s [,$i=MYSQLI_STORE_RESULT])

Equivalent procedural form – the link $l becomes the first argument.

Likewise, the property $error corresponds to the procedural function mysqli_error($l). Unless otherwise noted, each function returns TRUE on success and FALSE on failure.

Connection Functions

__construct([$s1=host [,$s2=user [,$s3=password [,$s4=db [,$i=port [,$s5=socket]]]]]])

Connects to the MySQL server and selects database $s4. Defaults come from the mysqli.default_* php.ini settings. Nothing is returned; the procedural form mysqli_connect(...) returns the link resource.

$connect_errno gives the error number of the last connection attempt (zero means no error). $connect_error gives the error message as a string, or NULL if there was no error. get_connection_stats() returns an array of connection statistics; stat() returns the server status string.

init() returns a resource for use with real_connect(). real_connect([$s1 [,$s2 [,$s3 [,$s4 [,$i [,$s5 [,$i2]]]]]]]) behaves like __construct(), except that it operates on the object created by init(), accepts an extra flags parameter $i2, and can be combined with options(). The flags $i2 include:
MYSQLI_CLIENT_COMPRESS

Uses a compressed protocol.

MYSQLI_CLIENT_FOUND_ROWS

Returns the number of matched rows instead of the number of affected rows.

MYSQLI_CLIENT_IGNORE_SPACE

Allows spaces after function names; reserves all function names as keywords.

MYSQLI_CLIENT_INTERACTIVE

Uses interactive_timeout (instead of wait_timeout) seconds of inactivity before disconnecting.

MYSQLI_CLIENT_SSL

Uses SSL encryption for the connection.

options($i, $m) sets extra connection options before connecting. $i can be:
MYSQLI_OPT_CONNECT_TIMEOUT

Number of seconds for the connection timeout.

MYSQLI_OPT_LOCAL_INFILE

Enables or disables LOAD LOCAL INFILE.

MYSQLI_INIT_COMMAND

A statement executed as soon as the connection is established.

MYSQLI_READ_DEFAULT_FILE

Reads settings from the given file instead of my.cnf.

MYSQLI_READ_DEFAULT_GROUP

Reads settings from the given group in my.cnf or the file set by MYSQLI_READ_DEFAULT_FILE.

MYSQLI_SERVER_PUBLIC_KEY

Path to the RSA public key file used with SHA-256 based authentication.

ssl_set($s1, $s2, $s3, $s4, $s5) configures an SSL connection: $s1 is the key path, $s2 the certificate path, $s3 the certificate-authority path, $s4 a directory of trusted CA certificates in PEM format, and $s5 the list of allowable SSL ciphers. ping() pings the server and attempts to reconnect if the connection was lost. change_user($s1, $s2, $s3) switches to user $s1 with password $s2 and database $s3. select_db($s) selects $s as the current database. close() closes the connection.


ch07-php-real-connect.php:
<?php
$mysqli = mysqli_init();
if (!$mysqli) die('mysqli_init failed');
$mysqli->options(MYSQLI_INIT_COMMAND,
                            'SET AUTOCOMMIT = 0');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
if (!$mysqli->real_connect('localhost', 'root', 'pw', 'testDB')) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
             . mysqli_connect_error());
}
echo $mysqli->stat()."\n";
print_r($mysqli->get_connection_stats());
$mysqli->close();
?>

// Sample output:
// Uptime: 435665 Threads: 3 Questions: 2624 Slow queries: 0 Opens: 527 Flush tables: 1
// Open tables: 104 Queries per second avg: 0.006
// Array
// (
//     [bytes_sent] => 142
//     [bytes_received] => 104
//     [packets_sent] => 4
//     [packets_received] => 3
//     ...
// )

Query Functions

query($s [,$i=MYSQLI_STORE_RESULT]) runs statement $s. If $i=MYSQLI_USE_RESULT, every subsequent call fails with "Commands out of sync" until free_result() is called. It returns a mysqli_result object on success, or FALSE on failure. For non-DML statements (anything other than INSERT, UPDATE, or DELETE – see Data Manipulation) it behaves like real_query($s) followed by store_result() or use_result().

escape_string($s) escapes NUL, \n, \r, \, ', ", and Control-Z so that $s can be embedded safely in an SQL statement – using it consistently is one of the defenses discussed in Injection Attacks.

refresh($i) refreshes caches or tables, or resets replication server information; $i can be one of MYSQLI_REFRESH_{LOG|TABLES|HOSTS|STATUS|THREADS|SLAVE|MASTER}.

$affected_rows gives the number of rows affected by the previous INSERT, UPDATE, REPLACE, or DELETE. $field_count gives the number of columns in the last query's result. $insert_id gives the AUTO_INCREMENT value generated by the previous query, or 0 if there was none.

$info returns a string describing the last query:
INSERT INTO

Records: 50 Duplicates: 0 Warnings: 0

LOAD DATA INFILE

Records: 10 Duplicates: 0 Warnings: 0

ALTER TABLE

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

UPDATE

Rows matched: 30 Changed: 30 Warnings: 0

multi_query($s) runs several statements in $s, separated by ;. The first result set is obtained with use_result() or store_result(); the remaining ones are stepped through with more_results() and next_result().

$errno gives the error code of the last call (zero means no error). $error gives a description of the last error, or an empty string. $error_list gives an array of errors from the last executed command. $sqlstate gives the SQLSTATE string of the last operation ('00000' means no error). get_warnings() retrieves the result of SHOW WARNINGS. $warning_count gives the number of warnings raised by the most recent query.

Information Functions

get_charset() returns the connection's default character set as an object. character_set_name() returns it as a string. set_charset($s) changes the default character set.


ch07-php-charset.php:
<?php
$link = mysqli_connect("localhost", "root", "pwd", "testDB");
echo mysqli_character_set_name($link)."\n";
print_r(mysqli_get_charset($link));
mysqli_set_charset($link, 'utf8');
?>

utf8 stdClass Object ( [charset] => utf8 [collation] => utf8_general_ci [dir] => [min_length] => 1 [max_length] => 3 [number] => 33 [state] => 1 [comment] => UTF-8 Unicode )
set_local_infile_handler($func) registers a callback invoked for LOAD DATA LOCAL INFILE, letting application code stream and transform the source data before it is loaded. set_local_infile_default() removes a handler previously set with set_local_infile_handler().


ch07-php-load-local-infile.php:
<?php
$db = mysqli_init();
$db->real_connect("localhost", "root", "pwd", "test");

function loadMe($stream, &$buffer, $buflen, &$errmsg){
   $buffer = fgets($stream);
   echo $buffer;
   $buffer = strtoupper(str_replace(",", "\t", $buffer));
   return strlen($buffer);
}

$db->set_local_infile_handler("loadMe");
$db->query(
           "LOAD DATA LOCAL INFILE 'input.txt' INTO TABLE t1");
$db->set_local_infile_default();
?>
$client_info is a string describing the client library. $client_version is the client library version as an integer. $server_info is a string describing the server. $server_version is the server version as an integer. $host_info describes the type of connection in use. $protocol_version is the MySQL protocol version in use. For the procedural form of these six properties, prepend mysqli_get_ rather than mysqli_; for example, the procedural form of $protocol_version is mysqli_get_proto_info($l).


ch07-php-client-info.php:
<?php
$S = new mysqli("localhost", "root", "password", "testDB");
echo $S->client_info."\n";
echo $S->client_version."\n";
echo $S->server_info."\n";
echo $S->server_version."\n";
echo $S->host_info."\n";
echo $S->protocol_version."\n";
?>

mysqlnd 5.0.11-dev - 20120503 - $Id: 40933630edef551dfaca71298a83fad8d03d62d4 $ 50011 5.6.16-log 50616 localhost via TCP/IP 10

The mysqli_result Class

Every successful query returns a mysqli_result object. With a few exceptions, the procedural form of each method is obtained by prepending mysqli_ to the method name and passing the result object as the first parameter – for example, the procedural form of fetch_row() is mysqli_fetch_row($result).

fetch_row() returns the next row as a numeric array. fetch_assoc() returns the next row as an associative array. fetch_array([$i = MYSQLI_BOTH]) returns the next row as an associative array, a numeric array, or both, depending on whether $i is MYSQLI_BOTH, MYSQLI_NUM, or MYSQLI_ASSOC. fetch_object([$s1 [, $s2]]) returns the current row as an object of class $s1 (default stdClass), optionally passing constructor arguments $s2. fetch_all([$i = MYSQLI_NUM]) fetches every remaining row as an array of numeric or associative arrays. data_seek($i) moves the row pointer to offset $i.

fetch_field() returns the next field's metadata as an object. fetch_fields() returns an array of all field metadata objects. $current_field gives the current field offset (procedural form: mysqli_field_tell($result)). field_seek($i) moves the field cursor to offset $i.

$num_rows gives the number of rows in the result. $field_count gives the number of fields (procedural form: mysqli_num_fields($result)). $lengths returns an array of the byte lengths of each column in the current row (procedural form: mysqli_fetch_lengths($result)). free(), close(), or free_result() releases the memory held by a result (procedural form: mysqli_free_result($result)).


ch07-php-mysqli-result.php:
<!DOCTYPE html><html><head></head><body><pre>
<?php
// S for SQL
$S = new mysqli("localhost", "root", "password", "testDB");

$S->query("
DROP TABLE IF EXISTS tbl");

$S->query("
CREATE TABLE tbl (
   a INT,
   b VARCHAR(20))");

$S->query("
INSERT INTO tbl VALUES
   (0,'Hello World'),
   (1,'Testing 123'),
   (2,'Good Day!'),
   (3,'How Are You?'),
   (4,'Good Luck!')");

// R for result
$R = $S->query("SELECT * FROM tbl");

$row = $R->fetch_row();
echo $row[0]."|".$row[1]."<br/>";

$row = $R->fetch_assoc();
echo $row['a']."|".$row['b']."<br/>";

$row = $R->fetch_array();
echo $row[0]."|".$row['b']."<br/>";

class C{
   public $c=5;
}
$row = $R->fetch_object("C");
echo $row->a."|".$row->b."|".$row->c."<br/>";

$R->data_seek(3);
$row = $R->fetch_all();
print_r($row);

while ($field = $R->fetch_field()){
   echo "\n".$R->current_field."|";
   print_r($field);
}

$R->free();
?>
</pre></body></html>

Query result of SELECT * FROM tbl:
ab
0Hello World
1Testing 123
2Good Day!
3How Are You?
4Good Luck!
Script output, walking that result with each fetch method in turn: 0|Hello World 1|Testing 123 2|Good Day! 3|How Are You?|5 Array ( [0] => Array ( [0] => 3 [1] => How Are You? ) [1] => Array ( [0] => 4 [1] => Good Luck! ) ) 1|stdClass Object ( [name] => a [orgname] => a [table] => tbl [orgtable] => tbl [def] => [db] => test [catalog] => def [max_length] => 12 [length] => 11 [charsetnr] => 63 [flags] => 32768 [type] => 3 [decimals] => 0 ) 2|stdClass Object ( [name] => b [orgname] => b [table] => tbl [orgtable] => tbl [def] => [db] => test [catalog] => def [max_length] => 0 [length] => 60 [charsetnr] => 33 [flags] => 0 [type] => 253 [decimals] => 0 )