When accessing columns of type ENUM or SET, your results may not always come back as expected. Given:
SQL:
CREATE TABLE `items` (
`item_key` char(4) NOT NULL DEFAULT 'xxxx',
`item_id` smallint(5) unsigned NOT NULL,
`item_media` enum('pdf','tape','cd','dvd','mp3','mp4') NOT NULL DEFAULT 'mp3',
`item_file` varchar(40) DEFAULT NULL,
PRIMARY KEY (`item_key`,`item_id`,`item_media`)
) ENGINE=MyISAM;
INSERT INTO `items` (`item_key`, `item_id`, `item_media`) VALUES
('A1B2', 1, 'mp3'),
('A1B2', 2, 'mp4'),
('A1B2', 3, 'pdf'),
('A1B2', 4, 'dvd'),
('A1B2', 5, 'mp3');
<?php
...
$result = mysql_query("SELECT `item_id`, `item_media`+0 FROM `items`
WHERE `item_key`='A1B2' ORDER BY `item_media`";
while ($row = mysql_fetch_array($result) )
echo 'id=' . $row[0] . ', media=' . $row[1] . "\r\n";
...
?>
When just the query is run in phpMyAdmin,
you most likely will get:
id=3, media=1
id=4, media=4
id=1, media=5
id=5, media=5
id=2, media=6
but php will give you:
id=1, media=0
id=2, media=0
id=3, media=0
id=4, media=0
id=5, media=0
This is because the translation of <enum or set>+0
is evaluated as a string (converted to zero)+0,
This does need further research for a fix.
Executing statements
Statements can be executed with the mysqli_query(), mysqli_real_query() and mysqli_multi_query() functions. The mysqli_query() function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. Calling mysqli_query() is identical to calling mysqli_real_query() followed by mysqli_store_result().
Example #1 Connecting to MySQL
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT)") ||
!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>
Buffered result sets
After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query() combines statement execution and result set buffering.
PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.
Example #2 Navigation through buffered results
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT)") ||
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$res = $mysqli->query("SELECT id FROM test ORDER BY id ASC");
echo "Reverse order...\n";
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
$res->data_seek($row_no);
$row = $res->fetch_assoc();
echo " id = " . $row['id'] . "\n";
}
echo "Result set order...\n";
$res->data_seek(0);
while ($row = $res->fetch_assoc()) {
echo " id = " . $row['id'] . "\n";
}
?>
Exemplul de mai sus va afișa:
Reverse order... id = 3 id = 2 id = 1 Result set order... id = 1 id = 2 id = 3
Unbuffered result sets
If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.
Example #3 Navigation through unbuffered results
<?php
$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");
$res = $mysqli->use_result();
echo "Result set order...\n";
while ($row = $res->fetch_assoc()) {
echo " id = " . $row['id'] . "\n";
}
?>
Result set values data types
The mysqli_query(), mysqli_real_query()
and mysqli_multi_query() functions are used to execute
non-prepared statements. At the level of the MySQL Client Server Protocol,
the command COM_QUERY and the text protocol are used
for statement execution. With the text protocol, the MySQL server converts
all data of a result sets into strings before sending. This conversion is done
regardless of the SQL result set column data type. The mysql client libraries
receive all column values as strings. No further client-side casting is done
to convert columns back to their native types. Instead, all values are
provided as PHP strings.
Example #4 Text protocol returns strings by default
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $res->fetch_assoc();
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>
Exemplul de mai sus va afișa:
id = 1 (string) label = a (string)
It is possible to convert integer and float columns back to PHP numbers by setting the
MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option,
if using the mysqlnd library. If set, the mysqlnd library will
check the result set meta data column types and convert numeric SQL columns
to PHP numbers, if the PHP data type value range allows for it.
This way, for example, SQL INT columns are returned as integers.
Example #5 Native data types with mysqlnd and connection option
<?php
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
$mysqli->real_connect("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $res->fetch_assoc();
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>
Exemplul de mai sus va afișa:
id = 1 (integer) label = a (string)
See also
add a note
User Contributed Notes 1 note
developer at 4seasonsconsulting dot com ¶
11 years ago
