CODEWITHSUNDEEP
phppdo
Spatial Digger
Spatial Digger

Reputation: 1993

PDO: date not inserting, suggestions?

I'm trying to insert a date into the database using the following code, I get the following error: Error: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type date: ""

So the date is not being passed, yet I can return the date from the POST: echo $_SESSION['dateOpen']; this returns the date as 2014-06-01

I'm a bit of a noob, so any suggestions are welcome. Using postgresql database.

try {
$sql2 = "INSERT INTO excavation.contexts_spatial 
    (area_easting, 
    area_northing, 
    context_number, 
    open_date,
    close_date,
    excavation_method,
    contamination,
    zooarchaeology_comments,
    ceramic_comments) VALUES (
    :area_easting, 
    :area_northing, 
    :context_number, 
    :open_date, 
    :close_date, 
    :excavation_method,
    :contamination,
    :zooarchaeology_comments,
    :ceramic_comments)";

$stmt2 = $conn->prepare($sql2);

// prepare sql and bind parameters
$stmt2->bindParam(':area_easting', $area_easting, PDO::PARAM_INT);       
$stmt2->bindParam(':area_northing', $area_northing, PDO::PARAM_INT); 
$stmt2->bindParam(':context_number', $nextContext, PDO::PARAM_INT);
$stmt2->bindParam(':open_date', $open_date, PDO::PARAM_STR);
$stmt2->bindParam(':close_date', $close_date, PDO::PARAM_STR);
$stmt2->bindParam(':excavation_method', $excavation_method, PDO::PARAM_STR);
$stmt2->bindParam(':contamination', $contamination, PDO::PARAM_STR);
$stmt2->bindParam(':zooarchaeology_comments', $excavation_method, PDO::PARAM_STR);
$stmt2->bindParam(':ceramic_comments', $excavation_method, PDO::PARAM_STR);



// insert a row
$area_easting = $_SESSION['area_easting'];
$area_northing = $_SESSION['area_northing'];
$nextContext = $_SESSION['nextContext'];
$open_date = $_SESSION['dateOpen'];
$close_date = $_SESSION['dateClose'];
$excavation_method = $_SESSION['excavationMethod'];
$contamination = $_SESSION['contamination'];
$zooarchaeology_comments = $_SESSION['zooarchaeologyComments'];
$ceramic_comments = $_SESSION['ceramicComments'];
$stmt2->execute();

echo "New records created successfully in contexts spatial<br />";
}
catch(PDOException $e)
{
 echo "Error: " . $e->getMessage();
}

Upvotes: 2

Views: 439

Answers (1)

jeroen
jeroen

Reputation: 91734

You are executing the query twice: Once before you assign your session variables to the parameters you have bound in the query and once after.

You need to remove the first $stmt2->execute(); statement.

Upvotes: 3

Related Questions