I'm really hoping someone here can help me out with this one...
I'm trying to search through a table via php but having difficulty with case - The problem is the data I'm searching through is encrypted (aes_encrypt) and it doesn't seem to matter what I do the search is case sensitive - I want the search to ignore case.
Here's the gist of what I have:
$query = "SELECT id, username, aes_decrypt(lastname,'$vark') AS lastname FROM $dbtable WHERE lastname=aes_encrypt('$lastnameSearch','$vark') ORDER BY id DESC" ;
It doesn't seem to matter what I do, I'll only get a match when the value of $lastname matches the case of the original entry. I've tried everything I can think of... LIKE, UPPER, LCASE, BINARY, changing everything to uppercase, to lowercase, decrypting the column data as opposed to encrypting $lastname then comparing...
I'd like for users not to have to get the case correct when doing a search (kind of defeats the purpose of a search).
I'm also really hoping that I don't have to force the original data to a single case when encrypting it in the first place.
Thanks in advance.