Get case sensitive query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Amanda1998
Forum Newbie
Posts: 23
Joined: Tue Dec 13, 2011 10:25 am

Get case sensitive query

Post by Amanda1998 »

Hi you all.. first the code

Code: Select all

function checkSecAnswer($userID,$answer)
{
global $mySQL;
	if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND `answ` = ? LIMIT 1"))
	{
		$answer = ($answer);
		$SQL->bind_param('is',$userID,$answer);
		$SQL->execute();
		$SQL->store_result();
		$numRows = $SQL->num_rows();
		$SQL->close();
		if ($numRows >= 1) { return true; }
	} else {
		return false;
	}
}
They have to answer the Sec question, witch is stored as "Lower and Upper Cases Sec Question", but if they type "myanswer" or "MYANSWER", they still get access anyway... how to fixed guys please..thanks ... cheers
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get case sensitive query

Post by mikosiko »

Amanda1998
Forum Newbie
Posts: 23
Joined: Tue Dec 13, 2011 10:25 am

Re: Get case sensitive query

Post by Amanda1998 »

Thanks @mikosiko, still not luck at all...
if i do

Code: Select all

	if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND LOWER(`answ`) = ? LIMIT 1"))
	{
		$answer = strtolower($answer);
Tha do it, but students can type their Sec Question lowercase even on MySQL table have been stored like "MyAnswER" , witch is the case that I need.. any help?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get case sensitive query

Post by mikosiko »

did you really read the link that I provide?... the last code that you posted doesn't have nothing related to that link
Amanda1998
Forum Newbie
Posts: 23
Joined: Tue Dec 13, 2011 10:25 am

Re: Get case sensitive query

Post by Amanda1998 »

I did, but dont get it :(
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get case sensitive query

Post by mikosiko »

let see.... this paragraph doesn't ring a bell for you? (Disclaimer: I'm not trying to be rude or nothing similar... just trying to guide you in a different way)
The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:
so...
step 1: Find your character set and collation...
to find the collation:

Code: Select all

SELECT COLLATION(VERSION());
step 2: Force one of the operands to have a case sensitive or binary collation... p.e (assuming that your collation is utf8_general_ci)

Code: Select all

          if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND `answ` = BINARY ? LIMIT 1"))  or

         if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND LOWER(`answ`) = ? COLLATION utf8_bin LIMIT 1"))  // assuming that your collation is utf8_general_ci
Amanda1998
Forum Newbie
Posts: 23
Joined: Tue Dec 13, 2011 10:25 am

Re: Get case sensitive query

Post by Amanda1998 »

(Disclaimer: I'm not trying to be rude or nothing similar... just trying to guide you in a different way)
:roll: @mikosiko ... You have been nice to me, don't worry... And yes that solve my issue.. thank you very much..
My issue has been solved ...
Post Reply