Different MySQL Query Result in Localhost and Online Server

Question

I have this query works on localhost:

$clicks = $wpdb->get_results(
    "SELECT * 
    FROM {$wpdb->prefix}data
    WHERE date BETWEEN ( NOW() - INTERVAL {$args['day']} DAY ) AND NOW()
    {$where}
    ORDER BY {$args['order_by']} {$args['order']}
    LIMIT {$args['data_per_page']} OFFSET {$offset}",

    ARRAY_A
);

$clicks returns the expected result array.

My localhost uses XAMPP 1.8.3, it uses PHP 5.5.11 and 5.6.16 (based on this).

When I test it on online server (I use Hostgator with PHP 5.4.45, and MySQL 5.5.42), $click returns empty array.

There is no syntax error caution or any warning so I am confused.

Is it syntax problem? And what is the best practice to debug MySQL query?

what classes does $wpdb use? It looks like pdo. Are your credentials ok? — davejal, Dec 06 '15 at 02:38
It's a WordPress class to access DB: https://codex.wordpress.org/Class_Reference/wpdb. My credentials are fine. Other queries are executed normally. Only this query that has the problem. — maurisrx, Dec 06 '15 at 03:51
so your using wordpress and I assume you cannot use the php error reporting options I provided, but could you run wordpress in debug mode or something? http://stackoverflow.com/q/1080679/3664960 — davejal, Dec 06 '15 at 03:54
Yes, I've been turning on wp debug since the beginning but I am not seeing any error. I think the query is fine because it's executed normally on localhost, but not online server. Might it be because of different MySQL versions? — maurisrx, Dec 06 '15 at 04:15
Did you try to run the query on a MySQL client, phpMyAdmin or anything? (On the target server, replacing the values that you need). Also if the query runs ok, try to go on the sourcecode where the query trully runs and change the code to just print the query and exit, may be the parameters are being filled wrong. — fbiazi, Dec 06 '15 at 15:30
I think I found the issue. I store the date in DB using UTC timezone. But the online server uses CST timezone. So instead of using `NOW()` I define a new `$date` variable using PHP `time()` and replace the `NOW()` in the query with the variable. — maurisrx, Dec 07 '15 at 14:19

score 1 · Accepted Answer · answered Dec 09 '15 at 03:46

I store the date in DB using UTC timezone. But the online server uses CST timezone. So instead of using NOW() I define a new $date variable using PHP time() and replace the NOW() in the query with the variable like this:

    $date = date( 'Y-m-d', time() );

    global $wpdb;
    $clicks = $wpdb->get_results(
        "SELECT * 
        FROM {$wpdb->prefix}data
        WHERE DATE(date) BETWEEN ( {$date} - INTERVAL {$args['day']} DAY ) AND {$date}
        {$where}
        ORDER BY {$args['order_by']} {$args['order']}
        LIMIT $args['data_per_page'] OFFSET {$offset}",

        ARRAY_A
    );

So now the query always return the same results in localhost and online server.

Different MySQL Query Result in Localhost and Online Server

1 Answers1