PHP MYSQL query DB and ignore spaces and taxt case

PHP
1

Hi

I’m looking for some help with this please if it can be done.
What I have is a form which the user enters a code and the script check the db to see if it exists and then directs the user to a page. That part works fine but only if the user enters the code exactly as its stored in the db.
for example if the code is WY09VGM in the db then thats exactly what the user has to enter. If they enter WY09 VGM its not picked up same goes for wy09vgm.

Is there a way I can set this up so that the script will ignore spaces and text case when it runs the search on the db?

Any help as always much appreciated

Thanks in advance.

2

http://php.net/manual/en/function.str-replace.php


$str = str_replace(' ', '', $string);

This will remove all the whitespace from the string $string

3

Well IMHO, it is good to ask the users to enter exact code. If you have stored the code in the DB in uppercase then you have to replace all the occurrences of spaces and covert them to uppercase before you use it in the query.


$usercode = $_POST['usercode'];
$usercode = strtoupper(str_replace(' ', '', $usercode));
$query = "SELECT * FROM tblename WHERE code='$usercode'";
4

actually, string comparisons are case-insensitive by default

:slight_smile:

5

Depends on the Mysql database configuration surely? Otherwise why would they offer separate case-sensitive and case-INsensitive optoins when first setting up the database?

6

this from http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

7

I would go with rajug’s solution.

I normally force strings to be stored in lower case in the database and then convert any user input strings to lowercase for any case-insensitive comparisons. I then use php functions to format, if needed, any retrieved strings before displaying them in the browser.

This way if your code is in a class method, script or whatever you can transfer it from one application to another and not have to worry about how the database might or might not be configured regarding how it does its comparisons by default. You’re telling it what to do regardless of how it is setup by default.

8

you mean like first and last names? so how do you avoid insulting clients when you display their name as mcdonald instead of McDonald?

oh, wait, i see how you do it…

I then use php functions to format, if needed, any retrieved strings before displaying them in the browser.
neat trick

:cool:

9

unless a string must be stored as entered by a user, I store everything by default in lowercase and then bend it, shape it, colour it, decorate it or whatever using whatever method (php functions, sql formatting, css etc) is best suited before displaying it in the browser after retrieval from the db.

10

nice backpedal :smiley:

11

I’m not back pedaling from anything.

I just posted how I normally do things and that I would go with rajug’s solution.

12

sure you are

you gave a blanket statement, and then had to qualify it when i gave a pretty obvious example of where it falls down go boom

and if you do go with rajug’s solution as you say you would, storing everything in upper case, then you still have the same problem, destroying information like the proper case of a surname

:smiley: :smiley:

13

99% of times I have been able to use php functions to get the formatting I need after storing strings in lower case.

I haven’t destroyed anything and none of my clients have ever complained to me about my code destroying any of their users’ data.

I build commercial websites according to the specifications of my clients :slight_smile:

14

nice backpedal – blame the client :smiley:

15

if you see it as blaming the client then so be it. I don’t have a problem with that :slight_smile:

If my client wants the data stored in a particular way, then I will do so every time unless there is some technical reason for not doing so which I would discuss with them.

At the end of the project I want them to pay my fee :slight_smile:

16

that’s all fine and dandy, and once again you’re making a solid effort to get the last word, but i would like to turn the thread back over to its original topic…

… which was how to do case-insensitive comparisons

(given that removing spaces from the search string might be needed and is easily accomplished)

my point is that mysql performs comparisons in a case-insensitive manner right out of the box, so there’s nothing special that needs to be done

furthermore, distorting the user’s origial data, by storing it either in lowercase (as you said you do) or in uppercase (as rajug suggested) is harmful

advice to the original poster: just don’t do what these guys are telling you, store all data exactly as entered

17

Da Manual also says - not always :wink:

As to the original issue - I think in certain circumstances then forcing the user input is fine. For example regarding a ZIP/post code (which is what the original example looks like).

18

i scanned that page for “not always” and didn’t find it

could you elaborate?

19

Since you said the search is case-insensitive by default, and posted a manual page, I’ve simply responded with a circumstance (search operator) where it CAN be case-sensitive, and posted the corresponding manual page :wink:

20

my point is that mysql performs comparisons in a case-insensitive manner right out of the box, so there’s nothing special that needs to be done

that’s fine :slight_smile:

I didn’t say there was anything wrong with that.

I just posted how I would do it (which is basically along the lines of rajug’s solution) and the reasons for doing so.

Now if you think what I posted is a load of rubbish, I don’t have an issue with that at all because it doesn’t change the way I do things and you are entitlled to your opnion.

storing everything in lower case is not harmful since I can reformat it to the clients specificatons during output or if it doesn’t need to be stored exactly as the user entered it.