动态CURDATE和INSERT INTO的问题

Question

I'm trying to add a record to one of my tables. One of the columns is meant to be filled with a future date depending on the information entered, sometimes a month, sometimes a year.

I'm using CURDATE() + INTERVAL 1 MONTH/YEAR to get the future date.

However, when the record is added, the date appears as 0000-00-00.

This is the code I'm using:

if ($_POST['type'] == "month") {
    $expire = 'CURDATE() + INTERVAL 1 MONTH';
} else if ($_POST['type'] == "year") {
    $expire = 'CURDATE() + INTERVAL 1 YEAR';
}

$insertSQL = 'INSERT INTO users (dateAdded, name, email, promoEndDate) VALUES (now(), :name, :email, :expire)';
$rsInsert = $pdo->prepare($insertSQL);
$rsInsert->bindParam(':name', $_POST['name'], PDO::PARAM_STR);
$rsInsert->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
$rsInsert->bindParam(':expire', $expire, PDO::PARAM_STR);
$rsInsert->execute();

I have previously used CURDATE without binding and it has worked, but I need the interval to depend on the user's choice.

I have also tried using $rsInsert->bindValue(':expire', $expire, PDO::PARAM_STR) but I get the same result.

Any help would be appreciated.

Thanks.

Answer 1

This might be a little hacky, but since you aren't using any user entered parameters in that particular insert statement not terrible.

if ($_POST['type'] == "month") {
    $expire = 'CURDATE() + INTERVAL 1 MONTH';
} else if ($_POST['type'] == "year") {
    $expire = 'CURDATE() + INTERVAL 1 YEAR';
}

$insertSQL = 'INSERT INTO users (dateAdded, name, email, promoEndDate) VALUES (now(), :name, :email, '. $expire .')';
$rsInsert = $pdo->prepare($insertSQL);
$rsInsert->bindParam(':name', $_POST['name'], PDO::PARAM_STR);
$rsInsert->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
$rsInsert->execute();

Not necessarily relevant to your question, but you should probably consider sanitizing your variables if they are coming in from $_POST as well as including a default case for post type, since if neither month or year are specified your variable won't be added causing a insert failure.