RE: [PHP-DB] Case sensitive query

[Bastien Koert]

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

[Ron Piggott]

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

[Philip Hallstrom]

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

[Brent Baisley]

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

[Bastien Koert]

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

[tg-php]

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

[Bastien Koert]

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

[Micah Stevens]

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

[Micah Stevens]

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

[Chris Payne]

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

[Doug Thompson]

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

[Rosen]

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

Re: [PHP-DB] case to comment, please

[Stuart]

Nabil wrote:
1-the problem is that the 30 second of execution time expired before i can
send even 200 records.
http://php.net/set_time_limit

Personally I'd call set_time_limit once for each iteration of the loop 
with a sensible timeout like 30 seconds. Alternatively you can call it 
once at the start of the script with a timeout of 0 to let it run as 
long as it needs to.

2- in case of not connection of the remote page, and i did not get the $var1
printed  ($data == $var1) then i have to re submit this record.
The for loop doesn't make much sense since $var1/2/3 don't change with 
each iteration, but assuming $i is used to select the record to send 
something like this should do the trick...

for ($i=0 ; $i
I'd suggest you add something to ensure you don't retry the same record 
forever, but I'll leave that as an exercise for the reader :)

--
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] case to comment, please

[Nabil]

I have the following example case:
1- More than 1000 record in my MySQL database.
2- I have to submit those record via HTTP GET or POST method.
3- I  have to read the confirmation message that will be printed on the
remote page showing me that the vars have been inserted in the remote
database.
4- of course, I have a limitation of executing time of 30 seconds and i
should not modify the php.ini

i did the following scenario :

http://anotherwebserver/url2.php?var1=$var1$var2=$var2&var3=$var3";,
"r") ;
$data = fread($handle, 11);
fclose($handle);
if ($data == $var1) return true; else return false;
}
for ($i=0 ; $i

1-the problem is that the 30 second of execution time expired before i can
send even 200 records.
2- in case of not connection of the remote page, and i did not get the $var1
printed  ($data == $var1) then i have to re submit this record.

Regards

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] case

[Paul Burney]

on 6/11/02 10:15 AM, James Kupernik at [EMAIL PROTECTED] appended the
following bits to my mbox:

> I want to test a value that was typed into a form, but I don't want the case
> to be a problem, how can I get around this??

This isn't a database related question, so it should probably be posted to
PHP General:



That said, you can use a case insensitive regular expression to do what you
want, for example:

if (preg_match('~^ma$~i',$ShipState)) { // do something; }

You can read up at:



HTH.

Sincerely,

Paul Burney






-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] case

[Martin Clifford]

Use strtolower() on all entries from the form, so you can compare them (in lowercase) 
without worry about whether the user entered "MA", "ma", "Ma", or "mA".

Martin

>>> "James Kupernik" <[EMAIL PROTECTED]> 06/11/02 10:15AM >>>
I want to test a value that was typed into a form, but I don't want the case
to be a problem, how can I get around this??

This is what I have now:

function calculate_tax($OrderedByState,$ShipState)
{
 global $totalPrice;
 //tax calculation
 $taxRate = "0.05";
 if ($OrderedByState=="MA" && $ShipState=="MA") {
$taxcost=$taxRate*$totalPrice;
 } else if ($OrderedByState=="MA" && $ShipName != "" && $ShipState!="MA") {
$taxcost= "0.00";
 } else if ($OrderedByState=="MA" && $ShipState=="") {
$taxcost=$taxRate*$totalPrice;
 } else if ($ShipState=="MA") {
$taxcost=$taxRate*$totalPrice;
 } else {
$taxcost= "0.00";
 }

 return $taxcost;
}


Thanks for any help!



-- 
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-DB] case

[James Kupernik]

I want to test a value that was typed into a form, but I don't want the case
to be a problem, how can I get around this??

This is what I have now:

function calculate_tax($OrderedByState,$ShipState)
{
 global $totalPrice;
 //tax calculation
 $taxRate = "0.05";
 if ($OrderedByState=="MA" && $ShipState=="MA") {
$taxcost=$taxRate*$totalPrice;
 } else if ($OrderedByState=="MA" && $ShipName != "" && $ShipState!="MA") {
$taxcost= "0.00";
 } else if ($OrderedByState=="MA" && $ShipState=="") {
$taxcost=$taxRate*$totalPrice;
 } else if ($ShipState=="MA") {
$taxcost=$taxRate*$totalPrice;
 } else {
$taxcost= "0.00";
 }

 return $taxcost;
}


Thanks for any help!



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] CASE tool

[Adv. Systems Design]

Hi:

Anyone offer recommendations for a CASE/E-R
modeling/Data dictionary tool that can be used with
MySQL?

Thanks

Luis

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. 
http://im.yahoo.com

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] case-insensitive select?

[eon]

i use ...

SELECT *, UPPER(name) AS testfield FROM testtable ORDER BY testfield

hope it helps.




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] case insensitive search

[Doug Semig]

You could use the UPPER function.  Fortunately you're using PostgreSQL,
which conforms with much of the ANSI standard.  UPPER and LOWER are
implementations of the fold functions required by ANSI SQL.  This would be
the most standard way to solve your problem (with the notable exception of
support for this on MySQL, don't get me started on how brain dead MySQL is
about string comparisons...though it is a tiny little bit better with its
glaringly non-standard BINARY keyword).

You could alternatively use the much older ~* syntax that PostgreSQL
inherited from its ancestors.  But that's completely non-standard, and
there's only one reason to use it since PostgreSQL has the UPPER function
and a LIKE predicate that very closely conforms to the ANSI SQL standard.
That reason is if you want to use regex-like pattern specifiers instead of
SQL-like pattern specifiers.

If you're running a brand-spanking-new 7.1 (beta), you could use the
popular (but non-standard, as far as I can tell--at least I couldn't find
any mention of it in ANSI SQL '92 and I haven't looked at '99 yet) ILIKE.
(Actually, '92 gives ILIKE reserved word status, but does not specify
anything else for it.)

To sum it up, try these:

... WHERE UPPER(blah) LIKE UPPER('%blah%')
... WHERE blah ~* '*blah*'   (Note the regex-like pattern specifiers)
... WHERE blah ILIKE '%blah%'   (Only on the next version)

Doug


At 06:36 PM 2/10/01 +1000, Cameron wrote:
>im trying to do a case insensitive search in pgsql with a "WHERE blah
>LIKE '%blah%'"
>
>the problem is that currently if something contains Blah not just blah
>it wont return that. suggestion? i know i can suck out every row and
>then eregi them but that is majorly slow and inefficent . . .
>
>Cameron



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] case insensitive search

[Cameron]

im trying to do a case insensitive search in pgsql with a "WHERE blah
LIKE '%blah%'"

the problem is that currently if something contains Blah not just blah
it wont return that. suggestion? i know i can suck out every row and
then eregi them but that is majorly slow and inefficent . . .

Cameron


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]