Why is php turning my string into 0

One thing I am learning the hard way is, if you don't write it down you will forget it. I always think I will remember my thoughts. But nope. The next day, my mind is blank and I am frustrated. So there is an simple issue I ran into in MySQL and PHP and I am making sure I writing down this time before I forget it.

A while ago, I wrote a simple query that failed. The problem is not that it failed; I couldn't understand why it was failing. Here is the error I got.

Query Failed:
0
Error: You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near '0' at line 1

What is the zero? The zero is where the query that failed is supposed to be shown. But I know I didn't write zero, so it's time to check the code.

public function getLatestMonthSummaries(){
    $sql = "SELECT 
                p.post_id,p.post_title,p.post_url,p.date_published,p.summary,p.page_views,p.thumbnail,
                m.month_start 
            FROM post p 
            JOIN (
            SELECT 
                CONCAT(year(date_published),"-",month(date_published),'-01') as 'month_start',
                CONCAT(year(date_published),"-",month(date_published),'-31 23:59:59') as 'month_end'
            FROM post
            GROUP BY CONCAT(year(date_published),"-",month(date_published))
            ORDER BY p.date_published DESC
            LIMIT 1) m ON p.date_published BETWEEN m.month_start and m.month_end
            WHERE p.status = 'published'
            AND p.date_published < NOW()";
    return $this->simplyQuery($sql);
}

Found the code. I read it, 10 times, I still couldn't see what was wrong. Why was the query turning into zero So it's time to make things less complicated. I striped down the query just to test.

$sql = "SELECT 
            CONCAT(year(date_published),"-",month(date_published),'-01') as 'month_start',
            CONCAT(year(date_published),"-",month(date_published),'-31 23:59:59') as 'month_end'
        FROM post";
return $this->simplyQuery($sql);

Still shows the same error. Now, I'm getting angry. So I use var_dump:

$sql = "SELECT 
            CONCAT(year(date_published),"-",month(date_published),'-01') as 'month_start',
            CONCAT(year(date_published),"-",month(date_published),'-31 23:59:59') as 'month_end'
        FROM post";
var_dump($sql);
exit;

Var_dump gives me a big fat zero. Did I say I was mad? So the problem is inside the string. I copy and paste it in *MysqlWorkbench* and it runs smooth like a baby's bottom.

I return to my code editor and I notice the slight variation in color in the string.

So what was really happening is this:

$sql =  "string1" - "string2" - "string3";

Which according to PHP is equal to 0. So the solution of course is to escape the double quotes to prevent php from evaluating it as a math operation.

$sql = "SELECT 
            CONCAT(year(date_published),\"-\",month(date_published),'-01') as 'month_start',
            CONCAT(year(date_published),\"-\",month(date_published),'-31 23:59:59') as 'month_end'
        FROM post";
return $this->simplyQuery($sql);

I know it is not very hard to fix but it can be very annoying. And because php decided that the value is zero it is very hard to find what the real problem is. Maybe there are times where people want to use subtraction with their string but so far, I have not found a use for it.

Anyway, I documented because this is the second time it happens to me. Now I hope when you Google "Why is php turning my string into 0" you can land right here and I can comfort you.

---