Having a problem displaying data from last inserted data

Question

I'm designing a staff rota planner....have three tables Staff (Staff details), Event (Event details), and Job (JobId, JobDate, EventId (fk), StaffId (fk)). I need to display the last inserted job detail with the staff name. I've been at it for couple of hours and getting nowhere. Thanks for the help in advance. My code is the following:

$eventId = $_POST['eventid'];
$selectBox = $_POST['selectbox'];
$timePeriod = $_POST['time'];
$selectedDate = $_POST['date'];
$count = count($selectBox);

echo "<fieldset><center>";
echo "<form name=submit action='' method=post>";

//display the selected event
$eventsql = "SELECT EventName FROM Event WHERE EventId= ".$eventId;
$result = mysql_query($eventsql);

while ($eventarray = mysql_fetch_array($result))
{
    $eventName = $eventarray['EventName'];
    echo "<h3><font color=red>".$eventName." </font></h3>"; 
}

//constructing the staff selection  
if (empty($selectBox))
{
    echo "<p>You didn't select any member of staff to be assigned.";
    echo "<p><input type='button' value='Go Back' onClick='history.go(-1)'>";
} else
    {
        echo "<p> You selected ".$count. " staff for this show.";

        for ($i=0;$i<$count;$i++) 
        {
            $selectId[$i] = $selectBox[$i];

            //insert the details into the Job table in the database
            $insertJob = "INSERT INTO Job (JobDate, TimePeriod, EventId, StaffId) 
                            VALUES ('".$selectedDate."', '".$timePeriod."', ".$eventId.", 
                            ".$selectId[$i].")";
            $exeinsertJob = mysql_query($insertJob) or die (mysql_error());


        }
    }   

    //display the staff details (last inserted) assigned to the job
    $insertedlist = "SELECT Staff.LastName, Staff.FirstName, Job.JobDate, Job.TimePeriod
                        FROM Staff, Job
                        WHERE Job.StaffId = Staff.StaffId
                        AND Job.JobDate = ".$selectedDate;
    $exeinsertedlist = mysql_query($insertedlist) or die (mysql_error());

    if ($exeinsertedlist) 
    {
        echo "<p><table cellspacing='1' cellpadding='3'>";
        echo "<tr><th>Last Name</th><th>First Name </th><th>Date</th><th>Hours</th></tr>";

        while ($joblistarray = mysql_fetch_array($exeinsertedlist))
                {                   
                echo "<tr><td align=center>".$joblistarray['LastName']."
                    </td><td align=center>".$joblistarray['FirstName']."
                    </td><td align=center>".$joblistarray['JobDate']."
                    </td><td align=center>".$joblistarray['TimePeriod']."</td></tr>";
                }

            echo "</table>";

            echo "<h3><a href=AssignStaff.php>Add More Staff?</a></h3>";
        }
        else {
                    echo "The Job list can not be displayed at this time. Try again.";
                    echo "<p><input type='button' value='Go Back' onClick='history.go(-1)'>";
                }




echo "</center>";   
echo "</fieldset>";
echo "</form>"; 

Answer 1

You have mysql_inserted_id to get the last inserted job id.

http://php.net/manual/en/function.mysql-insert-id.php

Answer 2

A few issues to look at:

• Looks like your Job.JobDate value may need to be enclosed by quotes in the insertedlist query (unless this is a timestamp).
• Where are you setting the $eventname variable?