CODEWITHSUNDEEP
phpmysqlsql
Nils Ziehn
Nils Ziehn

Reputation: 4331

mysql insert ignore, replace or update on duplicate - did insert?

I am using php to 'insert ignore' a row into my database. Is there a way to find out whether a row was inserted?

the code looks like this:

if($stmt = $mysqli->prepare('INSERT IGNORE INTO my_table (key_a, key_b) VALUES (?, ?)'))
{
    $stmt->bind_param('ss', 'hello', 'world');
    $stmt->execute();
    $stmt->close();
}

Thank you guys!

Upvotes: 3

Views: 874

Answers (4)

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

Try like this:

if($stmt->execute())
{
echo "Success";

}
else
{
echo "Error";

}

Also check mysqli::$affected_rows

$mysqli->affected_rows

mysqli::$affected_rows -- mysqli_affected_rows — Gets the number of affected rows in a previous MySQL operation

Upvotes: 3

marcoG
marcoG

Reputation: 11

I think the execute() method will return a true or false, so I would suggest more sth. like this (also note that u need to do this before you close the connection): 

if ($stmt->execute()) {
echo "success" 
} 
else
{
echo "Error"
}

, also consider to fetch the statement to see how many lines were affected. You can do that with mysqli_stmt_affected_rows(statement), it will give you the lines affected. If you use it, it couls look like this: 

int mysqli_stmt_affected_rows ( mysqli_stmt $stmt )

also read here.

Upvotes: 1

Bimal Poudel
Bimal Poudel

Reputation: 1234

MySQL has ON DUPLICATE KEY feature where you can define what to do if your insert fails because of some constrains like unique_key or primary_key being inserted twice.

On that cases, cases, you can trap such errors. If the query inserts normally, it won't execute this block. If the query fails, the block will be executed.

Now, you may tweak this feature. For example, in your table, add one insert_attempts columns with default 0 (zero) value. And try to execute:

INSERT INTO my_table (key_a, key_b)
VALUES (?, ?) 
ON DUPLICATE KEY UPDATE
    insert_attempts = insert_attempts+1
;

After all records are successful; SELECT the rows with insert_attempt > 0.

Upvotes: 1

Zanon
Zanon

Reputation: 30790

Try the following: $mysqli->affected_rows

Link:

/* update rows */
$mysqli->query("UPDATE Language SET Status=1 WHERE Percentage > 50");
printf("Affected rows (UPDATE): %d\n", $mysqli->affected_rows);

Upvotes: 2

Related Questions