Insert NULL value using prepare()

Question

I have similars queries

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = %s,
    WHERE
        id= %d
    ",
    $_POST['date'] == '' ? "NULL": $_POST['date'],
    $_POST['id']
);
$wpdb->query($sql);

This will results in:

UPDATE `date` SET 'NULL' WHERE `id` = $_POST['id'] 

so prepare() is adding single quotes to NULL and the query sets the field to NULL string not NULL value. The only fix for me is to take the variable outside of the prepare() function like this:

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';

/* Here I declare the variabile outside of the prepare() */
$date = $output['date'] == '' ? "NULL" : $_POST['date'];
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = $date,
    WHERE
        id = %d
    ",
    $_POST['id']
);
$wpdb->query($sql);
0
silvered.dragon 2 months 0 Answers 10 views 0

Leave an answer