Using PHP & MySQL to populate dropdown

Question

I am trying to populate a second dropdown list using the value selected from a first dropdown list using PHP and MySQL, and without refreshing the page. I thought this would be simple but can't get it to work so any help would be much appreciated.

So far, I have the following:

HTML Form (form.php)

<select name="list1" id="list1">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
</select>

<select name="list2" id="list2">

</select>

JavaScript (within form.php)

<script type="text/javascript">
  $("#list1").change(function() {
    $("#list2").load("get_list2.php?id=" + $("#list1").val());
  });
</script>

get_list2.php

require_once("config.php");

$q1 = mysql_query("SELECT * FROM mytable WHERE id = '$_GET[id]'");
while($row1 = mysql_fetch_assoc($q1)){
  echo "<option>".$row1['item']."</option>";
}

Thanks!

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — insertusernamehere
– insertusernamehere, Commented Mar 3, 2013 at 15:59
Try calling get_list2.php on it's own with an id and see what's returned. — Anonymous
– Anonymous, Commented Mar 3, 2013 at 15:59
Your code seems vulnerable for SQL injections. Don't use $_GET directly in a statemenet like this. Check out Bobby Tables — insertusernamehere
– insertusernamehere, Commented Mar 3, 2013 at 16:00
If you want to update the second list without refreshing the page you should look into AJAX. If the complete list isn't too long you could also load all elements togehter with the first list and change the displayed items using Javascript. — insertusernamehere
– insertusernamehere, Commented Mar 3, 2013 at 16:03
Thanks for the tips @insertusernamehere - Does the move from MySQL mean that my code will just one day stop working? — mckeegan375
– mckeegan375, Commented Mar 3, 2013 at 16:42

Guicara · Accepted Answer · 2013-03-03 17:11:51Z

4

Like other members have says, you should use PDO (with prepared statements) instead of mysql_.

One possible implementation:

HTML (form.php)

<select name="list1" id="list1">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
</select>

<select name="list2" id="list2"></select>

<script type="text/javascript">
$("#list1").change(function() {
    $.ajax({
        url : "get_list2.php?id=" + $(this).val(),                          
        type: 'GET',                   
        dataType:'json',                   
        success : function(data) {  
            if (data.success) {
                $('#list2').html(data.options);
            }
            else {
                // Handle error
            }
        }
    });
});
</script>

PHP (get_list2.php)

require_once("config.php");

$id = $_GET['id'];

if (!isset($id) || !is_numeric($id))
    $reponse = array('success' => FALSE);
else {
    // Where $db is a instance of PDO

    $query = $db->prepare("SELECT * FROM mytable WHERE id = :id");
    $query->execute(array(':id' => $id));
    $rows = $query->fetchAll(PDO::FETCH_ASSOC);

    $options = "";
    foreach ($rows as $row) {
        $options .= '<option value="'. $row .'">'. $row .'</option>';
    }

    $response = array(
        'success' => TRUE,
        'options' => $options
    );
}

header('Content-Type: application/json');
echo json_encode($response);

PS : not tested but it should works... I guess.

edited Mar 3, 2013 at 17:11

answered Mar 3, 2013 at 16:21

Guicara

1,6982 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mckeegan375 Over a year ago

Thanks Guicara this will be a great help to me!

Guicara Over a year ago

You're welcome ;) I have updated my post: the options (<option...) are now built by the PHP Script. It's more optimized for client.

Collectives™ on Stack Overflow

Using PHP & MySQL to populate dropdown

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related