ajax post multiple values for mysql query

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);
}

?>

Use the url as it is... an url and set ajax method post or get. Use data: in you ajax to pass whatever you want. Example: data: {id: sid, var1:var1 — cpugourou
– cpugourou, Commented Apr 2, 2016 at 20:28
That's exactly what i tried, but i can't get it right. I don't know how to use the post method and still get the right query for the #docentddl. — RoyBerner
– RoyBerner, Commented Apr 2, 2016 at 20:47
type:"post", url:"Docent.php", data:{var1: var1, var2:var2}. Delete dataType and contentType. In PHP $_POST('var1') , $_POST('var2'). — cpugourou
– cpugourou, Commented Apr 2, 2016 at 20:54
Also use html('blabla') instead of empty plus append . This replace it all. Then append in the each is fine. — cpugourou
– cpugourou, Commented Apr 2, 2016 at 20:56
Thank you for your suggestions. I've tried it, but i still can't get it to work. I.ve updated my code and add the php code. — RoyBerner
– RoyBerner, Commented Apr 2, 2016 at 21:14

cpugourou · Accepted Answer · 2016-04-02 23:14:26Z

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']);
}

Collectives™ on Stack Overflow

ajax post multiple values for mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related