Jump to content

Archived

This topic is now archived and is closed to further replies.

Iceman512

Dynamic PHP drop down menu

Recommended Posts

Hello all,

I am trying to create a php menu which allows the user to select certain items from the MySQL database based on their selection from a drop down menu.

For example; I have a table row in my database entitled [color=red]category[/color]. On my web page, I have a drop down menu with 15 categories. I want to allow the user to select a category from the drop-down list and the php code conducts a '[color=red]SELECT[/color]' query according to the selection.

Something like "[color=red]SELECT * from dbtable WHERE category='drop-down-selection'[/color]"

I look forward to any response.
Thanks!

Share this post


Link to post
Share on other sites
You can do something like.

[code]
<?php
echo '
<form action="' ,$_SERVER['PHP_SELF'], '" method="post">
<select name="category">
<option value="1">Category 1</option>
<option value="2">Category 2</option>
<option value="3">Category 3</option>
<option value="4">Category 4</option>
<option value="5">Category 5</option>
</select>
<input type="submit" name="category_submit" value="Submit" />
</form>';

// If category_submit isset continue getting the results
if (isset($_POST['category_submit']))
{
//Cast $catID as an int.
$catID = (int)$_POST['category'];

//Do the query
$select = mysql_query("
SELECT *
FROM tableName
WHERE categoryID = '$catID'") or die(mysql_error());

//Continue with what you want to do with the data.
}
?>
[/code]

What exactly do you want to do with this?

Share this post


Link to post
Share on other sites
Jay, thanks for your post!

I have tried your code snippet, but it doesn't return anything. There are no error messages, but there is also no data.
The reason I need this to function so specifically is because I have been asked to build a database driven site for a registered charity. The database holds form data (in the way of reports) that can be organised by category, date, time, etc.
For added functionality, the user wants to be able to select only certain forms from the db that fall into a specific category, such as '[color=red]Accident[/color]' or '[color=red]Trespass[/color]' and so forth.

Here is my code so far:

[code]<?php
$con = mysql_connect("dbhost","dbname","dbpass");
if (!$con)
  {
  die('Could not connect to the database: ' . mysql_error());
  }

echo '
<form action="' ,$_SERVER['PHP_SELF'], '" method="post">
<select name="category">
<option value="1">Anti Social Behaviour</option>
<option value="2">Abuse (Physical)</option>
<option value="3">Abuse (Verbal)</option>
<option value="4">Accident - Vehicle</option>
<option value="5">Accident - General</option>
</select>
<input type="submit" name="category_submit" value="Submit" />
</form>';

// If category_submit isset continue getting the results
if (isset($_POST['category_submit']))
{
//Cast $catID as an int.
$catID = (int)$_POST['category'];

mysql_select_db("dbname", $con);

//Do the query
$select = mysql_query("
SELECT *
FROM dbtable
WHERE category = '$catID'") or die(mysql_error());

//Continue with what you want to do with the data.
}
?>
<html>
<head>
</head>

<body>
<table width="800px" align="center">
<tr>
<th width="100px" class="data">Date</th>
<th width="100px" class="data">Time</th>
<th width="100px" class="data">Employee</th>
<th width="100px" class="data">Department</th>
<th width="100px" class="data">Location</th>
<th width="100px" class="data">Reference</th>
<th width="100px" class="data">Category</th>
</tr><tr>
<td width="100px" class="data"><? echo $row['date']; ?></td>
<td width="100px" class="data"><? echo $row['time']; ?></td>
<td width="100px" class="data"><? echo $row['employee']; ?></td>
<td width="100px" class="data"><? echo $row['department']; ?></td>
<td width="100px" class="data"><? echo $row['location']; ?></td>
<td width="100px" class="data"><? echo $row['reference']; ?></td>
<td width="100px" class="data"><? echo $row['category']; ?></td>
</tr>
</table>
</body>
</html>[/code]

Can you see any errors?
Thanks for any additional help!

Share this post


Link to post
Share on other sites
Ok the way I did it was it was looking for a catID on the category column.  My suggestion is that you do a Primary column with auto_increment and call it catID.  Change [tt]WHERE category = '$catID'[/tt] to [tt]WHERE catID = '$catID'[/tt]

Can you post the table structure ?

Share this post


Link to post
Share on other sites
Sure, here's the table layout:

[code]CREATE TABLE `dbtable` (
`id` int(4) NOT NULL auto_increment,
`date` date NOT NULL,
`time` time NOT NULL,
`employee` varchar(35) NOT NULL,
`department` varchar(35) NOT NULL,
`location` varchar(35) NOT NULL,
`reference` varchar(20) NOT NULL,
`description` varchar(5000) NOT NULL,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
);[/code]

The only problem I can see with assigning an integer to the category row is that the report categories are identified by name, such as '[color=red]Accident[/color]' or '[color=red]Trespass[/color]'.

Share this post


Link to post
Share on other sites
fill in the xxxxxx ok play around enjoy

<html>
<head>
<title>search2</title>
<body>
search me via search box
<form  method="post" action="xxxxx.php">
<SELECT NAME="name">
<?
$db=mysql_connect("xxx","xxx","xxx");
mysql_select_db("xxxxxx", $db);
$query="select * from xxxxxxx";

$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){
echo"<OPTION VALUE='".$record["xxx"]."'>".$record[xxx];
}

?>
</select>
<input type="submit" value="submit">
</form>
</body>
</html>

Share this post


Link to post
Share on other sites
Hello all,

[color=red]Redarrow[/color], I have tried your suggestion, but again, it doesn't return anything. There are no errors, but also no results. It appears as though the drop down list is being populated from the database. Am I setting the form action correctly? Here's my code so far:

[code]
<html>
<head>
<title>Drop Down Results</title>
<body>
search me via search box
<form  method="post" action="<? $_SERVER['PHP_SELF'] ?>">
<SELECT NAME="name">
<?
$db=mysql_connect("dbhost","dbname","dbpass");
mysql_select_db("dbname", $db);
$query="select * from dbtable";

$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){
echo"<OPTION VALUE='".$record["category"]."'>".$record[category];
}

?>
</select>
<input type="submit" value="submit">
</form>
</body>
</html>
[/code]

[color=blue]This is what I want to do:[/color]

[b][color=green]Here is a sample of the Database:[/color][/b]
[img]http://www.bc-security.co.uk/images/DB-Sample.gif[/img]

[b][color=green]Here is the form which the user sees:[/color][/b]
[img]http://www.bc-security.co.uk/images/CT-Select.gif[/img]

[b][color=green]Based on their selection from the drop down menu, the results matching that selected category will be displayed as folows:[/color][/b]
[img]http://www.bc-security.co.uk/images/CT-Results.gif[/img]

I hope this explains my intentions!
Thanks for any further input!

Share this post


Link to post
Share on other sites
Is this because you aren't doing anything with returned data?
[code]
html>
<head>
<title>Drop Down Results</title>
<body>
search me via search box
<form  method="post" action="<? $_SERVER['PHP_SELF'] ?>">
<SELECT NAME="name">
<?
$db=mysql_connect("dbhost","dbname","dbpass");
mysql_select_db("dbname", $db);
$query="select * from dbtable";

$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){
echo"<OPTION VALUE='".$record["category"]."'>".$record[category];
}

?>
</select>
<input type="submit" value="submit">
</form>
</body>
</html>
[/code]
This is all fine, the submit submits back to this same page. But you haven't set it up to actually do anything when submitted...

First thing I'd do is put that into a function (note that it jumps out of php into html at start of function, and back into php at end).


[code]
function displayDropDown() {
?>
search me via search box
<form  method="post" action="<? $_SERVER['PHP_SELF'] ?>">
<SELECT NAME="name">
<?
$db=mysql_connect("dbhost","dbname","dbpass");
mysql_select_db("dbname", $db);
$query="select * from dbtable";

$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){
echo"<OPTION VALUE='".$record["category"]."'>".$record[category];
}

?>
</select>
<input type="submit" value="submit">
</form>
<?php
}
[/code]

The next thing I'd do is add a check to your page when it first loads:
<?php
if(isset($_POST['name'])){
//do all your database query to return the results
} else {
displayDropDown();
}
?>

So if you haven't had a submit (you could do if($_POST['submit']) instead of if(isset($_POST['name'])) ) it will just display your drop down list.

If it [i]has[/i] been selected, you can [i]still[/i] display your drop down list, but also then display the table of your results... 

[code]
if(isset($_POST['name'])){
$name = $_POST['name'];
$db=mysql_connect("dbhost","dbname","dbpass");
mysql_select_db("dbname", $db);
$result=mysql_query("select * from dbtable where category='$name';");

displayDropDown();
?>
<table width="100%">
<tr>
<th>Date</th>
<th>Time</th>
<th>Employee</th>
<th>Category</th>
</tr>
<?php
while($row=mysql_fetch_assoc($result)){?>
<tr>
<td><?php echo $row['date']; ?></td>
<td><?php echo $row['time']; ?></td>
<td><?php echo $row['employee']; ?></td>
<td><?php echo $row['category']; ?></td>
</tr>
<?php
}
?>
</table><?php
} else {
displayDropDown();
}
?>
[/code]

Now this should display the drop down. If you select an option, click submit - it should reload the page (submits to itself) and this time, it should present the drop down at the top, with underneath it a table. The table should consist of all the results (if any) that are pulled from the database.
If you then select another category in the drop down list and hit submit, it should do it again - but with the next category that matches.

Hope that this helps...

Share this post


Link to post
Share on other sites
Chips,

I tried your code and it works like a charm, thank you so much. Also sincere thanks to everyone who has contributed here so far!

Iceman

Share this post


Link to post
Share on other sites
Hello all,

In case anyone else has this type of problem, here is my
completed, working code:

[code]
<?php
function displayDropDown() {
?>
search me via search box
<form  method="post" action="<? $_SERVER['PHP_SELF'] ?>">
<SELECT NAME="name">
<?
echo"<option>Category_1</option>";
echo"<option>Category_2</option>";
echo"<option>Category_3</option>";
echo"<option>Category_4</option>";
?>
</select>
<input type="submit" value="submit">
</form>
<?php
}

if(isset($_POST['name'])){
$name = $_POST['name'];
$db=mysql_connect("dbhost","dbname","dbpass");
mysql_select_db("dbname", $db);
$result=mysql_query("select * from dbtable where category='$name';");

displayDropDown();
?>
<table width="100%">
<tr>
<th>Date</th>
<th>Time</th>
<th>Employee</th>
<th>Category</th>
</tr>
<?php
while($row=mysql_fetch_assoc($result)){?>
<tr>
<td><?php echo $row['date']; ?></td>
<td><?php echo $row['time']; ?></td>
<td><?php echo $row['employee']; ?></td>
<td><?php echo $row['category']; ?></td>
</tr>
<?php
}
?>
</table><?php
} else {
displayDropDown();
}
?>
[/code]

Once again, a huge thank you to everyone who contributed!

Iceman

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.