Case sensitivity

[JimJx]

Hi all,

I know that searches in MySQL are supposed to be case insensitive so this is driving me nutz...

Quick run down...

1 table, 13 columns.
Columns 1 - 12
are type text
collation is latin1_swedish_ci
Column 13
Set to mediumint autoincrement, primary key
collation is latin1_swedish_ci

So, I am searching with this:

$query = sprintf (
                "SELECT name,address,city,phone, category
                 FROM valley
                 WHERE CONCAT_WS(' ',keywords,category) LIKE '%$search%'
                 ORDER BY name LIMIT %d,%d",
                $start - 1,         
                $per_page + 1);     
}

The $search comes from a form. For example, if I enter 'books' in the form I get no results. However, if I enter 'Books' in the form, I get 5 results, which is what I would expect from my db.

I know this got a little long, but I wanted to show what I had checked and the outcome of everything.

If anyone has any ideas as to why this search is being case sensitive, I would love to hear them. Or alternatively if anyone has a workaround, that would also be greatly appreciated.

Jim

 

[towerbase]

I would guess that the table valley was created using the BINARY attribute to define some of your text columns. This would make them case sensitive.

Can you show us the exact SQL that was used to create the valley table?

Andrew
Hampshire, UK

 

[JimJx]

CREATE TABLE valley (
`Category` VARCHAR(25) not null ,
`Name` VARCHAR(50) not null ,
`Description` TEXT ,
`Contact` VARCHAR(20) not null ,
`Phone` TEXT(10) not null ,
`Fax` TEXT(10) ,
`Address` VARCHAR(25) ,
`City` VARCHAR(25) ,
`State` VARCHAR(2) ,
`ZipCode` MEDIUMINT ,
`Email` VARCHAR(50) ,
`URL` VARCHAR(50) ,
`Keywords` VARCHAR(100),
'ID' MEDIUMINT AUTOINCREMENT
}

 

[r937]

would you be interested in some suggested improvements to your table?

r937.com | rudy.ca

 

[JimJx]

Always interested in improvements/suggestions.....

 

[r937]

TEXT is intended for extremely large chunks of text, like chapters in a book

so where you have TEXT(10) you should be saying VARCHAR(10)

zipcode should be VARCHAR(10), not MEDIUMINT

and keywords should probably be in a separate one-to-many table

what did you use to produce your CREATE TABLE statement? because the last line is invalid in three ways --

'ID' MEDIUMINT AUTOINCREMENT

try running this: SHOW CREATE TABLE valley


r937.com | rudy.ca

 

[JimJx]

Honestly, I don't remember where I got that create table from, it has been a while.... But, I integrated your suggestions into the table.

Actually, I deleted the table and recreated it. I was hoping that that would get rid of the case sensitiveness... No joy....

Anyway, here is the way that I recreated it.....

CREATE TABLE valley (
Category VARCHAR(25) not null ,
Name VARCHAR(50) not null ,
Description VARCHAR (250) ,
Contact VARCHAR(20) not null ,
Phone VARCHAR(10) not null ,
Fax VARCHAR(10) ,
Address VARCHAR(25) ,
City VARCHAR(25) ,
State VARCHAR(2) ,
ZipCode VARCHAR(15) ,
Email VARCHAR(50) ,
URL VARCHAR(50) ,
Keywords VARCHAR(100),
ID MEDIUMINT AUTOINCREMENT PRIMARY KEY
)

 

[r937]

actually, if you ran exactly that, it fails on a syntax error

what aren't you telling us? why can't you do the SHOW CREATE TABLE?

r937.com | rudy.ca

 

[JimJx]

CREATE TABLE `valley` (\n `Category` varchar(25) NOT NULL default '',\n `Name` varchar(50) NOT NULL default '',\n `Description` varchar(250) default NULL,\n `Contact` varchar(30) NOT NULL default '',\n `Phone` varchar(10) NOT NULL default '',\n `Fax` varchar(10) default NULL,\n `Address` varchar(25) default NULL,\n `City` varchar(25) default NULL,\n `State` char(2) default NULL,\n `ZipCode` varchar(12) default NULL,\n `Email` varchar(50) default NULL,\n `URL` varchar(50) default NULL,\n `Keywords` varchar(100) default NULL,\n `ID` mediumint(9) NOT NULL auto_increment,\n PRIMARY KEY (`ID`)\n) ENGINE=MyISAM AUTO_INCREMENT=2099 DEFAULT CHARSET=latin1

 

[r937]

okay, i created the table, added some data, and ran your query from the first post

it was case-insensitive, sorry

r937.com | rudy.ca

 

[JimJx]

No need to apologize.... You are just covering all of the bases.

But that still begs the question: What is going on here? Have I stumbled across a bug or what???

 

[r937]

maybe try it without the CONCAT_WS?

if it is a bug, and you want to report it, you will have to make a reproducible test case, in other words, in addition to the CREATE TABLE you will need to provide INSERT statements as well, along with the mysql query (i.e. not the php query)

r937.com | rudy.ca

 

[Olavxxx]

Hi, for a quick fix, I guess you could do a lcase() / lower() on the mysql field and compare that to the strtolower() in php, for the comparison variable?

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[JimJx]

Since the DB already has the first letter of all of the keywords capitalized, I went with UCFIRST

Not an ideal solution, but I will have to live with it for now.....