Forum Moderators: coopster
I am developing a PHP-Mysql site and the project has outgrown my capabilities. I have learnt a lot by reading this site's posts and library but now I am getting anxious as my deadline to deliver approaches...so it's time for me to ask for some guidance.
Here's the scenario:
The site will contain a fairly big database containing information about 2 cruise lines (ships, schedules, images, departure dates, length of the cruise, pricing...the works). Now, I need to allow users to search cruises by different cirteria: cruise line, region, departure month and ship.
Jatar's previous post on the topic was very helpful and this is what I have so far: (now I am using a dummy database just for testing and displaying results in a simple table, the real thing will be far more complicated)
<?php
$conecta = @mysql_connect ('localhost' , 'user' , 'password') or die ('no hay conexion');
mysql_select_db ('database' , $conecta) or die ('no se encuentra la base de datos');
$sql = "SELECT * FROM table where field like '". $_GET['estado']."'" ;
$datos = mysql_query ($sql);
?>
<form name="fff" method="get" action="actions3.php">
<select name="estado">
<option value="one">il</option>
<option value="big">ks</option>
<option value="project">wa</option>
<option value="for">ca</option>
<option value="my">vt</option>
<option value="scarse">ok</option>
<option value="knowledge">az</option>
</select>
<input type="submit" >
</form>
<h3>Resultados de la busqueda</h3>
<table>
<?php while ($renglones = @mysql_fetch_assoc($datos)) {?>
<tr>
<td><?php echo $renglones['first']?></td>
<td><?php echo $renglones['whatever']?></td>
<td><?php echo $renglones['iAmPanickin']?></td>
<td><?php echo $renglones['PanickAttack']?></td>
</tr>
<?php }?>
</table>
This code works fine for the first search criterion but..what next?
Ideally, the search page would contain 3 or more dropdown lists, say one for region, one for ship and one for month of departure. Now, this is wat I want to achieve: let users select only one search criterion, or 2, or 3. That represents no problem but...what happens once they have searched by the first criterion and results have been displayed. Should I create a temporary table containing this first searche's results and then another temporaty table containnig the filtered results of the second search and so on? Is this even possible?
Should I construct the query in such a way that accepts one, two or three variables and then create a brand new query every time the user hits submit regardless of the previous results? Does that even make sense?
Thanks guys
I wouldn't mess around with a temporary table. Run the query, display the results. If the use selects an additional search criteria option, rebuild the query and get a new result set. You can optimize your query by building the appropriate INDEX (or indexes) over the table.
So yes, you have made sense and your latter perspective seems the best approach.
First problem: how do I cross reference tables? I have one table containing just ship names and an ID. The ID field is the primary key and I want to make reference to its values in another table. ie. In the departures table y want to use just the id generated in the ships table and not the whole name of the ship.
Second problem: I need to display only available departure dates (there is no use displaying march sailings in august). Initially, I was going to add 3 different fields for dates. One for day, one for month and one for year and then create ai index for the month´s field. However, it is maybe a better idea to use a date field. But then how do I display only future sailings? SHould I use a PHP timestamp and then somehow compare it to the date field from the database every time a query is used? Some sort of invisible criteria? How should approach this?
Thank you for your help
