php/mysql query help: populate a list based on selected items

wabash · March 15, 2011

Hello,

I'm looking for an example or tutorial on how to populate a list based on selected items from multiple drop down menus. Similar to how a real-estate website works or how cars.com works.

Using cars as an example how would I go about echoing search results from three different drop down menus? Say the drop down categories are: Make, Model and Color? A user selects one out of every category and then clicks the search button. All items that meet the specifications of the search are printed.

Any help would be greatly appreciated.

kickstart · March 15, 2011

Hi

Personally I would have the select lists containing the items but with values of the ID fields of the item.

For example, say you had a table of makes:-

CarMakeTable

Id, Make

1, Alfa Romeo

2, Audi

3, Bentley

And a table of actual cars

ActualCarsTable

Id, Make, Price

1, 1, 10000

2, 1, 11000

3, 1, 5000

4, 2, 7000

5, 2, 4000

6, 2, 11000

7, 3, 20000

You would have a drop down list of (say):-

<select name="make">
<option value="1">Alfa Romeo</option>
<option value="2">Audi</option>
<option value="3">Bentley</option>
</select>

Then to get all the details for a selected make:-

$sql = "SELECT a.Id, b.Make, a.Price
FROM ActualCarsTable a
INNER JOIN CarMakeTable b
ON a.Make = b.Id
WHERE b.Id = ".intval($_REQUEST['make'];

All the best

Keith

wabash · March 15, 2011

Hey Keith,

I can't thank you enough. This gets me going in the right direction.

wabash · April 5, 2011

Hi again, I've been trying to piece this test of mine together based on the example you've provided. I'm able to access the database tables and build the form. I'm just having a hard time figuring out how initiate the code you provided once the user makes a selection from the dropdown and presses submit This is the code I'm looking to implement:

$sql = "SELECT a.Id, b.Make, a.Price
FROM ActualCarsTable a
INNER JOIN CarMakeTable b
ON 'a.Make' = 'b.Id'
WHERE 'b.Id' = ".interval($_REQUEST['make'])";

This is what I've got going so far:


<?php

require_once 'login.php';
$db_server = mysql_connect($db_hostname,$db_username,$db_password);

if (!$db_server) die("Unable to connect to MySQL:" . mysql_error());

mysql_select_db($db_database)
or die("Unable to select database:" .mysql_error());

$query = "SELECT * FROM CarMakeTable";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " .mysql_error());

$make = $_POST["make"];

if (!isset($_POST['submit'])) { 

// if page is not submitted to itself echo the form

echo <<<_END
<html>
<head>
	<title>Make Test</title>
</head>
<body>
<form method="post" action="car3.php">
	Select a Car Make:<br />
	<select name="make">
	<option value="1">Alfa Romeo</option>
	<option value="2">Audi</option>
	<option value="3">Bentley</option>
	</select>
	<input type="submit" value="submit" name="submit">
</form>
</body>
</html>
_END;

} else {
$sql = "SELECT a.Id, b.Make, a.Price
FROM ActualCarsTable a
INNER JOIN CarMakeTable b
ON 'a.Make' = 'b.Id'
WHERE 'b.Id' = ".interval($_REQUEST['make'])";

}
?>

I'm getting a syntax error for this line

 $sql = "SELECT a.Id, b.Make, a.Price

any guidance on how I tie in the $sql variable would be so helpful. Continued thanks!

awjudd · April 5, 2011

Your syntax error is actually lower than that ... it is:

<?php
$sql = "SELECT a.Id, b.Make, a.Price
FROM ActualCarsTable a
INNER JOIN CarMakeTable b
ON a.Make = b.Id
WHERE b.Id = ".interval($_REQUEST['make'])";

I removed the ' from around each of the tables in the JOIN. If you want to delimit it somehow, you should be using the backticks (`).

$sql = "SELECT a.Id, b.Make, a.Price
FROM ActualCarsTable a
INNER JOIN CarMakeTable b
ON `a`.`Make` = `b`.`Id`
WHERE `b.Id` = ".interval($_REQUEST['make'])";

~judda

kickstart · April 5, 2011

Hi

As above, but what does the function interval do? Should that be intval?

All the best

Keith

wabash · April 6, 2011

Thanks Juddster/Keith. I've removed the ' from around the the tables which fixed my syntax error. I'm now able to select from the dropdown but get the following error on submission:

Fatal error: Call to undefined function interval()

Here is the code as it is now:

<?php

require_once 'login.php';
$db_server = mysql_connect($db_hostname,$db_username,$db_password);

if (!$db_server) die("Unable to connect to MySQL:" . mysql_error());

mysql_select_db($db_database)
or die("Unable to select database:" .mysql_error());

$query = "SELECT * FROM CarMakeTable";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " .mysql_error());

$make = $_POST["make"];

if (!isset($_POST['submit'])) { 

// if page is not submitted to itself echo the form

echo <<<_END
<html>
<head>
	<title>Make Test</title>
</head>
<body>
<form method="post" action="car3.php">
	Select a Car Make:<br />
	<select name="make">
	<option value="1">Alfa Romeo</option>
	<option value="2">Audi</option>
	<option value="3">Bentley</option>
	</select>
	<input type="submit" value="submit" name="submit">
</form>
</body>
</html>
_END;

} else {
$sql = "SELECT a.Id, b.Make, a.Price
FROM ActualCarsTable a
INNER JOIN CarMakeTable b
ON a.Make = b.Id
WHERE b.Id = ".interval($_REQUEST['make']);

}
?>

Can I get some guidance on how to define the .interval function or is there an easier way to do this section?

Continued thanks

Bill...

awjudd · April 6, 2011

Sorry Bill, as Keith mentioned, I had a typo ... it should be intval instead of interval.

~judda

wabash · April 8, 2011

Thanks guys for your help. I have gotten things somewhat going. What I'm trying to do now is query using selections from two drop downs to find the right car. For this test you can search by price and color. The tables I have now are:

CarMakeTable

Id, Make

1, Alfa Romeo

2, Audi

3, Bentley

ActualCarsTable

Id, Make, Price, Color

1, 1, 10000, 1

2, 1, 11000, 2

3, 1, 5000, 3

4, 2, 7000, 1

5, 2, 4000, 2

6, 2, 11000, 3

7, 3, 20000, 1

CarColorTable

Id, Color

1, Red

2, Green

3, Blue

I'm having luck when searching just the vehicle price with this code:

$amount = $_POST["amount"];

$query = "SELECT ActualCarsTable.Price, CarMakeTable.Make ".
   "FROM ActualCarsTable, CarMakeTable ".
   "WHERE ActualCarsTable.Make = CarMakeTable.ID AND ActualCarsTable.Price = '$amount'";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table 

while($row = mysql_fetch_array($result)){
echo $row['Make']. " and amount is ". $row['Price'];
echo "<br />";
}

But now I'm not sure how to integrate this additional query to search for cars that match the price and color criteria...this is what I have so far I'm getting a syntax error for the query:

<?php    

require_once 'login.php';

$db_server = mysql_connect($db_hostname,$db_username,$db_password);

if (!$db_server) die("Unable to connect to MySQL:" . mysql_error());

mysql_select_db($db_database)
or die("Unable to select database:" .mysql_error());

if (!isset($_POST['submit'])) {

echo <<<_END
<html>
<head>
	<title>Make Test</title>
</head>
<body>
<table style="height: 70px;" border="0" width="400">
<tbody>
<tr>
<td width="200" valign="top">
<form method="post" action="car5.php">
Select Amount:<br />
	<select name="amount">
	<option value="5000">5000</option>
	<option value="7000">7000</option>
	<option value="11000">11000</option>
	</select>
	<input type="submit" value="submit" name="submit">
</form>
</td>
<td width="400" valign="top">
<form method="post" action="car5.php">
Select Color:<br />
	<select name="color">
	<option value="red">Red</option>
	<option value="yellow">Yellow</option>
	<option value="green">Green</option>
	</select>
	<input type="submit" value="submit" name="submit">
</form>
</td>
</tr>
</tbody>
</table>

</body>
</html>
_END;

} else {

$amount = $_POST["amount"];
$color = $_POST["color"];

$query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id"
    . "FROM ActualCarsTable, CarMakeTable, CarColorTable"
    . "WHERE ActualCarsTable.Make = CarMakeTable.ID
. "AND ActualCarsTable.Color = 'CarColorTable.Id'" 
. "AND ActualCarsTable.Price = '$amount'" 
. "AND CarColorTable.Color = '$color'";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table 

while($row = mysql_fetch_array($result)){
echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['color'];
echo "<br />";
}
}
?>

Continued thanks, you have been so helpful thus far!

kickstart · April 8, 2011

Hi

You are not joining all the tables together. Also, personally best to split the join conditions off into an ON clause rather than just using the WHERE clause. You also have a missing " I think

$query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id"
    . "FROM ActualCarsTable"
    . "INNER JOIN CarMakeTable ON ActualCarsTable.Make = CarMakeTable.ID"
    . "INNER JOIN CarColorTable ON ActualCarsTable.Make = CarMakeTable.ID"
. "WHERE ActualCarsTable.Price = '$amount'" 
. "AND CarColorTable.Color = '$color'";

All the best

Keith

wabash · April 10, 2011

Hey Guys,

Just wanted to say thanks for your help, I've got the search query going w/ two drop downs. Here is the code as it stands working

<?php    

require_once 'login.php';

$db_server = mysql_connect($db_hostname,$db_username,$db_password);

if (!$db_server) die("Unable to connect to MySQL:" . mysql_error());

mysql_select_db($db_database)
or die("Unable to select database:" .mysql_error());

$color = $_POST["color"];
$amount = $_POST["amount"];

if (!isset($_POST['submit'])) {

echo <<<_END
<html>
<head>
	<title>Make Test</title>
</head>
<body>
<table style="height: 70px;" border="0" width="400">
<tbody>
<tr>
<td width="200" valign="top">
<form method="post" action="car6.php">
Select Amount:<br />
	<select name="amount">
	<option value="5000">5000</option>
	<option value="7000">7000</option>
	<option value="11000">11000</option>
	</select>
</td>
<td width="400" valign="top">
Select Color:<br />
	<select name="color">
	<option value="red">Red</option>
	<option value="green">Green</option>
	<option value="blue">Blue</option>
	</select>
	<input type="submit" value="submit" name="submit">
</form>
</td>
</tr>
</tbody>
</table>

</body>
</html>
_END;

} else {


$query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id 
	FROM ActualCarsTable 
	INNER JOIN CarMakeTable ON ActualCarsTable.Make = CarMakeTable.Id 
	INNER JOIN CarColorTable ON ActualCarsTable.Color = CarColorTable.Id
	WHERE ActualCarsTable.Price = '$amount'AND CarColorTable.Color = '$color'";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['Color'];
echo "<br />";


}
}
?>

One last question. I'm having a hard time figuring out how to tie in a "no results found" message if the query comes up empty. Any thoughts? Again thanks, this little test has really gotten me helped me over the hump.

Bill...

kickstart · April 11, 2011

Hi

Couple of ways to catch no records found.

You can set a flag before the select and reset it within the loop. So you know if it has gone into the loop and can put out a message based on that.

You can check the number of records returned before the loop.

Or you can initially use an if on the mysql_fetch_array, something like this:-

<?php
$query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id 
	FROM ActualCarsTable 
	INNER JOIN CarMakeTable ON ActualCarsTable.Make = CarMakeTable.Id 
	INNER JOIN CarColorTable ON ActualCarsTable.Color = CarColorTable.Id
	WHERE ActualCarsTable.Price = '$amount'AND CarColorTable.Color = '$color'";

$result = mysql_query($query) or die(mysql_error());

if($row = mysql_fetch_array($result))
{
echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['Color'];
echo "<br />";
while($row = mysql_fetch_array($result))
{
	echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['Color'];
	echo "<br />";
}
}
else
{
echo "No results found<br />";
}
?>

All the best

Keith

wabash · April 12, 2011

This worked great Keith. Many thanks for taking me through this process.

Cheers!

Bill

Sign In

php/mysql query help: populate a list based on selected items

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information