#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include "mysql_example.h" /* Pour MYSQL_HOST, MYSQL_USER, MYSQL_PASS */

int main(int argv, char *argc[])
{
    MYSQL_RES *results = NULL; MYSQL_ROW row; MYSQL *db = NULL;

    db = (MYSQL *)malloc(sizeof(MYSQL));
    mysql_init(db);
    mysql_options(db, MYSQL_INIT_COMMAND, "SET NAMES UTF8;");
    if(!mysql_real_connect(db, MYSQL_HOST, MYSQL_USER, MYSQL_PASS, NULL, 0, NULL, 0)) {
        fprintf(stderr, "Failed to connect to host %s (%s)", MYSQL_HOST, mysql_error(db));
        exit(EXIT_FAILURE);
    }

    mysql_set_character_set(db, "utf8");
    mysql_select_db(db, "my_database");
    mysql_query(db , "SELECT user_id AS id FROM users WHERE user_description='member' LIMIT 10000");
    results = mysql_use_result(db);

    while(row = mysql_fetch_row(results)) {
        printf("%s\n", row[0]);
    }

    mysql_free_result(results);
    exit(EXIT_SUCCESS);
}

<?php
/* ext/mysql */
$db = mysql_connect(/* params */);
mysql_query("SELECT 'foo'");

/* ext/mysqli */
$db = mysqli_connect(/* params */);
mysqli_query($db, "SELECT 'foo'");

<?php
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

/* Exactement équivalent à */
$pdo->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 0);

/* Contre sens, mais c'est comme ça : on doit passer MYSQL_ATTR_DIRECT_QUERY à 0 pour désactiver
   l'émulation, et non pas à 1 comme la logique sémantique le voudrait */

<?php
$stmt = $pdo->prepare("SELECT user_id FROM users LIMIT :limit");
$stmt->bindValue('limit', 10);
$stmt->execute();

$result = $stmt->fetch();
var_dump($result);

/*
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' 
*/

<?php
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0); /* Désactive le parseur interne à PDO et la couche d'émulation */
$stmt = $pdo->prepare("SELECT user_id FROM users LIMIT :limit"); /* Ici, un vrai prepare() sera envoyé au SGBD */
$stmt->bindValue('limit', 10);
$stmt->execute();

$result = $stmt->fetch();
var_dump($result);
/*
array(4) {
  ["user_id"]=>
  string(7) "18"
  [0]=>
  string(7) "18"
}
*/

<?php
$stmt = $pdo->prepare("SELECT user_id FROM users LIMIT :limit");
$stmt->bindValue('limit', 10, PDO::PARAM_INT); /* Précise à la couche d'émulation qu'il s'agit d'un entier (SQL) */
$stmt->execute();

$result = $stmt->fetch();
var_dump($result);
/*
array(4) {
  ["user_id"]=>
  string(7) "18"
  [0]=>
  string(7) "18"
}
*/

<?php
$db = mysqli_connect('myserver', 'myuser', 'secret', 'unknown_database');
/* PHP Warning:  mysqli_connect(): (42000/1049): Unknown database 'unknown_database' */

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $db = mysqli_connect('myserver', 'myuser', 'secret', 'unknown_database');
} catch (mysqli_sql_exception $e) {
    exit($e->getMessage());
}
try {
    mysqli_query($db, "SELECT foo FROM bar");
    /* ... */
} catch(mysqli_sql_exception $e) { }

<?php
mysqli_report(MYSQLI_REPORT_INDEX);
$db = mysqli_connect('myserver', 'myuser', 'secret', 'my_database');

mysqli_query($db, "SELECT photo FROM Users WHERE source !='' LIMIT 1000");

/* PHP Warning:  mysqli_query(): (00000/0): No index used in query/prepared statement ... */

<?php
$mysqli = mysqli_connect(/*...*/);

/* Requête et transfert immédiat de tous les résultats coté PHP */
$result = mysqli_query($mysqli, "SELECT id, nom, email FROM membres");

<?php
$ligne1 = mysqli_fetch_row($result);
mysqli_data_seek($result, mysqli_num_rows($result)); /* Déplaçons nous sur le dernier résultat */
$derniere_ligne = mysqli_fetch_row($result);

<?php
mysqli_free_result($result);

<?php
$mysqli = mysqli_connect(/*...*/);

/* Requête et transfert partiels des résultats coté PHP, il en reste coté MySQL */
$result = mysqli_query($mysqli, "SELECT id, nom, email FROM membres", MYSQLI_USE_RESULT);

$ligne1 = mysqli_fetch_row($result); /* Appel réseau éventuel pour récupérer le résultat */

/* Ceci mène à une erreur, on ne peut se déplacer dans un jeu de résultats non bufferisé
   car tout ou partie des résultats sont encore coté MySQL */
mysqli_data_seek($result, mysqli_num_rows($result));

/* Ceci mène à une erreur, on ne peut envoyer une autre requête tant qu'un jeu de résultats
   occupe de la place coté MySQL, il faut le libérer d'abord */
$result2 = mysqli_query($mysqli, "SELECT id, nom, email FROM membres", MYSQLI_USE_RESULT);

<?php
$m = mysqli_connect(/* params */);
$q = mysqli_query($m, 'SELECT * FROM Users LIMIT 1');
while($r = mysqli_fetch_row($q)) {
    /* do something */
}
mysqli_free_result($r);

<?php
$m = mysqli_connect(/* params */);
$ps = mysqli_prepare($m, 'SELECT * FROM Users LIMIT 1');
mysqli_stmt_execute($ps);
while(mysqli_stmt_fetch($ps)) {
    /* on aurait du lier les colonnes de résultats... */
}
mysqli_stmt_close($ps);

<?php
$m = mysqli_connect(/* params */);
$ps = mysqli_prepare($m, 'SELECT id FROM Users LIMIT 10'); /* la colonne 'id 'est de type INTEGER */
mysqli_stmt_execute($ps);
mysqli_stmt_bind_result($ps, $id); /* Lions le résultat sur $id */
while(mysqli_stmt_fetch($ps)) {
    var_dump($id);
}
/*
int(62)
int(64)
int(65)
int(66)
int(67)
int(68)
int(69)
int(70)
int(71)
int(72)
*/

<?php
$m = mysqli_connect(/* */);
$q = mysqli_query($m, 'SELECT id FROM users LIMIT 10';

while($r = mysqli_fetch_row($q)) {
    var_dump($r[0]);
}
/*
string(2) "62"
string(2) "64"
string(2) "65"
string(2) "66"
string(2) "67"
string(2) "68"
string(2) "69"
string(2) "70"
string(2) "71"
string(2) "72"
*/

<?php
$m = mysqli_connect(/* */);
mysqli_options($m, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true); /* uniquement disponible avec mysqlnd */
$q = mysqli_query($m, 'SELECT id FROM users LIMIT 10');

while($r = mysqli_fetch_row($q)) {
    var_dump($r[0]);
}
/*
int(62)
int(64)
int(65)
int(66)
int(67)
int(68)
int(69)
int(70)
int(71)
int(72)
*/

<?php
$m = mysqli_connect(/* params */);
$ps = mysqli_prepare($m, 'SELECT id, nom FROM Users LIMIT 1000');
mysqli_stmt_execute($ps);
mysqli_stmt_bind_result($ps, $id, $nom);

/* Récupère tous les résultats coté PHP (bufferise) */
mysqli_stmt_store_result($ps);

while(mysqli_stmt_fetch($ps)) {
    /* do something with $id and $nom */
}
mysqli_stmt_close($ps);

$m = mysqli_connect(/* params */);
$ps = mysqli_prepare($m, 'SELECT id, nom FROM Users LIMIT 1000');
mysqli_stmt_execute($ps);

/* Transforme la réponse en jeu de résultats mysqli_result */
$r = mysqli_stmt_get_result($ps);

while($result = mysqli_fetch_row($r)) {
    /* do something */
}
mysqli_free_result($r);
mysqli_stmt_close($ps);

<?php
$db  = mysqli_connect(/* */);
$result = mysqli_query($db, "SELECT very_huge_blob_column FROM foobar");

while($results[] = mysqli_fetch_row($result)) { }
mysqli_free_result($result); /* Notez que souvent cette étape est oubliée, c'est encore pire! */
foreach($results as $foo) { /* do something */ }

<?php
function memory_usage()
{
    $pid = getmypid();
    $r = explode(':',shell_exec("grep VmData /proc/$pid/status"));
    return '->'.trim($r[1])."\n";
}
$db = mysqli_connect(/* */);

echo "initial memory " . memory_usage();
$result = mysqli_query($db,"SELECT very_huge_blob_column FROM foobar");
echo "resultSet stored " . memory_usage();
while($results[] = mysqli_fetch_row($result)) { }
echo "query result saved " . memory_usage();
mysqli_free_result($result);
echo "resultSet freed " . memory_usage();
unset($results);
echo "saved result freed " . memory_usage();
unset($db);
echo "Db closed " . memory_usage();

>ZEND_MM_MEM_TYPE=mmap_anon php53libmysql/bin/php -dmemory_limit=-1 poc_mysqli.php
initial memory ->3348 kB
resultSet stored ->72724 kB
query result saved ->149012 kB
resultSet freed ->81156 kB
saved result freed ->25348 kB
Db closed ->24260 kB

99.92% (257,473,815B) (heap allocation functions) malloc/new/new[], --alloc-fns, etc.
->52.90% (136,314,880B) 0x69A01E: _zend_mm_alloc_int (zend_alloc.c:1908)
| ->52.60% (135,528,448B) 0x69A1DE: _estrndup (zend_alloc.c:2503)
| | ->52.60% (135,528,448B) 0x533BCE: php_mysqli_fetch_into_hash (mysqli.c:1191)
| |   ->52.60% (135,528,448B) 0x53F2E1: zif_mysqli_fetch_row (mysqli_nonapi.c:352)
| |     ->52.60% (135,528,448B) 0x70186A: zend_do_fcall_common_helper_SPEC (zend_vm_execute.h:320)
| |       ->52.60% (135,528,448B) 0x6D9D96: execute (zend_vm_execute.h:107)
| |         ->52.60% (135,528,448B) 0x6B4B98: zend_execute_scripts (zend.c:1236)
| |           ->52.60% (135,528,448B) 0x663D0C: php_execute_script (main.c:2308)
| |             ->52.60% (135,528,448B) 0x73BCDC: main (php_cli.c:1184)
| |               
| ->00.31% (786,432B) in 1+ places, all below ms_print's threshold (01.00%)
| 
->45.85% (118,130,675B) 0x52DD010: my_malloc (my_malloc.c:37)
| ->45.84% (118,112,344B) 0x52E0583: alloc_root (my_alloc.c:219)
| | ->45.83% (118,096,024B) 0x5307A40: cli_read_rows (client.c:1418)
| | | ->45.83% (118,096,024B) 0x5305955: mysql_store_result (client.c:2957)
| | |   ->45.83% (118,096,024B) 0x53EF09: zif_mysqli_query (mysqli_nonapi.c:540)
| | |     ->45.83% (118,096,024B) 0x70186A: zend_do_fcall_common_helper_SPEC (zend_vm_execute.h:320)
| | |       ->45.83% (118,096,024B) 0x6D9D96: execute (zend_vm_execute.h:107)
| | |         ->45.83% (118,096,024B) 0x6B4B98: zend_execute_scripts (zend.c:1236)
| | |           ->45.83% (118,096,024B) 0x663D0C: php_execute_script (main.c:2308)
| | |             ->45.83% (118,096,024B) 0x73BCDC: main (php_cli.c:1184)

<?php
$mysql = mysqli_connect(/* */);
$result = mysqli_query($mysql, "SELECT data FROM foobar");

while($results[] = mysqli_fetch_assoc($result)) { }
$results['data'] = 'foobarbaz';

while($results2[] = mysqli_fetch_array($result)) { }
echo $results2['data']; /* N'affiche PAS foobarbaz, mais la donnée originale */

/*
$result et $result2 contiennent presque les mêmes données, cependant, la mémoire
est totalement dupliquée entre les 2, malgré l'énorme quantité de données identiques */

>ZEND_MM_MEM_TYPE=mmap_anon php53mysqlnd/bin/php -dmemory_limit=-1 poc_mysqli.php
initial memory ->3208 kB
resultSet stored ->70452 kB
query result saved ->71220 kB
resultSet freed ->81148 kB
saved result freed ->19196 kB
Db closed ->19196 kB

<?php
$db = mysqli_connect(/* */);

$result = mysqli_query($db,"SELECT user_id, email FROM users LIMIT 5");
mysqli_data_seek($result, 5);
$data = mysqli_fetch_row($result);
do_something($data);
mysqli_free_result($result);
var_dump(mysqli_get_connection_stats($db));

/*
["buffered_sets"]=>
  string(1) "1"
["rows_fetched_from_server_normal"]=>
  string(1) "5"
["rows_buffered_from_client_normal"]=>
  string(1) "5"
["rows_fetched_from_client_normal_buffered"]=>
  string(1) "1"
["connect_success"]=>
  string(1) "1"
["connect_failure"]=>
  string(1) "0"
["connection_reused"]=>
  string(1) "0"
["reconnect"]=>
  string(1) "0"
["active_connections"]=>
  string(1) "1"
*/

<?php
class JPMysqli extends Mysqli
{
    public function __destruct()
    {
        $stats = $this->get_connection_stats();
        $this->close();
        if($diff = $stats["rows_fetched_from_server_normal"] - ($stats["rows_fetched_from_client_normal_unbuffered"] + $stats["rows_fetched_from_client_normal_buffered"])) {
            trigger_error("You didn't use *$diff* selected results", E_USER_NOTICE);
        }
    }
}

<?php
$db = new JPMysqli(/* */);

$result = mysqli_query($db,"SELECT user_id, email FROM users LIMIT 5");
mysqli_data_seek($result, 5);
$data = mysqli_fetch_row($result);
do_something($data);
exit();
/*
Notice : "You didn't use *4* selected results"
*/