Jump to content


Photo

Dynamic PHP drop down menu


  • Please log in to reply
10 replies to this topic

#1 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 03 July 2006 - 07:01 PM

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 category. 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 'SELECT' query according to the selection.

Something like "SELECT * from dbtable WHERE category='drop-down-selection'"

I look forward to any response.
Thanks!

#2 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 July 2006 - 07:35 PM

You can do something like.

<?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.
}
?>

What exactly do you want to do with this?
JayBachatero
SMF Developer && Converter Specialist

#3 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 03 July 2006 - 08:00 PM

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 'Accident' or 'Trespass' and so forth.

Here is my code so far:

<?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>

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

#4 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 July 2006 - 08:27 PM

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 WHERE category = '$catID' to WHERE catID = '$catID'

Can you post the table structure ?
JayBachatero
SMF Developer && Converter Specialist

#5 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 04 July 2006 - 11:21 AM

Sure, here's the table layout:

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`)
);

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 'Accident' or 'Trespass'.

#6 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 04 July 2006 - 11:39 AM

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>
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#7 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 04 July 2006 - 08:20 PM

Thank you redarrow,

I'm going to give that a try ASAP.

#8 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 05 July 2006 - 11:23 AM

Hello all,

Redarrow, 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:

<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>

This is what I want to do:

Here is a sample of the Database:
Posted Image

Here is the form which the user sees:
Posted Image

Based on their selection from the drop down menu, the results matching that selected category will be displayed as folows:
Posted Image

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

#9 Chips

Chips
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 05 July 2006 - 12:48 PM

Is this because you aren't doing anything with returned data?
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>
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).


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
}

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 has been selected, you can still display your drop down list, but also then display the table of your results... 

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();
}
?>

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...

#10 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 05 July 2006 - 01:24 PM

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

#11 Iceman512

Iceman512
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 05 July 2006 - 01:44 PM

Hello all,

In case anyone else has this type of problem, here is my
completed, working 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();
}
?>

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

Iceman




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users