Select name from database

[Sharkadder]

Hi there,I have a mysql database which holds records about people. My aim was to search the database and display records found onto the screen, this was all working fine but i found that players who had apostrophe's in their name such as the name Adam O'Donnell brought up a mysql_numrows error on return.The name is typed into an input box and once you click the search button it will reload the page, send the name to php through a $_post command and then search the database. Here is the code i have tried so far:

		#the search term from the html form once submit was pressed		$searchterm = $_POST['SearchTerm'];	$searchterm = trim(rawurldecode($searchterm));		#replace any black slash's	$searchterm = str_replace("\\","",$searchterm);	print $searchterm;		#get the search category from the html form	$searchcategory = $_POST['Category'];	$characterremove = array("/"," ");		#remove any forward slashes from search category	$searchcategory = str_replace($characterremove,"",$searchcategory);	$con = mysql_connect('h50mysql107.secureserver.net','sensiswos','Sens1sw0s');	@mysql_select_db("sensiswos",$con) or die(mysql_error());	mysql_query("SET NAMES utf8");	$query="SELECT * FROM FootballPlayers WHERE lower($searchcategory) LIKE lower('%$searchterm%') AND lower(PlayerManager) LIKE lower('$playermanagersearch') COLLATE utf8_bin ORDER BY Name asc";	print $query;	$result=mysql_query($query);	$num=mysql_numrows($result);	mysql_close();

Ok so that is my code, when i run the query with a name without an apostrophe it works fine and returns a result, if i use a name with an apostrophe in i then get the following error generated once i print the query out:

Adam O'DonnellSELECT * FROM FootballPlayers WHERE lower(Name) LIKE lower('%Adam O'Donnell%') AND lower(PlayerManager) LIKE lower('%') COLLATE utf8_bin ORDER BY Name ascWarning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/content/S/h/a/Sharkadder/html/worldfootballleagues/sql_testing/search.php on line 195

Ok so that is my code, i have tried using mysql_escape_string() when searching the database and that doesn't work and i have also tried doing a search without my removing the \\ as shown in the code above and that doesn't work.just to mention one final thing, the name is saved into the database without a \ before the apostrophe, so it is in the database as Adam O'Donnell and not Adam O\'Donnell. The other thing to mention is that i replace the \ after the page has reloaded because once i type the name in and submit it, on refresh the search term produces a \ before the apostrophe and so i replace it with blank.If you can help where i am going wrong then that would be great.Many thanks,Mark

[JamesB]

The other thing to mention is that i replace the \ after the page has reloaded because once i type the name in and submit it, on refresh the search term produces a \ before the apostrophe and so i replace it with blank.

That sounds like Magic Quotes is enabled in PHP, the best thing to do would be to reverse the effects of magic quotes, then use mysql_real_escape_string().To reverse magic quotes, (this means you won't need to do the str_replace on the $_POST values), you can put this at the top of the script: (before you read any $_POST values)

if (get_magic_quotes_gpc()) {	@array_walk_recursive($_GET,	 'stripslashes');	@array_walk_recursive($_POST,	'stripslashes');	@array_walk_recursive($_REQUEST, 'stripslashes');	@array_walk_recursive($_COOKIE,  'stripslashes');}

and to escape the values, put this after you've connected to SQL, and before the $query variable is created:

$searchcategory = mysql_real_escape_string($searchcategory, $con);$searchterm = mysql_real_escape_string($searchterm, $con);$playermanagersearch = mysql_real_escape_string($playermanagersearch, $con);// these 2 lines should protect % and _ characters in the LIKE search$searchterm = str_replace(array('%', '_'), array('\\%', '\\_'), $searchterm);$playermanagersearch = str_replace(array('%', '_'), array('\\%', '\\_'), $playermanagersearch);

also you should probably validate the $searchcategory variable before it gets escaped, to make sure it is a valid field name.

[Sharkadder]

Your code didn't work, still says no results found although i am no longer getting the numrows error. In fact your code doesn't work for any person in my database with or without apostrophe, so it must be going wrong somewhere. Below is a copy of my updated code and query, i moved the $searchterm and $playermanager = str_replace codes down a bit as no results were showing for anything when they were in the position you said:

if (get_magic_quotes_gpc()) {	@array_walk_recursive($_GET,	 'stripslashes');	@array_walk_recursive($_POST,	'stripslashes');	@array_walk_recursive($_REQUEST, 'stripslashes');	@array_walk_recursive($_COOKIE,  'stripslashes');}if (isset($_POST['search'])){	$searchterm = $_POST['SearchTerm'];	$searchterm = trim(rawurldecode($searchterm));	#$searchterm = str_replace("\\","",$searchterm);	print $searchterm;	#$searchterm = trim(utf8_encode($searchterm));	$searchcategory = $_POST['Category'];	$characterremove = array("/"," ");	$searchcategory = str_replace($characterremove,"",$searchcategory);	$con = mysql_connect('h50mysql107.secureserver.net','sensiswos','Sens1sw0s');	@mysql_select_db("sensiswos",$con) or die(mysql_error());	$searchcategory = mysql_real_escape_string($searchcategory, $con);	$searchterm = mysql_real_escape_string($searchterm, $con);	$playermanagersearch = mysql_real_escape_string($playermanagersearch, $con);		mysql_query("SET NAMES utf8");	$query="SELECT * FROM FootballPlayers WHERE lower($searchcategory) LIKE lower('%$searchterm%') AND lower(PlayerManager) LIKE lower('$playermanagersearch') COLLATE utf8_bin ORDER BY Name asc";	$searchterm = str_replace(array('%', '_'), array('\\%', '\\_'), $searchterm);	$playermanagersearch = str_replace(array('%', '_'), array('\\%', '\\_'), $playermanagersearch);		print $query;	$result=mysql_query($query);	$num=mysql_numrows($result);	mysql_close();}

So i again type in Adam O'Donnell and it just says that no results are found. If i put the "$searchterm = str_replace(array..." and "$playermanagersearch = str_replace(array..." code before the query then it doesn't find anything, not even people who have no apostrophe in their name. Where it is in the code above i am back to the problem i had before, fidns names but not people with apostrophe in their name.the printed search term and query with the way the code is above now looks like this:

Adam O\'DonnellSELECT * FROM FootballPlayers WHERE lower(Name) LIKE lower('%Adam O\\\'Donnell%') AND lower(PlayerManager) LIKE lower('%') COLLATE utf8_bin ORDER BY Name asc

Any further help would be apprechiated on locating players with apostrophes in their names.

[JamesB]

The fact that your first print statement shows a backslash in the echo, makes me think the array_walk_recursive() method I used isn't working to reverse magic quotes.Try changing:

if (get_magic_quotes_gpc()) {	@array_walk_recursive($_GET,	 'stripslashes');	@array_walk_recursive($_POST,	'stripslashes');	@array_walk_recursive($_REQUEST, 'stripslashes');	@array_walk_recursive($_COOKIE,  'stripslashes');}

to this:

function fixMagicQuotes($array) {	foreach ($array as $key => $value) {		$array[$key] = is_array($value) ? fixMagicQuotes($value) : stripslashes($value);	}	return $array;}if (get_magic_quotes_gpc()) {	$_GET	 = fixMagicQuotes($_GET);	$_POST	= fixMagicQuotes($_POST);	$_REQUEST = fixMagicQuotes($_REQUEST);	$_COOKIE  = fixMagicQuotes($_COOKIE);}

like before, I have not tested this code, so my apologies if it does not work.

[Sharkadder]

ok i tried that and it never worked, the query now shows this:

Adam O'DonnellSELECT * FROM FootballPlayers WHERE lower(Name) LIKE lower('%Adam O\'Donnell%') AND lower(PlayerManager) LIKE lower('%') COLLATE utf8_bin ORDER BY Name asc

[JamesB]

well the magic quotes reversal appears to have worked, so that's one step forward.maybe i'm missing something, but the query looks fine to me, are you getting any errors?

[justsomeguy]

You should be checking for errors from the database:$result=mysql_query($query) or exit(mysql_error());

[Sharkadder]

ok i added in the slight error check modification and still nothing. I get no errors from the search, just it doesn't appear to find anything.the record definately exists and is definately in the database without a \ before the apostrophe. What else do you think i could try?

[JamesB]

For debug purposes, try changing the query to just the first part:

$query="SELECT * FROM FootballPlayers WHERE lower($searchcategory) LIKE lower('%$searchterm%')";

if that doesn't find any matches, try searching for just the letter d in the HTML search box.if it does find matches, add the rest of the query back bit by bit:

AND lower(PlayerManager) LIKE lower('%')COLLATE utf8_binORDER BY Name asc

[justsomeguy]

Your query should look like this:

SELECT * FROM FootballPlayers WHERE lower(Name) LIKE '%Adam O\'Donnell%' AND lower(PlayerManager) LIKE '%' COLLATE utf8_bin ORDER BY Name asc

You can use PHP to convert the values you're looking for to lowercase before adding them to the query. If that doesn't return records, then there aren't any records that match the conditions.

[Sharkadder]

Ok it still says no records found. The name is definately in the database and so i'm thinking the apostrophe isn't matching the one from the raw database. I have done as justsomeguy says and taken off the condition to search a category but it just says no results. If i change the name for one without an apostrophe in then it finds the name and so it's got to be to do with removing the apostrophe.Here is the current code:

if (get_magic_quotes_gpc()) {	$_GET	 = fixMagicQuotes($_GET);	$_POST	= fixMagicQuotes($_POST);	$_REQUEST = fixMagicQuotes($_REQUEST);	$_COOKIE  = fixMagicQuotes($_COOKIE);}if (isset($_POST['search'])){	$searchterm = $_POST['SearchTerm'];	$searchterm = trim(rawurldecode($searchterm));	#$searchterm = str_replace("\\","",$searchterm);	print $searchterm;	#$searchterm = trim(utf8_encode($searchterm));	$searchcategory = $_POST['Category'];	$characterremove = array("/"," ");	$searchcategory = str_replace($characterremove,"",$searchcategory);	$con = mysql_connect('h50mysql107.secureserver.net','sensiswos','Sens1sw0s');	@mysql_select_db("sensiswos",$con) or die(mysql_error());	$searchcategory = mysql_real_escape_string($searchcategory, $con);	$searchterm = mysql_real_escape_string($searchterm, $con);	$playermanagersearch = mysql_real_escape_string($playermanagersearch, $con);		mysql_query("SET NAMES utf8");	#$query="SELECT * FROM FootballPlayers WHERE lower($searchcategory) LIKE lower('%$searchterm%') AND lower(PlayerManager) LIKE lower('$playermanagersearch') COLLATE utf8_bin ORDER BY Name asc";	$query = "SELECT * FROM FootballPlayers WHERE lower(Name) LIKE '%Adam O\'Donnell%' AND lower(PlayerManager) LIKE '%' COLLATE utf8_bin ORDER BY Name asc";	$searchterm = str_replace(array('%', '_'), array('\\%', '\\_'), $searchterm);	$playermanagersearch = str_replace(array('%', '_'), array('\\%', '\\_'), $playermanagersearch);		print $query;	$result=mysql_query($query) or exit(mysql_error());	print $result;	$num=mysql_numrows($result);	print $num;	mysql_close();

updated printed query, result and number of rows count is:

SELECT * FROM FootballPlayers WHERE lower(Name) LIKE '%Adam O\'Donnell%' AND lower(PlayerManager) LIKE '%' COLLATE utf8_bin ORDER BY Name ascResource id #30

[justsomeguy]

Either the values in the database were stored with a slash before the quote, or it's a different character from the regular single quote. MS Word, for example, does not use regular single quotes for apostrophes, it uses a higher UTF character that is angled. If you want to check that you can select the values from the database and go through them character-by-character to print out the code for each character. The ord function will return the ASCII value of a character. e.g.:

for ($i = 0; $i < strlen($searchterm); $i++){  echo $searchterm[$i] . ': ' . ord($searchterm[$i]) . '<br>';}

Run a loop like that for the search term and the value you get from the database and compare them to see if they're really the same characters.

[Sharkadder]

ok i decided to try on a different person in case of data input error but the same thing occured. The name John O'Shea was tried and it worked well and it now works for both names.I looked at your code and the apostrophe was the same character, i then looked at what you said and the result now shows. Some of the suggested code must have been wrong as an error on my part but it now works well. Do you recommend using a slash before the apostrophe in the raw database or leave it without one?Thanks for all of the help and suggestions...i am not sure if i need the code which was first suggested for my post variables and if i can cut down the code then let me know but the problem is now solved.The name now shows

[justsomeguy]

You would only want a slash in the database value if you want to store the slash. The reason you need a slash in the query is because the character needs to be escaped. You're not telling the database to search for the slash.

[Sharkadder]

yeah i knew that much but just wodnered if it was good practise.anyways, cheers dude.