MATCH() returning empty set

[soycharliente]

I've never used match() before and I don't know what I'm missing. I'm trying to make a basic search input box on the homepage. Return results that match the input in either the firstname or lastname. Not exact matches. Could be contained within a longer string.

 

SELECT * FROM `users`
WHERE MATCH (`firstname`,`lastname`) AGAINST ('John' IN BOOLEAN MODE)
ORDER BY `lastname` ASC, `firstname` ASC

 

I'm getting an empty set with dummy data consisting of many john, johnson, johnny, etc. in both firstname and lastname.

 

Any help or a reference to example that better explains? I don't find the MySQL entry on match() very helpful AT ALL.

 

Thanks.

[soycharliente]

Tried this too after doing more reading:

SELECT * FROM `users`
WHERE MATCH(`firstname`, `lastname`) AGAINST ('+*John*' IN BOOLEAN MODE)
ORDER BY `lastname` ASC

[soycharliente]

I even tried using LIKE. However, I am finding that it is completely case sensitive even though the manual explicitly says it's not.

 

SELECT * FROM `users`
WHERE `firstname` LIKE '%Smith%' OR `lastname` LIKE '%Smith%'
ORDER BY `lastname` ASC, `firstname` ASC

 

That returns John Smith but not John Blacksmith. And if I change to lowercase, it switches the returned result.

[soycharliente]

Found out I didn't have the DB setup to do FULLTEXT on firstname and lastname. Did that and still getting empty set.

[soycharliente]

I figured out I could do this.

 

SELECT * FROM `users`
WHERE UPPER(`firstname`) LIKE UPPER('%blah%')
OR UPPER(`lastname`) LIKE UPPER('%blah%')
ORDER BY `lastname` ASC, `firstname` ASC

 

If anyone ends up being able to help me, please post. I'm going to move forward with this since it's working. Not really happy about doing this, but maybe it's the only way.

[fenway]

Show us the table definition.

[soycharliente]

CREATE TABLE IF NOT EXISTS `users` (
  `id` int( NOT NULL auto_increment,
  `eid` int(9) NOT NULL,
  `firstname` varchar(64) collate utf8_bin NOT NULL,
  `lastname` varchar(64) collate utf8_bin NOT NULL,
  `email` varchar(255) collate utf8_bin default NULL,
  `address` varchar(64) collate utf8_bin default NULL,
  `city` varchar(64) collate utf8_bin default NULL,
  `state` varchar(4) collate utf8_bin default NULL,
  `zip` varchar(16) collate utf8_bin default NULL,
  `phone` varchar(16) collate utf8_bin default NULL,
  `gender` enum('M','F') collate utf8_bin default NULL,
  `ethnicity` varchar(255) collate utf8_bin default NULL
  PRIMARY KEY  (`id`),
  UNIQUE KEY `eid` (`eid`)
)

[fenway]

Yup, just as I suspected... utf8_bin... a binary, case-sensitive, collation.

[soycharliente]

Hmm. So change them all to utf8_general_ci?

[fenway]

Hmm. So change them all to utf8_general_ci?

Sure.