My database is not updating

Question

So,

I am a beginning 'nerd' and my job is now to make a kind of schedule where people can put their name in the input. I work with JS with the following code:

var timeoutId; $('form input').on('input propertychange change', function() {
console.log('Invoer bewerking');
clearTimeout(timeoutId);
timeoutId = setTimeout(function() {
    saveToDB();
}, 1000); }); function saveToDB() {  console.log('Opslaan naar Database');
form = $('.formulier24');
$.ajax({
    url: "ajax.php",
    type: "POST",
    data: form.serialize(),
    beforeSend: function(xhr) {
        $('.HowAbout').html('Opslaan...');
    },
    success: function(data) { console.error(data) ;
        var jqObj = jQuery(data); 
        var d = new Date();
        $('.HowAbout').html('Opgeslagen om: ' + d.toLocaleTimeString());
    },
}); } $('.formulier24').submit(function(e) {
saveToDB();
e.preventDefault(); });

and the AJAX file is as the following code:

<?php include ('connect.php'); if(isset($_POST['formulier24'])) {
$userName = $_POST['userName'];
$hours = $_POST['hours'];
$sql = "UPDATE evenement SET userName = '$userName' WHERE hours = '$hours'";
mysql_select_db('u7105d15197_main');
$retval = mysql_query($sql, $conn);

if (!$retval) {
    die('Could not update data: ' . mysql_error());
}
echo " Updated data successfully\n";

mysql_close($conn); } ?>

The website says it is saving, but the updated information won't show up in the database. Does anybody know what I am doing wrong in this situation? P.S. it is a auto update form without a button.

Not php-savvy, but is this correct? `$sql = "UPDATE evenement SET userName = '$userName' WHERE hours = '$hours'` Aren't you using `$params` as literals this way? — Giorgos Altanis, Mar 07 '17 at 20:22
first update mysql to mysqli or pdo $sql = "UPDATE evenement SET userName = '$userName' WHERE hours = '$hours'"; then echo $sql; die(); then check alert , run this query in phpmyadmin — Shafiqul Islam, Mar 07 '17 at 20:35
You're using insecure and unmaintained database functions that have been deprecated for almost a decade. Stop it now. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — miken32, Mar 07 '17 at 20:41
Ohh. Thanks for sharing @miken32. I am going to make it all mysqli (I guess) — Willem-Jelle, Mar 07 '17 at 20:44
I'd recommend PDO, much easier and more modern object-oriented syntax. — miken32, Mar 07 '17 at 21:06
You're updating a record, does that record exist to be updated? — miken32, Mar 07 '17 at 21:20

miken32 · Accepted Answer · 2017-09-13T18:33:05.620

I suspect your problem is that your UPDATE query is trying to update a row that doesn't exist. A REPLACE query will insert data, or replace it if there is a conflict with a table key.

While you're fixing that, you may as well toss out the code you have above. Give me 30 seconds with that web page and I could erase your whole database. (For example, what would happen if someone posted Hours as foo' OR 1=1 OR 'foo?)

It's a matter of personal preference, but I find PDO much easier to work with. It's less verbose and allows for much easier building of prepared statements, which are an essential security measure for any web application. It also allows you to use modern error handling methods like exceptions.

<?php
/* this block could be in a separate include file if it's going to be reused */
$db_host = "localhost";
$db_name = "u7105d15197_main";
$db_user = "user";
$db_pass = "asldkfjwlekj";
$db_opts = array(
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
);
$conn = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass, $db_opts);


if(isset($_POST['formulier24'])) {
    $sql = "REPLACE INTO evenement SET userName = ?, hours = ?";
    $parameters = array($_POST["userName"], $_POST["hours"]);
    try {
        $stmt = $conn->prepare($sql);
        $result = $stmt->execute($parameters);
        $return = "Updated data successfully!";
    } catch (PDOException $e) {
        $return = "Could not update data! Error: " . $e->getMessage();
    }

    header("Content-Type: application/json");
    echo json_encode($return);
}

My database is not updating

1 Answers1