Possible VARCHAR issue, possible collation issue? Help!!

Hi,

I hope someone can help me with the issue I am experiencing.

If it's relevant I'm Running Apache 2.0.58 on windows XP, PHP 5.1.4 and
MySQL 5.0.22...not up to date I know but fairly current and more than
adequate for my testing server.

The problem I am experiencing is probably down to my inexperience with MySQL
but I would appreciate some guidance with this.

I have created several HTML forms to query my MySQL database using PHP, all
is good except for one field which does not return the values expected. The
field in the table is set as VARCHAR(20) since it needs to store part
numbers and these are in the format AA100-ZZ9999 (with some exceptions which
start with 3 alpha characters followed by 6 numeric). When I search for 100
I get no results but when I search for AA the correct results are returned.
The Collation is ascii_bin is this the problem??

Also is there a way of making PHP forms acting on a MySQL database not case
specific?

I hope someone can help me.

Thanks in advance.



Regards,

Andy.

Re: Possible VARCHAR issue, possible collation issue? Help!!

On Sun, 27 May 2007 20:59:48 GMT, "Andy"
wrote:

>I have created several HTML forms to query my MySQL database using PHP, all
>is good except for one field which does not return the values expected. The
>field in the table is set as VARCHAR(20) since it needs to store part
>numbers and these are in the format AA100-ZZ9999 (with some exceptions which
>start with 3 alpha characters followed by 6 numeric). When I search for 100
>I get no results but when I search for AA the correct results are returned.

What do your queries look like? Offhand I suppose you're probably
wanting a

WHERE field LIKE '%100%'

type query, but using something else.

>Also is there a way of making PHP forms acting on a MySQL database not case
>specific?

In terms of searching, this is the default behavior. When you persist
data, it will be saved using whatever case was provided by the user,
but when you query against the data, your queries will be
case-insensitive by default.

hth
Bulworth : funha@fung.arg | My email address is ROT13 encoded, decode to mail.
--------------------------|--------------------------------- ------------------
Brillant Developer && DBA | Public releases now @