John Davidson

Message:

  $wpdb->query(

    $wpdb->prepare(

      "INSERT INTO `wp_product` (" .

      " `SoldDate`, `NoImage`  " .

      ") VALUES (" .

      " %s, %s" .

      ") " .

      "ON DUPLICATE KEY UPDATE " .

      "`SoldDate` = VALUES(`SoldDate`), ".

      "`NoImage` = VALUES(`NoImage`), ".

      array(

        $car->SoldDate,

        !$car->PhotoList

      )

    )

  );

I have this code and it seems NULL values, "" or 0 values turns into 0000-00-00 00:00:00, how do you insure that this date is always NULL when we receive 0, NULL, ""? The code used to work, but now it always return 0000-00-00 00:00:00 for every product and I am not sure why.

---

My Answer:
To ensure that NULL values, empty strings, or 0 values stay as NULL when inserting with PDO prepare, you can modify your code as follows:

php
$wpdb->query(
$wpdb->prepare(
"INSERT INTO `wp_product` (" .
" `SoldDate`, `NoImage` " .
") VALUES (" .
" %s, %s" .
") " .
"ON DUPLICATE KEY UPDATE " .
"`SoldDate` = VALUES(`SoldDate`), " .
"`NoImage` = VALUES(`NoImage`)",
array(
($car->SoldDate != '' && $car->SoldDate != null) ? $car->SoldDate : null,
($car->PhotoList != '' && $car->PhotoList != null) ? $car->PhotoList : null
)
)
);


In this modified code, we check if the values of `$car->SoldDate` and `$car->PhotoList` are not empty strings or NULL before inserting them into the query. If they are empty strings or NULL, we set them to NULL in the query. This way, the date will stay as NULL when the input is 0, NULL, or an empty string.