How do I use Ajax to run an SQL query within a php while loop?

Question

I have a table of Data which is pulled via SQL into DataTables. I want to use AJAX to run an SQL query which deleted the row based on $id = $row["id"];.

Index.php:

$link = mysqli_connect("localhost", "bradlyspicer_root", "", "bradlyspicer_ResellerDB");

$id = $_POST['id'];
$deleterow = "DELETE FROM Offences WHERE id = ?";
    if($stmt = mysqli_prepare($link, $deleterow)){ // $link being your connection
        mysqli_stmt_bind_param($stmt, "s", $id);
        mysqli_stmt_execute($stmt);
        echo 'success';
        echo $id;
    } else {
        echo 'fail!';
        printf("Error: %s.\n", mysqli_stmt_error($stmt));
    }

Functions.php:

$id = $_POST['id'];
$deleterow = "DELETE FROM Offences WHERE id = ?";
    if($stmt = mysqli_prepare($link, $deleterow)){ // $link being your connection
        mysqli_stmt_bind_param($stmt, "s", $id);
        mysqli_stmt_execute($stmt);
        echo 'success';
    } else {
        echo 'fail!';
        printf("Error: %s.\n", mysqli_stmt_error($stmt));
    }

Custom.js:

$( ".delbtn" ).click(function(){
    var itemID = $(this).attr("itemID");
    console.log(itemID)
    $.ajax({
        url:"functions.php", //the page containing php script
        data: { id: itemID}, // itemID passed as id
        type: "POST", //request type
        success:function(result){
            alert(result);
        },
        error: function() {
            alert('Error occured');
        }
    });
});

I can't find where I pass the $id in the button from Index.php to Functions.php, any explanation would be appreciated.

Update: Since updating the script and trying to Debug, I'm not getting much of a response from the error which outputs:

fail!Error: .

Put it as a data- attribute on the button, bind a click-handler to the button that grabs that attribute and uses it in the `data` property of the ajax request. See [the documentation for .ajax](https://api.jquery.com/jquery.ajax/) — Patrick Q, Jul 10 '19 at 19:40
@LIGHT No, I just would have liked to see OP put in a little more effort. There's a _lot_ missing between what they have and what they want to accomplish, and much of it could have been found with some simple searching. — Patrick Q, Jul 10 '19 at 19:48
`mysqli_error` needs an argument passed. It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jul 10 '19 at 20:22

LIGHT · Accepted Answer · 2019-07-10T20:32:23.180

2

Index.php:

Add a delete button identifier class delbtn and a data attribute that carries this row's id data-itemID

<?php
    while($row = mysqli_fetch_array($dataTablesResult)){
        $id = $row["id"];
        echo '
        <tr>
          <td>
            <button type="button" data-itemID="'.$id.'" class="delbtn btn btn-danger" >Remove</button>
          </td>
        </tr>
        ';
    }
?>

Functions.php:

Capture $_POST['id'] sent by ajax

$id = $_POST['id'];
$deleterow = "DELETE FROM Offences WHERE id = ?";
if($stmt = mysqli_prepare($link, $deleterow)){ // $link being your connection
    mysqli_stmt_bind_param($stmt, "s", $id);
    mysqli_stmt_execute($stmt);
}

Custom.js:

Run the jQuery function when a button with .delbtn class is clicked. Capture and store the row id from data attribute as $(this).data("itemID"). Then send the data using data: { id: itemID} within ajax request

$(".delbtn").click(function(){
    itemID = $(this).data("itemID");
    $.ajax({
        url:"functions.php", //the page containing php script
        data: { id: itemID}, // itemID passed as id
        type: "POST", //request type
        success:function(result){
            alert(result);
        }
    });
});

edited Jul 10 '19 at 20:32

answered Jul 10 '19 at 19:40

LIGHT

5,604
10
35
78

Note that in Functions.php something will need to actually _call_ `deleteData()`. Perhaps a second data element (something like "action") should be passed, indicating what code should be run within the file. Hard to say without knowing what the file actually looks like. And really, a file named Functions.php probably isn't the appropriate file to be directly calling for something like this. – Patrick Q Jul 10 '19 at 19:52
True, however, I assume, this question was just for question's sake. – LIGHT Jul 10 '19 at 19:56
@Dharman, thanks for pointing it out, I guess `intval()` will work just fine in this case. – LIGHT Jul 10 '19 at 20:17
Right. I'll edit my answer again. I just copied/pasted OP's code and tweaked it as per OP's requirement, so.. – LIGHT Jul 10 '19 at 20:20
Thank you for your thorough explanation on this @LIGHT, I've spent some time reflecting on this, I noticed I had loaded this incorrectly as well as the src for my JS was included in the header. So that has been resolved as well. I am getting an alert but the data isn't being removed and the Alert box isn't actually showing anything. Any feedback would be appreciated. – Bradly Spicer Jul 11 '19 at 15:02
I think you haven't declared `$link` variable. can you paste your `functions.php` code – LIGHT Jul 11 '19 at 15:33
Please see above for the full edit @LIGHT, to confirm, it does now state "Success", however, it doesn't show me the id and the row is not removed. In the rendered page I CAN see that the id is correct: `` so it appears to just be the SQL part? – Bradly Spicer Jul 11 '19 at 15:46
@LIGHT After much debugging and tweaking, I can 100% confirm the issue is related to passing the $id variable. Setting the WHERE id = ? part manually to an id in the database works fine. But using ? does not pass that data over. – Bradly Spicer Jul 11 '19 at 16:13
Managed to fix this by changing `var itemID = $(this).attr("itemID")` to `var itemID = $(this).attr("id")`. – Bradly Spicer Jul 11 '19 at 20:40
Yes, because `itemID` isn't attribute, its `data` attribute, can be pulled by `.data()` – LIGHT Jul 12 '19 at 04:36

score 1 · Answer 2 · answered Jul 11 '19 at 16:07

1

i think if you change this line :

mysqli_stmt_bind_param($stmt, "s", $id);

to this is one:

mysqli_stmt_bind_param($stmt, "i", $id);

it may works

answered Jul 11 '19 at 16:07

sohaieb azaiez

768
9
20

fine, if you write **echo $id;** directly after **$id = $_POST['id'];** what result will be displayed for you ? just to understand if the id is passed correctly from the client or not – sohaieb azaiez Jul 11 '19 at 16:12
Nothing is displayed, I have tried this already, see comments in other thread. $id = $id = $_POST['id']; is not working – Bradly Spicer Jul 11 '19 at 16:14
I'm sorry because i did not notice that you wrote that in comments .. nice, then, that's what I need for now, so tell me just if you write **console.log(itemID)** directly after this line **itemID = $(this).data("itemID");** and check your console to see the result, and tell me if your browser is retreiving the itemID correctly from the Dom element or not ? if yes , then add this option to your ajax request: **processData:false,** and tell me the result – sohaieb azaiez Jul 11 '19 at 16:17
and to be sure for something, your problem was using the **$(this).data("itemID")** instead of the **attr( )** function, so I sugguest to change this line like this ==> **var itemID = $(this).attr("itemID");** – sohaieb azaiez Jul 11 '19 at 16:33
Thanks for the updates to this. The console log only contains the following: `[Violation] 'load' handler took 871ms` When the button is clicked. I have updated the script in my OP to reflect – Bradly Spicer Jul 11 '19 at 17:12
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/196338/discussion-between-bradly-spicer-and-sohaieb). – Bradly Spicer Jul 11 '19 at 19:08

How do I use Ajax to run an SQL query within a php while loop?

2 Answers2