I'm currently using PDO connection for perform some mysql queries and since I use the command $conn->prepare("HERE THE QUERY") I want to know how to format characters like quotes and double quotes.
When I have cases like this one:
$conn->prepare("SELECT * FROM ('SELECT DISTINCT (user_id) FROM table1')");
This is fine because in the nested SELECT there isn't a particular character that can cause problems. But how can we handle special cases like that?
Here a strange example (forget the mysql.. this is quite irrelevant, focus on the quotes situation) with quotes and double quote inside the nested SELECT:
$conn->prepare("SELECT * FROM ('SELECT user_id, CONCAT('[\"",GROUP_CONCAT(DISTINCT(cat) ORDER BY user_id DESC SEPARATOR "\",\""),"\"]') cat_grouped FROM table_1') select1");
What should be the right quotation mark syntax according to this example query? If i use ' instead of " when I prepare the query the problem is quite fixed, but I want to understand if there is a smart way to maintain the double quotes.
firstly I recommend using single quotes - they're faster :D
The main issue is using the same quotes with each other. Doing this causes pre-mature closing, and I'm sure you'd like to save that pre-mature embarrassment.
See in simple terms:
"string has star"ted"
As you can see, the first double quote the file gets to is the one after star. This closes the string after star, rendering the ted" in a fatal error.
What you want to do is escape the quotes that conflicts with the opening quote. Single quotes inside double quotes are fine, and vice versa.
Escape single quotes inside single quotes and double quotes inside double quotes - the rest should be ok to leave. Also I recommend using backticks for your mysql tables and fields to avoid some errors down the road if they deiced to add some new keyword that just so happens to match your table/field name
e.g.
if using single quotes:
$conn->prepare('SELECT * FROM table WHERE string_field = \'value\'');
if using double quotes:
$conn->prepare("SELECT * FROM table WHERE string_field = \"value\"");
if mixing:
$conn->prepare('SELECT * FROM table WHERE string_field = "value"');
\ is the escape character used for situations like this :)
The alternative is concatting strings:
$conn->prepare('SELECT * FROM table WHERE field = '. $foo);
so breaking it up, you declare string same was as usual with preferred quotes, then to add stuff to it, you use . to concat