Hey all. Me again.
I am working with a database that contains data about multiple cruises. I am setting up a site that will allow the user to select multiple things. These include:
Name of cruise ship.
Departure Port.
Port of Calls.
These items are in three pull down menus. The user has the ability to select one of 5 (or more options depending on the pull down menu) or select 'any'.
The important databases are the following:
SHIP
SHIP NUM (619, 620, 607, 610, 602)
SHIP_NAME(ONE, TWO, THREE, FOUR, FIVE)
CAPTAIN_NAME(NAME1, NAME2, NAME3, NAME4, NAME5)
YEAR_BUILT(1, 2, 3, 4, 5)
PORT_OF_CALL
PORT_NUM(1,2,3,4,5)
PORT_NAME(PORT1, PORT2, PORT3, PORT4, PORT5)
LOCATION(LOC1, LOC2, LOC3, LOC4, LOC5)
CRUISE
DEPART_DATE(2009, 2001, 2007, 2005, 2000)
SHIP_NUM(619,601,607,610,602)
DEPART_PORT(DEP1, DEP2, DEP3, DEP4, DEP5)
Hope these help. Now my question is:
How would i get the following information to print correctly. Lets say the user selects ANY for all three options (port of call, boat name, and departure port). How could i create a select statement to display ALL cruises with the following information:
SHIP_NAME - CAPTAIN - DEPART PORT - PORT OF CALLS
Is this possible?
I have tried multiple SQL statements that semi work. But it just aint happening. Here my current code:
<?php include 'config.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link href="design.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search for <?php echo "$_POST[boat]" ?></title>
</head>
<body>
<?php
$port = $_POST['port'];
$boat = $_POST['boat'];
$depart_port = $_POST['depart'];
if (strcmp($port,"Any") == 0)
$port = '%';
else
$port = $_POST['port'];
if (strcmp($boat,"Any") == 0)
$boat = '%';
else
$boat = $_POST['boat'];
if (strcmp($depart_port,"Any") == 0)
$depart_port = '%';
else
$depart_port = $_POST['depart'];
$sql = "select DISTINCT S.SHIP_NAME, S.CAPTAIN_NAME, C.DEPART_PORT, C.SHIP_NUM from SHIP S, CRUISE C where (S.SHIP_NAME = \"$boat\" OR S.SHIP_NAME LIKE '$boat') GROUP BY C.DEPART_PORT";
//query the database
$result = mysql_query($sql);
//show the number of rows in result set
$numrows = mysql_num_rows($result);
//make table
echo '<table border ="1">';
echo '<tr>';
echo '<td>',"Ship Name",'</td>';
echo '<td>',"Captain",'</td>';
echo '<td>',"Depart Port",'</td>';
echo '<td>',"Ports of call",'</td>';
echo '<tr>';
for($i=0;$i<$numrows;$i++)
{//display info from table
echo '<tr>';
$row=mysql_fetch_array($result);
echo '<td>',($row[0]),'</td>';
echo '<td>',($row[1]),'</td>';
echo '<td>',($row[2]),'</td>';
$ship_num = $row[3];
echo '<td>';
$sql1 = "select P.PORT_NAME, P.LOCATION FROM SHIP S, ROUTE R, CRUISE C, PORT_OF_CALL P WHERE S.SHIP_NUM = C.SHIP_NUM AND S.sHIP_NUM = R.SHIP_NUM AND R.PORT_NUM = P.PORT_NUM AND S.SHIP_NUM = '$ship_num'";
//query the database
$result1 = mysql_query($sql1);
$numrows1 = mysql_num_rows($result1);
for($j=0;$j<$numrows1;$j++)
{
$row1=mysql_fetch_array($result1);
echo $row1[0];
echo ', ';
echo $row1[1];
echo '; ';
}
echo '</td>';
echo '</tr>';
}//end for
echo '</table>';
?>
</body>
</html>This gives the following data:
Ft. Lauderdale, FL5Ship Name Captain Depart Port Ports of call
Victoria Walter Ralegh Ft. Lauderdale, FL Istanbul, Turkey; Venice, Italy; Madrid, Spain;
Victoria Walter Ralegh Miami, FL Venice, Italy; Town A, Morocco; Town C, Ireland;
Victoria Walter Ralegh New Orleans Istanbul, Turkey; Madrid, Spain; Town B, England;
Victoria Walter Ralegh New York, NY Town A, Morocco; Town B, England; Town C, Ireland;
Victoria Walter Ralegh Port Canaveral, FL Madrid, Spain; Town B, England; Town C, Ireland;
Hope you can help me out here. Thanks a ton.