CODEWITHSUNDEEP
phpmysqlajax
Neil_Tinkerer
Neil_Tinkerer

Reputation: 119

How to Insert/Update/Delete in one Ajax POST

I have a user modifiable table where — technically — in one session a user could retrieve a previously saved session and insert a new row, update a row, and delete a row.

Currently, on save, I am deleting all rows of the previous session and simply using INSERT to add all the rows all over again (see code below).

While this works, it just seems wrong somehow. I'm thinking that I should be using a combination of INSERT, UPDATE, and DELETE to do this correctly? What is the best method to do this? Can it be done with a single Ajax POST and, if so, do I then perform three separate queries based on the need to INSERT, UPDATE, DELETE? Of course, that will mean trying to keep track of the status of each row throughout a session. I'm hoping there's a simply (recommended) way to do this. Thank you for any direction you can provide.

$sql_delete = "DELETE FROM worksheet_1_1b WHERE user_id = '$user_id'";
    $result = mysqli_query($dbc, $sql_delete);

    $sql_insert = "INSERT INTO worksheet_1_1b (user_id, key_position, area, ro, span, criteria1, criteria2, criteria3, urgency) VALUES ('$user_id','$key_position','$area','$ro','$span','$criteria1','$criteria2','$criteria3','$urgency' )";
    $result = mysqli_query($dbc, $sql_update);

Upvotes: 0

Views: 529

Answers (1)

Michael McPherson
Michael McPherson

Reputation: 498

Just spitballing, here: I think this is a solid case for an architecture which makes use to two way bindings (Angular springs to mind) to keep the DB in sync with the UI.

However, if you're determined to run this via AJAX calls, you could fairly easily wrap all the CRUD requests into a single data object, and then parse it (after retrieving it from $_POST) from there, being sure to escape your values and insert them into parameterized queries to ameliorate SQL injection based attack vectors.

A data structure like

{data=
    {
        create={
            /*some data in whatever format*/
        },
        update={
            /*some data in whatever format*/
        },
        delete={
            /*some data in whatever format*/
        }
    }
}

would be an option, for example.

Upvotes: 1

Related Questions