php and mysql code works on localhost but there is issue with online server

Question

I am working on web app for treasure hunt all connection to the online server is correct and the application is working fine and save and get the data from / to online mysql database.

on my application I register a session for (start time) and session for (end time) where the (end time) means that the user has finished the game.

I am getting the winner number from this (end time) and organize the winners based on there (end time)

the problem is that on localhost (using XAMPP) I get the winner number correctly. while if I upload my work to my hosting (Linux with CPanel) I always get the winner as 0 (even if it wasn't the first)

This is the result that I get online

while if I am on localhost I get the winner number correctly

this is the code to get the winner:

function getWinner($uid, $set){

    $db = new Db();
    $winner = 0;
    $query = "SELECT endtime FROM eid_race WHERE id = '$uid' AND endtime IS NOT NULL";
    $result = $db->query($query);
    if(sizeof($result) == 0){
        $updateEndtime = "UPDATE eid_race SET endtime = UNIX_TIMESTAMP() WHERE id = '$uid'";
        $db->execute($updateEndtime);
    }
        $query = "SELECT COUNT(id) FROM eid_race WHERE id <> '$uid' AND endtime IS NOT NULL AND endtime < (SELECT endtime FROM eid_race WHERE id = '$uid') AND questionset = '$set'";
    $result = $db->query($query);
    echo '  result:  ' . $result[0][0];
    if(sizeof($result) == 1){
        $winner = $result[0][0];
        echo '  winner:  '.$winner;
    }

    echo '  winner after:  '.$winner;


    return $winner+1;

}

when I test the query directly on phpmyadmin I get correct result but the php result still 0

SELECT COUNT(id) FROM eid_race WHERE id <> '577dd5759fa67' AND endtime IS NOT NULL AND endtime < (SELECT endtime FROM eid_race WHERE id = '577dd5759fa67') AND questionset = 'A'

Put a test file with phpinfo() on server and compare the PHP/Mysql versions. — Suman Singh, Jul 07 '16 at 04:39
@SumanSingh is right and since you are dealing with `datetime` and `timestamp` in `mysql` then you should check the server `timezone` and `date time settings`. — Avishek, Jul 07 '16 at 04:50
@SumanSingh **localhost:** PHP Version 5.6.15 , mysql Client API version mysqlnd 5.0.11-dev , **Online Server:** PHP Version 5.3.29, mysql Client API version 5.6.30 -- — Ahmad Saleh, Jul 07 '16 at 04:55
@AvishekChat how can I prevent the changes between online timezone and localhost. **localhost timezone:** Europe/Berlin , **Online Server Timezone: ** UTC — Ahmad Saleh, Jul 07 '16 at 05:00
@SumanSingh I tested the code and it returns correct result on phpmyadmin (check my post update) — Ahmad Saleh, Jul 07 '16 at 05:15
Please echo the query and run into directly on live server phpmyadmin — Suman Singh, Jul 07 '16 at 05:32

score 0 · Answer 1 · answered Jul 07 '16 at 05:27

0

You can modify the timezone of mysql on my.ini file in windows and my.cnf file in linux and mac system. But I think the best solution is to change the timezone from query using

 set time_zone = '+2:00';   --Timezone:Europe/Berlin

and check the current datetime using select now();

answered Jul 07 '16 at 05:27

Avishek

795
1
6
20

my localhost time_zone is '+2:00' (Europe/Berlin), Online is UTC – Ahmad Saleh Jul 07 '16 at 05:42
checking SELECT now() *localhost: * 2016-07-07 08:43:12, **Online: ** 2016-07-07 13:43:49 – Ahmad Saleh Jul 07 '16 at 05:46
Ok, then run the query `set time_zone = '+2:00';` on the server and check. – Avishek Jul 07 '16 at 05:58
still nothing changed, I tried to set variable `$end_time = time()` on php and assigned it to `endtime` column instead of `UNIX_TIMESTAMP()` . still nothing changed – Ahmad Saleh Jul 07 '16 at 07:39
checkout this post http://stackoverflow.com/questions/34428563/set-timezone-in-php-and-mysql – Avishek Jul 07 '16 at 08:28

php and mysql code works on localhost but there is issue with online server

1 Answers1