SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PHP MYSQL query DB and ignore spaces and taxt case

    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. #2
    SitePoint Evangelist mrwooster's Avatar
    Join Date
    Jan 2006
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://php.net/manual/en/function.str-replace.php
    PHP Code:
    $str str_replace(' '''$string); 
    This will remove all the whitespace from the string $string

  3. #3
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    $usercode $_POST['usercode'];
    $usercode strtoupper(str_replace(' '''$usercode));
    $query "SELECT * FROM tblename WHERE code='$usercode'"
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    actually, string comparisons are case-insensitive by default

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by da manual
    By default, string comparisons are not case sensitive and use the current character set. The default is latin1 (cp1252 West European), which also works well for English.
    this from http://dev.mysql.com/doc/refman/5.1/...operators.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sketchgal View Post
    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.
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    I normally force strings to be stored in lower case in the database...
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    unless a string must be stored as entered by a user
    nice backpedal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    I'm not back pedaling from anything.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    I build commercial websites according to the specifications of my clients
    nice backpedal -- blame the client
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you see it as blaming the client then so be it. I don't have a problem with that

    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Da Manual also says - not always

    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).
    Ian Anderson
    www.siteguru.co.uk

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by siteguru View Post
    i scanned that page for "not always" and didn't find it

    could you elaborate?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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
    Ian Anderson
    www.siteguru.co.uk

  20. #20
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    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.

  21. #21
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hi Kalon,

    I'm curious; what advantages do you gain by lowering the case of all strings you save in the database?

    Personally I wouldn't do it, however I am interested in how you benefit from doing so.

    Cheers,

    Anthony.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  22. #22
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    Hi Kalon,

    I'm curious; what advantages do you gain by lowering the case of all strings you save in the database?

    Personally I wouldn't do it, however I am interested in how you benefit from doing so.

    Cheers,

    Anthony.
    if there is no technical reason or requirement (from client or whatever) to store the string exactly as entered then I store the string in lower case purely by choice and for the sake of consistancy.

    There might not be any advantages, but what I can say is that there have been no disadvantages either for me up till now.

    Storing strings in lower case has never prevented me from easily providing any ouputed data from the database according to the client's specifications.

    But as I said before, if the client asked me to store the data in a particular way and I saw problems in then providing their desired output, then I would mention it to them and discuss alternatives and/or better options for storing the data.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    if there is no technical reason or requirement (from client or whatever) to store the string exactly as entered
    so are you saying that you have to ask the client whether it's okay to destroy the case of surnames like McDonald, or do you make the decision not to destroy it on your own?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    only thing i can think of is for a slight speed increase when doing insensitive string comparison (which... mysql automatically does anyway, apparantly... learn something new every day)... even if it was sensitive, my impulse would be to store as-is (data integrity), and use functions for insensitive comparison (SELECT.... WHERE LOWER(field) = LOWER(str)), unless i knew for a fact that the table data would be of a specific format.

    in the OP's case...i'm assuming you're saying the codes are all uppercase, so rajug's code should work correctly.

    Off Topic:

    Which is faster, PHP strtoupper() or MYSQL UPPER() ? Or is there a difference? (theoretically they're both Big-O 'n' level functions)

  25. #25
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a function to reproduce that format so it's not an issue for me.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •