|
| PORTAIL | ANNUAIRE | ARTICLES | COMPARATEUR HÉBERGEURS | DEVIS | FORUMS | RÉDUCTEUR D'URL |
|
23/09/2007, 16h09
|
#1 (permalink) |
Messages: n/a
Hébergeur: |
Case sensitivity
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 |
|
|
23/09/2007, 18h42
|
#2 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
JimJx wrote:
> 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 > What's your CREATE TABLE look like? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
|
|
23/09/2007, 20h34
|
#3 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
> What's your CREATE TABLE look like?
> > -- > ================== > Remove the "x" from my email address > Jerry Stuckle > JDS Computer Training Corp. > jstuck...@attglobal.net > ================== 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 } |
|
|
|
23/09/2007, 21h13
|
#4 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
"JimJx" <webmaster@valleywebnet.com> schreef in bericht news:1190576047.676633.152430@r29g2000hsg.googlegr oups.com... >> What's your CREATE TABLE look like? >> >> -- >> ================== >> Remove the "x" from my email address >> Jerry Stuckle >> JDS Computer Training Corp. >> jstuck...@attglobal.net >> ================== > > 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 > } > maybe a "show full columns from valley" give more related info.... |
|
|
|
23/09/2007, 21h50
|
#5 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
On Sep 23, 4:13 pm, "Luuk" <l...@invalid.lan> wrote:
> "JimJx" <webmas...@valleywebnet.com> schreef in berichtnews:1190576047.676633.152430@r29g2000hsg.g ooglegroups.com... > > > > >> What's your CREATE TABLE look like? > > >> -- > >> ================== > >> Remove the "x" from my email address > >> Jerry Stuckle > >> JDS Computer Training Corp. > >> jstuck...@attglobal.net > >> ================== > > > 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 > > } > > maybe a "show full columns from valley" give more related info.... Field Type Collation Null Key Default Extra Privileges Comment Category varchar(25) latin1_swedish_ci select,insert,update,references Name varchar(50) latin1_swedish_ci select,insert,update,references Description text latin1_swedish_ci YES NULL select,insert,update,references Contact varchar(20) latin1_swedish_ci select,insert,update,references Phone tinytext latin1_swedish_ci select,insert,update,references Fax tinytext latin1_swedish_ci YES NULL select,insert,update,references Address varchar(25) latin1_swedish_ci YES NULL select,insert,update,references City varchar(25) latin1_swedish_ci YES NULL select,insert,update,references State char(2) latin1_swedish_ci YES NULL select,insert,update,references ZipCode mediumint(9) NULL YES NULL select,insert,update,references Email varchar(50) latin1_swedish_ci YES NULL select,insert,update,references URL varchar(50) latin1_swedish_ci YES NULL select,insert,update,references Keywords varchar(100) latin1_swedish_ci YES NULL select,insert,update,references ID int(11) NULL PRI NULL auto_increment select,insert,update,references |
|
|
|
24/09/2007, 00h10
|
#6 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
JimJx wrote:
> On Sep 23, 4:13 pm, "Luuk" <l...@invalid.lan> wrote: >> "JimJx" <webmas...@valleywebnet.com> schreef in berichtnews:1190576047.676633.152430@r29g2000hsg.g ooglegroups.com... >> >> >> >>>> What's your CREATE TABLE look like? >>>> -- >>>> ================== >>>> Remove the "x" from my email address >>>> Jerry Stuckle >>>> JDS Computer Training Corp. >>>> jstuck...@attglobal.net >>>> ================== >>> 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 >>> } >> maybe a "show full columns from valley" give more related info.... > > Field Type Collation Null Key Default Extra Privileges > Comment > Category varchar(25) latin1_swedish_ci > select,insert,update,references > Name varchar(50) latin1_swedish_ci > select,insert,update,references > Description text latin1_swedish_ci YES NULL > select,insert,update,references > Contact varchar(20) latin1_swedish_ci > select,insert,update,references > Phone tinytext latin1_swedish_ci > select,insert,update,references > Fax tinytext latin1_swedish_ci YES NULL > select,insert,update,references > Address varchar(25) latin1_swedish_ci YES NULL > select,insert,update,references > City varchar(25) latin1_swedish_ci YES NULL > select,insert,update,references > State char(2) latin1_swedish_ci YES NULL > select,insert,update,references > ZipCode mediumint(9) NULL YES NULL > select,insert,update,references > Email varchar(50) latin1_swedish_ci YES NULL > select,insert,update,references > URL varchar(50) latin1_swedish_ci YES NULL > select,insert,update,references > Keywords varchar(100) latin1_swedish_ci YES NULL > select,insert,update,references > ID int(11) NULL PRI NULL auto_increment > select,insert,update,references > Interesting. What collation is the connection using? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
|
|
24/09/2007, 00h47
|
#7 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
On Sep 23, 7:10 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> JimJx wrote: > > On Sep 23, 4:13 pm, "Luuk" <l...@invalid.lan> wrote: > >> "JimJx" <webmas...@valleywebnet.com> schreef in berichtnews:1190576047.676633.152430@r29g2000hsg.g ooglegroups.com... > > >>>> What's your CREATE TABLE look like? > >>>> -- > >>>> ================== > >>>> Remove the "x" from my email address > >>>> Jerry Stuckle > >>>> JDS Computer Training Corp. > >>>> jstuck...@attglobal.net > >>>> ================== > >>> 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 > >>> } > >> maybe a "show full columns from valley" give more related info.... > > > Field Type Collation Null Key Default Extra Privileges > > Comment > > Category varchar(25) latin1_swedish_ci > > select,insert,update,references > > Name varchar(50) latin1_swedish_ci > > select,insert,update,references > > Description text latin1_swedish_ci YES NULL > > select,insert,update,references > > Contact varchar(20) latin1_swedish_ci > > select,insert,update,references > > Phone tinytext latin1_swedish_ci > > select,insert,update,references > > Fax tinytext latin1_swedish_ci YES NULL > > select,insert,update,references > > Address varchar(25) latin1_swedish_ci YES NULL > > select,insert,update,references > > City varchar(25) latin1_swedish_ci YES NULL > > select,insert,update,references > > State char(2) latin1_swedish_ci YES NULL > > select,insert,update,references > > ZipCode mediumint(9) NULL YES NULL > > select,insert,update,references > > Email varchar(50) latin1_swedish_ci YES NULL > > select,insert,update,references > > URL varchar(50) latin1_swedish_ci YES NULL > > select,insert,update,references > > Keywords varchar(100) latin1_swedish_ci YES NULL > > select,insert,update,references > > ID int(11) NULL PRI NULL auto_increment > > select,insert,update,references > > Interesting. What collation is the connection using? > > -- > ================== > Remove the "x" from my email address > Jerry Stuckle > JDS Computer Training Corp. > jstuck...@attglobal.net > ================== latin1_swedish_ci |
|
|
|
24/09/2007, 11h16
|
#8 (permalink) |
|
Messages: n/a
Hébergeur: |
Re: Case sensitivity
On Sep 24, 1:47 am, JimJx <webmas...@valleywebnet.com> wrote:
> On Sep 23, 7:10 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote: > > > > > JimJx wrote: > > > On Sep 23, 4:13 pm, "Luuk" <l...@invalid.lan> wrote: > > >> "JimJx" <webmas...@valleywebnet.com> schreef in berichtnews:1190576047.676633.152430@r29g2000hsg.g ooglegroups.com... > > > >>>> What's your CREATE TABLE look like? > > >>>> -- > > >>>> ================== > > >>>> Remove the "x" from my email address > > >>>> Jerry Stuckle > > >>>> JDS Computer Training Corp. > > >>>> jstuck...@attglobal.net > > >>>> ================== > > >>> 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 > > >>> } > > >> maybe a "show full columns from valley" give more related info.... > > > > Field Type Collation Null Key Default Extra Privileges > > > Comment > > > Category varchar(25) latin1_swedish_ci > > > select,insert,update,references > > > Name varchar(50) latin1_swedish_ci > > > select,insert,update,references > > > Description text latin1_swedish_ci YES NULL > > > select,insert,update,references > > > Contact varchar(20) latin1_swedish_ci > > > select,insert,update,references > > > Phone tinytext latin1_swedish_ci > > > select,insert,update,references > > > Fax tinytext latin1_swedish_ci YES NULL > > > select,insert,update,references > > > Address varchar(25) latin1_swedish_ci YES NULL > > > select,insert,update,references > > > City varchar(25) latin1_swedish_ci YES NULL > > > select,insert,update,references > > > State char(2) latin1_swedish_ci YES NULL > > > select,insert,update,references > > > ZipCode mediumint(9) NULL YES NULL > > > select,insert,update,references > > > Email varchar(50) latin1_swedish_ci YES NULL > > > select,insert,update,references > > > URL varchar(50) latin1_swedish_ci YES NULL > > > select,insert,update,references > > > Keywords varchar(100) latin1_swedish_ci YES NULL > > > select,insert,update,references > > > ID int(11) NULL PRI NULL auto_increment > > > select,insert,update,references > > > Interesting. What collation is the connection using? > > > -- > > ================== > > Remove the "x" from my email address > > Jerry Stuckle > > JDS Computer Training Corp. > > jstuck...@attglobal.net > > ================== > > latin1_swedish_ci There is your answer. ci = case insensitive |
|
|
 |
| Outils de la discussion | |
|
|
