iamthestreets iamthestreets - 4 months ago 18
SQL Question

Update 1 row at a time and each row has a different value when using x-editable for the same user

I have a HTML table with, for this example, 2 rows (could be more). The middle column is editable and each row in the HTML table is a different column in the database. When I edit row 1 and refresh the page row 2 now has the same value as row 1. I do not want this. The user id (pk) will be the same for each row. The documentation about creating a new record says this would work with updating existing, but does not really help me. I am looking for as much help as I can get.

Here is an image of my table:
enter image description here

How do I get each row to keep its value and not update all other rows?

For example if I update the column in row 1, I want only row 1's column to be updated.

Please help!

Here is my code:

HTML Code:

<td><a class="myeditable" data-name="fb_url" data-type="text" data-pk="<?php echo ($userid);?>" title="Edit"><?php echo ($result['fb_url']);?></a></td>
<td><a class="myeditable" data-name="tw_url" data-type="text" data-pk="<?php echo ($userid);?>" title="Edit"><?php echo ($result['tw_url']);?></a></td>


Post.php Code

require("config.php");

$pk = $_POST['pk'];
$name = $_POST['name'];
$value = $_POST['value'];

if(!empty($value)) {
try // save user selection to the database
{
$stmt = $db->prepare("UPDATE social_preferences SET fb_url = :fburl, tw_url = :twurl WHERE user_id = :userID AND ");
$stmt->bindParam(":userID", $pk, PDO::PARAM_INT);
$stmt->bindParam(':twurl', $value);
$stmt->bindParam(':fburl', $value);
$stmt->execute();
} catch(PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); }
}else {
echo 'No value...';
}


JS Code:

$(document).ready(function() {
//toggle `popup` / `inline` mode
$.fn.editable.defaults.mode = 'inline';


$('.myeditable').editable({
url: 'post.php',
ajaxOptions: {
type: 'post',
},
success: function(data, config) {
if(data && data.id) {
//set pk
$(this).editable('option', 'pk', data.id);
}
}
});
});


When I check the network tab in Chrome dev tools I see this:
Method: POST
Status: 302 Moved Temporarily

Answer

OP is talking about rows, but in fact it are columns.

$accepted_names = array('fb_url', 'tw_url');
if (in_array($_POST['name'], $accepted_names)) {
    if(!empty($_POST['value'])) {
        $value = $_POST['value'];
        $id = $_POST['pk'];
        try {
            $stmt = $db->prepare('UPDATE social_preferences SET ' . $_POST['name'] . ' = :value
                                  WHERE user_id = :id');
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $stmt->bindParam(':value',$value);
            $stmt->execute();
        }
        catch (PDOExeption $e) {
            echo json_encode(array('status' => 'failed', 'msg' => $e->getMessage()));
            exit;
        }
        echo json_encode(array('id' => $id, 'status' => 'oké', 'msg' => $value));
    }
    else {
         echo jsonencode(array('status' => 'failed', 'msg' => 'No value ....'));
    }
}
else {
    header($_SERVER['SERVER_PROTOCOL'] . ' 422 Unprocessable entity');
}