Page 1 of 1

Case-sensitivity with SQL

Posted: Fri Mar 28, 2008 1:40 pm
by Nial
I have a database field (bfArticleName) that I need to be case-sensitive. At the moment, I just do the following:

Code: Select all

$q = "SELECT * FROM ".BF_TBL_PREFIX.BF_TBL_ARTICLES." WHERE bfArticleName LIKE '".$page."' COLLATE latin1_general_cs";
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:

Code: Select all

$q = "SELECT * FROM ".BF_TBL_PREFIX.BF_TBL_ARTICLES." WHERE bfArticleName='".$page."'";
Making my code valid on old PHP and MySQL boxes!

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
 
Here's the search SQL I'm trying:

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
 
Here's the error I get:

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