Mysql insert ignore, replace or update with duplicate - insert?
I am using php to "insert ignore" a row into my database. Is there a way to find out if a row has been 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();
}
Thanks guys!
source to share
Try it like this:
if($stmt->execute())
{
echo "Success";
}
else
{
echo "Error";
}
Also check mysqli :: $ affected_rows
$mysqli->affected_rows
mysqli :: $ affected_rows - mysqli_affected_rows - Get the number of affected rows in a previous MySQL operation
source to share
MySQL has an ON DUPLICATE KEY function where you can determine what to do if your insert fails due to some constraint like unique_key or primary_key being inserted twice.
In these cases, you can catch such errors. If the query is inserted normally, it will not execute this block. If the request fails, the block will be executed.
You can now customize this feature. For example, in your table add one column, insert_attempts, with a default of 0 (zero). And try:
INSERT INTO my_table (key_a, key_b)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE
insert_attempts = insert_attempts+1
;
After successful completion of all records; SELECT rows with insert_attempt> 0.
source to share
I think the execute () method will return true or false, so I would suggest more sth. (also note that you need to do this before closing the connection):
if ($stmt->execute()) {
echo "success"
}
else
{
echo "Error"
}
also look at the sample to see how many rows were affected. You can do this with mysqli_stmt_affected_rows (statement), this will give you the affected rows. If you use it, it looks like this:
int mysqli_stmt_affected_rows ( mysqli_stmt $stmt )
also read here .
source to share