Insert Function not Working

[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

 

//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!?

 

 

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

[KevinM1]

You seem to have a scope problem.  Namely, the $result within your function is not the same as the $result in your while-loop.  You need to store the returned $result from the function in a variable, then test against that.  Just because you named your conditional variable $result doesn't mean it's magically being set/updated.

 

Your best bet is to remove the while-loop altogether and run the function twice, storing the results in separate variables.  If neither is equal to "Journey Not Available" then add the strings together, else, there's a scheduling conflict you need to handle.

[millsy007]

Thanks

Okay here is what I am thinking, is this what you meant?

 

elseif ($depart == 1 and $arrive == 3) {

        $inserted .= insertjourney($name, $id, 1);
$inserted .= insertjourney($name, $id, 2);
//Combine String
//IF string contains 'Journey Not Available'
//HandleConflict($name, $id)
}

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());

		$inserted = "variables input<br>";
		return $inserted;
	}

	else {

	$inserted = "Journey Not Available";
	return $inserted;
}
}

function handleconflict($name, $id) 
{
	$query = "
	SELECT  journey.id
	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];

	$query = "
      	        UPDATE 	      journey j, passengers p, shuttle s 
	SET 		j.occupancy=j.occupancy - 1 
	WHERE 	      p.journey_id = j.id 
	AND 		s.id = j.shuttle_id 	
	AND 		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());
}