Forum Moderators: coopster
I want to write a select statement that will find records where category ="Life's Ups and Downs", but I'm totally at a loss as to how to do this.
I know it should look something like:
Select * from MyDatabase WHERE category = "Life's Ups and Downs"
But this doesn't work.
From what I've read I need to do some escaping to make this work, but I really can't figure out what I'm supposed to do.
Could someone please give me the syntax to search on a string that contains single quotes/apostrophes?
Many thanks
Here is what I tried:
$query_Recordset1 = 'SELECT * FROM categories WHERE SubCategory = "Life''s Ups and Downs"';
And it gave me a parse error...
[edited by: riddleyw at 7:58 pm (utc) on July 3, 2009]
$Test=mysql_real_escape_string( "Life's Ups and Downs");
mysql_select_db($database_mydatabase, $mydatabase);
$query_Recordset1 = "SELECT * FROM categories WHERE SubCategory = '$Test'";
$Recordset1 = mysql_query($query_Recordset1, $mydatabase) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
?>
<BODY>
<?php
echo mysql_num_rows($Recordset1);
?>
[edited by: riddleyw at 7:57 pm (utc) on July 3, 2009]
I tried this:
$Test=mysql_real_escape_string( "Life's Ups and Downs");
mysql_select_db($database_mydatabase, $mydatabase);
$query_Recordset1 = 'SELECT * FROM categories WHERE SubCategory like "Life\'\'s Ups and Downs"';
$Recordset1 = mysql_query($query_Recordset1, $mydatabase) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
?>
<BODY>
<?php
echo mysql_num_rows($Recordset1);
?>
Here is what I tried:
$query_Recordset1 = 'SELECT * FROM categories WHERE SubCategory = "Life''s Ups and Downs"';
It's so hard to see single and double quotes in message boards in this font, but I definately see three ' at the end there.
You should use mysql_real.... but the skinny is:
MySQL needs either a single quote or double quote to delimit values in a where clause.
Whatever you choose, for any values in the where it must be doubled OR escaped.
PHP doesn't have the benefit of perl's qq function, so you have to use HEREDOC syntax or just plain double quotes, which is, most of the time, how you'd store a DB query in a variable. The reason being, you often want to use variables in the select statement, and variables interpolate with double quotes, they do not with single quotes.
This is confusing, because you have a string with single quotes in it and you think the var won't interpolate with the single quotes. But it does, with the string being delimited in PHP with double quotes, the "internal" single quote is just another character.
$test = 'I will';
$string = "Example, '$test' interpolate";
-> Example, 'I will' interpolate
$string = 'Example, "$test" interpolate';
-> Example, "$test" interpolate
So for the purpose of demonstration, in the below my carats ^ will represent DOUBLE QUOTES, my pound signs/hashes # will represent a single quote.
$select = ^select * from table where field=#Life##s ups and downs#^;
See how the value has a doubled single quote, because I'm using single quotes to delimit the where? Inversely, you would need to concatenate if you use SINGLE quotes to create a statement, and doubles to delimit the where value, because it won't interpolate with single quotes.
$var = 'He said "I\'m Otta here", now.';
Note, in PHP, I have to escape the single quote, but since I'm using double quotes in mySQL, I don't have to escape it before putting it in the where.
$var = preg_replace('/^+/g','^^',$var); // ^= ", in this example, remember ^ is a double quote
$select = #select * from table where field=^#.$var . #^#;
Will work,
$select = #select * from table where field=^#$var#^#;
Will not, and will give you a parsing error because PHP thinks the string ends at the first # (single quote.)
