Case-sensitivity with SQL
Posted: Fri Mar 28, 2008 1:40 pm
I have a database field (bfArticleName) that I need to be case-sensitive. At the moment, I just do the following:
This works fine for older versions of PHP and MySQL, but runs into problems on servers running lesser versions. What I'd like to do is change bfArticleName's collation in the database to latin1_swedish_cs by default, and then only change the COLLATION when searching (to allow for case-insensitive searching of bfArticleName). It doesn't matter if the search is incompatible with older versions because I use MySQL fulltext boolean searches (which I believe have only been introduced in recent versions).
If bfArticleName is set to latin1_swedish_cs by default, I can then just do:
Making my code valid on old PHP and MySQL boxes!
Here's my table structure:
Here's the search SQL I'm trying:
Here's the error I get:
Code: Select all
$q = "SELECT * FROM ".BF_TBL_PREFIX.BF_TBL_ARTICLES." WHERE bfArticleName LIKE '".$page."' COLLATE latin1_general_cs";If bfArticleName is set to latin1_swedish_cs by default, I can then just do:
Code: Select all
$q = "SELECT * FROM ".BF_TBL_PREFIX.BF_TBL_ARTICLES." WHERE bfArticleName='".$page."'";Here's my table structure:
Code: Select all
bfArticleID int(11) auto_increment
bfArticleName varchar(255) latin1_swedish_ci
bfArticleTimestamp int(11)
bfArticleContributor varchar(255) latin1_swedish_ci
bfArticleContributorIP varchar(255) latin1_swedish_ci
bfArticleCurrent tinyint(1)
bfArticle longtext latin1_swedish_ci
Code: Select all
SELECT *, MATCH(bfArticleName COLLATE latin1_swedish_ci,
bfArticle COLLATE latin1_swedish_ci) AGAINST('Nial' IN BOOLEAN MODE) AS score
FROM bf_articles
WHERE MATCH(bfArticleName COLLATE latin1_swedish_ci,
bfArticle COLLATE latin1_swedish_ci) AGAINST('Nial' IN BOOLEAN MODE) AND bfArticleCurrent = '1'
GROUP BY bfArticleName
ORDER BY score DESC
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near 'COLLATE latin1_swedish_ci, bfArticle COLLATE latin1_swedish_ci) AGAINST('Nial' I' at line 1