+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: CI Database Error when Setting Timezone in MySQL (/showthread.php?tid=24031)
CI Database Error when Setting Timezone in MySQL - El Forum - 10-28-2009
[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` DESCI'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?
CI Database Error when Setting Timezone in MySQL - El Forum - 10-28-2009
[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);CI Database Error when Setting Timezone in MySQL - El Forum - 10-28-2009
[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?
CI Database Error when Setting Timezone in MySQL - El Forum - 10-28-2009
[eluser]thdls55[/eluser]
The setting will stay active for all subsequent queries in the current connection. So, you have to set it back.
CI Database Error when Setting Timezone in MySQL - El Forum - 10-29-2009
[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?
CI Database Error when Setting Timezone in MySQL - El Forum - 10-29-2009
[eluser]chuckleberry13[/eluser]
Nevermind
SET time_zone = @@global.time_zone did it.