Using LAST_INSERT_ID() over two functions which both use execute();

Question

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");
}

You can select lastid+1 in constructor and use it in each fuction by $this->lastid — JanuszO, Mar 29 '17 at 14:06
I think you have already a error in your code : if($x && $y = true), use double equal sign — themadmax, Mar 29 '17 at 14:44
@JanuszO why do you think that `lastid + 1` would give you proper result? What about `auto_increment_offset`? — Mjh, Mar 29 '17 at 15:54

Using LAST_INSERT_ID() over two functions which both use execute();

0 Answers0