reference/mysqli/quickstart.xml
c2088ac3e8ffe9d7b316f0b9a5bf7c3d9eeae5c0
...
...
@@ -36,22 +36,21 @@
36
36
<![CDATA[
37
37
<?php
38
38
$mysqli = mysqli_connect("example.com", "user", "password", "database");
39
-
$res = mysqli_query($mysqli, "SELECT 'Please, do not use ' AS _msg FROM DUAL");
40
-
$row = mysqli_fetch_assoc($res);
39
+
$result = mysqli_query($mysqli, "SELECT 'Please do not use the deprecated mysql extension for new development. ' AS _msg FROM DUAL");
40
+
$row = mysqli_fetch_assoc($result);
41
41
echo $row['_msg'];
42
42

43
43
$mysql = mysql_connect("example.com", "user", "password");
44
44
mysql_select_db("test");
45
-
$res = mysql_query("SELECT 'the mysql extension for new developments.' AS _msg FROM DUAL", $mysql);
46
-
$row = mysql_fetch_assoc($res);
45
+
$result = mysql_query("SELECT 'Use the mysqli extension instead.' AS _msg FROM DUAL", $mysql);
46
+
$row = mysql_fetch_assoc($result);
47
47
echo $row['_msg'];
48
-
?>
49
48
]]>
50
49
</programlisting>
51
50
&example.outputs;
52
51
<screen>
53
52
<![CDATA[
54
-
Please, do not use the mysql extension for new developments.
53
+
Please do not use the deprecated mysql extension for new development. Use the mysqli extension instead.
55
54
]]>
56
55
</screen>
57
56
</example>
...
...
@@ -76,24 +75,18 @@ Please, do not use the mysql extension for new developments.
76
75
<programlisting role="php">
77
76
<![CDATA[
78
77
<?php
78
+

79
79
$mysqli = mysqli_connect("example.com", "user", "password", "database");
80
-
if (mysqli_connect_errno()) {
81
-
echo "Failed to connect to MySQL: " . mysqli_connect_error();
82
-
}
83
80

84
-
$res = mysqli_query($mysqli, "SELECT 'A world full of ' AS _msg FROM DUAL");
85
-
$row = mysqli_fetch_assoc($res);
81
+
$result = mysqli_query($mysqli, "SELECT 'A world full of ' AS _msg FROM DUAL");
82
+
$row = mysqli_fetch_assoc($result);
86
83
echo $row['_msg'];
87
84

88
85
$mysqli = new mysqli("example.com", "user", "password", "database");
89
-
if ($mysqli->connect_errno) {
90
-
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
91
-
}
92
86

93
-
$res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL");
94
-
$row = $res->fetch_assoc();
87
+
$result = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL");
88
+
$row = $result->fetch_assoc();
95
89
echo $row['_msg'];
96
-
?>
97
90
]]>
98
91
</programlisting>
99
92
&example.outputs;
...
...
@@ -105,7 +98,7 @@ A world full of choices to please everybody.
105
98
</example>
106
99
</para>
107
100
<para>
108
-
The object oriented interface is used for the quickstart because the
101
+
The object-oriented interface is used for the quickstart because the
109
102
reference section is organized that way.
110
103
</para>
111
104
<para>
...
...
@@ -121,20 +114,14 @@ A world full of choices to please everybody.
121
114
<programlisting role="php">
122
115
<![CDATA[
123
116
<?php
117
+

124
118
$mysqli = new mysqli("example.com", "user", "password", "database");
125
-
if ($mysqli->connect_errno) {
126
-
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
127
-
}
128
119

129
-
$res = mysqli_query($mysqli, "SELECT 'Possible but bad style.' AS _msg FROM DUAL");
130
-
if (!$res) {
131
-
echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error;
132
-
}
120
+
$result = mysqli_query($mysqli, "SELECT 'Possible but bad style.' AS _msg FROM DUAL");
133
121

134
-
if ($row = $res->fetch_assoc()) {
122
+
if ($row = $result->fetch_assoc()) {
135
123
echo $row['_msg'];
136
124
}
137
-
?>
138
125
]]>
139
126
</programlisting>
140
127
&example.outputs;
...
...
@@ -171,9 +158,9 @@ Possible but bad style.
171
158
</para>
172
159
<para>
173
160
The hostname <literal>localhost</literal> has a special meaning.
174
-
It is bound to the use of Unix domain sockets. It is not possible
175
-
to open a TCP/IP connection using the hostname <literal>localhost</literal>
176
-
you must use <literal>127.0.0.1</literal> instead.
161
+
It is bound to the use of Unix domain sockets.
162
+
To open a TCP/IP connection to the localhost, <literal>127.0.0.1</literal> must be used
163
+
instead of the hostname <literal>localhost</literal>.
177
164
</para>
178
165
<para>
179
166
<example>
...
...
@@ -181,19 +168,14 @@ Possible but bad style.
181
168
<programlisting role="php">
182
169
<![CDATA[
183
170
<?php
171
+

184
172
$mysqli = new mysqli("localhost", "user", "password", "database");
185
-
if ($mysqli->connect_errno) {
186
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
187
-
}
173
+

188
174
echo $mysqli->host_info . "\n";
189
175

190
176
$mysqli = new mysqli("127.0.0.1", "user", "password", "database", 3306);
191
-
if ($mysqli->connect_errno) {
192
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
193
-
}
194
177

195
178
echo $mysqli->host_info . "\n";
196
-
?>
197
179
]]>
198
180
</programlisting>
199
181
&example.outputs;
...
...
@@ -270,9 +252,9 @@ mysqli.default_socket=/tmp/mysql.sock
270
252
<para>
271
253
For setting a connection option, the connect operation has to be
272
254
performed in three steps: creating a connection handle with
273
-
<function>mysqli_init</function>, setting the requested options using
274
-
<function>mysqli_options</function>, and establishing the network
275
-
connection with <function>mysqli_real_connect</function>.
255
+
<function>mysqli_init</function> or <methodname>mysqli::__construct</methodname>,
256
+
setting the requested options using <methodname>mysqli::options</methodname>,
257
+
and establishing the network connection with <methodname>mysqli::real_connect</methodname>.
276
258
</para>
277
259
<para>
278
260
<emphasis role="bold">Connection pooling</emphasis>
...
...
@@ -296,14 +278,14 @@ mysqli.default_socket=/tmp/mysql.sock
296
278
<emphasis role="bold">Persistent connection</emphasis>
297
279
</para>
298
280
<para>
299
-
If a unused persistent connection for a given combination of host, username,
300
-
password, socket, port and default database can not be found in the connection pool,
281
+
If an unused persistent connection for a given combination of host, username,
282
+
password, socket, port and default database cannot be found in the connection pool,
301
283
then mysqli opens a new connection. The use of persistent connections can be
302
284
enabled and disabled using the PHP directive <link linkend="ini.mysqli.allow-persistent">mysqli.allow_persistent</link>.
303
285
The total number of connections opened by a script can be limited with
304
286
<link linkend="ini.mysqli.max-links">mysqli.max_links</link>. The maximum number of persistent connections
305
287
per PHP process can be restricted with <link linkend="ini.mysqli.max-persistent">mysqli.max_persistent</link>.
306
-
Please note, that the web server may spawn many PHP processes.
288
+
Please note that the web server may spawn many PHP processes.
307
289
</para>
308
290
<para>
309
291
A common complain about persistent connections is that their state is
...
...
@@ -318,12 +300,12 @@ mysqli.default_socket=/tmp/mysql.sock
318
300
The mysqli extension supports both interpretations of a persistent connection:
319
301
state persisted, and state reset before reuse. The default is reset.
320
302
Before a persistent connection is reused, the mysqli extension implicitly
321
-
calls <function>mysqli_change_user</function> to reset the state. The
303
+
calls <methodname>mysqli::change_user</methodname> to reset the state. The
322
304
persistent connection appears to the user as if it was just opened. No
323
305
artifacts from previous usages are visible.
324
306
</para>
325
307
<para>
326
-
The <function>mysqli_change_user</function> function is an expensive operation.
308
+
The <methodname>mysqli::change_user</methodname> call is an expensive operation.
327
309
For best performance, users may want to recompile the extension with the
328
310
compile flag <constant>MYSQLI_NO_CHANGE_USER_ON_PCONNECT</constant> being set.
329
311
</para>
...
...
@@ -338,7 +320,7 @@ mysqli.default_socket=/tmp/mysql.sock
338
320
<para>
339
321
<simplelist>
340
322
<member><methodname>mysqli::__construct</methodname></member>
341
-
<member><methodname>mysqli::init</methodname></member>
323
+
<member><function>mysqli_init</function></member>
342
324
<member><methodname>mysqli::options</methodname></member>
343
325
<member><methodname>mysqli::real_connect</methodname></member>
344
326
<member><methodname>mysqli::change_user</methodname></member>
...
...
@@ -353,32 +335,27 @@ mysqli.default_socket=/tmp/mysql.sock
353
335
<title>Executing statements</title>
354
336
<para>
355
337
Statements can be executed with the
356
-
<function>mysqli_query</function>, <function>mysqli_real_query</function>
357
-
and <function>mysqli_multi_query</function> functions.
358
-
The <function>mysqli_query</function> function is the most
338
+
<methodname>mysqli::query</methodname>, <methodname>mysqli::real_query</methodname>
339
+
and <methodname>mysqli::multi_query</methodname>.
340
+
The <methodname>mysqli::query</methodname> function is the most
359
341
common, and combines the executing statement with a
360
342
buffered fetch of its result set, if any, in one call.
361
-
Calling <function>mysqli_query</function> is identical to
362
-
calling <function>mysqli_real_query</function>
363
-
followed by <function>mysqli_store_result</function>.
343
+
Calling <methodname>mysqli::query</methodname> is identical to
344
+
calling <methodname>mysqli::real_query</methodname>
345
+
followed by <methodname>mysqli::store_result</methodname>.
364
346
</para>
365
347
<para>
366
348
<example>
367
-
<title>Connecting to MySQL</title>
349
+
<title>Executing queries</title>
368
350
<programlisting role="php">
369
351
<![CDATA[
370
352
<?php
353
+

354
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
371
355
$mysqli = new mysqli("example.com", "user", "password", "database");
372
-
if ($mysqli->connect_errno) {
373
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
374
-
}
375
356

376
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
377
-
!$mysqli->query("CREATE TABLE test(id INT)") ||
378
-
!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
379
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
380
-
}
381
-
?>
357
+
$mysqli->query("DROP TABLE IF EXISTS test");
358
+
$mysqli->query("CREATE TABLE test(id INT)");
382
359
]]>
383
360
</programlisting>
384
361
</example>
...
...
@@ -387,12 +364,12 @@ if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
387
364
<emphasis role="bold">Buffered result sets</emphasis>
388
365
</para>
389
366
<para>
390
-
After statement execution results can be retrieved at once to be buffered
391
-
by the client or by read row by row. Client-side result set buffering
392
-
allows the server to free resources associated with the statement
367
+
After statement execution, results can be either retrieved all at once
368
+
or read row by row from the server. Client-side result set buffering
369
+
allows the server to free resources associated with the statement's
393
370
results as early as possible. Generally speaking, clients are slow
394
371
consuming result sets. Therefore, it is recommended to use buffered
395
-
result sets. <function>mysqli_query</function> combines statement
372
+
result sets. <methodname>mysqli::query</methodname> combines statement
396
373
execution and result set buffering.
397
374
</para>
398
375
<para>
...
...
@@ -407,32 +384,27 @@ if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
407
384
<programlisting role="php">
408
385
<![CDATA[
409
386
<?php
387
+

388
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
410
389
$mysqli = new mysqli("example.com", "user", "password", "database");
411
-
if ($mysqli->connect_errno) {
412
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
413
-
}
414
390

415
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
416
-
!$mysqli->query("CREATE TABLE test(id INT)") ||
417
-
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
418
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
419
-
}
391
+
$mysqli->query("DROP TABLE IF EXISTS test");
392
+
$mysqli->query("CREATE TABLE test(id INT)");
393
+
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
420
394

421
-
$res = $mysqli->query("SELECT id FROM test ORDER BY id ASC");
395
+
$result = $mysqli->query("SELECT id FROM test ORDER BY id ASC");
422
396

423
397
echo "Reverse order...\n";
424
-
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
425
-
$res->data_seek($row_no);
426
-
$row = $res->fetch_assoc();
398
+
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
399
+
$result->data_seek($row_no);
400
+
$row = $result->fetch_assoc();
427
401
echo " id = " . $row['id'] . "\n";
428
402
}
429
403

430
404
echo "Result set order...\n";
431
-
$res->data_seek(0);
432
-
while ($row = $res->fetch_assoc()) {
405
+
foreach ($result as $row) {
433
406
echo " id = " . $row['id'] . "\n";
434
407
}
435
-
?>
436
408
]]>
437
409
</programlisting>
438
410
&example.outputs;
...
...
@@ -465,14 +437,14 @@ Result set order...
465
437
<programlisting role="php">
466
438
<![CDATA[
467
439
<?php
440
+

468
441
$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");
469
-
$res = $mysqli->use_result();
442
+
$result = $mysqli->use_result();
470
443

471
444
echo "Result set order...\n";
472
-
while ($row = $res->fetch_assoc()) {
445
+
foreach ($result as $row) {
473
446
echo " id = " . $row['id'] . "\n";
474
447
}
475
-
?>
476
448
]]>
477
449
</programlisting>
478
450
</example>
...
...
@@ -481,9 +453,9 @@ while ($row = $res->fetch_assoc()) {
481
453
<emphasis role="bold">Result set values data types</emphasis>
482
454
</para>
483
455
<para>
484
-
The <function>mysqli_query</function>, <function>mysqli_real_query</function>
485
-
and <function>mysqli_multi_query</function> functions are used to execute
486
-
non-prepared statements. At the level of the MySQL Client Server Protocol,
456
+
The <methodname>mysqli::query</methodname>, <methodname>mysqli::real_query</methodname>
457
+
and <methodname>mysqli::multi_query</methodname> functions are used to execute
458
+
non-prepared statements. At the level of the MySQL Client Server Protocol,
487
459
the command <literal>COM_QUERY</literal> and the text protocol are used
488
460
for statement execution. With the text protocol, the MySQL server converts
489
461
all data of a result sets into strings before sending. This conversion is done
...
...
@@ -498,23 +470,19 @@ while ($row = $res->fetch_assoc()) {
498
470
<programlisting role="php">
499
471
<![CDATA[
500
472
<?php
473
+

474
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
501
475
$mysqli = new mysqli("example.com", "user", "password", "database");
502
-
if ($mysqli->connect_errno) {
503
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
504
-
}
505
476

506
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
507
-
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
508
-
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
509
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
510
-
}
477
+
$mysqli->query("DROP TABLE IF EXISTS test");
478
+
$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))");
479
+
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')");
511
480

512
-
$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
513
-
$row = $res->fetch_assoc();
481
+
$result = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
482
+
$row = $result->fetch_assoc();
514
483

515
484
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
516
485
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
517
-
?>
518
486
]]>
519
487
</programlisting>
520
488
&example.outputs;
...
...
@@ -540,26 +508,22 @@ label = a (string)
540
508
<programlisting role="php">
541
509
<![CDATA[
542
510
<?php
543
-
$mysqli = mysqli_init();
511
+

512
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
513
+

514
+
$mysqli = new mysqli();
544
515
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
545
516
$mysqli->real_connect("example.com", "user", "password", "database");
546
517

547
-
if ($mysqli->connect_errno) {
548
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
549
-
}
550
-

551
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
552
-
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
553
-
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
554
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
555
-
}
518
+
$mysqli->query("DROP TABLE IF EXISTS test");
519
+
$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))");
520
+
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')");
556
521

557
-
$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
558
-
$row = $res->fetch_assoc();
522
+
$result = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
523
+
$row = $result->fetch_assoc();
559
524

560
525
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
561
526
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
562
-
?>
563
527
]]>
564
528
</programlisting>
565
529
&example.outputs;
...
...
@@ -577,14 +541,12 @@ label = a (string)
577
541
<para>
578
542
<simplelist>
579
543
<member><methodname>mysqli::__construct</methodname></member>
580
-
<member><methodname>mysqli::init</methodname></member>
581
544
<member><methodname>mysqli::options</methodname></member>
582
545
<member><methodname>mysqli::real_connect</methodname></member>
583
546
<member><methodname>mysqli::query</methodname></member>
584
547
<member><methodname>mysqli::multi_query</methodname></member>
585
548
<member><methodname>mysqli::use_result</methodname></member>
586
549
<member><methodname>mysqli::store_result</methodname></member>
587
-
<member><methodname>mysqli_result::free</methodname></member>
588
550
</simplelist>
589
551
</para>
590
552
</section>
...
...
@@ -594,7 +556,7 @@ label = a (string)
594
556
<para>
595
557
The MySQL database supports prepared statements. A prepared statement
596
558
or a parameterized statement is used to execute the same statement
597
-
repeatedly with high efficiency.
559
+
repeatedly with high efficiency and protect against SQL injections.
598
560
</para>
599
561
<para>
600
562
<emphasis role="bold">Basic workflow</emphasis>
...
...
@@ -610,52 +572,33 @@ label = a (string)
610
572
with <literal>?</literal>.
611
573
</para>
612
574
<para>
575
+
Prepare is followed by execute. During execute the client binds
576
+
parameter values and sends them to the server. The server executes
577
+
the statement with the bound values using the previously created internal resources.
578
+
</para>
579
+
<para>
613
580
<example>
614
-
<title>First stage: prepare</title>
581
+
<title>Prepared statement</title>
615
582
<programlisting role="php">
616
583
<![CDATA[
617
584
<?php
585
+

586
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
618
587
$mysqli = new mysqli("example.com", "user", "password", "database");
619
-
if ($mysqli->connect_errno) {
620
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
621
-
}
622
588

623
589
/* Non-prepared statement */
624
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
625
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
626
-
}
590
+
$mysqli->query("DROP TABLE IF EXISTS test");
591
+
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
627
592

628
593
/* Prepared statement, stage 1: prepare */
629
-
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
630
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
631
-
}
632
-
?>
633
-
]]>
634
-
</programlisting>
635
-
</example>
636
-
</para>
637
-
<para>
638
-
Prepare is followed by execute. During execute the client binds
639
-
parameter values and sends them to the server. The server creates a
640
-
statement from the statement template and the bound values to
641
-
execute it using the previously created internal resources.
642
-
</para>
643
-
<para>
644
-
<example>
645
-
<title>Second stage: bind and execute</title>
646
-
<programlisting role="php">
647
-
<![CDATA[
648
-
<?php
594
+
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
595
+

649
596
/* Prepared statement, stage 2: bind and execute */
650
597
$id = 1;
651
-
if (!$stmt->bind_param("i", $id)) {
652
-
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
653
-
}
598
+
$label = 'PHP';
599
+
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string
654
600

655
-
if (!$stmt->execute()) {
656
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
657
-
}
658
-
?>
601
+
$stmt->execute();
659
602
]]>
660
603
</programlisting>
661
604
</example>
...
...
@@ -675,70 +618,57 @@ if (!$stmt->execute()) {
675
618
<programlisting role="php">
676
619
<![CDATA[
677
620
<?php
621
+

622
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
678
623
$mysqli = new mysqli("example.com", "user", "password", "database");
679
-
if ($mysqli->connect_errno) {
680
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
681
-
}
682
624

683
625
/* Non-prepared statement */
684
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
685
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
686
-
}
626
+
$mysqli->query("DROP TABLE IF EXISTS test");
627
+
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
687
628

688
629
/* Prepared statement, stage 1: prepare */
689
-
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
690
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
691
-
}
630
+
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
692
631

693
632
/* Prepared statement, stage 2: bind and execute */
694
-
$id = 1;
695
-
if (!$stmt->bind_param("i", $id)) {
696
-
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
697
-
}
698
-

699
-
if (!$stmt->execute()) {
700
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
701
-
}
633
+
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string
702
634

703
-
/* Prepared statement: repeated execution, only data transferred from client to server */
704
-
for ($id = 2; $id < 5; $id++) {
705
-
if (!$stmt->execute()) {
706
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
707
-
}
635
+
$data = [
636
+
1 => 'PHP',
637
+
2 => 'Java',
638
+
3 => 'C++'
639
+
];
640
+
foreach ($data as $id => $label) {
641
+
$stmt->execute();
708
642
}
709
643

710
-
/* explicit close recommended */
711
-
$stmt->close();
712
-

713
-
/* Non-prepared statement */
714
-
$res = $mysqli->query("SELECT id FROM test");
715
-
var_dump($res->fetch_all());
716
-
?>
644
+
$result = $mysqli->query('SELECT id, label FROM test');
645
+
var_dump($result->fetch_all(MYSQLI_ASSOC));
717
646
]]>
718
647
</programlisting>
719
648
&example.outputs;
720
649
<screen>
721
650
<![CDATA[
722
-
array(4) {
651
+
array(3) {
723
652
[0]=>
724
-
array(1) {
725
-
[0]=>
653
+
array(2) {
654
+
["id"]=>
726
655
string(1) "1"
656
+
["label"]=>
657
+
string(3) "PHP"
727
658
}
728
659
[1]=>
729
-
array(1) {
730
-
[0]=>
660
+
array(2) {
661
+
["id"]=>
731
662
string(1) "2"
663
+
["label"]=>
664
+
string(4) "Java"
732
665
}
733
666
[2]=>
734
-
array(1) {
735
-
[0]=>
667
+
array(2) {
668
+
["id"]=>
736
669
string(1) "3"
737
-
}
738
-
[3]=>
739
-
array(1) {
740
-
[0]=>
741
-
string(1) "4"
670
+
["label"]=>
671
+
string(3) "C++"
742
672
}
743
673
}
744
674
]]>
...
...
@@ -760,7 +690,7 @@ array(4) {
760
690
</para>
761
691
<para>
762
692
Also, consider the use of the MySQL multi-INSERT SQL syntax for INSERTs.
763
-
For the example, multi-INSERT requires less round-trips between
693
+
For the example, multi-INSERT requires fewer round-trips between
764
694
the server and client than the prepared statement shown above.
765
695
</para>
766
696
<para>
...
...
@@ -769,10 +699,18 @@ array(4) {
769
699
<programlisting role="php">
770
700
<![CDATA[
771
701
<?php
772
-
if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {
773
-
echo "Multi-INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
774
-
}
775
-
?>
702
+

703
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
704
+
$mysqli = new mysqli("example.com", "user", "password", "database");
705
+

706
+
$mysqli->query("DROP TABLE IF EXISTS test");
707
+
$mysqli->query("CREATE TABLE test(id INT)");
708
+

709
+
$values = [1, 2, 3, 4];
710
+

711
+
$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
712
+
$stmt->bind_param('iiii', ...$values);
713
+
$stmt->execute();
776
714
]]>
777
715
</programlisting>
778
716
</example>
...
...
@@ -785,8 +723,7 @@ if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {
785
723
for prepared statements and non-prepared statements. Prepared statements
786
724
are using the so called binary protocol. The MySQL server sends result
787
725
set data "as is" in binary format. Results are not serialized into
788
-
strings before sending. The client libraries do not receive strings only.
789
-
Instead, they will receive binary data and try to convert the values into
726
+
strings before sending. Client libraries receive binary data and try to convert the values into
790
727
appropriate PHP data types. For example, results from an SQL
791
728
<literal>INT</literal> column will be provided as PHP integer variables.
792
729
</para>
...
...
@@ -796,32 +733,29 @@ if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {
796
733
<programlisting role="php">
797
734
<![CDATA[
798
735
<?php
736
+

737
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
799
738
$mysqli = new mysqli("example.com", "user", "password", "database");
800
-
if ($mysqli->connect_errno) {
801
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
802
-
}
803
739

804
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
805
-
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
806
-
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
807
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
808
-
}
740
+
/* Non-prepared statement */
741
+
$mysqli->query("DROP TABLE IF EXISTS test");
742
+
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
743
+
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
809
744

810
745
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
811
746
$stmt->execute();
812
-
$res = $stmt->get_result();
813
-
$row = $res->fetch_assoc();
747
+
$result = $stmt->get_result();
748
+
$row = $result->fetch_assoc();
814
749

815
750
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
816
751
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
817
-
?>
818
752
]]>
819
753
</programlisting>
820
754
&example.outputs;
821
755
<screen>
822
756
<![CDATA[
823
757
id = 1 (integer)
824
-
label = a (string)
758
+
label = PHP (string)
825
759
]]>
826
760
</screen>
827
761
</example>
...
...
@@ -849,41 +783,29 @@ label = a (string)
849
783
<programlisting role="php">
850
784
<![CDATA[
851
785
<?php
852
-
$mysqli = new mysqli("example.com", "user", "password", "database");
853
-
if ($mysqli->connect_errno) {
854
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
855
-
}
856
786

857
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
858
-
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
859
-
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
860
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
861
-
}
787
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
788
+
$mysqli = new mysqli("example.com", "user", "password", "database");
862
789

863
-
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {
864
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
865
-
}
790
+
/* Non-prepared statement */
791
+
$mysqli->query("DROP TABLE IF EXISTS test");
792
+
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
793
+
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
866
794

867
-
if (!$stmt->execute()) {
868
-
echo "Execute failed: (" . $mysqli->errno . ") " . $mysqli->error;
869
-
}
795
+
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
796
+
$stmt->execute();
870
797

871
-
$out_id = NULL;
872
-
$out_label = NULL;
873
-
if (!$stmt->bind_result($out_id, $out_label)) {
874
-
echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;
875
-
}
798
+
$stmt->bind_result($out_id, $out_label);
876
799

877
800
while ($stmt->fetch()) {
878
801
printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
879
802
}
880
-
?>
881
803
]]>
882
804
</programlisting>
883
805
&example.outputs;
884
806
<screen>
885
807
<![CDATA[
886
-
id = 1 (integer), label = a (string)
808
+
id = 1 (integer), label = PHP (string)
887
809
]]>
888
810
</screen>
889
811
</example>
...
...
@@ -899,14 +821,14 @@ id = 1 (integer), label = a (string)
899
821
</para>
900
822
<para>
901
823
It is also possible to buffer the results of a prepared statement
902
-
using <function>mysqli_stmt_store_result</function>.
824
+
using <methodname>mysqli_stmt::store_result</methodname>.
903
825
</para>
904
826
<para>
905
827
<emphasis role="bold">Fetching results using mysqli_result interface</emphasis>
906
828
</para>
907
829
<para>
908
830
Instead of using bound results, results can also be retrieved through the
909
-
mysqli_result interface. <function>mysqli_stmt_get_result</function>
831
+
mysqli_result interface. <methodname>mysqli_stmt::get_result</methodname>
910
832
returns a buffered result set.
911
833
</para>
912
834
<para>
...
...
@@ -915,31 +837,21 @@ id = 1 (integer), label = a (string)
915
837
<programlisting role="php">
916
838
<![CDATA[
917
839
<?php
918
-
$mysqli = new mysqli("example.com", "user", "password", "database");
919
-
if ($mysqli->connect_errno) {
920
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
921
-
}
922
840

923
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
924
-
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
925
-
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
926
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
927
-
}
841
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
842
+
$mysqli = new mysqli("example.com", "user", "password", "database");
928
843

929
-
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC"))) {
930
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
931
-
}
844
+
/* Non-prepared statement */
845
+
$mysqli->query("DROP TABLE IF EXISTS test");
846
+
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
847
+
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
932
848

933
-
if (!$stmt->execute()) {
934
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
935
-
}
849
+
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
850
+
$stmt->execute();
936
851

937
-
if (!($res = $stmt->get_result())) {
938
-
echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;
939
-
}
852
+
$result = $stmt->get_result();
940
853

941
-
var_dump($res->fetch_all());
942
-
?>
854
+
var_dump($result->fetch_all(MYSQLI_ASSOC));
943
855
]]>
944
856
</programlisting>
945
857
&example.outputs;
...
...
@@ -948,10 +860,10 @@ var_dump($res->fetch_all());
948
860
array(1) {
949
861
[0]=>
950
862
array(2) {
951
-
[0]=>
863
+
["id"]=>
952
864
int(1)
953
-
[1]=>
954
-
string(1) "a"
865
+
["label"]=>
866
+
string(3) "PHP"
955
867
}
956
868
}
957
869
]]>
...
...
@@ -959,7 +871,7 @@ array(1) {
959
871
</example>
960
872
</para>
961
873
<para>
962
-
Using the <classname>mysqli_result interface</classname> offers the additional benefit of
874
+
Using the <classname>mysqli_result</classname> interface offers the additional benefit of
963
875
flexible client-side result set navigation.
964
876
</para>
965
877
<para>
...
...
@@ -968,35 +880,24 @@ array(1) {
968
880
<programlisting role="php">
969
881
<![CDATA[
970
882
<?php
971
-
$mysqli = new mysqli("example.com", "user", "password", "database");
972
-
if ($mysqli->connect_errno) {
973
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
974
-
}
975
883

976
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
977
-
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
978
-
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')")) {
979
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
980
-
}
884
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
885
+
$mysqli = new mysqli("example.com", "user", "password", "database");
981
886

982
-
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {
983
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
984
-
}
887
+
/* Non-prepared statement */
888
+
$mysqli->query("DROP TABLE IF EXISTS test");
889
+
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
890
+
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");
985
891

986
-
if (!$stmt->execute()) {
987
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
988
-
}
892
+
$stmt = $mysqli->prepare("SELECT id, label FROM test");
893
+
$stmt->execute();
989
894

990
-
if (!($res = $stmt->get_result())) {
991
-
echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;
992
-
}
895
+
$result = $stmt->get_result();
993
896

994
-
for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {
995
-
$res->data_seek($row_no);
996
-
var_dump($res->fetch_assoc());
897
+
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
898
+
$result->data_seek($row_no);
899
+
var_dump($result->fetch_assoc());
997
900
}
998
-
$res->close();
999
-
?>
1000
901
]]>
1001
902
</programlisting>
1002
903
&example.outputs;
...
...
@@ -1006,19 +907,19 @@ array(2) {
1006
907
["id"]=>
1007
908
int(3)
1008
909
["label"]=>
1009
-
string(1) "c"
910
+
string(3) "C++"
1010
911
}
1011
912
array(2) {
1012
913
["id"]=>
1013
914
int(2)
1014
915
["label"]=>
1015
-
string(1) "b"
916
+
string(4) "Java"
1016
917
}
1017
918
array(2) {
1018
919
["id"]=>
1019
920
int(1)
1020
921
["label"]=>
1021
-
string(1) "a"
922
+
string(3) "PHP"
1022
923
}
1023
924
]]>
1024
925
</screen>
...
...
@@ -1034,11 +935,11 @@ array(2) {
1034
935
need to be escaped as they are never substituted into the query string
1035
936
directly. A hint must be provided to the server for the type of bound
1036
937
variable, to create an appropriate conversion.
1037
-
See the <function>mysqli_stmt_bind_param</function> function for more
938
+
See the <methodname>mysqli_stmt::bind_param</methodname> function for more
1038
939
information.
1039
940
</para>
1040
941
<para>
1041
-
Such a separation sometimes considered as the only security feature to
942
+
Such a separation is sometimes considered the only security feature to
1042
943
prevent SQL injection, but the same degree of security can be achieved with
1043
944
non-prepared statements, if all the values are formatted correctly. It should
1044
945
be noted that correct formatting is not the same as escaping and involves
...
...
@@ -1052,92 +953,6 @@ array(2) {
1052
953
The API does not include emulation for client-side prepared statement emulation.
1053
954
</para>
1054
955
<para>
1055
-
<emphasis role="bold">Quick prepared - non-prepared statement comparison</emphasis>
1056
-
</para>
1057
-
<para>
1058
-
The table below compares server-side prepared and non-prepared statements.
1059
-
</para>
1060
-
<table xml:id="mysqli.quickstart.prepared.comparison">
1061
-
<title>Comparison of prepared and non-prepared statements</title>
1062
-
<tgroup cols="3">
1063
-
<thead>
1064
-
<row>
1065
-
<entry></entry>
1066
-
<entry>Prepared Statement</entry>
1067
-
<entry>Non-prepared statement</entry>
1068
-
</row>
1069
-
</thead>
1070
-
<tbody>
1071
-
<row>
1072
-
<entry>Client-server round trips, SELECT, single execution</entry>
1073
-
<entry>2</entry>
1074
-
<entry>1</entry>
1075
-
</row>
1076
-
<row>
1077
-
<entry>Statement string transferred from client to server</entry>
1078
-
<entry>1</entry>
1079
-
<entry>1</entry>
1080
-
</row>
1081
-
<row>
1082
-
<entry>Client-server round trips, SELECT, repeated (n) execution</entry>
1083
-
<entry>1 + n</entry>
1084
-
<entry>n</entry>
1085
-
</row>
1086
-
<row>
1087
-
<entry>Statement string transferred from client to server</entry>
1088
-
<entry>1 template, n times bound parameter, if any</entry>
1089
-
<entry>n times together with parameter, if any</entry>
1090
-
</row>
1091
-
<row>
1092
-
<entry>Input parameter binding API</entry>
1093
-
<entry>Yes, automatic input escaping</entry>
1094
-
<entry>No, manual input escaping</entry>
1095
-
</row>
1096
-
<row>
1097
-
<entry>Output variable binding API</entry>
1098
-
<entry>Yes</entry>
1099
-
<entry>No</entry>
1100
-
</row>
1101
-
<row>
1102
-
<entry>Supports use of mysqli_result API</entry>
1103
-
<entry>Yes, use <function>mysqli_stmt_get_result</function></entry>
1104
-
<entry>Yes</entry>
1105
-
</row>
1106
-
<row>
1107
-
<entry>Buffered result sets</entry>
1108
-
<entry>
1109
-
Yes, use <function>mysqli_stmt_get_result</function> or
1110
-
binding with <function>mysqli_stmt_store_result</function>
1111
-
</entry>
1112
-
<entry>Yes, default of <function>mysqli_query</function></entry>
1113
-
</row>
1114
-
<row>
1115
-
<entry>Unbuffered result sets</entry>
1116
-
<entry>Yes, use output binding API</entry>
1117
-
<entry>
1118
-
Yes, use <function>mysqli_real_query</function> with
1119
-
<function>mysqli_use_result</function>
1120
-
</entry>
1121
-
</row>
1122
-
<row>
1123
-
<entry>MySQL Client Server protocol data transfer flavor</entry>
1124
-
<entry>Binary protocol</entry>
1125
-
<entry>Text protocol</entry>
1126
-
</row>
1127
-
<row>
1128
-
<entry>Result set values SQL data types</entry>
1129
-
<entry>Preserved when fetching</entry>
1130
-
<entry>Converted to string or preserved when fetching</entry>
1131
-
</row>
1132
-
<row>
1133
-
<entry>Supports all SQL statements</entry>
1134
-
<entry>Recent MySQL versions support most but not all</entry>
1135
-
<entry>Yes</entry>
1136
-
</row>
1137
-
</tbody>
1138
-
</tgroup>
1139
-
</table>
1140
-
<para>
1141
956
<emphasis role="bold">See also</emphasis>
1142
957
</para>
1143
958
<para>
...
...
@@ -1183,30 +998,21 @@ array(2) {
1183
998
<programlisting role="php">
1184
999
<![CDATA[
1185
1000
<?php
1001
+

1002
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1186
1003
$mysqli = new mysqli("example.com", "user", "password", "database");
1187
-
if ($mysqli->connect_errno) {
1188
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
1189
-
}
1190
1004

1191
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
1192
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1193
-
}
1005
+
$mysqli->query("DROP TABLE IF EXISTS test");
1006
+
$mysqli->query("CREATE TABLE test(id INT)");
1194
1007

1195
-
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
1196
-
!$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;")) {
1197
-
echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1198
-
}
1008
+
$mysqli->query("DROP PROCEDURE IF EXISTS p");
1009
+
$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;");
1199
1010

1200
-
if (!$mysqli->query("CALL p(1)")) {
1201
-
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
1202
-
}
1011
+
$mysqli->query("CALL p(1)");
1203
1012

1204
-
if (!($res = $mysqli->query("SELECT id FROM test"))) {
1205
-
echo "SELECT failed: (" . $mysqli->errno . ") " . $mysqli->error;
1206
-
}
1013
+
$result = $mysqli->query("SELECT id FROM test");
1207
1014

1208
-
var_dump($res->fetch_assoc());
1209
-
?>
1015
+
var_dump($result->fetch_assoc());
1210
1016
]]>
1211
1017
</programlisting>
1212
1018
&example.outputs;
...
...
@@ -1233,28 +1039,20 @@ array(1) {
1233
1039
<programlisting role="php">
1234
1040
<![CDATA[
1235
1041
<?php
1236
-
$mysqli = new mysqli("example.com", "user", "password", "database");
1237
-
if ($mysqli->connect_errno) {
1238
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
1239
-
}
1240
1042

1241
-
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
1242
-
!$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;')) {
1243
-
echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1244
-
}
1043
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1044
+
$mysqli = new mysqli("example.com", "user", "password", "database");
1245
1045

1046
+
$mysqli->query("DROP PROCEDURE IF EXISTS p");
1047
+
$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;');
1246
1048

1247
-
if (!$mysqli->query("SET @msg = ''") || !$mysqli->query("CALL p(@msg)")) {
1248
-
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
1249
-
}
1049
+
$mysqli->query("SET @msg = ''");
1050
+
$mysqli->query("CALL p(@msg)");
1250
1051

1251
-
if (!($res = $mysqli->query("SELECT @msg as _p_out"))) {
1252
-
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
1253
-
}
1052
+
$result = $mysqli->query("SELECT @msg as _p_out");
1254
1053

1255
-
$row = $res->fetch_assoc();
1054
+
$row = $result->fetch_assoc();
1256
1055
echo $row['_p_out'];
1257
-
?>
1258
1056
]]>
1259
1057
</programlisting>
1260
1058
&example.outputs;
...
...
@@ -1276,16 +1074,16 @@ Hi!
1276
1074
</para>
1277
1075
<para>
1278
1076
Stored procedures can return result sets. Result sets returned from a
1279
-
stored procedure cannot be fetched correctly using <function>mysqli_query</function>.
1280
-
The <function>mysqli_query</function> function combines statement execution
1077
+
stored procedure cannot be fetched correctly using <methodname>mysqli::query</methodname>.
1078
+
The <methodname>mysqli::query</methodname> function combines statement execution
1281
1079
and fetching the first result set into a buffered result set, if any.
1282
1080
However, there are additional stored procedure result sets hidden
1283
-
from the user which cause <function>mysqli_query</function> to fail
1081
+
from the user which cause <methodname>mysqli::query</methodname> to fail
1284
1082
returning the user expected result sets.
1285
1083
</para>
1286
1084
<para>
1287
1085
Result sets returned from a stored procedure are fetched using
1288
-
<function>mysqli_real_query</function> or <function>mysqli_multi_query</function>.
1086
+
<methodname>mysqli::real_query</methodname> or <methodname>mysqli::multi_query</methodname>.
1289
1087
Both functions allow fetching any number of result sets returned by a
1290
1088
statement, such as <literal>CALL</literal>. Failing to fetch all
1291
1089
result sets returned by a stored procedure causes an error.
...
...
@@ -1296,38 +1094,26 @@ Hi!
1296
1094
<programlisting role="php">
1297
1095
<![CDATA[
1298
1096
<?php
1097
+

1098
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1299
1099
$mysqli = new mysqli("example.com", "user", "password", "database");
1300
-
if ($mysqli->connect_errno) {
1301
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
1302
-
}
1303
1100

1304
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
1305
-
!$mysqli->query("CREATE TABLE test(id INT)") ||
1306
-
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
1307
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1308
-
}
1101
+
$mysqli->query("DROP TABLE IF EXISTS test");
1102
+
$mysqli->query("CREATE TABLE test(id INT)");
1103
+
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
1309
1104

1310
-
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
1311
-
!$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {
1312
-
echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1313
-
}
1105
+
$mysqli->query("DROP PROCEDURE IF EXISTS p");
1106
+
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
1314
1107

1315
-
if (!$mysqli->multi_query("CALL p()")) {
1316
-
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
1317
-
}
1108
+
$mysqli->multi_query("CALL p()");
1318
1109

1319
1110
do {
1320
-
if ($res = $mysqli->store_result()) {
1111
+
if ($result = $mysqli->store_result()) {
1321
1112
printf("---\n");
1322
-
var_dump($res->fetch_all());
1323
-
$res->free();
1324
-
} else {
1325
-
if ($mysqli->errno) {
1326
-
echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
1327
-
}
1113
+
var_dump($result->fetch_all());
1114
+
$result->free();
1328
1115
}
1329
-
} while ($mysqli->more_results() && $mysqli->next_result());
1330
-
?>
1116
+
} while ($mysqli->next_result());
1331
1117
]]>
1332
1118
</programlisting>
1333
1119
&example.outputs;
...
...
@@ -1389,44 +1175,71 @@ array(3) {
1389
1175
<programlisting role="php">
1390
1176
<![CDATA[
1391
1177
<?php
1178
+

1179
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1392
1180
$mysqli = new mysqli("example.com", "user", "password", "database");
1393
-
if ($mysqli->connect_errno) {
1394
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
1395
-
}
1396
1181

1397
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
1398
-
!$mysqli->query("CREATE TABLE test(id INT)") ||
1399
-
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
1400
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1401
-
}
1182
+
$mysqli->query("DROP TABLE IF EXISTS test");
1183
+
$mysqli->query("CREATE TABLE test(id INT)");
1184
+
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
1402
1185

1403
-
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
1404
-
!$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {
1405
-
echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1406
-
}
1186
+
$mysqli->query("DROP PROCEDURE IF EXISTS p");
1187
+
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
1407
1188

1408
-
if (!($stmt = $mysqli->prepare("CALL p()"))) {
1409
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
1410
-
}
1189
+
$stmt = $mysqli->prepare("CALL p()");
1411
1190

1412
-
if (!$stmt->execute()) {
1413
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
1414
-
}
1191
+
$stmt->execute();
1415
1192

1416
1193
do {
1417
-
if ($res = $stmt->get_result()) {
1194
+
if ($result = $stmt->get_result()) {
1418
1195
printf("---\n");
1419
-
var_dump(mysqli_fetch_all($res));
1420
-
mysqli_free_result($res);
1421
-
} else {
1422
-
if ($stmt->errno) {
1423
-
echo "Store failed: (" . $stmt->errno . ") " . $stmt->error;
1424
-
}
1196
+
var_dump($result->fetch_all());
1197
+
$result->free();
1425
1198
}
1426
-
} while ($stmt->more_results() && $stmt->next_result());
1427
-
?>
1199
+
} while ($stmt->next_result());
1428
1200
]]>
1429
1201
</programlisting>
1202
+
&example.outputs;
1203
+
<screen>
1204
+
<![CDATA[
1205
+
---
1206
+
array(3) {
1207
+
[0]=>
1208
+
array(1) {
1209
+
[0]=>
1210
+
int(1)
1211
+
}
1212
+
[1]=>
1213
+
array(1) {
1214
+
[0]=>
1215
+
int(2)
1216
+
}
1217
+
[2]=>
1218
+
array(1) {
1219
+
[0]=>
1220
+
int(3)
1221
+
}
1222
+
}
1223
+
---
1224
+
array(3) {
1225
+
[0]=>
1226
+
array(1) {
1227
+
[0]=>
1228
+
int(2)
1229
+
}
1230
+
[1]=>
1231
+
array(1) {
1232
+
[0]=>
1233
+
int(3)
1234
+
}
1235
+
[2]=>
1236
+
array(1) {
1237
+
[0]=>
1238
+
int(4)
1239
+
}
1240
+
}
1241
+
]]>
1242
+
</screen>
1430
1243
</example>
1431
1244
</para>
1432
1245
<para>
...
...
@@ -1438,28 +1251,42 @@ do {
1438
1251
<programlisting role="php">
1439
1252
<![CDATA[
1440
1253
<?php
1441
-
if (!($stmt = $mysqli->prepare("CALL p()"))) {
1442
-
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
1443
-
}
1444
1254

1445
-
if (!$stmt->execute()) {
1446
-
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
1447
-
}
1255
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1256
+
$mysqli = new mysqli("example.com", "user", "password", "database");
1448
1257

1449
-
do {
1258
+
$mysqli->query("DROP TABLE IF EXISTS test");
1259
+
$mysqli->query("CREATE TABLE test(id INT)");
1260
+
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
1450
1261

1451
-
$id_out = NULL;
1452
-
if (!$stmt->bind_result($id_out)) {
1453
-
echo "Bind failed: (" . $stmt->errno . ") " . $stmt->error;
1454
-
}
1262
+
$mysqli->query("DROP PROCEDURE IF EXISTS p");
1263
+
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
1264
+

1265
+
$stmt = $mysqli->prepare("CALL p()");
1455
1266

1456
-
while ($stmt->fetch()) {
1457
-
echo "id = $id_out\n";
1267
+
$stmt->execute();
1268
+

1269
+
do {
1270
+
if ($stmt->store_result()) {
1271
+
$stmt->bind_result($id_out);
1272
+
while ($stmt->fetch()) {
1273
+
echo "id = $id_out\n";
1274
+
}
1458
1275
}
1459
-
} while ($stmt->more_results() && $stmt->next_result());
1460
-
?>
1276
+
} while ($stmt->next_result());
1461
1277
]]>
1462
1278
</programlisting>
1279
+
&example.outputs;
1280
+
<screen>
1281
+
<![CDATA[
1282
+
id = 1
1283
+
id = 2
1284
+
id = 3
1285
+
id = 2
1286
+
id = 3
1287
+
id = 4
1288
+
]]>
1289
+
</screen>
1463
1290
</example>
1464
1291
</para>
1465
1292
<para>
...
...
@@ -1469,8 +1296,8 @@ do {
1469
1296
<simplelist>
1470
1297
<member><methodname>mysqli::query</methodname></member>
1471
1298
<member><methodname>mysqli::multi_query</methodname></member>
1472
-
<member><methodname>mysqli_result::next-result</methodname></member>
1473
-
<member><methodname>mysqli_result::more-results</methodname></member>
1299
+
<member><methodname>mysqli::next_result</methodname></member>
1300
+
<member><methodname>mysqli::more_results</methodname></member>
1474
1301
</simplelist>
1475
1302
</para>
1476
1303
</section>
...
...
@@ -1478,13 +1305,12 @@ do {
1478
1305
<section xml:id="mysqli.quickstart.multiple-statement">
1479
1306
<title>Multiple Statements</title>
1480
1307
<para>
1481
-
MySQL optionally allows having multiple statements in one statement string.
1482
-
Sending multiple statements at once reduces client-server
1483
-
round trips but requires special handling.
1308
+
MySQL optionally allows having multiple statements in one statement string,
1309
+
but it requires special handling.
1484
1310
</para>
1485
1311
<para>
1486
1312
Multiple statements or multi queries must be executed
1487
-
with <function>mysqli_multi_query</function>. The individual statements
1313
+
with <methodname>mysqli::multi_query</methodname>. The individual statements
1488
1314
of the statement string are separated by semicolon.
1489
1315
Then, all result sets returned by the executed statements must be fetched.
1490
1316
</para>
...
...
@@ -1498,30 +1324,25 @@ do {
1498
1324
<programlisting role="php">
1499
1325
<![CDATA[
1500
1326
<?php
1327
+

1328
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1501
1329
$mysqli = new mysqli("example.com", "user", "password", "database");
1502
-
if ($mysqli->connect_errno) {
1503
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
1504
-
}
1505
1330

1506
-
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
1507
-
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
1508
-
}
1331
+
$mysqli->query("DROP TABLE IF EXISTS test");
1332
+
$mysqli->query("CREATE TABLE test(id INT)");
1509
1333

1510
-
$sql = "SELECT COUNT(*) AS _num FROM test; ";
1511
-
$sql.= "INSERT INTO test(id) VALUES (1); ";
1512
-
$sql.= "SELECT COUNT(*) AS _num FROM test; ";
1334
+
$sql = "SELECT COUNT(*) AS _num FROM test;
1335
+
INSERT INTO test(id) VALUES (1);
1336
+
SELECT COUNT(*) AS _num FROM test; ";
1513
1337

1514
-
if (!$mysqli->multi_query($sql)) {
1515
-
echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
1516
-
}
1338
+
$mysqli->multi_query($sql);
1517
1339

1518
1340
do {
1519
-
if ($res = $mysqli->store_result()) {
1520
-
var_dump($res->fetch_all(MYSQLI_ASSOC));
1521
-
$res->free();
1341
+
if ($result = $mysqli->store_result()) {
1342
+
var_dump($result->fetch_all(MYSQLI_ASSOC));
1343
+
$result->free();
1522
1344
}
1523
-
} while ($mysqli->more_results() && $mysqli->next_result());
1524
-
?>
1345
+
} while ($mysqli->next_result());
1525
1346
]]>
1526
1347
</programlisting>
1527
1348
&example.outputs;
...
...
@@ -1549,15 +1370,15 @@ array(1) {
1549
1370
<emphasis role="bold">Security considerations</emphasis>
1550
1371
</para>
1551
1372
<para>
1552
-
The API functions <function>mysqli_query</function> and
1553
-
<function>mysqli_real_query</function> do not set a connection flag necessary
1373
+
The API functions <methodname>mysqli::query</methodname> and
1374
+
<methodname>mysqli::real_query</methodname> do not set a connection flag necessary
1554
1375
for activating multi queries in the server. An extra API call is used for
1555
-
multiple statements to reduce the likeliness of accidental SQL injection
1376
+
multiple statements to reduce the damage of accidental SQL injection
1556
1377
attacks. An attacker may try to add statements such as
1557
1378
<literal>; DROP DATABASE mysql</literal> or <literal>; SELECT SLEEP(999)</literal>.
1558
1379
If the attacker succeeds in adding SQL to the statement string but
1559
-
<literal>mysqli_multi_query</literal> is not used, the server will not
1560
-
execute the second, injected and malicious SQL statement.
1380
+
<methodname>mysqli::multi_query</methodname> is not used, the server will not
1381
+
execute the injected and malicious SQL statement.
1561
1382
</para>
1562
1383
<para>
1563
1384
<example>
...
...
@@ -1565,20 +1386,18 @@ array(1) {
1565
1386
<programlisting role="php">
1566
1387
<![CDATA[
1567
1388
<?php
1389
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1568
1390
$mysqli = new mysqli("example.com", "user", "password", "database");
1569
-
$res = $mysqli->query("SELECT 1; DROP TABLE mysql.user");
1570
-
if (!$res) {
1571
-
echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error;
1572
-
}
1391
+
$result = $mysqli->query("SELECT 1; DROP TABLE mysql.user");
1573
1392
?>
1574
1393
]]>
1575
1394
</programlisting>
1576
1395
&example.outputs;
1577
1396
<screen>
1578
1397
<![CDATA[
1579
-
Error executing query: (1064) You have an error in your SQL syntax;
1580
-
check the manual that corresponds to your MySQL server version for the right syntax
1581
-
to use near 'DROP TABLE mysql.user' at line 1
1398
+
PHP Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax;
1399
+
check the manual that corresponds to your MySQL server version for the right syntax to
1400
+
use near 'DROP TABLE mysql.user' at line 1
1582
1401
]]>
1583
1402
</screen>
1584
1403
</example>
...
...
@@ -1596,8 +1415,8 @@ to use near 'DROP TABLE mysql.user' at line 1
1596
1415
<simplelist>
1597
1416
<member><methodname>mysqli::query</methodname></member>
1598
1417
<member><methodname>mysqli::multi_query</methodname></member>
1599
-
<member><methodname>mysqli_result::next-result</methodname></member>
1600
-
<member><methodname>mysqli_result::more-results</methodname></member>
1418
+
<member><methodname>mysqli::next_result</methodname></member>
1419
+
<member><methodname>mysqli::more_results</methodname></member>
1601
1420
</simplelist>
1602
1421
</para>
1603
1422
</section>
...
...
@@ -1694,14 +1513,12 @@ $mysqli->commit();
1694
1513
<programlisting role="php">
1695
1514
<![CDATA[
1696
1515
<?php
1516
+

1517
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1697
1518
$mysqli = new mysqli("example.com", "user", "password", "database");
1698
-
if ($mysqli->connect_errno) {
1699
-
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
1700
-
}
1701
1519

1702
-
$res = $mysqli->query("SELECT 1 AS _one, 'Hello' AS _two FROM DUAL");
1703
-
var_dump($res->fetch_fields());
1704
-
?>
1520
+
$result = $mysqli->query("SELECT 1 AS _one, 'Hello' AS _two FROM DUAL");
1521
+
var_dump($result->fetch_fields());
1705
1522
]]>
1706
1523
</programlisting>
1707
1524
&example.outputs;
...
...
@@ -1777,7 +1594,7 @@ array(2) {
1777
1594
<para>
1778
1595
Meta data of result sets created using prepared statements are accessed
1779
1596
the same way. A suitable <classname>mysqli_result</classname> handle is
1780
-
returned by <function>mysqli_stmt_result_metadata</function>.
1597
+
returned by <methodname>mysqli_stmt::result_metadata</methodname>.
1781
1598
</para>
1782
1599
<para>
1783
1600
<example>
...
...
@@ -1785,11 +1602,14 @@ array(2) {
1785
1602
<programlisting role="php">
1786
1603
<![CDATA[
1787
1604
<?php
1605
+

1606
+
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
1607
+
$mysqli = new mysqli("example.com", "user", "password", "database");
1608
+

1788
1609
$stmt = $mysqli->prepare("SELECT 1 AS _one, 'Hello' AS _two FROM DUAL");
1789
1610
$stmt->execute();
1790
-
$res = $stmt->result_metadata();
1791
-
var_dump($res->fetch_fields());
1792
-
?>
1611
+
$result = $stmt->result_metadata();
1612
+
var_dump($result->fetch_fields());
1793
1613
]]>
1794
1614
</programlisting>
1795
1615
</example>
1796
1616



Tools (French Manual)