Wordpress - $wpdb->insert - MySQL NOW()

问题:

is there any possibility to use the MySQL NOW() in the $wpdb->insert call?

When I use the following code, NOW() is not working.

$data = array(
        'id' => NULL,
        'order' => serialize($_POST['data']['Order']),
        'created' => NOW(),
        'user_id' => $current_user->ID
    );

$wpdb->insert(ORDERS_TABLE, (array) $data );

回答1:

I believe the canonical approach is to use the WordPress current_time() function passing it 'mysql' as the first parameter to specify a mysql timestamp compatible format (the alternative is UNIX timestamp format) and '1' as the second parameter to specify local time (default is GMT), like this:

$data = array(
    'id' => NULL,
    'order' => serialize($_POST['data']['Order']),
    'created' => current_time('mysql', 1),
    'user_id' => $current_user->ID
);

$wpdb->insert(ORDERS_TABLE, $data);

current_time('mysql', 1) outputs 2012-07-18 12:51:13.

More here: http://codex.wordpress.org/Function_Reference/current_time

回答2:

As word "created" means that you only need the "NOW()", current date and time, on insert. You can alter the field created

 ALTER TABLE `ORDERS_TABLE` CHANGE `created` `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

and don't use created field at all in query, so your new query will look like..

$data = array(        
    'order' => serialize($_POST['data']['Order']),        
    'user_id' => $current_user->ID
);

$wpdb->insert(ORDERS_TABLE, (array) $data );

and when you run your query "created" will take default value, it will be equal to NOW(), you may have noticed that I have omitted "id" as well it will also get its default value, I assume that it is auto incremented field.

回答3:

For anyone using Wordpress 5.3 and above, the recommended approach is to now use wp_date, not current_time.

wp_date('Y-m-d H:i:s');

回答4:

At present this is still not clear that you can pass these SQL functions like NOW() easily using the $wpdb->insert() method within the WordPress $wpdb class.

Short of writing a class to extend the $wpdb class, the most straightforward way I can see is to use the $wpdb->query() method and write some code to convert your $data array to an SQL string to pass to the method.

$sql = sprintf( 
    'INSERT INTO table (id, order, created, user_id) VALUES (%d, %d, %s, %d)',
    $data[id], $data['order'], $data['created'], $data['user_id'] 
);

$wpdb->update( $sql );

More here: http://codex.wordpress.org/Class_Reference/wpdb

回答5:

It's an old topic, but I found a silly solution for using NOW() outside SQL statements and it works:

$mysql_now = $wpdb->get_row( "SELECT NOW() as dbNow", ARRAY_A );
echo $mysql_now['dbNow'];

You are welcome.