CI Database Error when Setting Timezone in MySQL

[eluser]chuckleberry13[/eluser]
I'm getting an error running a particular query. If I copy and past the query into phpMyAdmin it runs fine, if I run it with CI I get a database error.

Here's the code

Code:

$sql = "SET time_zone = TIME_FORMAT(SEC_TO_TIME('".$offset."'),'%H:%i');
            SELECT YEAR(`timestamp`) AS year, WEEKOFYEAR(`timestamp`) AS week, DATE(`timestamp`) AS day, `user_id`, `list_id`, MIN(timestamp) as first_call, MAX(TIMESTAMP) as last_call, TIME_TO_SEC(TIMEDIFF(MAX( TIMESTAMP ), MIN(timestamp))) as total_time
            FROM (`".$this->transaction_table."`)
            WHERE `list_id` IN ('".implode("','",$list_ids)."')
            GROUP BY `day`, `list_id` ORDER BY `week`  DESC";
return $this->db->query($sql);

Here's the error

Code:

A Database Error Occurred

        
Error Number: 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 ';
            SELECT YEAR(`timestamp`) AS year, WEEKOFYEAR(`timestamp`) AS week, DATE(`ti' at line 1

SET time_zone = TIME_FORMAT(SEC_TO_TIME('-21600'),'%H:%i');
            SELECT YEAR(`timestamp`) AS year, WEEKOFYEAR(`timestamp`) AS week, DATE(`timestamp`) AS day, `user_id`, `list_id`, MIN(timestamp) as first_call, MAX(TIMESTAMP) as last_call, TIME_TO_SEC(TIMEDIFF(MAX( TIMESTAMP ), MIN(timestamp))) as total_time
            FROM (`call_transactions`)
            WHERE `list_id` IN ('5','54')
            GROUP BY `day`, `list_id` ORDER BY `week`  DESC

I've found that if I take out the SET time_zone line it will run in CI ok. But why would CI choke on this query, when if you run the query natively it runs fine? Any ideas?

[eluser]thdls55[/eluser]
Hi chuckleberry13,

MySQL batch queries are not supported in PHP.

Try something like the following:

Code:

$sql = "SET time_zone = TIME_FORMAT(SEC_TO_TIME('".$offset."'),'%H:%i')";

$this->db->query($sql);

$sql = "SELECT YEAR(`timestamp`) AS year, WEEKOFYEAR(`timestamp`) AS week, DATE(`timestamp`) AS day, `user_id`, `list_id`, MIN(timestamp) as first_call, MAX(TIMESTAMP) as last_call, TIME_TO_SEC(TIMEDIFF(MAX( TIMESTAMP ), MIN(timestamp))) as total_time
            FROM (`".$this->transaction_table."`)
            WHERE `list_id` IN ('".implode("','",$list_ids)."')
            GROUP BY `day`, `list_id` ORDER BY `week`  DESC";

return $this->db->query($sql);

[eluser]chuckleberry13[/eluser]
Ah, didn't know that. Thanks that worked. My question is, how long will that MySql set time_zone take effect. If I want mysql to go back to the original timezone do I have to reset it back? Is it just for the next query?

[eluser]thdls55[/eluser]
The setting will stay active for all subsequent queries in the current connection. So, you have to set it back.

[eluser]chuckleberry13[/eluser]
Thanks for your help on this. Do you know of an easy way in MySql just to reset the time_zone back to the global setting, or default timezone?

[eluser]chuckleberry13[/eluser]
Nevermind

SET time_zone = @@global.time_zone did it.