Concat Help: Adding string to Query in mysql/php

Question

I thought I could figure this out on my own, but apparently I am struggling with it. I had originally the following code:

 $query = "SELECT cards.card_id,title,description,meta_description,seo_keywords,price FROM cards,card_cheapest WHERE cards.card_id = card_cheapest.card_id ORDER BY card_id";
$result = mysql_query($query);


// Open file for writing
$myFile = "googleproducts.txt";
$fh = fopen($myFile, 'w') or die("can't open file");

// Loop through returned data and write (append) directly to file
fprintf($fh, "%-200s %-200s  %-800s   %-200s %-800s\n", "id", "label","description","price","seo keywords");
fprintf($fh, "\n");
while ($row = mysql_fetch_assoc($result)) {
 fprintf($fh, "%-200s  %-200s  %-800s  %-200s  %-800s\n", $row['card_id'], $row['title'], $row['description'],$row['price'], $row['seo_keywords']);
}

// Close out the file
fclose($fh);
?>

What I needed to do was add "By Amy" to the title when the file was printing to a text file, so I thought was I wouldconcat it like so:

$query = "SELECT cards.card_id,concat(title, "By Amy"),description,meta_description,seo_keywords,price FROM cards,card_cheapest WHERE cards.card_id = card_cheapest.card_id ORDER BY card_id";

Everytime I would try to run the file, I would get an error saying "( ! ) Parse error: syntax error, unexpected T_STRING in C:\wamp\www\output.php on line 22" . I know the query works in my sequel pro, but when I try to incorporate it in the actual file it files

Answer 1

It's because you are enclosing a double quoted string (By Amy) inside another double quoted string (SELECT...card_id).

The PHP parser doesn't understand what's going on, so you're getting that error before your query is even sent to the database.

Escape the inner string by changing the two inner " to \"

For example:

$query = "SELECT cards.card_id, concat(title, "By Amy"), 
    description, meta_description, 
    seo_keywords, price 
FROM cards, card_cheapest 
WHERE cards.card_id = card_cheapest.card_id 
ORDER BY card_id";

to

$query = "SELECT cards.card_id, concat(title, \"By Amy\") AS TitleConcat, 
    description, meta_description, 
    seo_keywords, price 
FROM cards, card_cheapest 
WHERE cards.card_id = card_cheapest.card_id 
ORDER BY card_id";

EDIT: Notice the addition of AS TitleConcat after the concat field. This renames the column to TitleConcat and then you can access it by $row['TitleConcat'].

An alternative is to use AS title and the change would be transparent to the rest of your code (that is, you wouldn't have to change $row['title'] to $row['TitleConcat']), however in general this a little risky in my opinion (but it looks fine for the code you have posted).

Answer 2

One interesting method I use for long strings to get out of figuring out what type of quoting to use is heredocs. A sample heredoc looks something like this:

$string = <<<STRING
Lots of text
'this works'
"and this"
You can even use $variable expansion
STRING;

First off, the <<< indicates the start of a heredoc. Then you pass it a name to identify where your multi-line string stops. In this case we use STRING. However, you can use whatever name you want. One drawback to using heredocs is that in order to tell php that your multi-line string is done, you have to use the same identifier name, with a semicolon, as the first entry on the line. So this:

$string = <<<STRING
Lots of text
'this works'
"and this"
You can even use $variable expansion
    STRING; // This won't do what you expect it to

Won't work, because of the spaces that proceed it. Also you can't break out for calling functions:

$string = <<<STRING
Lots of text
'this works'
"and this"
You can even use $variable expansion
my_function_call() <-- this gets interpreted as text, not the result of the function
STRING; // This won't do what you expect it to

To take your example into consideration:

$query = <<<SQL
SELECT cards.card_id,concat(title, "By Amy"),description,meta_description,seo_keywords,price
FROM cards,card_cheapest 
WHERE cards.card_id = card_cheapest.card_id 
ORDER BY card_id
SQL;

So if you're doing something like outputting lots of SQL, without needing to rely on function calls, this works out quite nicely. For shorter strings I recommend using a standard string however.