How Could i fetch the data from Database in a Drop Down menu

Question

I tried the following but id didn't work

//PHP CODE

$query = "SELECT kategorite FROM kategorite";

$data = mysql_query($conn, $query);

$makes = array();

while($row = mysql_fetch_array($data))
{
array_push($makes, $row["Lloji"]);
}
echo json_encode($makes);

//JAVASCRIPT CODE

$(document).ready(function () {

$.getJSON("getTipin.php", success = function(data)
{
var options = "";

for(var i=0; i < data.length; i++)

{

 options += "<option value='" + data[i].toLowerCase() + "'>" + data[i] + "</option>";

}

$("#type").append(options);

$("type").change();
});

You only select the `kategorite` (`kategorite FROM kategorite`?) fields from the table so `$row` doesn't have `Lloji` key. Also you haven't closed the `ready` function, missing `})`. Please learn debugging. The interpreters will tell you what's wrong with your code. — Ram, Jan 04 '15 at 12:33
Sidenote: The original `mysql_*` functions are deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, use `mysqli_*` or `PDO_MySQL` (as suggested below). — Bjorn, Jan 04 '15 at 14:03

score 2 · Answer 1 · answered Jan 04 '15 at 13:53

The code contains some little errors, in addition to those addressed in the other comments. For example you call change() on $('type') instead of $('#type'). Also, not all browsers take well not being supplied with JSON's content type.

In general this problem is made up of two parts:

Fetch the data and output JSON

// Here I strongly suggest to use PDO.

$dbh = new PDO('mysql:host=localhost;port=3306;dbname=database', 'user', 'password',
    array(
        PDO::ATTR_PERSISTENT        => true,
        PDO::ATTR_EMULATE_PREPARES  => false,
        PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION
    )
);
$query  = "SELECT kat_id, kategorite FROM kategorite WHERE kategorite LIKE :search";
$stmt   = $dbh->prepare($query);

// Always check parameter existence and either issue an error or supply a default
$search = array_key_exists('search', $_POST) ? $_POST['search'] : '%';

$stmt->bindParam(':search', $search, PDO::PARAM_STRING);
$stmt->execute();
$reply = array();
while ($tuple = $stmt->fetch(PDO::FETCH_NUM)) {
    $reply[] = array(
        'value' => $tuple['kat_id'],
        'text' => $tuple['kategorite'],
    );
};

// See: http://stackoverflow.com/questions/477816/what-is-the-correct-json-content-type
Header('Content-Type: application/json');

// Adding Content-Length can improve performances in some contexts, but it is
// needless with any kind of output compression scheme, and if things go as they
// should, here we have either zlib or gz_handler running.

// die() ensures no other content is sent after JSON, or jQuery might choke.
die(json_encode($reply));

Populate combo box from JSON in jQuery

function fillCombo($combo) {
    $.post('/url/to/php/endpoint',
        { search: '%' },
        function(options) {
            for (i in options) {
                $combo[0].options[i] = {
                    value: options[i].value,
                    text : options[i].text
                };
            }
            $combo.change();
        }
    );
}

fillCombo($('#comboBox');

In this case since the data returned is in the same format used by the comboBox, you could also shorten and accelerate things with:

        function(options) {
            $combo[0].options = options;
            $combo.change();
        }

In general you want the server to do as little work as possible (server load costs money and impacts performances), but also the client to do as little work as possible (client load impacts site's perception and responsivity). What data exchange format to use is something almost always worth thinking upon.

For very long lists without paging, for example, you might want to cut the data being sent, by only encoding the option's text. You will then send

[ 'make1','make2','make3'... ]

instead of

[ { "option": "1", "value": "make1" }, { "option": "2", "value": "make2" }, ... ]

and use a slower client cycle to fill up the combo box.

How Could i fetch the data from Database in a Drop Down menu

1 Answers1

Fetch the data and output JSON

Populate combo box from JSON in jQuery