ajax为mysql查询发布了多个值

Question

I've got this ajax code that passes one variable for a mysql query. I need to pass an other variable. I already got the variable, but i don't know how to add it to the existing code.

this is the code

function Docent(){

var opleidingid = $('#opleidingddl').val();
var datum = $('#datumddl :selected').text();

$('#docentddl').html();
$('#docentddl').html("<option>Loading.....</option>");
$.ajax({
       type:"POST",
       url:"Docent.php"
       data :
       {
       'opleidingid': opleidingid,
       'datum'      : datum
       },
       success: function(data){
       $('#docentddl').html();
       $('#docentddl').html("<option value='0'>Selecteer docent</option>");
       $.each(data,function(i,item){
              $('#docentddl').append('<option value="'+ data[i].Opleiding_ID +'">'+ data[i].Docent+'</option>');
              $('#docentddl').selectpicker('refresh');
              });
       },
       complete: function(){
       }
       });

 }

PHP

<?php

include ('config.php');

$opleidingid    = $_POST['opleidingid'];
$datum        = $_POST['datum'];


$sql=mysql_query("SELECT * FROM Docent_relatie WHERE Opleiding_ID = 'opleidingid'");
if(mysql_num_rows($sql)){
    $data = array();
    while($row=mysql_fetch_array($sql)){
        $data[] = array(
                        'Opleiding_ID' => $row['Opleiding_ID'],
                        'Docent' => $row['Docent'],
                        'OpleidingDatum' => $row['OpleidingDatum']
                        );
    }
    header('Content-type: application/json');
    echo json_encode($data);
}

?>

Answer 1

ok. so I am back to my computer. It should look like something like this. One important comment. you DO NOT want to use mysql deprecated method.

you DO WANT to use PDO instead of mysql or mysqli.

Follow this rule, always:

PDO uses prepared statment that can be reused at will.

1: you prepare the statment using ? per values.

2: you bind the values (by order of appearance in the query, 1,2,3....)

3: you execute the statement.

4: you use a foreach loop to go across the result array, not a while.

To be noted. In PHP, the foreach loop across the result is a redundancy. You can simply directly echo $result and deal with it in javascript (in this case, in the query, instead of select *, select Opleiding_ID,Docent,OpleidingDatum but I did not wanted to "modify" your logic.

Javascript:

function Docent() {
var opleidingid = $('#opleidingddl').val();
var datum = $('#datumddl :selected').text();

$('#docentddl').html("<option>Loading.....</option>");
var datas = {'opleidingid': opleidingid, 'datum': datum};

$.ajax({
    cache: false,
    type: "POST",
    url: "Docent.php",
    data: datas,
    success: function (data) {
        var result = $.parseJSON(data);
        if (result.ctrl === true) {
                $('#docentddl').html("<option value='0'>Selecteer docent</option>");
                $.each(result.response, function (i, item) {
                    $('#docentddl').append('<option value="' + item.Opleiding_ID + '">' + item.Docent + '</option>');
                });
                $('#docentddl').selectpicker('refresh');
        }else{
            alert(result.response);// error message from php
        }
    }
});

}

PHP:

define("SQLHOST", "127.0.0.1");
define("SQLDB", "databasename");
define("SQLUSER", "login");
define("SQLPASS", "password");
try {
    $con = new PDO('mysql:host=' . SQLHOST . ';dbname=' . SQLDB . ';charset=UTF8', SQLUSER, SQLPASS);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt= $con->prepare("SELECT * FROM Docent_relatie WHERE Opleiding_ID = ? AND datum= ?");
} catch (PDOException $e) {
    echo json_encode(['ctrl' => false, 'response' => 'Connection failed to the database: ' . $e->getMessage()]);
}
$opleidingid = (isset($_POST['opleidingid'])) ? $_POST['opleidingid'] : null; // control if ajax sent proper value
$datum = (isset($_POST['datum'])) ? $_POST['datum'] : null; // control if ajax sent proper value
if ($opleidingid !== null && $datum !== null) {
    $stmt->bindParam(1, $opleidingid, PDO::PARAM_INT); // could be PARAM_STR depending if $opleidingid is a int or a string
    $stmt->bindParam(2, datum, PDO::PARAM_STR);
    $stmt->execute();
    $result = $stmt->fetchall(PDO::FETCH_ASSOC);
    $data = array();
    if (count($result) !== 0) {
        foreach ($result as $key => $row) {
            $data[] = array(
                'Opleiding_ID' => $row['Opleiding_ID'],
                'Docent' => $row['Docent'],
                'OpleidingDatum' => $row['OpleidingDatum']
            );
        }
        echo json_encode(['ctrl' => true, 'response' => $data]);
    }else{
        echo json_encode(['ctrl' => false, 'response' => 'No results found']);   
    }
} else {
    echo json_encode(['ctrl' => false, 'response' => 'ajax did not send values']);
}