Case sensitivity in MySQL

[LinuxGold]

I am creating a KJV search engine for my Church. I am using Windows 2000 Pro (plan to implement into Linux eventually as design become finalized), PHP5, MySQL 5.0.15-nt and Apache2. I am working on MySQL query to select a text that matches a string in two ways: case sensitive and non-case sensitive. I researched on Google, MySQL & Php online documents and emailed to the MySQL mailing list. I received a suggestion from a user to use WHERE BINARY function to select case sensitively. I'm currently trying to figure out a way to select non-sensitively. I pasted the entire code so that you can understand the point of my design. Help would be greatly appreciated. If you see anything more that you would like to suggest, that will be great as well!

Thanks in advance,

Scott

--begin code--

<?php
// Server specifications:
// PHP5
// MySQL 5.0.15-nt
// Apache2
// Windows 2000 Pro
// Test Strings total non sensitive: TOTAL 7 results
$srch='JEHOVAH';//case sensitive: 4 results
//$srch='Jehovah';//case sensitive: 3 results
//$srch='jehovah';//case sensitive: 0 results
//$srch='Jehovah ';//(With trailing space) case sensitive: 0 results; non sensitive: 3 results; 
// Test case selector
$case=1;// 0=case not sensitive; 1=case sensitive; 
$link=mysql_connect(
'localhost',
'apache',
''
);
if(!$link)
{
die('Could not connect...' . mysql_error());
}
// Works with 'WHERE BINARY' as recommended by Brent Baisley ([e-mail]) from MySQL List.
// Once passed, create a stored procedure for this.
if($case==1)
{
$result=mysql_query("
SELECT 
bo.book, 
b.chapter, 
b.verse, 
b.text 
FROM 
avkjv.books bo 
LEFT JOIN 
avkjv.bible b 
ON 
b.book=bo.number 
WHERE BINARY
text 
LIKE 
'%$srch%';
");
}
else// Still case sensitive -- Need to figure out a way to select nonsensitively.
{
$result=mysql_query("
SELECT 
bo.book, 
b.chapter, 
b.verse, 
b.text 
FROM 
avkjv.books bo 
LEFT JOIN 
avkjv.bible b 
ON 
b.book=bo.number 
WHERE 
text 
LIKE 
'%$srch%';
");
}
// If database is down or query is screwed up, inform user in a nice way.
if(!$result)
{
?><p>Sorry, the database is currently down, please try again later.</p><?
}
// Need to figure out a way to show user that there are no recordsets.
//elseif {}
// Datas has been selected, let's populate using definition list. (Accessibility?)
else
{
// Prepare the definition list format for printf
$format='
<dl>
<dd>%s %s:%s</dd>
<dt>%s</dt>
</dl>';
while($row=mysql_fetch_array($result, MYSQL_NUM))
{
printf($format,$row[0],$row[1],$row[2],$row[3]);
}
// Free up memory!
mysql_free_result($result);
// Close db 
mysql_close($link);
}
?>

--end code--

[directrix]

You can force a case-insensitive search by converting both strings to the same case using upper() or lower(). For example:

... where upper(text) LIKE upper('jehovah');

But you should need to do this only if your database field is defined as binary.

[LinuxGold]

Ok. Let's suppose I want to optimize the mysql selection as I heard that UPPER() can cause overhead in MySQL performance. I'm trying to make it as optimized as possible because I will be using that server for something else as well i.e. email server, etc.... (Linux)

[directrix]

What is the type of your text field? If it's char, varchar, or text, then a comparison with a (non-binary) search string will already be case-insensitive.

In other words, for those field types, the default string comparison ignores case.

[LinuxGold]

Aha, it's text then. Removed BINARY...

Changed and now works. -- Check if I'm missing anything? (trying to optimize my server)

<?php
// Test Strings total non sensitive: TOTAL 7 results
$srch='JEHOVAH';//case sensitive: 4 results
//$srch='Jehovah';//case sensitive: 3 results
//$srch='jehovah';//case sensitive: 0 results
//$srch='Jehovah ';//(With trailing space) case sensitive: 0 results; non sensitive: 3 results; 
// Test case selector
$case=1;// 0=case not sensitive; 1=case sensitive; (checkbox radio)
$link=mysql_connect(// Connects MySQL Database
'localhost',
'apache',
''
);
if(!$link)// Connection failure notification.
{
die('Could not connect...' . mysql_error());
}
// Works with 'WHERE BINARY' as recommended by Brent Baisley ([e-mail]) from MySQL List.
// Once passed, create a stored procedure for this with '0' or '1' case selector.
if($case==1)
{
$result=mysql_query("
SELECT 
bo.book, 
b.chapter, 
b.verse, 
b.text 
FROM 
avkjv.books bo 
LEFT JOIN 
avkjv.bible b 
ON 
b.book=bo.number 
WHERE 
text 
LIKE 
'%$srch%';
");
}
elseif($case=0)// Still case sensitive -- Need to figure out a way to select nonsensitively.
{
$result=mysql_query("
SELECT 
bo.book, 
b.chapter, 
b.verse, 
b.text 
FROM 
avkjv.books bo 
LEFT JOIN 
avkjv.bible b 
ON 
b.book=bo.number 
WHERE 
UPPER(text) 
LIKE 
'%" . strtoupper( $srch ) . "%'
");
}
// If database is down or query is screwed up, inform user in a nice way.
if(!$result)
{
?><p>Sorry, the database is currently down, please try again later.</p><?
}
// Need to figure out a way to show user that there are no recordsets.
// elseif {}
// Datas has been selected, let's populate using definition list. (Accessibility?)
else
{
// Prepare the definition list format for printf
$format='
<dl>
<dd>%s %s:%s</dd>
<dt>%s</dt>
</dl>';
while($row=mysql_fetch_array($result, MYSQL_NUM))
{
printf($format,$row[0],$row[1],$row[2],$row[3]);
}
// Free up memory!
mysql_free_result($result);
// Close db 
mysql_close($link);
}
?>

[directrix]

If your database field is not defined as binary, then I think the search ignoring case should be...

WHERE text LIKE '%$srch%'; "); // (as you have it.)

... and the case-sensitive search should be...

WHERE binary text LIKE '%$srch%'; ");

Another point, regarding the join, do you have an index defined on bible.book?