asked on

How do I populate my dropdown menu?

I am trying to populate a dropdown menu with data from a mysql database. I can't get the menu to populate with the provider_city column. After the menu is populated, I want to select one of the entries to have it show all the records in the database with that provider_city column. Not sure what I am doing wrong. Help is appreciated. Thank. I am including the two php files and the database file I am using.cities_menu.zip

Norie

Not 100% sure what you are trying to do but you need to change the ID of the div within the 'container' div, it's currently the same as the ID for the dropdown.

dgarofalo

ASKER

Thank you. What I am trying to do is have the Cities dropdown menu populated with the cities within the database. Then, when you select a city, it will load all of the service providers located within that city.

Chris Stanyon

Hey there,

The problem you have is because the code that generates the <options> from your DB is wrong. An <option> tag should look lilke this:

<option value="someValue">Display Text</option>

What you have is this:

<option value="someValue">

You need to change this line:

$output .= '<option value="'.$row["provider_city"].'">';

To this:

$output .= '<option value="'.$row["provider_city"].'">' . $row["provider_city"] . '</option>';

A cleaner way to do that is to use the sprintf() function:

$output .= sprintf('<option value="%1$s">%1$s</option>', $row["provider_city"]);

dgarofalo

ASKER

Okay, thanks Chris. I can get the menu to populate, but I can't get the correct providers to show up when you select a city. For example, if you click Appleton, all of the providers from Appleton should show up on the page. Can you help me with that part? Thanks.

Norie

Did you try what I suggested, i.e. changing the id of the div within the container div?

For example, I changed that id to 'provider' in load_data_select3.php.

<body>
	<div class="flex-container">
		<div><!--Begin Column 2-->
			<div class="container">
				<h3>City</h3><select id="provider_city" name="provider_city">
					<option value="">
					</option>
					<option value="">
						Show All Specialties
					</option><?php echo fill_city($connect); ?>
				</select><br>
				<br>
				<div class="row" id="provider"></div>
				
			</div></div><!--End Column 2-->
			
			
	</div>
	<script>
	    
	   $(document).ready(function(){  
	        $('#provider_city').change(function(){  
	             var provider_city = $(this).val();  
	             $.ajax({  
	                  url:"load_city.php",  
	                  method:"POST",  
	                  data:{provider_city:provider_city},  
	                  success:function(data){  
	                       $('#provider').html(data);  
	                  }  
	             });  
	        });  
	   });  
	</script>
</body>

Open in new window

To handle the changed id I updated the change event function for the dropdown to this.

	    
	   $(document).ready(function(){  
	        $('#provider_city').change(function(){  
	             var provider_city = $(this).val();  
	             $.ajax({  
	                  url:"load_city.php",  
	                  method:"POST",  
	                  data:{provider_city:provider_city},  
	                  success:function(data){  
	                       $('#provider').html(data);  
	                  }  
	             });  
	        });  
	   });

Open in new window

I also changed the fill_city function so that the cities were actually visible in the dropdown.

 function fill_city($connect)  
 {  
      $output = '';  
      $sql = "SELECT * FROM provider";  
      $result = mysqli_query($connect, $sql);  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '<option value="'.$row["provider_city"].'">'.$row["provider_city"];  
      }  
      return $output;  
 }

Open in new window

Finally, I changed the code in load_city.php to return provider rather than the provider_city.

      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '<div class="container"><div style="border:1px solid #ccc; padding:20px; margin-bottom:20px;">'.'<span class="provider_city">'.$row["provider_name"].'</span>'.'</div></div>';  
      }  
      echo $output;

Open in new window

Chris Stanyon

Norie's answer is generally heading in the right direction. However, given the data from your DB, you should adjust your queries so that you don't end up with duplicate entries in the dropdown. You can do this by using DISTINCT. Also, it's considered best-practice to only select the columns you need, rather than selecting *.

I would also suggest you start making use of Object Oriented notation for your queries. Not only is this considered a more modern approach, you'll find it a bit cleaner as well. Finally, when passing data into your queries, you should really consider user Prepared Statements. This is an approach that will make your code safer as it prevents SQL Injection attacks.

Here's a quick update to your code:

<?php   
$connect = new mysqli("localhost", "root", "wipeout", "dev.sandbox.com");

function fill_city($connect)
{  
    $output = '';  
    $result = $connect->query("SELECT DISTINCT provider_city FROM provider ORDER BY provider_city");  
 
    while ($row = $result->fetch_object()) { 
        $output .= sprintf('<option value="%1$s">%1$s</option>', $row->provider_city);
    }

    return $output;  
} 
?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Chris Stanyon // EE - 29162662</title>

        <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        <script type="text/javascript">
        $(document).ready(function() {
            $('#provider_city').change(function() {  
                $.ajax({  
                    url     : "load_city.php",  
                    method  : "POST",  
                    data    : { provider_city : $(this).val() }
                }).done(function(data) {
                    $('#providers').html(data);  
                });
            });
        }); 
        </script>
    </head>

    <body>
        <h3>City</h3>
        
        <select id="provider_city" name="provider_city">
            <option value=""></option>
            <option value="">Show All Specialties</option>
            <?php echo fill_city($connect); ?>
        </select>
        
        <div class="row" id="providers"></div>
    </body>
</html>

Open in new window

<?php 
$connect = mysqli_connect("localhost", "root", "wipeout", "dev.sandbox.com");  

if (isset($_POST["provider_city"])) {
    if ($_POST["provider_city"] != '') { 
        $stmt = $connect->prepare("SELECT DISTINCT provider_name FROM provider WHERE provider_city = ?");
        $stmt->bind_param("s", $_POST['provider_city']);
    } else {  
        $stmt = $connect->prepare("SELECT DISTINCT provider_name FROM provider");
    }  

    $stmt->execute();
    $result = $stmt->get_result();  

    while ($row = $result->fetch_object()) {
        printf('<div class="container"><div style="border:1px solid #ccc; padding:20px; margin-bottom:20px;"><span class="provider_city">%s</span></div></div>', $row->provider_name);  
    }
}

Open in new window

dgarofalo

ASKER

Thank you Norie and Chris. I think I am closer. I am getting the following error now.

Fatal error: Call to undefined method mysqli_stmt::get_result() in /Applications/AMPPS/www/drop4/load_city.php on line 13

Database file is the same so I am just attaching the two files.

Thanks again for the help. :)
load_city.php
load_data_select3.php

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dgarofalo

ASKER

Thank you Chris and Norie. I appreciate the help!