Cross-Matching Data in Tables

[max_w1]

Hello, needed some advice on my current project for a bus reservation system. the project is almost complete only  the waiting list system is left pending.

 

the 4 tables which are involved in the waiting list management are like this:

 

1. Distance table

 

City    Distance

AK            0

AN            154

CB            205

 

 

2. Seat Chart

seat_no        from_city        to_city

1                    AK                  CB

2                    AN                  CB

4                    AK                  CB

 

 

 

3. Vacant seats

seat_no        from_city        to_city

1                    AK                  CB

5                    AN                  CB

 

4. Waiting Passengers

seat_no        from_city        to_city

1                    AK                  CB

 

 

So far i have managed to get the distances according to the city code.

what i am confused now is, how to match the waiting list passengers with Vacant seats?

there are a few restrictions for which i have made a category formula, please check the attachment for the category formula.

 

[attachment deleted by admin]

[teynon]

I don't understand your picture because 1, I don't know what RAC is and I don't know what you are referring to when you say source.

 

Secondly, why don't you blend your vacant seats into your seat chart and eliminate a table. Just add a "passenger_id" to a seat.

 

If passenger_id = 0, seat is vacant.

[max_w1]

Thank for the reply teynon, i should have been more clear.

RAC means Reservation Against Cancellation, by RAC Passenger i mean the person who is in waiting list or just an entry in  Waiting Passengers table

By Source i mean the city where a person Boards the Bus.

 

I have to keep the Vacant seats table separate because there are lot of other scripts and calculations which are making the application slow if i blend my vacant seats into my seat chart.

[teynon]

Ok, I really don't understand your formula or how having an ID in the other table will slow it down. (Rather, I think having the other table would actually make it slow down more.)

 

I also don't understand your question, because by the looks of your table, you have already matches the waiting list passenger to your vacant seats.

[max_w1]

The tables you see is just for the sake of explanation, the actual table contains of entries. The reason i kept  Vacant seats table separate  is because it is used a lot of times and by lot of different pages.

[teynon]

Can we see the actual table structure and any code your currently have?

[max_w1]

$this->load->model('racallot_model');
	$r =  $this->db->count_all_results('rac_status')-1;
	$v =  $this->db->count_all_results('vac_status')-1;

				  for ($i=0; $i<=$r; $i++)
							  {
							  
							  for ($j=0; $j<=$v; $j++)
							  {
							  $asnmt =  $this->racallot_model->match_cat($i, 1, $j, 1);
							  
										  if($asnmt == "SA")
										{
										echo $asnmt."---";
							  		echo "i=".$i."j=".$j."<br>";
									$readdb = $this->racallot_model->getDb('rac_status', $j-1);
											//echo element('berth_no', $readdb);
											//echo $v;
											$readdbv = $this->racallot_model->getDb('vac_status', $j);

											$rseat = element('berth_no', $readdb);
											$vseat = element('berth_no', $readdbv);


											echo $rseat."-to-".$vseat;
							 /* if ($i == 1)
											{
											$readdb = $this->racallot_model->getDb('rac_status', $j-1);
											//echo element('berth_no', $readdb);
											//echo $v;
											$readdbv = $this->racallot_model->getDb('vac_status', $j);

											$rseat = element('berth_no', $readdb);
											$vseat = element('berth_no', $readdbv);


											//echo $rseat."-to-".$vseat;
					//seat alloted to the passanger------------------------------.
													$this->db->insert('reserv_status', $readdb);
													$this->db->insert('reserv_status', $readdbv);
		          //removing naam 'o' nishan of the rac passanger from rac chart 

												$this->db->where('berth_no', 63);
												$this->db->delete('rac_status');
					//the rac passanger goes and sits in the vaccant seat hence there will be no vac seat left, so deleate the seat.
												if (!empty($vseat))
													{
													$this->db->where('berth_no', 68);
													$this->db->delete('vac_status');

													}
											}*/




										}
										if($asnmt == "SNA")
										{
										// Do Nothing.
										//...
										}
										if($asnmt == "CD")
										{
										/*echo $asnmt['1']."---";
							  			echo "i=".$i."j=".$j."<br>";*/

										}
										if($asnmt == "CSD")
										{

										}
										if($asnmt == "CS")
										{

										}
							  }
							  }

and the racallot_model

$this->load->model('racallot_model');
	$r =  $this->db->count_all_results('rac_status')-1;
	$v =  $this->db->count_all_results('vac_status')-1;

				  for ($i=0; $i<=$r; $i++)
							  {
							  
							  for ($j=0; $j<=$v; $j++)
							  {
							  $asnmt =  $this->racallot_model->match_cat($i, 1, $j, 1);
							  
										  if($asnmt == "SA")
										{
										echo $asnmt."---";
							  		echo "i=".$i."j=".$j."<br>";
									$readdb = $this->racallot_model->getDb('rac_status', $j-1);
											//echo element('berth_no', $readdb);
											//echo $v;
											$readdbv = $this->racallot_model->getDb('vac_status', $j);

											$rseat = element('berth_no', $readdb);
											$vseat = element('berth_no', $readdbv);


											echo $rseat."-to-".$vseat;
							 /* if ($i == 1)
											{
											$readdb = $this->racallot_model->getDb('rac_status', $j-1);
											//echo element('berth_no', $readdb);
											//echo $v;
											$readdbv = $this->racallot_model->getDb('vac_status', $j);

											$rseat = element('berth_no', $readdb);
											$vseat = element('berth_no', $readdbv);


											//echo $rseat."-to-".$vseat;
					//seat alloted to the passanger------------------------------.
													$this->db->insert('reserv_status', $readdb);
													$this->db->insert('reserv_status', $readdbv);
		          //removing naam 'o' nishan of the rac passanger from rac chart 

												$this->db->where('berth_no', 63);
												$this->db->delete('rac_status');
					//the rac passanger goes and sits in the vaccant seat hence there will be no vac seat left, so deleate the seat.
												if (!empty($vseat))
													{
													$this->db->where('berth_no', 68);
													$this->db->delete('vac_status');

													}
											}*/




										}
										if($asnmt == "SNA")
										{
										// Do Nothing.
										//...
										}
										if($asnmt == "CD")
										{
										/*echo $asnmt['1']."---";
							  			echo "i=".$i."j=".$j."<br>";*/

										}
										if($asnmt == "CSD")
										{

										}
										if($asnmt == "CS")
										{

										}
							  }
							  }

[max_w1]

sorry, here is the well organized code

 

 

Racallot model  --this model actually does all the hard work by matching distances of the cities and passengers  and letting us know how they can be processed. it returns values like, SA(Seat Alloted), SNA(Seat Not Alloted), CD(Check Destination), CSD(Source and destination).

http://pastebin.com/3Vkzrgcc

 

view page ractest.php:

http://pastebin.com/5ifd7MF1