note 111870 deleted from mysqli.quickstart.statements by salathe
| From: | salathe@php.net | Date: | Mon, 21 Sep 2020 14:45:54 +0000 |
| Subject: | note 111870 deleted from mysqli.quickstart.statements by salathe | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-215296@lists.php.net to get a copy of this message | ||
Note Submitter: developer at 4seasonsconsulting dot com
----
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.