Need Help to make advance search in PHP website Project

[AMITKUMAR]

I am making a Railway Search website for University Project and i want to implement advanced Search functionality but as i am new to this field i need help from you guys please help me to create that function

 

here i am describing : I have 3 Database with relevant fields one is Station db one is Train db and third is train timetable Db, Trains i have added using Station ID with arrival departure timings. now how can i implement search code to get In-between all trains results which user queried

 

for example we have 5 stations A B C D E, 3 train going to A to E one is without any stopping between A to E second is stopping only C and third is stopping all. if any one have queried about A to E then all 3 trains should be displayed, if A to C queried then 2 trains should be displayed and if A to D then only one train should be displayed. Please help me to sort out this search

 

Thanks.

[ginerjm]

First thing I would do is consolidate your tables into one db. Why have 3 databases for common data?

[QuickOldCar]

If post some sample data each database may be able to get more help.

 

What should have is something like this in the end to compare

 

train number, station number/location arrive, station number/location depart, arrive time, depart time

[Barand]

From what you said I would expect tables like this

+-----------+          +-----------+          +-----------+          
|  station  |          | timetable |          |  train    |
+-----------+          +-----------+          +-----------+          
| stationid |---+      | tt_id     |    +-----|  trainid  |
| name      |   +-----<| stationid |    |     |  type     |
+-----------+          | trainid   |>---+     +-----------+
                       | time      |
                       +-----------+

so if your train schedule looks like this

+-------------+-------+-------+-------+
| Station     |  T1   |  T2   |  T3   |
+-------------+-------+-------+-------+
| Station A   | 10:00 | 11:00 | 12:00 |
| Station B   | 10:20 |   -   |   -   |
| Station C   | 10:50 |   -   | 12:45 |
| Station D   | 11:10 |   -   |   -   |
| Station E   | 11:30 | 12:15 | 13:25 |
+-------------+-------+-------+-------+

then the ttable data would be

+-------+-----------+---------+----------+
| tt_id | stationid | trainid | time     |
+-------+-----------+---------+----------+
|     1 | A         |       1 | 10:00:00 |
|     2 | B         |       1 | 10:20:00 |
|     3 | C         |       1 | 10:50:00 |
|     4 | D         |       1 | 11:10:00 |
|     5 | E         |       1 | 11:30:00 |
|     6 | A         |       2 | 11:00:00 |
|     7 | E         |       2 | 12:15:00 |
|     8 | A         |       3 | 12:00:00 |
|     9 | C         |       3 | 12:45:00 |
|    10 | E         |       3 | 13:25:00 |
+-------+-----------+---------+----------+

and your query would  be

SELECT trainid 
FROM ttable a
INNER JOIN ttable b USING (trainid)
WHERE a.stationid = 'A' AND b.stationid = 'C';

+---------+
| trainid |
+---------+
|       1 |
|       3 |
+---------+

Edited March 7, 2016 by Barand

[AMITKUMAR]

 

From what you said I would expect tables like this

+-----------+          +-----------+          +-----------+          
|  station  |          | timetable |          |  train    |
+-----------+          +-----------+          +-----------+          
| stationid |---+      | tt_id     |    +-----|  trainid  |
| name      |   +-----<| stationid |    |     |  type     |
+-----------+          | trainid   |>---+     +-----------+
                       | time      |
                       +-----------+

so if your train schedule looks like this

+-------------+-------+-------+-------+
| Station     |  T1   |  T2   |  T3   |
+-------------+-------+-------+-------+
| Station A   | 10:00 | 11:00 | 12:00 |
| Station B   | 10:20 |   -   |   -   |
| Station C   | 10:50 |   -   | 12:45 |
| Station D   | 11:10 |   -   |   -   |
| Station E   | 11:30 | 12:15 | 13:25 |
+-------------+-------+-------+-------+

then the ttable data would be

+-------+-----------+---------+----------+
| tt_id | stationid | trainid | time     |
+-------+-----------+---------+----------+
|     1 | A         |       1 | 10:00:00 |
|     2 | B         |       1 | 10:20:00 |
|     3 | C         |       1 | 10:50:00 |
|     4 | D         |       1 | 11:10:00 |
|     5 | E         |       1 | 11:30:00 |
|     6 | A         |       2 | 11:00:00 |
|     7 | E         |       2 | 12:15:00 |
|     8 | A         |       3 | 12:00:00 |
|     9 | C         |       3 | 12:45:00 |
|    10 | E         |       3 | 13:25:00 |
+-------+-----------+---------+----------+

and your query would  be

SELECT trainid 
FROM ttable a
INNER JOIN ttable b USING (trainid)
WHERE a.stationid = 'A' AND b.stationid = 'C';

+---------+
| trainid |
+---------+
|       1 |
|       3 |
+---------+

Exactly sir you what i mean to say you understand-ed but the thing is when hit search button how could i implement in search page so that any queries it will handle dynamically.

[Jacques1]

This is your University project, not ours. We can help we you with small, specific problems, but we won't give you personal PHP lessons or even write the code for you.

 

You don't know how to make dynamic queries? Learn it, start writing code, try to solve your own problems. That's really the whole point of a University project. When you're completely stuck, post the relevant code and tell us exactly what you don't understand.

[ginerjm]

Bravo Jacques1!!

 

I wish more of us here on this forum felt the same as Jacques when answering obvious student project requests for help.

 

OP - You are at university to learn and are probably paying good money to do so. So LEARN! As Jacques says - when you have something YOU have written and need help with some portion of it, then ask here. We are not un-paid professors, but we can be free tutors.

[AMITKUMAR]

This is your University project, not ours. We can help we you with small, specific problems, but we won't give you personal PHP lessons or even write the code for you.

 

You don't know how to make dynamic queries? Learn it, start writing code, try to solve your own problems. That's really the whole point of a University project. When you're completely stuck, post the relevant code and tell us exactly what you don't understand.

 

 

Bravo Jacques1!!

 

I wish more of us here on this forum felt the same as Jacques when answering obvious student project requests for help.

 

OP - You are at university to learn and are probably paying good money to do so. So LEARN! As Jacques says - when you have something YOU have written and need help with some portion of it, then ask here. We are not un-paid professors, but we can be free tutors.

Thanks both of you sir! i really appreciate you motivated me i have made a query my self but am feeling to reached dead end  here is my code:

 

// FETCH TRAIN DETAILS    TIMING IN STATIONS STATION NAME ETC DATA

@$query1o=mysql_query("SELECT * FROM entry_ir_route WHERE

train_id = '$train_id1'

AND  week_day >= '$week_day1' AND  week_day <= '$week_day2'

AND  station_arrival_time >= '$station_arrival_time1' AND  

station_arrival_time <= '$station_arrival_time2'

AND  ( station_id = '$stationId1' OR   station_id = '$stationId2' )

");

 

what am mistaking i don't know but it giving me wrong search results some times returns blank data even though trains have stoppages on that particular station .

Edited March 8, 2016 by AMITKUMAR

[Barand]

As we have have no idea about

• how your data is stored
• what data you have in your table
• what values you pass to the query

what makes you think we can help? We cannot look over your shoulder to see what you are doing.
 
If you post an SQL dump of "entry_ir_route" table then I'll have a look.
[edit] Also what are the values in those variables you are using in the query?

Edited March 8, 2016 by Barand

[maxxd]

Quite honestly, ignoring the data retrieval issues you're having, there are several issues with the code you've posted. I'm not sure what your instructor is teaching, but he or she certainly shouldn't be advocating the use of mysql_* functions as they not only have deprecated for about a decade, they're completely removed from the current version of PHP. Hopefully he or she is also explaining the idea of data sanitation because you're dropping variables directly into your query, practically inviting a SQL injection attack.

 

I'd look very closely at the PDO library and prepared statements - if nothing else you may get extra credit, and you'll definitely get more robust, safer code out of it.

[AMITKUMAR]

As we have have no idea about

• how your data is stored
• what data you have in your table
• what values you pass to the query

what makes you think we can help? We cannot look over your shoulder to see what you are doing.

 

If you post an SQL dump of "entry_ir_route" table then I'll have a look.

[edit] Also what are the values in those variables you are using in the query?

as you told sir, am pasting all my code which i used in :

<?php
function stationIdToName($stationId)
{
@$query1=mysql_query("SELECT * FROM entry_ir_station WHERE id = '$stationId' LIMIT 1");
while($row1=mysql_fetch_array($query1))
{ @$stationName	= $row1['station_name'];   }
return $stationName;
}
function trainIdToName($trainId)
{
@$query1=mysql_query("SELECT * FROM entry_ir_train WHERE id = '$trainId' LIMIT 1");
while($row1=mysql_fetch_array($query1))
{ @$trainName	= $row1['train_name'];   }
return $trainName;
}
function trainIdToCode($trainId)
{
@$query1=mysql_query("SELECT * FROM entry_ir_train WHERE id = '$trainId' LIMIT 1");
while($row1=mysql_fetch_array($query1))
{ @$trainCode	= $row1['train_code'];   }
return $trainCode;
}
function dayIdToName($dayId)
{
if($dayId=='0'){return 'SUNDAY';}
if($dayId=='1'){return 'MONDAY';}
if($dayId=='2'){return 'TUESDAY';}
if($dayId=='3'){return 'WEDNESDAY';}
if($dayId=='4'){return 'THRUSDAY';}
if($dayId=='5'){return 'FRIDAY';}
if($dayId=='6'){return 'SATURDAY';}
if($dayId=='7'){return 'DAILY';}
if($dayId=='1,2,5,6'){return 'M, TU, F, S';}
if($dayId=='2,3,5,6'){return 'TU, W, F, S';}
if($dayId=='2,3,6,0'){return 'TU, W, S, SU';}
if($dayId=='1,0,4,3'){return 'SU, M, W, TH';}
}
?>

<?php

@$query1A=mysql_query("SELECT * FROM entry_ir_station WHERE station_name ='$station_from'");
@$count1= mysql_num_rows($query1A);
@$query1B=mysql_query("SELECT * FROM entry_ir_station WHERE station_name = '$station_to'");
@$count2= mysql_num_rows($query1B);
if($count1 >='1' AND $count2 >='1')
{
@$query1C=mysql_query("SELECT * FROM entry_ir_station WHERE station_name = '$station_from'");
while($row1C=mysql_fetch_array($query1C))
{ @$stationId1	= $row1C['id'];   }
@$query1D=mysql_query("SELECT * FROM entry_ir_station WHERE station_name = '$station_to'");
while($row1D=mysql_fetch_array($query1D))
{ @$stationId2	= $row1D['id'];  }
// MAKE A LIST OF TRAINS CROSSING STATION FROM AND MAKE THE SAME FOR STATION TO
// NOW COMPARE THESE TRAIN IDS, IF BOTH ARE SAME THEN TAT PARTICULAR TRAIN IS CROSSING BOTH STATIONS
// NOW COMPARE THE TIMING IF TRAIN CROSSING STATION FROM IS LESS THAN STATION FOR 
// AND DAY STATION FROM IS EITHER LESS THAN OR EQUAL TO STATION TO, IF ALL THESE 
// THINGS MATCHES THEN PROCEED WITH THE DETAILS OF THAT TRAIN
@$query1e=mysql_query("SELECT * FROM entry_ir_route WHERE 
station_id = '$stationId1' ORDER BY station_arrival_time ASC");
while($row1e=mysql_fetch_array($query1e))
{ 
//echo 'loop 1';
@$train_id1	= $row1e['train_id']; 
@$station_id1	= $row1e['station_id'];
@$line_id1= $row1e['line_id'];
@$week_day1	= $row1e['week_day'];
@$station_arrival_time1	= $row1e['station_arrival_time'];
@$station_departure_time1	= $row1e['station_departure_time'];

@$query1f=mysql_query("SELECT * FROM entry_ir_route WHERE 
station_id = '$stationId2' ORDER BY station_arrival_time ASC");
while($row1f=mysql_fetch_array($query1f))
{ 
@$train_id2	= $row1f['train_id']; 
@$station_id2	= $row1f['station_id'];
@$line_id2 = $row1f['line_id'];
@$week_day2	= $row1f['week_day'];
@$station_arrival_time2	= $row1f['station_arrival_time'];
@$station_departure_time2	= $row1f['station_departure_time'];
 
if($train_id1==$train_id2 AND ($station_arrival_time2 > $station_arrival_time1 AND $week_day1>=$week_day1)  )
{

// FETCH TRAIN DETAILS    TIMING IN STATIONS STATION NAME ETC DATA
@$query1o=mysql_query("SELECT * FROM entry_ir_route WHERE 
train_id = '$train_id1'
AND  week_day >= '$week_day1' AND  week_day <= '$week_day2' 
AND  station_arrival_time >= '$station_arrival_time1' AND  
station_arrival_time <= '$station_arrival_time2'
AND  ( station_id = '$stationId1' OR   station_id = '$stationId2' ) 
");

while($row1o=mysql_fetch_array($query1o))
{ 
@$this_train_id	= $row1o['train_id']; 
@$this_station_id	= $row1o['station_id'];
@$this_line_id = $row1o['line_id'];
@$this_week_day	= $row1o['week_day'];
@$this_station_time	= $row1o['station_time'];
@$this_station_arrival_time	= $row1o['station_arrival_time'];
@$this_station_departure_time	= $row1o['station_departure_time'];

@$thisTrainName = trainIdToName($this_train_id);
@$thisTrainCode = trainIdToCode($this_train_id);
@$thisStationName = stationIdToName($this_station_id);
@$thisDayName = dayIdToName($this_week_day);
//echo $this_train_id.'<br />';
?>
<section class="section">
<div class="col span_20"> <?php echo $thisTrainCode; ?></div>
<div class="col span_20"><?php echo $thisTrainName; ?> </div>
<div class="col span_20"><?php echo $thisStationName; ?> </div>
<div class="col span_10"><?php echo $this_station_arrival_time; ?> </div>
<div class="col span_10"><?php echo $this_station_departure_time; ?> </div>
<div class="col span_20"><?php echo $thisDayName; ?> </div>
</section>
<?php
}

}
}
}
?>

Sorry i have attested a TXT File because file uploader shown me error that i dont have permission to upload SQL file, just change extension is required from TXT to SQL.

entry_ir_route.txt

[AMITKUMAR]

Quite honestly, ignoring the data retrieval issues you're having, there are several issues with the code you've posted. I'm not sure what your instructor is teaching, but he or she certainly shouldn't be advocating the use of mysql_* functions as they not only have deprecated for about a decade, they're completely removed from the current version of PHP. Hopefully he or she is also explaining the idea of data sanitation because you're dropping variables directly into your query, practically inviting a SQL injection attack.

 

I'd look very closely at the PDO library and prepared statements - if nothing else you may get extra credit, and you'll definitely get more robust, safer code out of it.

 

Actually sir PHP is not in my Course i have myself choosen this subject because i want to continue this project as commercial after successful graduation. so doing as scratch which am gathering from inet

[Jacques1]

I don't mean to put you down, but there's a lot to learn before you can use your code on any kind of real website. In fact, turning this project into a business might be a bit overambitious. You should concentrate more on learning the basics of programming and PHP in particular.

 

You already seem to struggle with the basic PHP syntax. Do you understand that the “@” symbol in PHP denotes the error suppression operator? You do not put it in front of every variable. Actually, you shouldn't use it anywhere in your code.

 

And as three(!) people already told you, the mysql_* functions are ancient. They have been removed from PHP and must not be used in new projects. Use PDO. Use PDO. Use PDO. Click on this link for further information.

 

Maybe you should postpone your project for a few days and start with the basics like the PHP syntax and PDO. Otherwise you may end up with thousands of lines of bad code.

[Barand]

 

SELECT * FROM entry_ir_route WHERE

train_id = '$train_id1'

AND  week_day >= '$week_day1' AND  week_day <= '$week_day2'

AND  station_arrival_time >= '$station_arrival_time1' AND  

station_arrival_time <= '$station_arrival_time2'

AND  ( station_id = '$stationId1' OR   station_id = '$stationId2' )

You still haven't said what values you used when your query doesn't work

[AMITKUMAR]

 

SELECT * FROM entry_ir_route WHERE

train_id = '$train_id1'

AND  week_day >= '$week_day1' AND  week_day <= '$week_day2'

AND  station_arrival_time >= '$station_arrival_time1' AND  

station_arrival_time <= '$station_arrival_time2'

AND  ( station_id = '$stationId1' OR   station_id = '$stationId2' )

I have station_id = '$stationId1' = DURG

  station_id = '$stationId2' = KHARAGPUR

 

Search result showing = 12869 CST HWH Weekly, DURG -> KHARAGPUR 5:25 -16:50

but another train was not showing which is 12101 Jananeshwari on DB?

 

When i feed: stationid1 = BHOPAL

stationid2= DURG

 

Search result showing = 12853 AMARKANTAK EXPRESS, DURG -> BHOPAL 18:20 -10:30

but quit shocking why not showing 12854 which is correct train instead of above also residing in db?

[Barand]

 

SELECT * FROM entry_ir_route WHERE

train_id = '$train_id1'

AND  week_day >= '$week_day1' AND  week_day <= '$week_day2'

AND  station_arrival_time >= '$station_arrival_time1' AND  

station_arrival_time <= '$station_arrival_time2'

AND  ( station_id = 'DURG' OR   station_id = 'KHARAGPUR' )

 

None of these values you mention in the results (12869, CST, HWH, Weekly, DURG, KHARAGPUR) appear in the data dump you provided, so there is no way your query produced those results.

 

You say stationid1 = DURG, yet the ids are numeric in the data. And what about the other 5 values in the query?

 

So, when you can you can provide accurate, meaningful information I will help. Until then I am not going to waste any more of my time