When I insert a new row, I can easily get the new row number (its an auto-inc primary key). The issue, however, is what if the row already exists when I try to do the insert? Sure I can do a SELECT before the insert, but I was hoping to reduce it from two external queries.
Here's some basic example code:
$actor = "mr. wonderful";
$query = "INSERT INTO `mytable` (`id`, `name`) VALUES(DEFAULT, '$actor') ON DUPLICATE KEY UPDATE `name` = '$actor'";
if($mysqli->query($query) === true){
print $query."<br/>Success. (".$mysqli->insert_id.")<br/>";
}else{
echo mysqli_error($mysqli);
echo "<br/>".$mysqli->insert_id;
}
ON DUPLICATE KEY UPDATE was new to me, but since I didn't want to change the existing data already there, the insert_id wouldn't update to that current row in question.
[15min later]
And, perhaps posting this now is pointless but I believe I have a working solution and thought I'd share it.
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`)
Assuming 'id' is the auto-incremented key.
There were some comments here
dev.mysql.com stating this still does not work if nothing is updated, but it's working for me. I tested this with MySql 5.0.10
"You're not going crazy. You're going sane in a crazy world!" ~Tick
