RE: [PHP-DB] Case sensitive query
Hey Ron Sure can with the BINARY keyword http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html Bastien> From: [EMAIL PROTECTED]> To: php-db@lists.php.net> Date: Thu, 6 Mar 2008 22:45:09 -0500> Subject: [PHP-DB] Case sensitive query> > Is it possible to do a case sensitive query to mySQL? What would an> example syntax look like? Ron> > > -- > PHP Database Mailing List (http://www.php.net/)> To unsubscribe, visit: http://www.php.net/unsub.php> _
[PHP-DB] Case sensitive query
Is it possible to do a case sensitive query to mySQL? What would an example syntax look like? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
better to use the sql UPPER/LOWER and keep your variable values the same Except that they should be escaping the variable to make it db-safe so that will change it ... so if you're going to do that, might as well do this: $safe_stringinput = _escape_string(strtoupper($stringinput); One trick is to force the case in your comparison: $ucstringinput = strtoupper($stringinput); $qry = "select * from sometable where upper(address) like '%$ucstringinput%'" Didn't think LIKE was case sensitive, but regardless... forcing upper or lowercase in your comparison doesn't affect output but will make your case sensitivity issue moot. HTH -TG = = = Original message = = = Hi there everyone, I have a little problem, I have a search where people can search the address of a property BUT the search is case sensitive, I don~t WANT it to be. I~m using MySQL and PHP and I generally use something like WHERE address LIKE ~%$stringinput%~ which works with the numbers ONLY, but when I add the address if I don~t put a capital infront of each part of the address it won~t show up. Any ideas how I can make it case INSENSITIVE? (Think that~s the correct phrase). Any help would really be appreciated. Thanks everyone Regards Chris Payne -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
Check to see if you have the Binary option on the field. That would make searches case sensitive. If it is a binary field, you need to drop the binary option. On Aug 24, 2005, at 4:53 PM, Chris Payne wrote: Hi there everyone, I have a little problem, I have a search where people can search the address of a property BUT the search is case sensitive, I don’t WANT it to be. I’m using MySQL and PHP and I generally use something like WHERE address LIKE ‘%$stringinput%’ which works with the numbers ONLY, but when I add the address if I don’t put a capital infront of each part of the address it won’t show up. Any ideas how I can make it case INSENSITIVE? (Think that’s the correct phrase). Any help would really be appreciated. Thanks everyone Regards Chris Payne -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005 -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
better to use the sql UPPER/LOWER and keep your variable values the same Bastien From: <[EMAIL PROTECTED]> To: CC: <[EMAIL PROTECTED]> Subject: Re: [PHP-DB] Case sensitive Date: Wed, 24 Aug 2005 14:29:56 -0400 One trick is to force the case in your comparison: $ucstringinput = strtoupper($stringinput); $qry = "select * from sometable where upper(address) like '%$ucstringinput%'" Didn't think LIKE was case sensitive, but regardless... forcing upper or lowercase in your comparison doesn't affect output but will make your case sensitivity issue moot. HTH -TG = = = Original message = = = Hi there everyone, I have a little problem, I have a search where people can search the address of a property BUT the search is case sensitive, I don~t WANT it to be. I~m using MySQL and PHP and I generally use something like WHERE address LIKE ~%$stringinput%~ which works with the numbers ONLY, but when I add the address if I don~t put a capital infront of each part of the address it won~t show up. Any ideas how I can make it case INSENSITIVE? (Think that~s the correct phrase). Any help would really be appreciated. Thanks everyone Regards Chris Payne -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
One trick is to force the case in your comparison: $ucstringinput = strtoupper($stringinput); $qry = "select * from sometable where upper(address) like '%$ucstringinput%'" Didn't think LIKE was case sensitive, but regardless... forcing upper or lowercase in your comparison doesn't affect output but will make your case sensitivity issue moot. HTH -TG = = = Original message = = = Hi there everyone, I have a little problem, I have a search where people can search the address of a property BUT the search is case sensitive, I don~t WANT it to be. I~m using MySQL and PHP and I generally use something like WHERE address LIKE ~%$stringinput%~ which works with the numbers ONLY, but when I add the address if I don~t put a capital infront of each part of the address it won~t show up. Any ideas how I can make it case INSENSITIVE? (Think that~s the correct phrase). Any help would really be appreciated. Thanks everyone Regards Chris Payne -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
Perhaps its a collatioon issue? Are you using the same characterset for both? Bastien From: Micah Stevens <[EMAIL PROTECTED]> To: php-db@lists.php.net Subject: Re: [PHP-DB] Case sensitive Date: Wed, 24 Aug 2005 11:26:38 -0700 Not sure here, as according to my experience, and the MySQL docs, SQL pattern matching (as you would use with the 'LIKE' syntax) is case-insensitive. From the docs: "In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. " http://dev.mysql.com/doc/mysql/en/pattern-matching.html If this for some reason is different on your server, another route (although likely slower) is a REGEXP search.. -Micah On Wednesday 24 August 2005 1:53 pm, Chris Payne wrote: > Hi there everyone, > > > > I have a little problem, I have a search where people can search the > address of a property BUT the search is case sensitive, I donât WANT it to > be. Iâm using MySQL and PHP and I generally use something like WHERE > address LIKE â%$stringinput%â which works with the numbers ONLY, but when I > add the address if I donât put a capital infront of each part of the > address it wonât show up. Any ideas how I can make it case INSENSITIVE? > (Think thatâs the correct phrase). > > > > Any help would really be appreciated. > > > > Thanks everyone > > > > Regards > > > > Chris Payne -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
In fact, by issuing the following query: SELECT "TEST" LIKE "test"; you can prove this to yourself, if it's case insensitive, it will return true (1), otherwise false (0). -Micah On Wednesday 24 August 2005 1:53 pm, Chris Payne wrote: > Hi there everyone, > > > > I have a little problem, I have a search where people can search the > address of a property BUT the search is case sensitive, I don’t WANT it to > be. I’m using MySQL and PHP and I generally use something like WHERE > address LIKE ‘%$stringinput%’ which works with the numbers ONLY, but when I > add the address if I don’t put a capital infront of each part of the > address it won’t show up. Any ideas how I can make it case INSENSITIVE? > (Think that’s the correct phrase). > > > > Any help would really be appreciated. > > > > Thanks everyone > > > > Regards > > > > Chris Payne -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive
Not sure here, as according to my experience, and the MySQL docs, SQL pattern matching (as you would use with the 'LIKE' syntax) is case-insensitive. From the docs: "In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. " http://dev.mysql.com/doc/mysql/en/pattern-matching.html If this for some reason is different on your server, another route (although likely slower) is a REGEXP search.. -Micah On Wednesday 24 August 2005 1:53 pm, Chris Payne wrote: > Hi there everyone, > > > > I have a little problem, I have a search where people can search the > address of a property BUT the search is case sensitive, I don’t WANT it to > be. I’m using MySQL and PHP and I generally use something like WHERE > address LIKE ‘%$stringinput%’ which works with the numbers ONLY, but when I > add the address if I don’t put a capital infront of each part of the > address it won’t show up. Any ideas how I can make it case INSENSITIVE? > (Think that’s the correct phrase). > > > > Any help would really be appreciated. > > > > Thanks everyone > > > > Regards > > > > Chris Payne -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Case sensitive
Hi there everyone, I have a little problem, I have a search where people can search the address of a property BUT the search is case sensitive, I don’t WANT it to be. I’m using MySQL and PHP and I generally use something like WHERE address LIKE ‘%$stringinput%’ which works with the numbers ONLY, but when I add the address if I don’t put a capital infront of each part of the address it won’t show up. Any ideas how I can make it case INSENSITIVE? (Think that’s the correct phrase). Any help would really be appreciated. Thanks everyone Regards Chris Payne -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005
Re: [PHP-DB] Case sensitive search
Rosen wrote: Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 "a" 2 "A" When I perform "select * from test where data='a' " - it return me both rows. http://dev.mysql.com/doc/mysql/en/Case_Sensitivity_Operators.html Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Case sensitive search
Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 "a" 2 "A" When I perform "select * from test where data='a' " - it return me both rows. By default in MySQL comparing of strings is case-insensitive. How can I perform a case sensitive search in text fields ? Tnanks in advance, Rosen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
