How to check if an UPDATE mysqli query is correctly executed?

Viewed 111 times

Here is my case:

$sql = 'UPDATE user SET password = ? WHERE username = ? AND password = ?';
if($stmt->prepare($sql)) {
    $stmt->bind_param('sss', $newPass, $_SESSION['username'], $oldPass);
    $stmt->execute();
}

Now, how can I see if the UPDATE query is successfully executed? And more precisely how can I see if the old password and username are correct so that I can store the new password? I've tried by doing this:

$res = $stmt->execute();
echo 'Result: '.$res;

But I always get:

Result: 1

even if the old password is not correct.

Answers

A query which updates no rows is NOT an error condition. It's simply a succesful query that didn't change anything. To see if an update actually did change anything, you have to use mysqli_affected_rows()

Monday, September 5, 2022

sumanthmarigowda-msft

$stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?");
/* BK: always check whether the prepare() succeeded */
if ($stmt === false) {
  trigger_error($this->mysqli->error, E_USER_ERROR);
  return;
}
$id = 1;
/* Bind our params */
/* BK: variables must be bound in the same order as the params in your SQL.
 * Some people prefer PDO because it supports named parameter. */
$stmt->bind_param('si', $content, $id);

/* Set our params */
/* BK: No need to use escaping when using parameters, in fact, you must not, 
 * because you'll get literal '' characters in your content. */
$content = $_POST['content'] ?: '';

/* Execute the prepared Statement */
$status = $stmt->execute();
/* BK: always check whether the execute() succeeded */
if ($status === false) {
  trigger_error($stmt->error, E_USER_ERROR);
}
printf("%d Row inserted.n", $stmt->affected_rows);

Re your questions:

I get a message from my script saying 0 Rows Inserted

This is because you reversed the order of parameters when you bound them. So you're searching the id column for the numeric value of your $content, which is probably interpreted as 0. So the UPDATE's WHERE clause matches zero rows.

do I need to declare all the fields or is it ok to just update one field??

It's okay to set just one column in an UPDATE statement. Other columns will not be changed.

Thursday, September 1, 2022

m10themist-d6115bca5226

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

Sunday, October 2, 2022

blkhatpersian

That's not an associative array, it's a regular array, but the answer is the same. Use .Count and compare to 0.

An associative array is called a [hashtable] in PowerShell and its literal form uses @{} (curly braces).

@{}.Count -eq 0  # hashtable (associative array)
@().Count -eq 0  # array

Thursday, September 15, 2022

dougbeal

You want to use something like $elemMatch

db.collection.find({
  choices: {
    $elemMatch: {
      id: 2,
      l: "j"
    }
  }
})

MongoPlayground

EDIT

In an aggregation $project stage I would use $filter

db.poll.aggregate([
  {
    "$match": {
      "_id": 100
    }
  },
  {
    $project: {
      numberOfVotes: {
        $gt: [
          {
            $size: {
              $filter: {
                input: "$choices",
                as: "choice",
                cond: {
                  $and: [
                    {
                      $eq: [
                        "$$choice.id",
                        2
                      ]
                    },
                    {
                      $eq: [
                        "$$choice.l",
                        "j"
                      ]
                    }
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])

MongoPlayground

Wednesday, November 23, 2022

ueouoeuoeu

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysqli

sql-update

prepared-statement

How to check if an UPDATE mysqli query is correctly executed?

Answers

php,mysqli,sql-update,prepared-statement