i have the following tables in my database:
CREATE TABLE `tblbrands` (
`brandid` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
PRIMARY KEY (`brandid`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
CREATE TABLE `tblcars` (
`carid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`brandid` tinyint(2) unsigned NOT NULL,
`typeid` tinyint(2) unsigned NOT NULL,
`model` varchar(30) NOT NULL,
`price` double(9,2) unsigned NOT NULL,
`year` smallint(4) unsigned NOT NULL,
`mileage` int(6) unsigned NOT NULL,
`status` tinyint(1) unsigned NOT NULL,
`description` text NOT NULL,
`image` varchar(60) NOT NULL,
`thumb` varchar(60) NOT NULL,
`sideImage` varchar(60) NOT NULL,
`rearImage` varchar(60) NOT NULL,
`sideThumb` varchar(60) NOT NULL,
`rearThumb` varchar(60) NOT NULL,
`sellerID` smallint(5) unsigned NOT NULL,
`dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`carid`),
KEY `brandid` (`brandid`),
KEY `typeid` (`typeid`),
KEY `status` (`status`),
CONSTRAINT `tblcars_ibfk_1` FOREIGN KEY (`brandid`) REFERENCES `tblbrands` (`brandid`),
CONSTRAINT `tblcars_ibfk_2` FOREIGN KEY (`typeid`) REFERENCES `tblcartypes` (`typeid`),
CONSTRAINT `tblcars_ibfk_3` FOREIGN KEY (`status`) REFERENCES `tblcarstatus` (`statusID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `tblcartypes` (
`typeid` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`typeid`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
i have this php script. it has two select boxes which retreive the options from the database tables above.
<?php
require_once 'autozim/includes/mysql_connect.php';
?>
<html>
<head>
<title>Ajax Cascading drop down test</title>
</head>
<body>
<h3>Ajax Cascading Drop-down test</h3>
<form action="" method="post">
<p><b>Select Car Type:</b>
<select name="carTypeID" id="carTypeID">
<?php
$sql = "SELECT typeid, name FROM tblcartypes ORDER by name";
$result = mysql_query( $sql );
while( $row = mysql_fetch_array( $result ) ) {
if ( isset( $_POST['cartypeid'] ) and ( $_POST['cartypeid'] == $row['typeid'] ) ) {
$selectedCarType = ' Selected ';
} else {
$selectedCarType = '';
}
?>
<option value="<?php echo $row['typeid']; ?>" <?php echo $selectedCarType; ?> ><?php echo $row['name']; ?></option>
<?php
}
?>
</select></p>
<p><b>Select Car:</b>
<select name="carID" id="carID">
<?php
$sql = "SELECT CONCAT( b.name, ' ', c.model ) AS car, c.carid FROM tblbrands AS b, tblcars AS c , tblcartypes AS ct ";
$sql .= "WHERE b.brandid = c.brandid AND c.typeid = ct.typeid";
$result = mysql_query( $sql );
while ( $row = mysql_fetch_array( $result ) ) {
if ( isset( $_POST['carID'] ) && ( $row['carid'] == $_POST['carid'] ) ) {
$selectedCar = ' Selected ';
} else {
$selectedCar = '';
}
?>
<option value="<?php echo $row['carid']; ?>" <?php echo $selectedCar; ?> ><?php echo $row['car']; ?></option>
<?php
}
?>
</select></p>
</form>
</body>
</html>
what i want is when the user selects a value from the first drop down, the second drop down should be populated with cars that have typeid matching the value selected in the first drop down. how do i do this?
