CODEWITHSUNDEEP
phpmysqlsql
Jamie
Jamie

Reputation: 379

Problem using set time_zone in MySQL

I'm trying to select data that meets a bunch of conditions below.

Problem is, I'm based in New Zealand (UTC+12) and server is shared - i.e. timezone not updatable (and at UTC-8). I'm trying to use SET time_zone at the start of my query but it doesn't seem to work.

Would really appreciate any ideas.

function getIndivDeal($id) {
     $result = mysql_query("
       SET time_zone = '+19:00'
       SELECT i, company, deal_type, deal_title, deal_message, valid_time_start, 
         valid_time_end, DATE_FORMAT(valid_expiry, '%D %b %y'), valid_mon, 
         valid_tue, valid_wed, valid_thu, valid_fri, valid_sat, valid_sun
       WHERE i = '" .mysql_real_escape_string($id). "'
       AND valid_expiry > CURDATE()
       AND valid_time_start >= CURTIME()
       AND valid_time_end < CURTIME()
       AND CASE DAYOFWEEK(CURDATE())
         WHEN 1 THEN valid_mon
         WHEN 2 THEN valid_mon
         WHEN 3 THEN valid_tue
         WHEN 4 THEN valid_wed
         WHEN 5 THEN valid_thu
         WHEN 6 THEN valid_fri
         WHEN 7 THEN valid_sat
       END = 1
    ");
    return $result;
}

// $result is used in another file like so:
$id = 2501;
getIndivDeal($id)
$result = getIndivDeal($id);
if (mysql_num_rows($result)) {
    while($row = mysql_fetch_array($result)) {
        echo $row["TIME_FORMAT(valid_time_start, '%l:%i %p')"];
    }
}

It works fine until I add SET time_zone and then it spits out the following error:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in..

Upvotes: 1

Views: 1235

Answers (1)

Jay
Jay

Reputation: 1097

Maybe it produces a n invalid mysql query! Did you try

mysql_query("SET time_zone = '+19:00'");
$result = mysql_query("
   SELECT i, company, deal_type, deal_title, deal_message, valid_time_start, 
     valid_time_end, DATE_FORMAT(valid_expiry, '%D %b %y'), valid_mon, 
     valid_tue, valid_wed, valid_thu, valid_fri, valid_sat, valid_sun
   WHERE i = '" .mysql_real_escape_string($id). "'
   AND valid_expiry > CURDATE()
   AND valid_time_start >= CURTIME()
   AND valid_time_end < CURTIME()
   AND CASE DAYOFWEEK(CURDATE())
     WHEN 1 THEN valid_mon
     WHEN 2 THEN valid_mon
     WHEN 3 THEN valid_tue
     WHEN 4 THEN valid_wed
     WHEN 5 THEN valid_thu
     WHEN 6 THEN valid_fri
     WHEN 7 THEN valid_sat
   END = 1
");
return $result;

Hope that helps?

Upvotes: 2

Related Questions