PHP and MySQL Dynamic Drop Down

cocoonfx · 03-28-2012, 10: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 Code:


			
<?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, 04: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 Code:


			
<?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, 09:41 AM

Hi Guys

still struggling with this...

PHP Code:


			
<?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, 02:57 PM

Anyone? really stumped...

edbr · 03-30-2012, 01: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, 03: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, 07: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, 08: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, 09: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, 09:29 AM

yes i was thinking array also

03-28-2012, 10:48 AM	#1
cocoonfx Join Date: Nov 2006 Location: Tamworth,UK Posts: 571	PHP and MySQL Dynamic Drop Down 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 Code: <?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... __________________ Regards Cocoonfxmedia http://www.cocoonfxmedia.co.uk/seo.html http://twitter.com/cocoonfxmedia http://facebook.com/cocoonfxmedialtd

03-28-2012, 04:49 PM	#2
cocoonfx Join Date: Nov 2006 Location: Tamworth,UK Posts: 571	Getting Closer 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 Code: <?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? __________________ Regards Cocoonfxmedia http://www.cocoonfxmedia.co.uk/seo.html http://twitter.com/cocoonfxmedia http://facebook.com/cocoonfxmedialtd

03-29-2012, 09:41 AM	#3
cocoonfx Join Date: Nov 2006 Location: Tamworth,UK Posts: 571	Confusing myself Hi Guys still struggling with this... PHP Code: <?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! __________________ Regards Cocoonfxmedia http://www.cocoonfxmedia.co.uk/seo.html http://twitter.com/cocoonfxmedia http://facebook.com/cocoonfxmedialtd

03-29-2012, 02:57 PM	#4
cocoonfx Join Date: Nov 2006 Location: Tamworth,UK Posts: 571	Anyone? really stumped... __________________ Regards Cocoonfxmedia http://www.cocoonfxmedia.co.uk/seo.html http://twitter.com/cocoonfxmedia http://facebook.com/cocoonfxmedialtd

03-30-2012, 01:05 AM	#5
edbr Join Date: Aug 2005 Location: Bali Posts: 8,394	im struggling to understand what you want , but im blaming a cold. can you try again and explain your problem as it stands? __________________ If you're happy and you know it shake your meds! different style links examples Bali Villas Flights to Bali

03-30-2012, 03:46 AM	#6
edbr Join Date: Aug 2005 Location: Bali Posts: 8,394	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? __________________ If you're happy and you know it shake your meds! different style links examples Bali Villas Flights to Bali

03-30-2012, 07:56 AM	#7
cocoonfx Join Date: Nov 2006 Location: Tamworth,UK Posts: 571	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. __________________ Regards Cocoonfxmedia http://www.cocoonfxmedia.co.uk/seo.html http://twitter.com/cocoonfxmedia http://facebook.com/cocoonfxmedialtd

03-30-2012, 08:58 AM	#8
edbr Join Date: Aug 2005 Location: Bali Posts: 8,394	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 . __________________ If you're happy and you know it shake your meds! different style links examples Bali Villas Flights to Bali

03-30-2012, 09:00 AM	#9
cocoonfx Join Date: Nov 2006 Location: Tamworth,UK Posts: 571	ok possibly an array is needed some where... crazy thing is I can do this with a search box. not a drop down ;o) __________________ Regards Cocoonfxmedia http://www.cocoonfxmedia.co.uk/seo.html http://twitter.com/cocoonfxmedia http://facebook.com/cocoonfxmedialtd

03-30-2012, 09:29 AM	#10
edbr Join Date: Aug 2005 Location: Bali Posts: 8,394	yes i was thinking array also __________________ If you're happy and you know it shake your meds! different style links examples Bali Villas Flights to Bali

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode