How can "Some String" != "Some String"?

Databases
1

I’m building a website that allows members to upload images.
The images themselves are not stored in the database - just the image information
(id, title, filename, thumbnail, date, ownerid).

I created small test tables in PHPMyAdmin, and everything was working fine until I got my image upload script working.
Apparently, SQL doesn’t recognize strings that are inserted by my script.

In other words:

SELECT * FROM 'image'
Returns all records


SELECT * FROM 'image' WHERE 'title' LIKE "Some Title"
Returns the row with "Some Title" if it was inserted by PHPMyAdmin.


SELECT * FROM 'image' WHERE 'title' LIKE "Some Title"
Returns 0 records if "Some Title" was inserted through my PHP script.


If I copy and paste or type the words "Some Title" in PHPMYAdmin:
SELECT * FROM 'image' WHERE 'title' LIKE "Some Title"
Now returns the correct data.

It *seems like I didn’t change anything, and it still looks like the same string to me, but to SQL
“Some Title” != “Some Title” for some mysterious reason.
Incidentally, all of the other rows with strings have the same issue.

Here’s what I’ve tried:

SHOW CREATE TABLE:
CREATE TABLE `image`
(`id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `thumbnail` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `date` date NOT NULL, `ownerid` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`))
ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

SHOW CREATE DATABASE:
CREATE DATABASE `imagedb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */

Of course, when I connect to my database, I always run:
!mysqli_set_charset($link, 'utf8')


All of my html and php pages contain:
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">


Someone suggested putting this at the top of my script:
ini_set('default_charset', 'UTF-8');
It didn't help, so I removed it.

I’ve already tried going through each table with PHPMyAdmin and
setting the collation for each column manually.

I also tried running this script:
SET character_set_results = 'utf8', character_set_client = 'utf8', 
character_set_connection = 'utf8', character_set_database = 'utf8', 
character_set_server = 'utf8'

Maybe it’s not an encoding (collation) problem after all…
But I’m stumped, so any help would be appreciated!

2

try removing the single quotes from the table and column names

3

Good idea - thanks Kalon. Unfortunately, I got the same result.

4

If you search for a complete string, why use LIKE?

5

do a binary compare and you will see whether the strings actually are equal

… WHERE ‘Some string’ = BINARY ‘Some string’

6

Thanks for your suggestions Guido and Rudy!
It looks like it was 2 problems: Encoding, and extraneous spaces.

When I tried using LIKE “%Some Title%” before, it didn’t help.
So I went back to “=”, but just left LIKE in the query since I was copying and pasting,
and without “%%” it means essentially the same as “=”.

So I just tried LIKE “%Some Title%”, and voila! But why?
They should be equal, not similar.

Looking closer, I see that my insert query is inserting extraneous spaces into my table.

So LIKE “%Some Title%” didn’t work originally because something was wrong with the encoding.
At some point, I fixed the encoding problem (see above), but didn’t notice because I was using “LIKE” (as in “=”).
So I was comparing " Some Title " with “Some Title” which are definitely not equal.

Now I just have to figure out where those spaces are coming from…

7

try using $inputstring = trim($inputstring) in your php before making the insert, to remove any spaces. Ditto when doing an search from an input form.

8

if the strings are from user inputs, it’s quite possible the user accidentally (or even deliberately for some reason) entered them.

you can get rid of leading and trailing spaces using trim() as Dr John suggested.

9

Thanks Dr John, and thanks again Kalon. I am now using trim for my user inputs - I’m sure it will save a lot of trouble in the future.