Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Apr 2012
    Posts
    89
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Comparing $_POST and MySQL Results - Case Insensitive

    Hey guys,

    How would I check if something is already in the database, when trying to post something. I have the following database:

    Code:
    CREATE TABLE `doc_items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(75) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
    I am using the following code to check if a value already exists:

    PHP Code:
    $result mysql_query("SELECT name FROM doc_items WHERE name = '".$_POST['name']."'");
    $row mysql_fetch_array($result);

    if(
    $row['name'] == $_POST['name'])
        
    $errors[] = "- This category already exists!"
    This works only when there is an exact match, for example, when the value in the database is "google" and I submit "google" into the form, it will return that it already exists. But if I input Google, it won't return that it already exists.

    I have tried using "LOWER(naam)" in the query, but also that doesn't work. How would I be able to check if a value (case insensitive) already exists in the database, without having an extra field for example that inserts everything in lowercase..

  • #2
    New Coder
    Join Date
    Apr 2012
    Posts
    89
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Found a way, I think. This:

    PHP Code:
    $result mysql_query("SELECT name FROM doc_items WHERE name = '".$_POST['name']."'");
    $row mysql_fetch_array($result);

    $name1 $_POST['name'];
    $name2 $row['name'];

    $comparing substr_compare("$name1","$name2",0,TRUE);

    if(
    $comparing == '0')
        
    $errors[] = "- This category already exists!";

    if(
    $comparing == '-1')
        
    $errors[] = "- This category already exists!"
    Example. "google" is already in the database. When I submit "google", it returns a "0". Whenever I type "Google" or "GOOGLE", it returns "-1". Can this work or can I get into problems with this somehow?

    I am only using my scripts for personal use, not online. I don't care about good code or anything, I care about code that works. So if this works, I will use it, if not, I will try to find a better solution.

  • #3
    Regular Coder Redcoder's Avatar
    Join Date
    May 2012
    Location
    /dev/null
    Posts
    332
    Thanks
    2
    Thanked 47 Times in 46 Posts
    $name=strtolower($_POST['name']) and then compare using $name will work ok.

    Another...maybe take a look at this dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

    Also consider jumping to PDO or if not mysqli later instead of using the mysql database driver.

  • #4
    New to the CF scene
    Join Date
    Jul 2012
    Location
    Berlin
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's not in the problem scope, but you should reconsider your code and use PDO, because as is, your code is in danger to be hacked

  • #5
    New Coder
    Join Date
    Apr 2012
    Posts
    89
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mimosatti View Post
    It's not in the problem scope, but you should reconsider your code and use PDO, because as is, your code is in danger to be hacked
    Quote Originally Posted by Hardist View Post
    I am only using my scripts for personal use, not online. I don't care about good code or anything.
    Quote Originally Posted by Redcoder View Post
    $name=strtolower($_POST['name']) and then compare using $name will work ok.
    That's just the thing, it doesn't. Because in my database there will be "Google" sometimes, with uppercase. That's the thing.

    The second code I have posted seems to be working fine, so I am going to stick with that. Thanks for the reply anyway! I appreciate it.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Don't use a substr_compare. That works on partials similar to a contains type which is probably not what you want.
    Simply use strcasecmp as your function.
    PHP Code:
    if (strcasecmp($name1$name2) == 0)
    {
        print 
    ' these strings are insensitively the same.';

    Controlling the case won't work either, not with what you are doing. If the name column is distinguished by the case sensitivity, specifying a control of the case on either or both would effectively massage all the data so as all cases match defeating entirely what you are doing here (otherwise you just need to select the data as dbms' are already case insensitive). You can use collation control for case sensitivity at a sql level.


  •  

    Posting Permissions

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