Populate select box from database using jQuery

Question

I am trying to populate a select box from values from a mysql database, using jQuery.

db call:

<?php 
include 'db.php';
$con = mysql_connect($host,$user,$pass);
$dbs = mysql_select_db($databaseName, $con);

$tableName = "tbl_title";
$result = mysql_query("SELECT * FROM $tableName");

$data = array();
while ( $row = mysql_fetch_row($result) )
{
    $data[] = $row;
}
//echo json_encode( $data );    
?>

HTML:

<select id="a1_title">
  <option>Default</option>
</select>

There are a bunch of examples that I have found, but nothing specifically related to what I am looking for, unless the force is not with me today.

Is there a link someone can point me to?

score 10 · Accepted Answer · answered Jun 03 '12 at 13:25

10

The below script will load the dropdown list from the JSON received from the PHP Page.

$(function(){

  var items="";
  $.getJSON("yourPHPPage.php",function(data){

    $.each(data,function(index,item) 
    {
      items+="<option value='"+item.ID+"'>"+item.Name+"</option>";
    });
    $("#a1_title").html(items); 
  });

});

Assuming the received JSON is in this format

[ { "ID" :"1", "Name":"Scott"},{ "ID":"2", "Name":"Jon"} ]

Another thing i noticed is that you are doing SELECT * FROM table name to get the items. I do not think you should do that. You should do only two columns (ID & NAME , if you you have those columns in your table.).

Here is a JSFiddle example to show how to fetch data from the JSON.

answered Jun 03 '12 at 13:25

Shyju

214,206
104
411
497

1

I like this... but it seems that I get "undefined" for the items in the select box. The data returned is: [["1","Mr."],["2","Miss"],["3","Ms."],["4","Mrs."],["5","Dr."]]. How do I modify my code to return the JSON with labels? – NinjaCat Jun 03 '12 at 14:48
Shyju, I know its been 3 years since this answer '-', but why did you say that about the "select * from"? I read it in somewhere else too, but I didn't understand. Is it about some kind of security? – Paula Fleck Jun 04 '15 at 13:00

score 1 · Answer 2 · answered Jun 03 '12 at 13:25

1

 // retrieve value and text from ajax
 var html = "<option value=\""+value+"\">"+text+"</option>";
 $("#a1_title").append(html);

answered Jun 03 '12 at 13:25

Arif

1,601
2
21
34

Manellen · Answer 3 · 2012-06-12T09:30:17.477

I have same problem, your suggestion is work

HTML & JS

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<script src="js/jquery-1.7.1.min.js" type="text/javascript"></script>
<title>Insert title here</title>
</head>
<body>
<script type="text/javascript">
$(function(){
      var items="";
      $.getJSON("get-data.php",function(data){
        $.each(data,function(index,item) 
        {
          items+="<option value='"+item.id+"'>"+item.name+"</option>";
        });
        $("#a1_title").html(items); 
      });
    });
</script>

<select id="a1_title">
  <option>Default</option>
</select>
</body>
</html>

php

include 'configurations/db-connections.php';

$q = "select id, name from college";
$sql = mysql_query($q);
$data = array();
while($row = mysql_fetch_array($sql, true)){
    $data[] = $row; 
};
echo json_encode($data);

Populate select box from database using jQuery

3 Answers3

Linked

Related