Syntax error or access violation

Question

The following SQL works as intented when executing in phpmyadmin however when I try to execute in my PHP script it throws an exception.

$log_stats = DB::select(DB::raw("
        SET @startDate = '$from';
        SET @endDate = '$db_to';
        SET @total_duration = (CAST(@endDate as DATETIME) - CAST(@startDate as DATETIME)); 

        SELECT t1.status
            ,SUM(IF(t2.cron_ran_at IS NULL OR t2.cron_ran_at > @endDate,
            CAST(@endDate AS DATETIME),
            t2.cron_ran_at) -
        IF(t1.cron_ran_at < @startDate,
           CAST(@startDate AS DATETIME),
            t1.cron_ran_at)) / @total_duration as duration
        FROM monitor_logs t1
        LEFT JOIN monitor_logs t2 ON t1.id = (t2.id - 1)
        WHERE (t2.cron_ran_at > @startDate OR t2.cron_ran_at IS NULL) AND t1.cron_ran_at < @endDate
        GROUP BY t1.status
"));

Exception thrown...

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SET @endDate = '2015-07-22 00:00:00'; SET @total_duration = (CAST(@endDate ' at line 2 (SQL: SET @startDate = '2015-07-14 00:00:00'; SET @endDate = '2015-07-22 00:00:00'; SET @total_duration = (CAST(@endDate as DATETIME) - CAST(@startDate as DATETIME)); SELECT t1.status ,SUM(IF(t2.cron_ran_at IS NULL OR t2.cron_ran_at > @endDate, CAST(@endDate AS DATETIME), t2.cron_ran_at) - IF(t1.cron_ran_at < @startDate, CAST(@startDate AS DATETIME), t1.cron_ran_at)) / @total_duration as duration FROM monitor_logs t1 LEFT JOIN monitor_logs t2 ON t1.id = (t2.id - 1) WHERE (t2.cron_ran_at > @startDate OR t2.cron_ran_at IS NULL) AND t1.cron_ran_at < @endDate GROUP BY t1.status )`

Answer 1

You can't run multiple mysql statements in one query in PHP. You should make this a stored procedure or function.