reference/mysqlinfo/concepts.xml
968c2bc60de3e93d7f38be0b9d2d1b9685a16ea7
...
...
@@ -18,7 +18,7 @@
18
18
The downside of the buffered mode is that larger result sets might require
19
19
quite a lot memory. The memory will be kept occupied till all references to the
20
20
result set are unset or the result set was explicitly freed, which will automatically
21
-
happen during request end the latest. The terminology "store result" is also used
21
+
happen during request end at the latest. The terminology "store result" is also used
22
22
for buffered mode, as the whole result set is stored at once.
23
23
</para>
24
24

...
...
@@ -31,17 +31,21 @@
31
31
</note>
32
32
33
33
<para>
34
-
Unbuffered MySQL queries execute the query and then return a <type>resource</type> while
35
-
the data is still waiting on the MySQL server for being fetched. This uses less memory
34
+
Unbuffered MySQL queries execute the query and then wait
35
+
for the data from the MySQL server to be fetched. This uses less memory
36
36
on the PHP-side, but can increase the load on the server. Unless the full result set was
37
37
fetched from the server no further queries can be sent over the same connection. Unbuffered
38
-
queries can also be referred to as "use result".
38
+
queries can also be referred to as "use result". Once all rows in the result set
39
+
are fetched, the result set is gone and it cannot be iterated again.
39
40
</para>
40
41

41
42
<para>
42
-
Following these characteristics buffered queries should be used in cases where you expect
43
-
only a limited result set or need to know the amount of returned rows before reading all
44
-
rows. Unbuffered mode should be used when you expect larger results.
43
+
Following these characteristics, unbuffered queries should be used only
44
+
when a large result set is expected that will be processed sequentially.
45
+
Unbuffered queries contain a number of pitfalls that makes it more
46
+
difficult to use them, e.g. the number of rows in the result set is unknown
47
+
until the last row is fetched.
48
+
Buffered queries are the easier and more flexible way to process result sets.
45
49
</para>
46
50
47
51
<!-- @TODO
...
...
@@ -62,14 +66,11 @@
62
66
<![CDATA[
63
67
<?php
64
68
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
65
-
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
69
+
$unbufferedResult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
66
70

67
-
if ($uresult) {
68
-
while ($row = $uresult->fetch_assoc()) {
69
-
echo $row['Name'] . PHP_EOL;
70
-
}
71
+
foreach ($unbufferedResult as $row) {
72
+
echo $row['Name'] . PHP_EOL;
71
73
}
72
-
$uresult->close();
73
74
?>
74
75
]]>
75
76
</programlisting>
...
...
@@ -80,33 +81,12 @@ $uresult->close();
80
81
<programlisting role="php">
81
82
<![CDATA[
82
83
<?php
83
-
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
84
+
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_password');
84
85
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
85
86

86
-
$uresult = $pdo->query("SELECT Name FROM City");
87
-
if ($uresult) {
88
-
while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
89
-
echo $row['Name'] . PHP_EOL;
90
-
}
91
-
}
92
-
?>
93
-
]]>
94
-
</programlisting>
95
-
</example>
96
-

97
-
<example>
98
-
<title>Unbuffered query example: mysql</title>
99
-
<programlisting role="php">
100
-
<![CDATA[
101
-
<?php
102
-
$conn = mysql_connect("localhost", "my_user", "my_pass");
103
-
$db = mysql_select_db("world");
104
-

105
-
$uresult = mysql_unbuffered_query("SELECT Name FROM City");
106
-
if ($uresult) {
107
-
while ($row = mysql_fetch_assoc($uresult)) {
108
-
echo $row['Name'] . PHP_EOL;
109
-
}
87
+
$unbufferedResult = $pdo->query("SELECT Name FROM City");
88
+
foreach ($unbufferedResult as $row) {
89
+
echo $row['Name'] . PHP_EOL;
110
90
}
111
91
?>
112
92
]]>
...
...
@@ -129,8 +109,7 @@ if ($uresult) {
129
109
<para>
130
110
The character set should be understood and defined, as it has an affect on every
131
111
action, and includes security implications. For example, the escaping mechanism
132
-
(e.g., <function>mysqli_real_escape_string</function> for mysqli, <function>mysql_real_escape_string</function>
133
-
for mysql, and <methodname>PDO::quote</methodname> for PDO_MySQL) will adhere to
112
+
(e.g., <function>mysqli_real_escape_string</function> for mysqli and <methodname>PDO::quote</methodname> for PDO_MySQL) will adhere to
134
113
this setting. It is important to realize that these functions will not use the character
135
114
set that is defined with a query, so for example the following will not have an effect
136
115
on them:
...
...
@@ -146,17 +125,16 @@ if ($uresult) {
146
125
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
147
126

148
127
// Will NOT affect $mysqli->real_escape_string();
149
-
$mysqli->query("SET NAMES utf8");
128
+
$mysqli->query("SET NAMES utf8mb4");
150
129

151
130
// Will NOT affect $mysqli->real_escape_string();
152
-
$mysqli->query("SET CHARACTER SET utf8");
131
+
$mysqli->query("SET CHARACTER SET utf8mb4");
153
132

154
133
// But, this will affect $mysqli->real_escape_string();
155
-
$mysqli->set_charset('utf8');
156
-

157
-
// But, this will NOT affect it (utf-8 vs utf8) -- don't use dashes here
158
-
$mysqli->set_charset('utf-8');
134
+
$mysqli->set_charset('utf8mb4');
159
135

136
+
// But, this will NOT affect it (UTF-8 vs utf8mb4) -- don't use dashes here
137
+
$mysqli->set_charset('UTF-8');
160
138
?>
161
139
]]>
162
140
</programlisting>
...
...
@@ -171,9 +149,8 @@ $mysqli->set_charset('utf-8');
171
149
<title>Possible UTF-8 confusion</title>
172
150
<para>
173
151
Because character set names in MySQL do not contain dashes, the string
174
-
"utf8" is valid in MySQL to set the character set to UTF-8. The string
175
-
"utf-8" is not valid, as using "utf-8" will fail to change the
176
-
character set.
152
+
"utf8" is valid in MySQL to set the character set to UTF-8 (up to 3 byte UTF-8 Unicode Encoding). The string
153
+
"UTF-8" is not valid, as using "UTF-8" will fail to change the character set and will throw an error.
177
154
</para>
178
155
</note>
179
156

...
...
@@ -184,16 +161,14 @@ $mysqli->set_charset('utf-8');
184
161
<?php
185
162
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
186
163

187
-
printf("Initial character set: %s\n", $mysqli->character_set_name());
164
+
echo 'Initial character set: ' . $mysqli->character_set_name() . "\n";
188
165

189
-
if (!$mysqli->set_charset('utf8')) {
190
-
printf("Error loading character set utf8: %s\n", $mysqli->error);
166
+
if (!$mysqli->set_charset('utf8mb4')) {
167
+
printf("Error loading character set utf8mb4: %s\n", $mysqli->error);
191
168
exit;
192
169
}
193
170

194
-
echo "New character set information:\n";
195
-
print_r( $mysqli->get_charset() );
196
-

171
+
echo 'Your current character set is: ' . $mysqli->character_set_name() . "\n";
197
172
?>
198
173
]]>
199
174
</programlisting>
...
...
@@ -201,38 +176,15 @@ print_r( $mysqli->get_charset() );
201
176

202
177
<example>
203
178
<title>Setting the character set example: <link linkend="ref.pdo-mysql.connection">pdo_mysql</link></title>
204
-
<para>
205
-
Note: This only works as of PHP 5.3.6.
206
-
</para>
207
179
<programlisting role="php">
208
180
<![CDATA[
209
181
<?php
210
-
$pdo = new PDO("mysql:host=localhost;dbname=world;charset=utf8", 'my_user', 'my_pass');
182
+
$pdo = new PDO("mysql:host=localhost;dbname=world;charset=utf8mb4", 'my_user', 'my_pass');
211
183
?>
212
184
]]>
213
185
</programlisting>
214
186
</example>
215
187

216
-
<example>
217
-
<title>Setting the character set example: mysql</title>
218
-
<programlisting role="php">
219
-
<![CDATA[
220
-
<?php
221
-
$conn = mysql_connect("localhost", "my_user", "my_pass");
222
-
$db = mysql_select_db("world");
223
-

224
-
echo 'Initial character set: ' . mysql_client_encoding($conn) . "\n";
225
-

226
-
if (!mysql_set_charset('utf8', $conn)) {
227
-
echo "Error: Unable to set the character set.\n";
228
-
exit;
229
-
}
230
-

231
-
echo 'Your current character set is: ' . mysql_client_encoding($conn);
232
-
?>
233
-
]]>
234
-
</programlisting>
235
-
</example>
236
188
</section>
237
189

238
190
</chapter>
239
191



Tools (Spanish Manual)