Mysql field not updating

[NorKayak]

I have this PHP code:

<?php
require_once '../includes/db-inc.php';

$Username = mysqli_real_escape_string($conn, $_POST['username']);
$Pwd = mysqli_real_escape_string($conn, $_POST['pwd']);
$Email = mysqli_real_escape_string($conn, $_POST['email']);
$allowSound = $_POST['isSoundAllowed'];
$allowMail = $_POST['isMailAllowed'];

$sql = "SELECT * FROM Cornichons WHERE Username='$Username'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);

$userID = $row['id'];

$sql = "UPDATE  Cornichons SET 
                Username = '$Username',
                Pwd = '$Pwd',
                Email = '$Email',
                allowSound = '$allowSound',
                allowMail = '$allowMail'
            WHERE id = '$userID';";
$result = mysqli_query($conn, $sql);

echo var_dump($Username);

$conn->close();

It updates properly all the fields except Username and Email. No error generated; it just doesn’t want to update.

If I use sql query inside phpMyAdmin those 2 fields update as expected.

Any idea what the problem might be?

[sibertius]

@NorKayak

Any idea what the problem might be?

Generally (without knowing PHP) I should check the content of query variable $sql.

[NorKayak]

Variable is ok. I test its content with ‘echo’ and it’s fine…

[mabismad]

What values do get updated into the fields?

When the database server mode is not set to Strict, invalid and out of range values get truncated/cast to the nearest valid value for a column’s data type.

You probably have some non-printing/white-space characters as part of the values. Using var_dump() on the variables helps identify this type of problem because it shows the length of the values.

You will most likely need to post all your form and form processing code for anyone here to be able to help with the problem.

You need to use a prepared query to prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished.

[NorKayak]

Thanks for the pointers Going to look into it.

[Drummin]

well 2 semi colons here ‘$userID’;”; are not helping matters.

[NorKayak]

Nope; that part works… As I said all fields are updated just not Usename and Email.

Been 6 hours on the thing and I can’t figure it out.

[mabismad]

Since you didn’t answer -

@mabismad

What values do get updated into the fields?

Nor posted all the code, we cannot help you further.

[NorKayak]

<?php
require_once '../includes/db-inc.php';

$Username = mysqli_real_escape_string($conn, $_POST['username']);
$Pwd = mysqli_real_escape_string($conn, $_POST['pwd']);
$Email = mysqli_real_escape_string($conn, $_POST['email']);
$allowSound = $_POST['isSoundAllowed'];
$allowMail = $_POST['isMailAllowed'];

$sql = "SELECT * FROM Cornichons WHERE Username='$Username'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);

$userID = $row['id'];

$sql = "UPDATE  Cornichons SET 
                Username = '$Username',
                Pwd = '$Pwd',
                Email = '$Email',
                allowSound = '$allowSound',
                allowMail = '$allowMail'
            WHERE id = '$userID';";
$result = mysqli_query($conn, $sql);

echo var_dump($Username);

$conn->close();

Here you have the full code. The variables are passed through an ajax function. I tested all of them with the echo thing and they are ok.

Here is the ajax function:

function saveProfile() {
        var username = document.getElementById("username").value;
        var pwd = document.getElementById("password").value;
        var email = document.getElementById("email").value;
        $.ajax('saveProfile.php', {
            type: 'POST',
            data: {
                username: username,
                pwd: pwd,
                email: email,
                isSoundAllowed: isSoundAllowed,
                isMailAllowed: isMailAllowed
            },
            async: true,
            success: function(data, status) {
                console.log(data);
            },
            error: function(errorMessage) {
                console.log(errorMessage);
            },
        });
    }

And here is what returns console.log for the $Username.

[Gandalf]

Well for one thing you shouldn’t be putting the values directly in your SQL statement but should be using prepared statements.

That aside, what are the values of $Username and $Email? and where are they coming from?

[NorKayak]

I will do the prepared statement later… I just wanted to have a quick check.

The 2 variables come from an ajax function call. That also works fine because

echo var_dump($Username);
or
echo var_dump($Email);

Returns me the right values for both variables.

[Thallius]

Maybe you username and email contains some special chars. That’s why you should use prepared statements.

Also you never ever should store a password uncrypted or hashed in a database. This is an offend to all your users.

Remember most user reuse their passwords. So if someone will register on your site, his password, which he might have used on many other sites, is readable for everyone with access to your database. Let’s say you have your database on a hosting service, normally many many administrating people from this company are able to read this.

[NorKayak]

I understand that… For the moment the site is ‘under construction’; No users yet; I’m only doing tests…

As for special char:

$Username = mysqli_real_escape_string($conn, $_POST['username']);

This should take care of it, no?

[m_hutley]

So… all the points the others have made are great and all. But i’d like a little more information about the problem.

@NorKayak

It updates properly all the fields except Username and Email.

Does it leave username and password alone? Does it empty them out? In what way does it “improperly” update the username and password?

[NorKayak]

All all the fiels are updated (their values change) but not Username and Email: Those the values remain the same. Not even cleared like with an empty variable…

[mabismad]

Until you tell or show us the actual result you are getting, what the expected result should be, and what is wrong with the result, we cannot help you. We are not sitting right next to you. Everything you see that looks correct, could be clues that would help us.

Going to take a guess. Since you are wrongly getting the id of the row matching the submitted username (you should be getting the id from your login session variable), and your username and email columns are not defined as unique indexes, you likely have more than one row of data for the same username value, and you are looking at the wrong row of data and it doesn’t look like it got updated.

Another possibility since you are using ajax, since you haven’t posted the form code for, is two requests are being made, the first one updates the data to the expected values, but the second one updates them back to the previous values.

@NorKayak

As for special char:

If you haven’t set the character set to match your database tables when you make the database connection (which is rarely done), you could be getting a character conversion occurring over the connection.

[m_hutley]

@mabismad

tell or show us

While you’re at it, echo $sql and show us

[NorKayak]

function saveProfile() {
        var username = document.getElementById("username").value;
        var pwd = document.getElementById("password").value;
        var email = document.getElementById("email").value;
        $.ajax('saveProfile.php', {
            type: 'POST',
            data: {
                username: username,
                pwd: pwd,
                email: email,
                isSoundAllowed: isSoundAllowed,
                isMailAllowed: isMailAllowed
            },
            async: true,
            success: function(data, status) {
                console.log(data);
            },
            error: function(errorMessage) {
                console.log(errorMessage);
            },
        });
    }

The function is called only once; I checked.

I begin to believe that something went wrong when creating the table and that somehow those to fields are ‘twisted’ in some way…

I will wait a little to see if somebody can give me some light else I will try re-create the table.

[NorKayak]

That’s the console.log(data) of the data returned by the ajax function. As you see it’s ok.

[m_hutley]

That’s not what i asked you to do.