Can't pull from database when there is an ' in the record

[meeble3]

I have the code:

$result = mysql_query("SELECT * from centres where area LIKE '%$town%'",$db);

while ($myrow = mysql_fetch_row($result)) {

echo blah

}

This works fine when $town equals 'London' but if it equals 'St John's Wood' which has an apostrophe in it, then it doesn't work.

In the database it is there as 'St John\'s Wood' but when I pass through 'St John\'s Wood' from the previous page it doesn't work. It doesn't work if I use stripslashes to remove the slash either.

Any ideas?

Cheers

James

 

[sleipnir214]

Wasn't this asked and answered recently?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[meeble3]

Where?

 

[sleipnir214]

I can't find it, either. I must be having some kind of deja-vu moment or something.

Do you have the literal value "St John\'s Wood" (with the visible backslash) in your database? If so, the data wasn't escaped correctly when it was inserted.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ingresman]

try

select ... where <table> area like '%john''s%';

note the 2 single quotes to escape the single one (if that makes sense).
This worked from data set up from scratch and queried from mysql command tool.
If you havn't got the data escaped correctly (as sleipnir214) suggests you've got a clean up excersie on your hands

 

[vbkris]

>>note the 2 single quotes to escape the single one (if that makes sense).

isnt it \' in mysql???


Known is handfull, Unknown is worldfull

 

[ingresman]

Yes '' works on a select and the \' also works. I'm trying to give meebl3 more options.
I think his main problem is altering the variable that holds the town name to be SQL friendly.
anyhow try

<?php

$town = "st john's wood";

$town2 = addslashes($town);

$link = mysql_connect("localhost","","");
mysql_selectdb("thou");
$qry = "select col1 as town from search where col1 like '" . $town2 . "'";
$rs=mysql_query($qry);

while($row=mysql_fetch_object($rs))
 echo "\n" . "town is " . $row->town;
?>