AJAX PHP Database query not working

Question

I am attempting to learn AJAX, but it is not going so well. On my site users can request training. Once the request is submitted instructors should be able to manage the request from this page. However when trying to "claim" a request, the AJAX does not function correctly, with no errors.

The inner html does not change
The database entry is not changed
I know the php file is being run as I had previously received errors

PHP/HTML/Javascript/AJAX

<table class="sidebar" style="width:50%;">
    <tr><th>My Open Requests</th></tr>
    <div id="myopen">
    <?php
        if(!empty($myopen)){
            foreach($myopen as $request){
                $date = date('F d Y - gA', $request['submitted']);
                echo '<tr><td>'.$request['vid'].' - '.$request['type'].' - '.$request['comments'].' - '.$date.'</td></tr>';
            }
        }
        else{
            echo '<tr><td>NONE</td></tr>';
        }
    ?>
    </div>
</table>

<table class="sidebar" style="width:50%;">
    <tr><th>My Closed Requests</th></tr>
    <?php
        if(!empty($myclosed)){
            foreach($myclosed as $request){
                $date = date('F d Y - gA', $request['submitted']);
                echo '<tr><td>'.$request['vid'].' - '.$request['type'].' - '.$request['comments'].' - '.$date.'</td></tr>';
            }
        }
        else{
            echo '<tr><td>NONE</td></tr>';
        }
    ?>
</table>

<table class="sidebar" style="width:50%;">
    <tr><th>Unclaimed Requests</th></tr>
    <div id="unclaimed">
    <?php
        if(!empty($unclaimed)){
            foreach($unclaimed as $request){
                $date = date('F d Y - gA', $request['submitted']);
                $id = $request['id'];
                $name = "'".$this->registry->getData('firstname').' '.$this->registry->getData('lastname')."'";
                echo '<tr><td>'.$request['vid'].' - '.$request['type'].' - '.$request['comments'].' - '.$date.' <a href="" onclick="claim('.$id.','.$name.')">Claim</a></td></tr>';

            }
        }
        else{
            echo '<tr><td>NONE :)</td></tr>';
        }
    ?>
    </div>
</table>
<table class="sidebar" style="width:50%;">
    <tr><th>Request Log</th></tr>
    <?php
        if(!empty($claimed)){
            foreach($claimed as $request){
                echo '<tr><td>'.print_r($request).'</td></tr>';
            }
        }
        else{
            echo '<tr><td>NONE</td></tr>';
        }
    ?>
</table>
<script>
    function claim(id, name){
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("myopen").innerHTML = xmlhttp.responseText;
            }
        };
        xmlhttp.open("POST", "/lib/AJAX/trainingRequestManage.php", true);
        xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        xmlhttp.send("id="+id+"&name="+name);
    }
</script>

trainingRequestManage.php

<?php
$id = $_POST["id"];
$id = $_POST["name"];

include('http://www.ivaoxa.org/config/db.php');

$sql = "UPDATE trainingRequests SET trainer='".$name."' WHERE id=".$id;

// Prepare statement
$stmt = $db->prepare($sql);

// execute the query
$stmt->execute();

echo 'test';

db.php

<?php
$db = new PDO('mysql:host=localhost;dbname=xx;charset=utf8', 'xx', 'xx');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

And thats not a correct way to prepare a statement so please review that as well so you learn the right way from start — Hanky Panky, Nov 25 '15 at 01:28
$myopen is an array containing the database info for each request that has been claimed. Similarly $unclaimjed are the databse info for the training requests that have not been claimed. The variables are computed before this is included and they get passed fine — IVAO CA-WM, Nov 25 '15 at 01:31
@Hanky I have gotten the prepared statement code directly from w3schools so if it is incorrect I am sorry — IVAO CA-WM, Nov 25 '15 at 01:32
For the record, the reason @Hanky웃Panky is saying it's wrong is because you're directly concatenating user-defined variables, which is incredibly dangerous if you like having things like data security, due to a risk of [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). Admittedly, I don't know how to do it properly, but I'm sure you can google "PHP safe SQL query building" and find out :D — Nic, Nov 25 '15 at 01:40
`$id = $_POST["id"]; $id = $_POST["name"];` ??? error reporting would have helped you here. Undefined variable name notice. — Funk Forty Niner, Nov 25 '15 at 22:34

score 2 · Answer 1 · answered Nov 25 '15 at 01:27

2

Unless http://www.ivaoxa.org/config/db.php is served as a plain file the following line is never going to work:

include('http://www.ivaoxa.org/config/db.php');

More info: http://php.net/manual/en/function.include.php

answered Nov 25 '15 at 01:27

Phellipe Ribeiro

491
3
13

To elaborate, if this file is located on the same server you need to provide the server path to the file. Something like this: include('config/db.php'); – aknatn Nov 25 '15 at 01:40
Not even if I put the db connect info directly into the php – IVAO CA-WM Nov 25 '15 at 02:46
Yup, sounds like you can something wrong with your connection string. Can you post the contents of you db.php? Leave out the sensitive info. Also, looks like you're using PDO so wrap this in a try catch so you can see the error: try { $stmt = $db->prepare($sql); } catch(Exception $e) { echo 'error: '; var_dump($e->getMessage()); } – aknatn Nov 25 '15 at 18:39
@aknatn Posted db.php into question. I added the try catch and still nothing – IVAO CA-WM Nov 25 '15 at 19:10
You should be getting an exception, no errors or anything? Try commenting the include for now and put the call to PDO in the try like from this post http://stackoverflow.com/a/8776392/563043 also verify PDO is installed: http://stackoverflow.com/a/3131455/563043 and that you have error reporting on: error_reporting(E_ALL); – aknatn Nov 25 '15 at 20:53

AJAX PHP Database query not working

1 Answers1