How to get round a " ' " ??

[The_Chancer]

Just a small query really..

The SQL query of:
SELECT pot_rev FROM enquiry WHERE agent LIKE '%Munro's%'

Will not work, for the obvious reason it has a ' in the word. Is there a way round it apart from the way below ?

SELECT pot_rev FROM enquiry WHERE agent LIKE '%Munro%'

It is possible to get the agent name in a variable then pass like this through PHP:

$hotel2 = ereg_replace("[\x27\x22]",'',trim($hotel));

But again it doesn't work as listings are not identical.

Any and all info would be appreciated.

[Frag]

Escape it.

See php's addslashes() function.

[The_Chancer]

I have tried that but it doesn't work.

Try this out

$hotel="Bill's shack";
$hotel2=addslashes($hotel);
$result=mssql_query("SELECT ref FROM enquiry WHERE hotel_var LIKE '%$hotel2%'");
$count=(mssql_num_rows($result));
echo $count;

That query WILL fall over due to the ' that I escaped in line 2.

Any other ideas ?

[edwardsbc]

The problem is you are using Micro$oft SQL. The escape sequence for MSSQL is different than it is for mySQL \'. To escape in MSSQL (Transact-SQL more accurately) you "double" the single quote. Convert 'Bill's Shack' to 'Bill''s Shack' where '' is two single quotes (not a double quote).

[chriskl]

You really should be using MSSQL's full text index stuff anyway, because LIKE searches are really slow and are not indexable.

Just search MSDN:

http://www.microsoft.com/msdn/

Chris

[bernouli]

SELECT pot_rev FROM enquiry WHERE agent LIKE '%Munro\'s%'

[The_Chancer]

Chriskl,
Having problems already with fulltext indexed on MSSQL - seeThis Thread Having to use LIKE in the meantime, but code can be improved in a newer version.

[The_Chancer]

Edwardsbc - many thanks... 🆒

[The_Chancer]

Automatically get the escape character ' by altering php.ini

magic_quotes_sybase = On

Magic_quotes_runtime = On

magic_quotes_gpc = On

Solves all problems for me 🆒

Cheers for your help tho..