$Stmt->Execute():How to Know If Db Insert Was Successful

$stmt-execute() : How to know if db insert was successful?

The execute() method returns a boolean ... so just do this :

if ($stmt->execute()) { 
   // it worked
} else {
   // it didn't
}

Update: since 2022 and beyond, a failed query will throw an error Exception. So you won't have to write any code to "skip other pieces of code further down the page" - it will be skipped automatically. Therefore you shouldn't add any conditions and just write the code right away:

$stmt = $connection->prepare("insert into table (blah) values (?)");
$stmt->bind_param("s", $blah);  
$stmt->execute();           

If you need to do something in case of success, then just do it right away, like

echo "success";

You will see it only if the query was successful. Otherwise it will be the error message.

PDO mysql: How to know if insert was successful

PDOStatement->execute() returns true on success. There is also PDOStatement->errorCode() which you can check for errors.

`$stmt-execute()` Returns False but Still Inserts

You are executing your query twice.

In fact your code reads:

$stmt->execute();
if(false===($stmt->execute())){
    $nbadmin->close();
    echo '<script>alert("Something went wrong; try again.")</script>';
    error();
}else{
    $nbadmin->close();
    finish();
}

However, the first row of that fragmen executes the prepared statement, which is then executed again as part of the if(...) condition.

This so performs another insert, which fails, probably because there is some unique constraint on your database table, I would guess on the username field.

You have two possible solutions. First, you could save the result of the execute() into a variable, like this:

$result = $stmt->execute();
if(false === $result) { ... }

Or else you could call the method directly inside the if(...) statement, like this:

// $stmt->execute(); // remove this line
if(false === $stmt->execute()) { ... }

I myself would favor the first option though.

Finally, please note that you execute also $nbadmin->prepare($sql) and $stmt->bind_param('sssssss', $user, $pass, $name, $branch, $officer, $type, $alert) twice, for the same reason, but those do not seem to generate an error.

How to know if PDO inserted a record on a table with a composite key?

PDO::lastInsertId appears to only work on auto incremented PKs, however, this requirement does not seem to be documented very well at http://php.net/manual/en/pdo.lastinsertid.php. Furthermore, it does not appear to work with composite keys.

pdo::rowCount() is required. See http://php.net/manual/en/pdostatement.rowcount.php. Thanks given to AbraCadaver and Ryan Vincent.

$sql='INSERT INTO docx_priv_projects (documents_id,projects_id)
SELECT :doc_id,t.id
FROM projects AS t INNER JOIN entities AS e ON e.id=t.id
WHERE t.id=:id AND e.record_status='active' AND e.sites_id=:sites_id';
$stmt = $conn->prepare($sql);
$stmt->execute(array('id'=>1379519490 ,'sites_id'=>2416619273,'doc_id'=>2972614382));
$x=$stmt->rowCount();  //Will be `1` if added

Trouble converting to parameterized queries

From the docs: Fetch results from a prepared statement into the bound variables.

fetch() returns either TRUE, FALSE or NULL, but not the result set you expected. Instead, it sets the output to the variables you previously bound (using bind_param()) by reference. This is why you have to use variables, and not actual scalar types.

If your query did not return any rows, fetch() will return NULL. Update your code as follows:

$stmt = $db->prepare($sql);
$stmt->bind_param("s", $username);
$stmt->execute();
if ($stmt->fetch() === TRUE)
    die(json_encode(array("status" => 400, "message" => "User already banned")));
$stmt->close();

And to fix the error on line 72, you have to pass the values by reference, using variables. Something like this:

$ip = NULL;
$expire = NULL;
$ban_creator = 1;

$result2->bind_param("sssssd", $username, $ip, $email, $banMsg, $expire, $ban_creator);

Don't forget to execute the query! You're checking $result2 before anything actually happened.

$smt-get_result() gives empty result after MySQL (only) INSERT

I changed $dataR = $sql->get_result(); to $dataR = $sql->affected_rows;, so the final code looks like:

$sql = $this->db_connection->prepare("INSERT INTO users (snowflake, name, salt, hash, mail) VALUES (?, ?, ?, ?, ?)");
$sql->bind_param("issss", $snowflakeID, $username, $salt, $password_hash, $useremail);
$sql->execute();
$dataR = $sql->affected_rows;
if($dataR > 0) {
     $this->messages[] = "Your account has been created successfully. You can now log in.";
} else {
     $this->errors[] = "Sorry, your registration failed. Please go back and try again.";
}

Insert Into query and expecting more than one result?

Since you are executing an INSERT query which doesn't have any results, you don't need or use bind_result for fetching data. bind_result is for binding to columns used in a SELECT statement.

mysqli_stmt::execute() returns true on success or false on failure so simply assign the value of that to your $return variable.

If execute() returns false, the INSERT statement failed for some reason. If it returns true, the INSERT was successful and you can determine the number of rows inserted by looking at the mysqli_stmt::$affected_rows property of your statement object.

That's why you're getting that error, hope it helps. You can find most of this information on the manual page for mysqli_stmt::execute().

---

Related Topics