Tuesday, 28 August 2018

Simple MySQL Command with varchar (64) Does Not Work Correctly

I have a table with a varchar(64) column with named 'value'. I want to select all the fields in the table sorted by this column.

It seems simple, but both my PHP code and phpMyAdmin are showing an incorrect ordering. I've tried changing the collation of the table and column, but nothing changes. I've tried renaming the column name, but nothing changes. Either MySQL is misbehaving at a basic level or I'm missing something.
Here is the result of SHOW CREATE TABLE Content_Data_String:
CREATE TABLE `Content_Data_String` (
 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
 `content_instance_id` mediumint(9) NOT NULL,
 `field_id` mediumint(9) NOT NULL,
 `value` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


Sorry guys. My problem was that my PHP form was inserting spaces in front of the field values. @Uueerdo was actually right here. Even after checking his answer, I couldn't see these extra spaces through phpMyAdmin. When reconstructing the tables I was using phpMyAdmin instead of my php form to create the values. Thus the false positive that spurred my original answer.
Edit: Yep, one extra space in a template file. What a waste of time. Sorry again guys.

0 comments:

Post a Comment