Sorry, this might be a newbie question, but here it is:

I want to check user input for duplicates already in the database. For example, if the user inputs "foobar" and "foobar" is already in the database, I want to return an error.

This is easy.

I also want to return an error if the user types in "fooBar" or "foobar " or "FoObAr" or "Foo Bar." How do I check that with a MySQL query?

Thanks so much in advance.

do you have a php script to handle the form yet?? if you you do can you post it?? it'll be easier to help..

It's everywhere... but, here are some pertinent parts:

$name = MySQL::SecureData(isFilledOut(checkDuplicateName($_POST['name']), 'program', $college_id));

function checkDuplicateName ($name, $type, $upperID = 0, $user = 0) {
global $db;
	switch ($type) {
		case 'program':
			$db->ExecuteSQL("SELECT * FROM cl_user_programs WHERE programName = '$name' AND collegeID = '$upperID'");
			if ($db->getNumRecordsReturned != 0)
				error('A program with that name is already associated with this college! Please try again with a different name.');
			break;
		case 'scholarship':
			$db->ExecuteSQL("SELECT * FROM cl_user_scholarships WHERE scholarshipName = '$name' AND collegeID = '$upperID'");
			if ($db->getNumRecordsReturned != 0)
				error('A scholarship with that name is already associated with this college! Please try again with a different name.');
			break;
		case 'college':
			$db->ExecuteSQL("SELECT * FROM cl_user_colleges WHERE collegeName = '$name' AND user = '$user'");
			if ($db->getNumRecordsReturned != 0)
				error('A college with that name is already in your list of colleges! Please try again with a different name.');
		default:
			error();
}

This should work, but I didn't test it. The MySQL REPLACE() will replce spaces with empty string and using the LIKE compare will make it case insensitive.

PHP Code:

function checkDuplicateName ($name, $type, $upperID = 0, $user = 0) {
global $db;
 switch ($type) {
  case 'program':
   $db->ExecuteSQL("SELECT * FROM cl_user_programs WHERE collegeID = '$upperID' HAVING REPLACE(programName, ' ', '') LIKE REPLACE('$name', ' ', '')");
   if ($db->getNumRecordsReturned != 0)
    error('A program with that name is already associated with this college! Please try again with a different name.');
   break;
  case 'scholarship':
   $db->ExecuteSQL("SELECT * FROM cl_user_scholarships WHERE collegeID = '$upperID' HAVING REPLACE(scholarshipName, ' ', '') LIKE REPLACE('$name', ' ', '')");
   if ($db->getNumRecordsReturned != 0)
    error('A scholarship with that name is already associated with this college! Please try again with a different name.');
   break;
  case 'college':
   $db->ExecuteSQL("SELECT * FROM cl_user_colleges WHERE user = '$user' HAVING REPLACE(collegeName, ' ', '') LIKE REPLACE('$name', ' ', '')");
   if ($db->getNumRecordsReturned != 0)
    error('A college with that name is already in your list of colleges! Please try again with a different name.');
  default:
   error();
} 


Will definitely check out this solution. Thanks so much!

yuechen,
After looking at your code, it seems you will be interested in Full text search feature

@prasanthmj, It looks interesting. Thanks for the heads up!

@mgraphic, THANK YOU! I'm still a newbie to SQL commands and it looks like I have HAVING and REPLACE under my belt! Your solution worked beautifully.

I'd have thought that "select count(*) " would be faster than "select *" .

Originally Posted by PaulW

I'd have thought that "select count(*) " would be faster than "select *" .

I don't know for sure, but I know with "SELECT COUNT(*)" would still require you to return the result in an array because it would always return a row (and you could not use mysql_num_rows). One thing I would do is select only the primary id field rather than all (*) fields to reduce the amount of memory used to do a simple count.

That's an excellent suggestion! Haha I didn't pay much attention to anything besides workability in this prototype code, so memory overusages are everywhere. I shall look into SELECT COUNT as I've never heard of it before. Thanks again!

Does MySQL have the equivalent of Oracle's SELECT COUNT(1) ?

And yes, select count(*) is bad - grounds for dismissal if you're working for me - I didn't want to complicate the initial answer.