Page 1 of 1

Insert Function Error

Posted: Mon Feb 23, 2009 3:27 pm
by millsy007
I am writing some code to book people into a coach schedule, part of my code will check which stops/journey the person is going on and book them in accordingly.

The program works by firstly seeing what their departure and destination is, for this example 1(Beach) to 3(City Center)
An IF ELSE statement then captures this and passes in the route details that will make up the journey between Beach and City Center, this will be route_id 1 and route_id 2

Code: Select all

//LINE 105
elseif ($depart == 1 and $arrive == 3) {
    while ( $result != "Journey Not Available" ) {
        $inserted .= insertjourney($name, $id, 1); //passing in first route id
    $inserted .= insertjourney($name, $id, 2);
    }
}
 
The insertjourneyFunction then checks the occupancy for each route of the journey, if there is space available the passenger is booked in, however if any of the routes are full then the function deletes the passenger record, adjust the occupancy back to what it was and sends a message saying the
journey was not available.

I added the while ( $result != "Journey Not Available" ) part so that as a route is not available on the journey is not available the inserting stops.

However I think it could be causing me problems, when I try to insert a record the passenger is added twice on the second journey

journey_id pssenger_name
421 wyne
422 wayne
421 wayne
and the occupancy for both the journeys is filled up, what am I doing wrong!?

Code: Select all

function insertjourney($name, $id, $route) 
{
    $query = "
    SELECT  journey.id, occupancy
    FROM    journey
    WHERE   journey.shuttle_id = '$id'
    AND     journey.route_id='$route'
    ";
    $qry_result = mysql_query($query) or die(mysql_error());
    $row = mysql_fetch_array($qry_result);
    $journid = $row[id];
    $occupancy = $row[occupancy];
    
    if ($occupancy <= 6) {
    
        $query = "
        INSERT INTO passengers (journey_id, passenger_name)
        VALUES      ('$journid', '$name')
        ";
        $qry_result = mysql_query($query) or die(mysql_error());
        
        $query = "
        UPDATE  journey
        SET         occupancy=occupancy+1
        WHERE   journey.id = '$journid'
        ";
        $qry_result = mysql_query($query) or die(mysql_error());
        
            $insertinfo = "variables input<br>";
            return $insertinfo;
        }
        
        else {
        $query = "
        UPDATE      journey j
        INNER JOIN  passengers p
        ON          p.journey_id = j.id
        INNER JOIN  shuttle s
        ON          s.id = j.shuttle_id
        SET         j.occupancy=j.occupancy-1
        WHERE       s.id = '$id'
        AND         p.passenger_name = '$name'
                    ";
        $result = mysql_query($query) or die(mysql_error());
        
        $query = "
        DELETE 
        FROM        passengers 
        WHERE       journey_id = $journid 
        AND         passenger_name = $name'
                    ";
        $result = mysql_query($query) or die(mysql_error());
        
        
        $result = "Journey Not Available";
        return $result;
    }
}