Check if exists, if yes, update to 1 ++ if not insert
Hi guys quick question, currently I have an insert statement
$query= "INSERT into new_mail VALUES ('$to1', '0')";
where fields are username and message_number
Currently, what I would do to check if a record exists is execute a select query and then check the number of rows using mysql_num_rows
(php). If strings == 1 then I get the current message_number and set it to
$row['message_number']+1.
Then I update this post with another request.
Is there an easier way to do all this in mysql only with only one query (check if it exists, if not insert, if so update message_number, increase by 1)?
source to share
Use INSERT...ON DUPLICATE KEY UPDATE
. There is an example in the MySQL manual that does almost what you want:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
To make this work, you need to add a UNIQUE index to the column you are using to check for duplicates. However, there is one important warning:
In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple unique indexes.
source to share
Depending on the structure of your table, you can use ON DUPLICATE KEY UPDATE
(link to MySQL manual) INSERT
:
INSERT into new_mail VALUES ('$to1', '0') ON DUPLICATE KEY UPDATE message_number=message_number+1
source to share
A bit confused by your question and your table structures, but I think you want something like this.
INSERT INTO new_mail (username, message_number)
VALUES ($username, $message_number)
ON DUPLICATE KEY UPDATE message_number=message_number + 1;
This is assumed to be username
your primary key (most likely something like userid). Hope this helps.
source to share
EDIT: ON DUPLICATE KEY UPDATE answers are better, but you can do this (select query escapes):
Assuming you are using the mysqli extenson:
$db = //Some construction of mysqli object;
$sql = 'UPDATE tablename SET RowValue = RowValue + 1 WHERE message_number = ?';
$updateStatement = $db->prepare($sql);
$updateStatement->bind_param('i', $message_number);
$message_number = //Set message number;
$updateStatement->execute();
if ($updateStatement->affectedRows == 0) {
$sql = 'INSERT INTO tablename (RowValue, message_number) VALUES (?, ?)';
$insertStatement = $db->prepare($sql);
$insertStatement->bind_param('ii', $rowValue, $messageNumber);
$rowValue = something;
$messageNumber = something;
$insertStatement->execute();
}
source to share