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.

I have attached my current database script. So if you really want to spend some time seeing how much of a mess im in, you can run this and test some sql statements with it!

Thank you for taking time to look this stuff over. Its REALLY appreciated.

Current site:

http://turing.cs.niu.edu/~z109079/

Check it out see what currently occurs! Thanks

Attachments
CREATE TABLE CUSTOMER
( CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CUSTOMER_NAME CHAR(35) NOT NULL,
STREET CHAR(20),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
PHONE_NUM CHAR(12),
EMAIL CHAR(25) );

INSERT INTO CUSTOMER
VALUES 
('101','Anthony Hogan','1214 Lucinda','Dekalb','IL','60115','555-655-0912', 'z111111@niu.edu');

INSERT INTO CUSTOMER
VALUES
('100','Tom Smith','1 Stevenson Dr','Dekalb','IL','60115','555-421-1122', 'z222222@niu.edu');

INSERT INTO CUSTOMER
VALUES
('102','George Petty','1st Washington','Washington','WA','12125','555-000-0112', 'z333333@niu.edu');

INSERT INTO CUSTOMER
VALUES
('103','Jacob Foster','10 N. Lucinda','Dekalb','IL','60115','555-161-8230', 'z444444@niu.edu');

INSERT INTO CUSTOMER
VALUES
('104','Mark Coste','453879 Wauconda','New York','NY','65145','555-041-0342', 'z555555@niu.edu');

CREATE TABLE SHIP
( SHIP_NUM CHAR(3) PRIMARY KEY,
SHIP_NAME CHAR(10),
CAPTAIN_NAME CHAR(35) NOT NULL,
YEAR_BUILT CHAR(4) );

INSERT INTO SHIP
VALUES
('619','Victoria', 'Walter Ralegh','2008');

INSERT INTO SHIP
VALUES
('601','Lusitania', 'Santa Maria','2005');

INSERT INTO SHIP
VALUES
('607','Mayflower', 'Henry Morgan','2009');

INSERT INTO SHIP
VALUES
('610', 'Titanic II', 'Edward "Blackbeard" Teach','2008');

INSERT INTO SHIP
VALUES
('602','Arizona II', 'James Cook', '2008');

CREATE TABLE CABIN_TYPE
( CABIN_TYPE CHAR(20) PRIMARY KEY,
PRICE DECIMAL(8, 2) NOT NULL,
DESCRIPTION CHAR(110) );

INSERT INTO CABIN_TYPE
VALUES
('Luxury A+', 1250.99 , 'The perfect room with everything you need');

INSERT INTO CABIN_TYPE
VALUES
('Third Class Box', 99.99 , 'Smaller than a box');

INSERT INTO CABIN_TYPE
VALUES
('Second Class', 399.99 , 'A little bit of everything' );

INSERT INTO CABIN_TYPE
VALUES
('Balcony', 1750.99 , 'The perfect room with everything you need - and a view!' );

INSERT INTO CABIN_TYPE
VALUES
('Luxury II', 789.99 , 'The perfect room with ALMOST everything you need' );

INSERT INTO CABIN_TYPE
VALUES
('Base Cabin', 289.99 , 'The basic room!' );

CREATE TABLE EXCURSION
( EX_TYPE CHAR(2),
PRICE DECIMAL(5, 2) NOT NULL,
DESCRIPTION CHAR(35),
DURATION CHAR(7),
PORT_NUM CHAR(2),
PRIMARY KEY (EX_TYPE, PORT_NUM) );

INSERT INTO EXCURSION
VALUES
('AA', 89.99, 'Scuba Diving', '4 hrs', '11');

INSERT INTO EXCURSION
VALUES
('AB', 74.99, 'Guided Tour', '8 hrs', '12');

INSERT INTO EXCURSION
VALUES
('BB', 105.99, 'Swim with dolphins', '2 hrs', '11');

INSERT INTO EXCURSION
VALUES
('BA', 34.99, 'Shopping Trip', '4 hrs', '10');

INSERT INTO EXCURSION
VALUES
('CC', 89.99, 'Historic Tour', '6 hrs', '15');

INSERT INTO EXCURSION
VALUES
('CD', 19.99, 'Tennis', '3 hrs', '13');

INSERT INTO EXCURSION
VALUES
('DD', 34.99, 'Guided tour of architecture' , '4.5 hrs', '14');

CREATE TABLE PORT_OF_CALL
(PORT_NUM CHAR(2) PRIMARY KEY,
PORT_NAME CHAR(15),
LOCATION CHAR(15) );

INSERT INTO PORT_OF_CALL
VALUES
('10', 'Istanbul', 'Turkey');

INSERT INTO PORT_OF_CALL
VALUES
('11', 'Venice', 'Italy');

INSERT INTO PORT_OF_CALL
VALUES
('12', 'Madrid', 'Spain');

INSERT INTO PORT_OF_CALL
VALUES
('13', 'Town A', 'Morocco');

INSERT INTO PORT_OF_CALL
VALUES
('14', 'Town B', 'England');

INSERT INTO PORT_OF_CALL
VALUES
('15', 'Town C', 'Ireland');

CREATE TABLE CREDIT_CARD
(CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CRED_TYPE CHAR(10),
NUMBER CHAR(16),
EXP CHAR(10),
CARD_HOLDER_NAME CHAR(25),
BILLING_ADD CHAR(45) );

INSERT INTO CREDIT_CARD
VALUES
('101', 'VISA', '5466415875986598', '01/11/11', 'Anthony Hogan', '1214 Lucinda');

INSERT INTO CREDIT_CARD
VALUES
('100', 'MASTERCARD', '5466415875986111', '01/07/12', 'Tom Smith', '1 Stevenson Dr');

INSERT INTO CREDIT_CARD
VALUES
('121', 'VISA', '546616025897489', '11/03/11', 'George Petty', '1st Washington');

INSERT INTO CREDIT_CARD
VALUES
('107', 'MASTERCARD', '5466112233558899', '12/12/12', 'Jacob Foster', '10 N. Lucinda');

INSERT INTO CREDIT_CARD
VALUES
('145', 'VISA', '5466415875986598', '01/01/16', 'Mark Coste', '453879 Wauconda');

CREATE TABLE CRUISE
(ORDER_DATE DATE,
SHIP_NUM CHAR(3) PRIMARY KEY,
DEPART_PORT CHAR(25) );

INSERT INTO CRUISE
VALUES
('01/11/08', '619', 'Miami, FL');

INSERT INTO CRUISE
VALUES
('06/07/09', '601', 'Ft. Lauderdale, FL');

INSERT INTO CRUISE
VALUES
('09/01/09', '607', 'Port Canaveral, FL');

INSERT INTO CRUISE
VALUES
('06/10/09', '610', 'New Orleans');

INSERT INTO CRUISE
VALUES
('06/12/09', '602', 'New York, NY');

CREATE TABLE BOOKING
(CUSTOMER_NUM CHAR(3),
ORDER_DATE DATE,
SHIP_NUM CHAR(3), 
PRIMARY KEY(CUSTOMER_NUM, SHIP_NUM) );

INSERT INTO BOOKING
VALUES
('100', '07/09/08', '619');

INSERT INTO BOOKING
VALUES
('101', '08/04/08', '619');

INSERT INTO BOOKING
VALUES
('102', '07/03/08', '601');

INSERT INTO BOOKING
VALUES
('103', '04/07/08', '607');

INSERT INTO BOOKING
VALUES
('104', '03/01/08', '610');

CREATE TABLE AMENITIES
(SHIP_NUM CHAR(3) PRIMARY KEY,
AM_NAME CHAR(20),
AM_DESC CHAR(50) );

INSERT INTO AMENITIES
VALUES
('601', 'Pool', 'BIG POOL');

INSERT INTO AMENITIES
VALUES
('602', 'Casino', 'lots of tables/slots');

INSERT INTO AMENITIES
VALUES
('607', 'Theatre', 'Movies/Shows');

INSERT INTO AMENITIES
VALUES
('610', 'Dance Club', 'Dance the night away!');

INSERT INTO AMENITIES
VALUES
('619', 'Climbing Wall', 'Rock climbing...');

CREATE TABLE ROUTE
(SHIP_NUM CHAR(3),
PORT_NUM CHAR(2),
PRIMARY KEY(PORT_NUM, SHIP_NUM) );

INSERT INTO ROUTE
VALUES
('601', '10');

INSERT INTO ROUTE
VALUES
('601', '11');

INSERT INTO ROUTE
VALUES
('601', '12');

INSERT INTO ROUTE
VALUES
('602', '13');

INSERT INTO ROUTE
VALUES
('602', '14');

INSERT INTO ROUTE
VALUES
('602', '15');

INSERT INTO ROUTE
VALUES
('607', '12');

INSERT INTO ROUTE
VALUES
('607', '14');

INSERT INTO ROUTE
VALUES
('607', '15');

INSERT INTO ROUTE
VALUES
('610', '10');

INSERT INTO ROUTE
VALUES
('610', '12');

INSERT INTO ROUTE
VALUES
('610', '14');

INSERT INTO ROUTE
VALUES
('619', '11');

INSERT INTO ROUTE
VALUES
('619', '13');

INSERT INTO ROUTE
VALUES
('619', '15');
This article has been dead for over six months. Start a new discussion instead.