CODEWITHSUNDEEP
phpmysql
Dave Morris
Dave Morris

Reputation: 569

How to change sql_mode at runtime

My MySQL server's sql_mode setting is set to STRICT. I want to change it to TRADITIONAL for a particular application I'm developing. However it's not possible for me to do this at the server level.

Is it possible to change the sql_mode setting at runtime from my PHP scripts?

Upvotes: 21

Views: 25630

Answers (2)

butterbrot
butterbrot

Reputation: 970

Hmm this should work

// connect to mysql and call the first query
mysqli_query($conn, "SET SESSION sql_mode = 'TRADITIONAL'");

Upvotes: 29

Devner
Devner

Reputation: 7255

SOLUTION FOR WORDPRESS:

For someone who wants to achieve the result in WordPress, following is my solution:

Place the following function in functions.php file of your theme / main page of your plugin:

function get_zeroed_datetime() {
    $modes = array("SET SESSION sql_mode = 'TRADITIONAL'");
    global $wpdb;
    $wpdb->set_sql_mode($modes);
    return '0000-00-00 00:00:00';
}

When performing a DB insert (for example), use it like so:

$wpdb->insert('test_table',
            array(
                'verified_on' => get_zeroed_datetime()          
            ),
            array(              
                '%s'
            ));

where 'verified_on' is a column name of the DATETIME format.

WHAT THE CODE ACHIEVES:

If sql_mode is set to STRICT on the server, then it won't allow you to insert zeroed values in DATETIME column. The above code helps in solving this issue.

Upvotes: 0

Related Questions