drew.d.lenhart

programming, software, technology, anything on my mind...

PHP, Ajax, And Selections

2014/10/29

I ran into a situation earlier that I quickly overcame and thought I would pass the knowledge along. I ran into a small situation where I needed TWO select boxes. Yes, your thinking no big deal, but I needed to run a couple SQL queries based on what was selected. The first select box pulls data from a MySQL database for selections. When making a selection, the second box takes the first selection, and performs a second SQL query to populate more options. To accomplish this, I am using PHP, Ajax, and MySQL.

For Example:

selectexamp

**I'll be using mock data below...


Set up PHP file. Create the first select box that fetches data and create a placeholder for the second. Javascript and Ajax will do the rest.

<html><head></head><body>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

<script src="http://ajax.js"></script>
<?php
include('connect.php'); //Dont forget your db connection information

        $sql = "SELECT * FROM table_name";
        $sth = $conn->query($sql);
        if (!$sth) {
            die("Database query failed!!! " . mysql_error());
        }
        // Set fetching mode
        $sth->setFetchMode(PDO::FETCH_ASSOC);
?>
<form id="" method="POST" name="" action="">
<?php
    //First Select Box
    echo "<select id = 'state' name='state'>";
    echo "<option value=''>-Select State-</option>";
    foreach ($sth as $row) :   
        $state = $row['state'];
        echo "<option value='" . $state . "'>" . $state . "</option>";                
    endforeach;
    echo "</select>";
$conn = null;
?>
<!--Second Selection Box-->                   
<select id = 'city' name='city'>
    <option>-Select City-</option>
</select>

<input name="do_something" id ="do_something" type="submit" value="GO!">
</form>

</body></html>

Now create the ajax.js javascript file. On document change, ajax is executing get_info.php. This PHP page is making the second query. Notice data is being posted through the ajax script:

$(document).ready(function(){
  $("#state").change(function(){
    var get_state = $("#state").val();
      $.ajax({
        type:"POST",
        url:"get_info.php",
        data:"state="+get_state,
        success: function(data) {
        $("#city").html(data);
        }
      });
    });
 });

Also notice that upon success, the data displays in the "#city" select box created in the first file.

Lastly, the get_info.php is needed to perform the final query.

<?php
include('includes/connect.php'); //Dont forget db connection info

$state = $_POST['state'];

$sql = "SELECT * FROM another_table WHERE state='$state'";

    $sth = $conn->query($sql);
    if (!$sth) {
        die("Database query failed!!! " . mysql_error());
    }
    // Set fetching mode
    $sth->setFetchMode(PDO::FETCH_ASSOC);

    foreach ($sth as $row) :   
        $city = $row['city'];
        echo "<option value='" . $city . "'>" . $city . "</option>";                
    endforeach;

$conn = null;
?>

Thats it! Thanks for reading. If I missed something or the article is not clear, please contact me.

--Drew