creating a search of db

how to account for names with apostrophe

[weddingm]

I am trying to write a code for a search of the db feature of last names and show the results. I can account for names with hyphens like example-sample. However, I cannot figure out how to get a name line O'Conner.

I am also worried about sql injection. Any help would be appreciated.

What I have so far:

$search=trim($_POST["searchwords"]);
$keyword = split( " ", $search);
$er=0;
if (strlen($search)<2)
{$er=$er+1;
echo "<font size='-1' color='red'>Your last name must be longer than 1 letter. </font><br><br><br>";}
if ($er<1)
{
$sql="SELECT * from xtable WHERE";
foreach ($keyword as $key)
{if($keyword[0]==$key)
{$sql=$sql." xname LIKE '%$key%' OR yname LIKE '%$key%'";}}
$sql=$sql." ORDER BY xname";
$result = @mysql_query($sql,$connection) or die("Couldn't connect to database at this time...");
while ($row = mysql_fetch_array($result)) {
etc...

[Readie]

A friend of mine had an issue like this when calling names with special characters from a mySQL database - what he did (bear in mind I've no idea how) is have another table with a list of characters, and their html counterpart

so ' would be '

I'm not sure what he did after that, but it came out neatly enough. I know it's not too helpful, but you might be able to extrapolate your own ideas from that.

[weddingm]

Thanks. That's an idea. I wonder how the big book sites search their dbs of titles.

[rocknbil]

mysql_real_escape_string() is supposed to handle this. Since I like seeing what I'm doing, an alternative is to double up any term quotes.

$term= "O'Malley";
$term= preg_replace("/'/","''",$term); // produces O''Malley

This value can now be inserted/searched.

Select * from table where name like '$term';

[weddingm]

I like using mysql_real_escape_string()

Would I use the code:

$term= "O'Malley";
$term=mysql_real_escape_string($term);