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:

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:

$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.. :)

Found a way, I think. This:

$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.

$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.

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

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

I am only using my scripts for personal use, not online. I don't care about good code or anything.

$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.

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.

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.