PDA

View Full Version : PHP and MySQL Dynamic Drop Down


cocoonfx
03-28-2012, 11:48 AM
Hi Guys


I am looking at some advise on the best practice for a MySQL database and PHP dynamic drop down. I have used the tutorial on the forum as a bases for my drop downs done by DJ but I am a little stuck.

Ok I have a database where the client wants a drop down for the service levels its members have for example.

Company A has services levels A,B,C,D
Company B has service levels A,C,D,Z

So when he selects A in drop down 1 it will show in Drop down 2 all the companies with A as a service standard and then list them.


<?php
include("includes/connect.php");


$make = $_POST['make'];



if ($make){
/////////////////////////////////////////////////
$query =sprintf("SELECT * FROM main where id='$make' ");
$result =mysql_query($query,$dbh);
$row = mysql_fetch_array($result);

/////////////////////////////////////////////////
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form id="form1" name="form1" method="post" action="dropdown.php" >

<select name="make" onChange="document.forms[0].submit()">
<option value="">Select Make</option>
<option value="1" <?php if(!(strcmp(1, $make))){echo "selected";}?>>A</option>
<option value="2" <?php if(!(strcmp(2, $make))){echo "selected";}?>>B</option>
<option value="3" <?php if(!(strcmp(3, $make))){echo "selected";}?>>C</option>
<option value="4" <?php if(!(strcmp(4, $make))){echo "selected";}?>>D</option>
</select>


<select name="model">
<option value="">Select Model</option>
<?php do { ?>
<option value="<?php echo $row['id']; ?>"><?php echo $row['COMPANY']; ?></option>
<?php }while ($row = mysql_fetch_array($result)); ?>
</select>

</form>

<?php do {?>
<lI>
<?php echo $row['COMPANY']; ?>
</lI>
<?php }while ($row = mysql_fetch_array($result)); ?>

</body>
</html>


In the MySQL database there is a field called SerivceStandard and the values are listed as above (A,B,C,D) and a field called COMPANY.

Can anyone advise the best way to do this?

I am a little rusty with PHP and MySQL search stings so forgive me...

cocoonfx
03-28-2012, 05:49 PM
Hi Guys


Sort of working this out but stuck on how to remove commas from the service level and then show the results for the companies in the second drop down.

I.e campany A has service standard A,B
Company B has C

I need the first drop down to show company A for both the option A and B.


<?php
include("includes/connect.php");


$name = $_POST['name'];



/////////////////////////////////////////////////
//$query =sprintf("SELECT * FROM main where ServiceStandards='$n' ");
//$result =mysql_query($query,$dbh);
//$row = mysql_fetch_array($result);

/////////////////////////////////////////////////

//$stripped = str_replace(array(',', '"'), '', $row['ServiceStandards']);
//$name = $stripped;

if ($name);{
$sql="SELECT id, COMPANY, TOWN, COUNTY, ServiceStandards,Active FROM main WHERE Active='Y' AND ServiceStandards LIKE '%" . $name . "%'";
$result1=mysql_query($sql);
$numrows=mysql_num_rows($result1);

echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>";
}


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form id="form1" name="form1" method="post" action="dropdown.php" >

<select name="name" onChange="document.forms[0].submit()">
<option value="">Select Cat</option>
<option value="A" <?php if(!(strcmp(A, $name))){echo "selected";}?>>A</option>
<option value="B" <?php if(!(strcmp(B, $name))){echo "selected";}?>>B</option>
<option value="C" <?php if(!(strcmp(C, $name))){echo "selected";}?>>C</option>
<option value="D" <?php if(!(strcmp(D, $name))){echo "selected";}?>>D</option>
<option value="E" <?php if(!(strcmp(E, $name))){echo "selected";}?>>E</option>
<option value="M" <?php if(!(strcmp(M, $name))){echo "selected";}?>>M</option>
</select>


<select name="model">
<option value="">Select Model</option>
<?php

while($numrow=mysql_fetch_array($result1)){
$Company =$numrow['COMPANY'];
echo "<option value=".$Company .">" .$Company ."</option>\n";

}
?>

</select>

</form>




</body>
</html>



Help?

cocoonfx
03-29-2012, 10:41 AM
Hi Guys

still struggling with this...


<?php
include("includes/connect.php");


$name = $_POST['name'];


if ($name);{
$sql="SELECT id, COMPANY, TOWN, COUNTY, ServiceStandards, Active FROM main WHERE Active='Y' AND ServiceStandards LIKE '%" .$name. "%'";
$result1=mysql_query($sql);
$numrows=mysql_num_rows($result1);

echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>";
}


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="dropdown.php" >
<select name="name" onChange="document.forms[0].submit()">
<option value="">Select Cat</option>
<option value="A" <?php if(!(strcmp(A, $name))){echo "selected";}?>>A</option>
<option value="B" <?php if(!(strcmp(B, $name))){echo "selected";}?>>B</option>
<option value="C" <?php if(!(strcmp(C, $name))){echo "selected";}?>>C</option>
<option value="D" <?php if(!(strcmp(D, $name))){echo "selected";}?>>D</option>
<option value="E" <?php if(!(strcmp(E, $name))){echo "selected";}?>>E</option>
<option value="M" <?php if(!(strcmp(M, $name))){echo "selected";}?>>M</option>
<option value="Y" <?php if(!(strcmp(Y, $name))){echo "selected";}?>>Y</option>
</select>

<select name="model">
<option value="">Company Name</option>
<?php

while($numrow=mysql_fetch_array($result1)){
$Company =$numrow['COMPANY'];
$Service =$numrow['ServiceStandards'];
echo "<option value=".$Service.">" .$Company."</option>\n";


}

?>
</select>
</form>

</body>
</html>


Ok this is working at the moment how I would expect. When I select D in the first drop down 2 companies are filtered and show in the second drop down. What I can't figure out is if in the second drop down I select company A I then want to below the drop downs show the company details.

Hope this makes sense? help!

cocoonfx
03-29-2012, 03:57 PM
Anyone? really stumped...

edbr
03-30-2012, 02:05 AM
im struggling to understand what you want , but im blaming a cold. can you try again and explain your problem as it stands?

edbr
03-30-2012, 04:46 AM
ah re read it after coffee, where do you want the detail to show?
it looks like the second form option needs to be a link. or a hover tool tip? or am I delirious?

cocoonfx
03-30-2012, 08:56 AM
No its probably the way I have described it. I will make it easier to understand

Table:

Company A - ServiceLevels = A,B,C,D,M
Company B - ServiceLevels = B,C,D

The drop downs need to filter the company name by individual Service level.

So in drop down 1 if I select B the in drop down 2 both companies will show.
If I select A then only Company A should show.

When the user in drop down 2 selects Company A the details need to echo below the drop down showing the full address of Company A. This is the part I am struggling with.

edbr
03-30-2012, 09:58 AM
ok i see i think , i will have to think on . you close the loop inside the form so that is basically where it collapses .

cocoonfx
03-30-2012, 10:00 AM
ok possibly an array is needed some where... crazy thing is I can do this with a search box. not a drop down ;o)

edbr
03-30-2012, 10:29 AM
yes i was thinking array also

edbr
03-31-2012, 05:30 AM
or you have a variable make a select statement based on that where COMPANY ='$Company'

cocoonfx
04-01-2012, 02:29 PM
Hi Edbr


I have given this a go but it doesn't work.

drop 1 = A Drop 2 = Company Name
Company 1
Company 2<--- click/select this

Should display below drop downs.
Company2 - Tamworth

or

Drop 1 = B Drop 2 = Company Name
Company 12 <-- Click/select this
Company 13

Should display below drop downs.
Company 12 - London

For some reason I can not get the select part to display.

edbr
04-02-2012, 03:01 AM
did you try repeating the while loop?

<?php
include("includes/connect.php");


$name = $_POST['name'];


if ($name);{
$sql="SELECT id, COMPANY, TOWN, COUNTY, ServiceStandards, Active FROM main WHERE Active='Y' AND ServiceStandards LIKE '%" .$name. "%'";
$result1=mysql_query($sql);
$numrows=mysql_num_rows($result1);

echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>";
}


?>
////////////////////////////////////


<form id="form1" name="form1" method="post" action="dropdown.php" >
<select name="name" onChange="document.forms[0].submit()">
<option value="">Select Cat</option>
<option value="A" <?php if(!(strcmp(A, $name))){echo "selected";}?>>A</option>
<option value="B" <?php if(!(strcmp(B, $name))){echo "selected";}?>>B</option>
<option value="C" <?php if(!(strcmp(C, $name))){echo "selected";}?>>C</option>
<option value="D" <?php if(!(strcmp(D, $name))){echo "selected";}?>>D</option>
<option value="E" <?php if(!(strcmp(E, $name))){echo "selected";}?>>E</option>
<option value="M" <?php if(!(strcmp(M, $name))){echo "selected";}?>>M</option>
<option value="Y" <?php if(!(strcmp(Y, $name))){echo "selected";}?>>Y</option>
</select>

<select name="model">
<option value="">Company Name</option>
<?php

while($numrow=mysql_fetch_array($result1)){
$Company =$numrow['COMPANY'];
$Service =$numrow['ServiceStandards'];
echo "<option value=".$Service.">" .$Company."</option>\n";


}

?>
</select>
</form>



while($numrow=mysql_fetch_array($result1)){
$Company =$numrow['COMPANY'];
$Service =$numrow['ServiceStandards'];
echo " $numrow['TOWN'] ";


}

cocoonfx
04-02-2012, 09:26 AM
Hi


Yeah I tried this but no joy. It seems I can not pull the data from the second drop down

http://www.legionella-conduct.co.uk/dropdown.php

If you look at the page you will see possibly what I mean.

So if you select D you should get 2 companies if you select the bottom one it should show the address of Nalco.

I have tried $model = $_POST['model']; this doesn't work. as well. really stuck!

edbr
04-02-2012, 09:51 AM
can you send me the database with some data and ill have a look, in a day or so , sorry just a bit snowed at the moment so not really concentrating on it, if you go to my profile you can send to the yahoo address or via the villa site in my sig