Running multiple sql Queries

[millsy007]

I am thinking of the best way of running a number of mysql queries depending on the value of a variable.

 

I need to fill in tables on my database that manages a coach timetable, where each coach/shuttle run has a number of journeys with in it.

For example a shuttle run between London and Moscow would incorporate the routes london to paris, paris to berlin ... etc

 

So if there is a shuttle going from London to Moscow it would mean that there are a number of journeys, each of which would need there own sql queries running.

The code below shows the sql I want to run, but I do not know how to logically break this up in code !?

 

 

i

f ($route==London To Moscow)
  {
  
//RUN A QUERY TO FIND DETAILS OF A PARTICULAR JOURNEY
$query = "
SELECT  journey.journey_id 
FROM 	shuttle, journey 
WHERE 	shuttle.id = journey.shuttle_id
and 	shuttle.depart_dttm = '$depart_dttm'
and	journey.route_id = 1
";

//GET THE JOURNEY ID
$journey_id = $row[journey_id ]

//PUT THE JOURNEY ID INTO THE PASSNGER TABLE TO SHOW THEY WILL BE ON THIS PART OF THE JOURNEY
INSERT INTO passengers ($name, $journey_id)


//RUN A QUERY TO FIND DETAILS OF THE NEXT JOURNEY THAT MAKES UP THE COMPLETE TRIP
$query = "
SELECT  journey.journey_id 
FROM 	shuttle, journey 
WHERE 	shuttle.id = journey.shuttle_id
and 	shuttle.depart_dttm = '$depart_dttm'
and	journey.route_id = 2
";

INSERT INTO passengers ($name, $journey_id)

}

 

What is a good way to organize this?

[landavia]

<?php
if ($route==London To Moscow)
  {

//RUN A QUERY TO FIND DETAILS OF A PARTICULAR JOURNEY
$query = "
SELECT  journey.journey_id
FROM    shuttle, journey
WHERE    shuttle.id = journey.shuttle_id
and    shuttle.depart_dttm = '$depart_dttm'
and   journey.route_id = 1
";

mysql_query($query);

//GET THE JOURNEY ID
$journey_id = mysql_insert_id(); //$row[journey_id ]

/*
PUT THE JOURNEY ID INTO THE PASSNGER TABLE TO SHOW 
THEY WILL BE ON THIS PART OF THE JOURNEY 
INSERT INTO passengers ($name, $journey_id)
*/

/*RUN A QUERY TO FIND DETAILS OF THE NEXT 
JOURNEY THAT MAKES UP THE COMPLETE TRIP*/
$query = "
SELECT  journey.journey_id
FROM    shuttle, journey
WHERE    shuttle.id = journey.shuttle_id
and    shuttle.depart_dttm = '$depart_dttm'
and   journey.route_id = 2
";
mysql_query($query);
$journey_id = mysql_insert_id(); 
INSERT INTO passengers ($name, $journey_id)
   
}

?>

like that?

but this is not finish yet..

u must have control 4 above query.

if the 1st query FAIL is fine.. the data will not insert

if the 2nd query FAIL.. u must FAIL the 1st too

[millsy007]

yes that looks right, is it not difficult though as if i get an error running the second query the sql would of already inserted the data?

[landavia]

yap.. that's problem too

well.. my teacher suggested me to use innodb