Linked select input box using sql database

Hi

I'm new to using PHP so I'm not 100% certain this is in the right section so please forgive me if it is.

I'm creating a website using php and sql server.

In the registration form I want the user to choose a car make from a dropdown (content from database)

I then want a selection drop down box after this on the same form populated with the car models - but only the models linked to the make chosen by the user.

The car make and model content is within the same table in the database.

I've been searching but not even sure where to start!

 

You're probably after PHP and JS.

This code should will select your manufacturers (you'll need to create your tables, so you may need to change some field/table names).

<?php
// Select all the manufacturers from the database
$query = mysql_query("SELECT id, name FROM `manufacturers` ORDER BY `name` ASC");
// Check how many results returned
if(mysql_num_rows($query) == 0)
{
	// No results returned so output message to user
	echo '<p>Error: there are no manufacturers in the database.</p>';
}
else
{
	// Results returned create select input
	echo '<select name="manufacturer_id" id="manufacturer">';
	// Loop through each result returned from database
	while($row = mysql_fetch_assoc($query))
	{
		// Output option for each result
		echo '<option value="' . $row['id'] . '">' . $row['name'] . '</option>';
	}
	// End select input
	echo '</select>';
}
?>

PHP:

Then, if you were to use the jquery library your JS would be something like the following, you'll need to create a page that selects the models from your database dependant on the manufacturer id specified, the code below sends the manufacturer id in $_GET['manufacturer_id']..

$$(document).ready(function() {
	// Detect a change on the manufacturer select
	$('#manufacturer').change(function() {
		// Store the manufacturer id selected in a var
		var manufacturer_id = $(this).find('option:selected').val();
		
		// Make a request to get the json dependant on the manufacturer id
		$.getJSON('json.php', { action: 'getModels', manufacturer_id: manufacturer_id }, function(json) {
			// Loop through all the models returned
			$.each(json.models, function(i,item) {
				// Add your code to append to a select for the car model
			});
		});
	});
});

Code (markup):

This isn't complete or tested but it should get you off to a good start.

 

Hi Thanks for that - I'll have a look over and see if I can make progress- I think its the JS script that I need to have the change when the option is selected. (where I have the "onChange="this.form.submit();">" which I'm guessing is refreshing the page.

I've not be idle though - this is what I've come up with (code below) - bear in mind I'm new to this so yes its messy - and I'm using sql server 2008 instead of mysql (not an option unfortunately)

Table names are Make, Model and Points - basically want a user to select a make, then model and have a hidden field with the points value.

So far with the below code I have:

Page loads with select make and then the dropdown,
can choose the option
second dropdown appears... choose model - then nothing - which is expected as there is no onchange event in the select option.

Then if I refresh the page it displays the carPoints field... but if there is more than one model it displays more than one... I'm thinking the JS script is what I need to make this work - but if there is anything you can spot where I'm making a dogs dinner of the code - would be much appreciated

<form method="post" action="" name="admin"> 
Select Make:<select name="value1" onChange="this.form.submit();"> 

<?php 
if (empty ($value1)) 
{
     echo(" <option value='Select'>Select Make</option>") ; 
}
else  
{
	echo "<option value='Select'>$value1</option>";
}

	$sql = "SELECT DISTINCT carMake from cars";
	$result = sqlsrv_query( $conn, $sql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));;	
	$count = sqlsrv_num_rows($result);

while($RS = sqlsrv_fetch_array($result))
{
echo("<option value=".$RS['carMake'].">".$RS['carMake']."</option>");
}
echo "</select>";
?>

<?php
if(isset($_POST['value1']))
{ 
$value1=$_POST['value1'];
?>
Select Model <select name="value2" onChange="">
<?php 
if (empty ($value2)) 
{
     echo(" <option value='Select'>Select Model</option>") ; 
}
else 
{
echo(" <option value='Select'>$value2</option> ");
}
	$sql2 = "SELECT * from cars where carMake='$value1'";
	$result2 = sqlsrv_query( $conn, $sql2, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));;	
	$count2 = sqlsrv_num_rows($result);
	
while($RS = sqlsrv_fetch_array($result2))
{
echo("<option value='carmodel'>".$RS['carModel']."</option>");
}
echo "</select>";	
}
else
{
	echo "\n";
}
?>	

<?php
if(isset($_POST['value2']))
{ 
	$value2=$_POST['value2'];	
	$sql3 = "SELECT * from cars where carModel='$value2'";
	$result3 = sqlsrv_query( $conn, $sql2, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));;	
	$count3 = sqlsrv_num_rows($result);		
	while($RS = sqlsrv_fetch_array($result3))
	{
	echo("<input type='text' name='test1' value =".$RS['carPoints']." >");
	}	
}
else{
	echo "\n";
}

?>

Code (markup):