Case insensitive PHP/MySQL queries

[sasha.yee]

Hi

 

I've been having a lot of trouble with just the tiny bit of following code:

$strNewEmail = strtoupper($strNewEmail);

$sql = "SELECT * FROM tblPartner_EmailSubscription WHERE UPPER(salesManagerEmail) LIKE '$strNewEmail' OR UPPER(salesContactEmail) LIKE '$strNewEmail';";
$result = @mysql_query($sql);
while ($row = @mysql_fetch_array($result)){
if ( ($row["SalesManagerEmail"] == $strNewEmail) ){
// sales contact added
$salesContactName = $row["SalesManager"];
@mysql_close($DatabaseConnection);
return "FAILED";
}else if ( ($row["SalesContactEmail"] == $strNewEmail) ){
// sales contact added
$salesContactName = $row["SalesContact"];
@mysql_close($DatabaseConnection);
return "FAILED";
}
}
return "PASS";

 

While I can punch the SQl query into PhpMyAdmin and get a result, if I try the script by itself it doesn't work as intended, coming up without any results for the query.

 

Could anyone please explain where I am going wrong?

 

Thanks

 

 

Sasha

 

[cooldude832]

add to $result or die(mysql_error());

[trq]

It doesn't help that your using the error supressor and also never checking your query to see if it succeeds or not.

[sasha.yee]

Thank you for the quick reply. I've removed the error suppressors, and added the check to see if anything is happening with the query, but to no avail.

 

I've just tested it then, and I've found that no matter the way that the form data is entered (it could be [email protected] or [email protected], or any variation of that) and it won't work.

[cooldude832]

remove OR and try ||

I believe inside the WHERE part you can use operators like &&, || == != etc

[mkoga]

OR is correct.

 

add to $result or die(mysql_error());

 

cooldude832's suggestion returned nothing?

 

 

[cooldude832]

Really? I've always used comparison operators (||,&&,==,!=) for my WHERE part logic with no issues you might as well try and just to see, it can't hurt.

[cooldude832]

is it returning fail? or nothign at all from the function you failed to write out completly

[sasha.yee]

unfortunately CoolDude's suggestion did not return any errors.

 

If you would like to see the entire function, here it is.

 

function ValidateExistingEmail($strNewEmail){
global $salesContactName;

$DatabaseConnection = mysql_connect("mysql.imagine-hosting.com", "****", "****") or die(mysql_error());
mysql_select_db("netcomm", $DatabaseConnection) or die(mysql_error());
$strNewEmail = strtoupper($strNewEmail);

$sql = "SELECT * FROM tblPartner_EmailSubscription WHERE UPPER(salesManagerEmail) = '$strNewEmail' OR UPPER(salesContactEmail) = '$strNewEmail';";
$result = mysql_query($sql, $DatabaseConnection) or die(mysql_error());
while ($row = mysql_fetch_array($result)){
if ( ($row["SalesManagerEmail"] == $strNewEmail) ){
	    // sales contact added
            $salesContactName = $row["SalesManager"];
		mysql_close($DatabaseConnection);
		return "FAILED";
	}else if ( ($row["SalesContactEmail"] == $strNewEmail) ){
            // sales contact added
            $salesContactName = $row["SalesContact"];
            mysql_close($DatabaseConnection);
	return "FAILED";
	}
}
return "PASS";
}

 

The operators aren't going to do anything. As I said before, I tried this query in phpmyadmin, and there is no problem there. By substituting $strNewEmail for a value that I know is in the database, there is always a row of data returned.

[cooldude832]

show the call to this function where the $strNewEmail is initialized from

[suma237]

already you write a code to compare the conditions then why you again used if condition

[cooldude832]

that confused me also, a better method is to say if the num results is >0 then return "true"