Ajax returns success but doesn't change the database

Question

I'm developing a small script of js to edit a profile in the way facebook used to be (click a button, edit and save without reloading the page). The problem is that when I run it, the ajax function returns sucess but akes no changes on the database. The function os js is this:

$('.savebtn').click(function(){
            var editdata    = $(".editbox").val();
            var parameter   = $(this).closest("td").find("#parameter").text();

            var datastring  = "data="+editdata+"&parameter="+parameter;

            var $t = $(this);

            console.log(datastring);

            $.ajax({
                type: "POST",
                url: BASE_URL + "/API/update_profile.php",
                data: datastring,
                cache: false,
                success: function()
                {
                    $t.closest('td').find('.curr_value').html(editdata);
                    $t.closest('td').find('.curr_value').hide;
                    console.log(editdata);
                    $(this).prev(".edit").hide();
                    $(this).prev(".curr_value").show();
                    $(this).prev('.edit_link').show();
                    $(this).hide();
                }
            });
        });

(Ignore the $t thing, somehow this works like this, but not if I use $(this))

Ajax executes the code for sucess but doesn't update anything on the database.

The PHP code for the database is:

<?php

include_once("../../config/connect_db.php");
include_once("../../database/cliente.php");

$parameter = $_POST['parameter'];
$data = $_POST['data'];
$id = $_SESSION['id'];

var_dump($_POST);

try {
    updateProfile($parameter, $data, $id);
}
catch (PDOException $e)
{
    echo 'Caught exception: ',  $e->getMessage(), "\n";
}

?>

function updateProfile($parameter, $data, $id)
{
    global $conn;
    $stmt = $conn->prepare("UPDATE biofood.users 
                            SET ? = ?
                            WHERE id = ?");
    $stmt->execute(array($parameter, $data. $id));
}

EDIT: As pointed out, this could be a problem with trying to pass a column name as a parameter. Changed the code to the following, but with no sucess:

function updateProfile($parameter, $data, $id)
{
    global $conn;
    $query = "UPDATE biofood.users 
              SET $parameter = $data
              WHERE id = $id";
    $stmt = $conn->prepare($query);
    $stmt->execute();
}

Your `$(this)` does not work, because `this` loses context in your `success()` function. You can define a variable like you already do, declare a variable such as `self` and assign `this` to it, or use the new ES6 arrow function syntax which maintains the context in your success function. — Mario Tacke, Jan 18 '16 at 18:52
@MarioTacke The $(this) is working. On the javascript part everything is working, the problem is that the db is not updated — Hugo Torres, Jan 18 '16 at 19:04
This initial comment was not to answer the question but to clarify the context of `this` in your success callback. What you are doing *is* working because you assign `$(this)` to `$t` outside of your ajax call. I was merely trying to describe why `this` changes within the success function. :) — Mario Tacke, Jan 18 '16 at 19:06
**What happens if the user enters a COLUMN NAME that does not exist on this table????** — RiggsFolly, Jan 19 '16 at 00:27
Second attempt at the query needs `SET $parameter = '$data'` — RiggsFolly, Jan 19 '16 at 00:31
Well in that case What is `$parameter` because it is in a position that denotes a column name in that query — RiggsFolly, Jan 19 '16 at 10:19

score 0 · Answer 1 · edited Jan 18 '16 at 19:35

0

This line:

$stmt->execute(array($parameter, $data. $id));

I think should be

$stmt->execute(array($parameter, $data, $id));

(notice the comma after $data)

edited Jan 18 '16 at 19:35

Toby Speight

27,591
48
66
103

answered Jan 18 '16 at 18:50

Yuval Perelman

4,499
1
22
32

Nice catch! Unfortunately, not enough :S – Hugo Torres Jan 18 '16 at 18:54
Have you logged your `$id` parameter? The update will go through even if it doesn't affect any records if the where condition excludes the set you want to update. Log it and run a select query manually with where id = your id from the log. – Mario Tacke Jan 18 '16 at 19:03

Mikey · Answer 2 · 2016-01-19T00:29:31.947

This might not solve your problem, but it might give you a better indication on where your problem is.

First, you are not checking whether it works or not as your updateProfile function returns nothing.

Modify your updateProfile function, so that it returns the number of rows affected. (BTW this is a safer way to write your function. If you can check or limit the value of $parameter prior to calling this function, it will be less prone to SQL injection.)

function updateProfile($parameter, $data, $id)
{
    global $conn;
    $stmt = $conn->prepare("UPDATE biofood.users SET $parameter = ? WHERE id = ?");
    $stmt->execute(array($data, $id));
    return $stmt->rowCount(); // # of rows affected
}

In the script that calls this function, get the value and send it back as a response. We'll send back a JSON.

$response = array();
try {
    $response['success'] = updateProfile($parameter, $data, $id);
} catch (PDOException $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
}

header('Content-Type: application/json');
echo json_encode($response);

In your JavaScript file, make the following change:

$.ajax({
     type: "POST",
     url: BASE_URL + "/API/update_profile.php",
     data: datastring,
     cache: false,
     success: function (data) {
         if (data.success) {
             $t.closest('td').find('.curr_value').html(editdata);
             $t.closest('td').find('.curr_value').hide;
             console.log(editdata);
             $(this).prev(".edit").hide();
             $(this).prev(".curr_value").show();
             $(this).prev('.edit_link').show();
             $(this).hide();
         }
     },
     dataType: 'json'
});

Ajax returns success but doesn't change the database

2 Answers2