PHP MySQL Delete function in while loop

Question

Evening,

I'm kinda stuck here. I've made a login for our employees to easily create new invoice (it's in danish though, sorry) and almost everything is completed. But one thing I can't figure out how to do, is to delete a material (materiale in danish) from a while loop. I've got a form with a while loop within it, which displays all of the materials, and right next to the material input fields I've created a small a href link, which should delete the field with some help from javascript. It deletes the field perfectly, but doesn't delete the data from our mysql database. So every time I refresh the page, then the data are back. Updating and inserting isn't a problem. The code looks like this:

$ct_update = 0;

foreach ($_POST['beskrivelse_update'] as $k => $value) {
    $beskrivelse_update = addslashes($value);
    $enhed_update           = addslashes($_POST['enhed_update'][$ct_update]);
    $stk_update         = addslashes($_POST['stk_update'][$ct_update]);
    $vejl_eks_moms_update   = addslashes($_POST['vejl_eks_moms_update'][$ct_update]);

    $update_id              = (int)$_POST['update_id'][$ct_update];

    $db->query("UPDATE faktura_materialer SET beskrivelse = '$beskrivelse_update', enhed = '$enhed_update', stk = '$stk_update', vejl_eks_moms = '$vejl_eks_moms_update', subtotal = '$stk_update' * '$vejl_eks_moms_update' WHERE id = '$update_id' LIMIT 1");

    $ct_update++;
}

$ct_ny = 0;

if(isset($_POST['beskrivelse_ny'])){
    foreach ($_POST['beskrivelse_ny'] as $k => $value) {
        $beskrivelse_ny = addslashes($value);
        $enhed_ny           = addslashes($_POST['enhed_ny'][$ct_ny]);
        $stk_ny         = addslashes($_POST['stk_ny'][$ct_ny]);
        $vejl_eks_moms_ny   = addslashes($_POST['vejl_eks_moms_ny'][$ct_ny]);

        $db->query("INSERT INTO faktura_materialer (faktura_id, beskrivelse, enhed, stk, vejl_eks_moms, subtotal) VALUES ('".$_faktura->faktura_id."', '".$beskrivelse_ny."', '".$enhed_ny."', '".$stk_ny."', '".$vejl_eks_moms_ny."', '".$stk_ny."' * '".$vejl_eks_moms_ny."')");

        $ct_ny++;
    }
}

Hopefully you understand what I want to do, otherwise just comment. Have a nice evening peeps :)

Edit:

I want the delete query to be submitted when the user clicks the submit button "Save changes". This is my javascript code:

<script type="text/javascript">
function myFunction()
{
var table = document.getElementById("produkter_rows");
var row = table.insertRow(-1);
var cell1 = row.insertCell(0);
var cell2 = row.insertCell(1);
var cell3 = row.insertCell(2);
var cell4 = row.insertCell(3);
var cell5 = row.insertCell(4);
cell1.innerHTML = '<td><input style="width:450px;" class="text-input" type="text" name="beskrivelse_ny[]" value=""></td>';
cell2.innerHTML = '<td><input style="width:60px;" class="text-input" type="text" name="enhed_ny[]" value=""></td>';
cell3.innerHTML = '<td><input style="width:30px;" class="text-input" type="text" name="stk_ny[]" value=""></td>';
cell4.innerHTML = '<td><input style="width:205px;" class="text-input" type="text" name="vejl_eks_moms_ny[]" value=""></td>';
cell5.innerHTML = '<td><a href="#" onclick="removeRow(this)" id="addNew" title="Slet produkt"><img src="images/icons/slet.gif" width="16" alt="Slet" /></a></td>';
}

removeRow = function(el) {
    $(el).parents("tr").remove()       
}
</script>

When the user clicks the delete icon right next to the material, then it will be deleted. And it does delete the input fields, but not from the database in mysql.

Second edit:

This is HTML and PHP for updating current data in MySQL database:

$materialer_query = $db->query("SELECT faktura_materialer.id as materialer_id, faktura.*, faktura_materialer.* FROM faktura, faktura_materialer WHERE faktura_materialer.faktura_id = faktura.id");

while($mat = $materialer_query->fetch_object()){
    ?>

    <tr>
        <input type="hidden" name="update_id[]" value="<? print $mat->id; ?>" />
        <td><input style="width:450px;" class="text-input" type="text" name="beskrivelse_update[]" value="<? print $mat->beskrivelse; ?>"></td>
        <td><input style="width:60px;" class="text-input" type="text" name="enhed_update[]" value="<? print $mat->enhed; ?>"></td>
        <td><input style="width:30px;" class="text-input" type="text" name="stk_update[]" value="<? print $mat->stk; ?>"></td>
        <td><input style="width:205px;" class="text-input" type="text" name="vejl_eks_moms_update[]" value="<? print $mat->vejl_eks_moms; ?>"></td>
        <td></td>
        <td><a href="#" onclick="removeRow(this)" id="addNew" title="Slet produkt"><img src="images/icons/slet.gif" width="16" alt="Slet" /></a></td>
    </tr>

    <?
}

The hidden input field name="update_id[]" is added to the form when I am updating. I hope this helps...

That's because I can't figure out how to run the query with the above code.. I know how you perform a DELETE FROM table WHERE id = something but not this one.. — dinkode.dk
– dinkode.dk, Commented Feb 15, 2014 at 18:11
We'll need to see the HTML/JavaScript which handles the deletion operation. From your description it sounds like you have an href (AJAX request?) which should handle deletion of the record. — Andrew Mackrodt
– Andrew Mackrodt, Commented Feb 15, 2014 at 18:17
I've edited my question - see above and have a look at the java script :) — dinkode.dk
– dinkode.dk, Commented Feb 15, 2014 at 18:19

Andrew Mackrodt · Accepted Answer · 2014-02-15 20:16:32Z

2

You need to inform the PHP script that you wish to delete the record. One solution would be to use a hidden input which is set when you call removeRow, e.g.

function removeRow(el) {
    // get the table row
    var row = $(el).parents('tr:first');

    // disable the input fields for the row
    $('input', row).attr('disabled', 'disabled');

    // rename update_id to delete_id and re-enable the field
    $('input[name="update_id[]"]', row).attr('name', 'delete_id[]').removeAttr('disabled');

    // hide the row
    row.hide();
}

This method no longer removes the table row from the DOM. However, it will disable the input fields contained in the row and adds a hidden input with the name beskrivelse_delete.

When the form is submitted, your PHP will now have a way to know which records need to be deleted.

foreach ($_POST['delete_id'] as $deleteId) {
    $deleteId = (int)$deleteId;
    $db->query("DELETE FROM faktura_materialer WHERE id = $deleteId");
}

Update: I've created a phpfiddle which has some improvements that you may wish to learn about. These points include:

Supporting deletion where JavaScript is not enabled
Using prepared statements (preferred to add_slashes)
Escaping output by using htmlspecialchars
Explicitly defining input ids to avoid guessing based on an incremental counter

edited Feb 15, 2014 at 20:16

answered Feb 15, 2014 at 18:10

Andrew Mackrodt

1,82614 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

dinkode.dk Over a year ago

Sorry if I didn't explain it well enough, but I want it to run with my javascript - see my edited question above :) but thanks anyway for you answer

Andrew Mackrodt Over a year ago

I've updated my answer accordingly. Note that I've hardcoded an id of 20, you'll need to concatenate the correct id in place.

dinkode.dk Over a year ago

Hmm, I've tried your code, but it doesn't work properly, sadly.. :(

dinkode.dk Over a year ago

Oh my bad! It deletes perfectly, but I can't figure out how to do it without hardcoding an id of 20 or 40

dinkode.dk Over a year ago

Do you know how I can access the id's of the while loop inside of my javascript code?

|

Collectives™ on Stack Overflow

PHP MySQL Delete function in while loop

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related