ok, so i have a search utility on a page... everything was seemingly working fine, that is until i started entering the data to go into the main table.... i got to a record that contained a ' character (ex. Women's).... i realize that this is a special character in PHP so thats prolly why its causing a problem.... now i cant search for this record in the database at all, because it contains a ' character....i can search for other records just fine still, as long as the record with the ' character isnt returned as well (ex. the record with the ' characer contains the word "apple", i can still search for "orange" but when i search for "apple" i get errors)..... if i remove the ' from the record or remove the record entirely, everything works fine... i suppose i could just remove all of the ' characters from the database, but that seems rather dumb to me, there must be a workaround
here is the funciton im using to search the table
<?
include("other.inc");
$connection = mysql_connect($host,$user,$password)or die ("couldn’t connect to server");
$db = mysql_select_db($database,$connection)or die ("Couldn’t select database");
$userinput = explode(" ","{$_POST['criteria']}");
$arraycount = count($userinput);
$delete_query = "delete from houses_temp";
$result_delete = mysql_query($delete_query) or die (mysql_error($connection));
$query = "SELECT * FROM houses where houseID like '%%'";
for ($i = 0; $i < $arraycount; $i++)
{
$query .= " and name like '%$userinput[$i]%'
or address like '%$userinput[$i]%'
or city like '%$userinput[$i]%'
or province like '%$userinput[$i]%'
or postalCode like '%$userinput[$i]%'
or phoneNum like '%$userinput[$i]%'
or altPhoneNum like '%$userinput[$i]%'
or description like '%$userinput[$i]%'
or grouping like '%$userinput[$i]%'
or latitude like '%$userinput[$i]%'
or longitude like '%$userinput[$i]%'";
}
//echo "$query";
//below is the table layout for results
$result = mysql_query($query) or die (mysql_error($connection));
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
extract($row);
$add_record = "insert into houses_temp (houseID, name, address, city, province, postalCode, phoneNum,
altPhoneNum, description, grouping, latitude, longitude) values ('$houseID', '$name', '$address',
'$city', '$province', '$postalCode', '$phoneNum', '$altPhoneNum', '$description', '$grouping',
'$latitude', '$longitude')";
$result_add = mysql_query($add_record) or die (mysql_error($connection));
}
header("Location: show_search.php");
}
?>
so what im basically doing is grabbing information from a form on a previous page and creating an array from this info using the explode function.... then for each member of the array, i search all the fields in the table looking for matches.... once i find any matches, i then copy these matches into a temporary table (that i previously deleted/emptied).... then i jump to another page that displays the results in this temporary table to the user
like i said before, everything works just fine, but once i enter a ' character anywhere in the table, i get the following error:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's Christian Temperance Union', 'Box 733', 'Aberdeen', 'SK',
can anybody help with this? i really dont want to have to limit the characters that can be used in my table, especially such a commonly used one
edit one more thing i just noticed.... when i enter the record originally (ex. Women's Group) i have a page that shows the record you just entered .... this page displays Women\'s Group, so the addslashes is getting done when i add the record.... also, when i view the record in the database is shows up just as i entered it (ex. Women's Group)
