One submit button. Two tables. On clicking submit a marker is created, along with its lng and lat values. This information is added to marker table.
The marker ID is a FK in the submissions table. Documentation for LAST_INSERT_ID();
says: The ID that was generated is maintained in the server on a per-connection basis. I think each execution resets this, but not sure.
It is currently inserting data into the marker table but nothing into the submissions table. I have it checking to make sure all fields have content if not it is an invalid form and an error shows, same for a db connection, but at the moment its taking me to a success page.
This SQL Query straight into mysql database gives the desired result:
INSERT INTO markers VALUES (NULL, 53.645792, -1.785035);
INSERT INTO submissions (submission_id, marker_id, title, info, image)
VALUES (1, LAST_INSERT_ID(), "this is a title", "info", "image");
My code in my files however don't...
Model:
function insertSubmissionMarker($conn,$lat,$lng)
{
$query="INSERT INTO markers VALUES (NULL, :lat, :lng)";
$stmt=$conn->prepare($query);
$stmt->bindValue(':lat', $lat);
$stmt->bindValue(':lng', $lng);
$affected_rows = $stmt->execute();
if($affected_rows==1)
{
return true;
}else{
return false;
}
}
function insertSubmissionContent($conn,$marker_id,$title,$info,$image)
{
$query="INSERT INTO submissions VALUES (NULL, LAST_INSERT_ID(), :title, :info, :fileToUpload)";
$stmt=$conn->prepare($query);
$stmt->bindValue(':title', $title);
$stmt->bindValue(':info', $info);
$stmt->bindValue(':fileToUpload', $image);
$affected_rows = $stmt->execute();
if($affected_rows==1)
{
return true;
}else{
return false;
}
}
function addToMap ($conn,$lat,$lng,$title,$info,$image){
$x = insertSubmissionMarker ($conn,$lat,$lng);
$y = insertSubmissionContent ($conn,$title,$info,$image);
if($x && $y = true)
{
return true;
}else{
return false;
}
}
Controller:
$conn=getConn();
$success=addToMap($conn,$title,$info,$lat,$lng,$image);
$conn=NULL; //close the connection
if($success)
{
include("views/add-map-confirm-view.php");
}else
{
$errorMsgs[]="Problem inserting data into the database";
include("views/add-map-error-view.php");
}