-1

So on my web page i have a database connection setup. Once a user posts a comment on the web page it adds the comment to the database and a column in the database called ID gets incremented by 1. This ID serves as the primary key for the each row in the database. So when the user wants to remove a comment it checks the database for the comment that has matching primary key.

My current solution is this.

add the comment to the database

the database adds the ID

get the ID from the database

store it together with the comment in an array

When a user clicks delete comment check if the comment ID matches the ID in the database.

My problem with this is that it feels weird calling the database to add the comment and then call the database again to get the comment ID.

Is there any better solution to this?

john denver
  • 47
  • 5
  • 1
    You should check the SQL and php doc on how to get the last inserted Id. – DigiLive Mar 15 '19 at 15:33
  • do you've any code to share with us? – A l w a y s S u n n y Mar 15 '19 at 15:33
  • Have a read of https://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php if you are using an auto increment column type. – Nigel Ren Mar 15 '19 at 15:33
  • Also, decide what you mean by 'DELETE'. Does a deleted record permanently disappear from the data set, or does it just become invisible to end users? – Strawberry Mar 15 '19 at 15:35
  • https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id – PM 77-1 Mar 15 '19 at 15:37
  • 1
    *"My problem with this is that it feels weird calling the database to add the comment and then call the database again to get the comment ID. Is there any better solution to this?"* Yes but not in MySQL use PostgreSQL instead which supports `INSERT INTO ... RETURNING *` which insertes the returns all values back to the application without needing to do a extra select to fetch the extra infromation like increments and or defualt column values. – Raymond Nijland Mar 15 '19 at 15:46

2 Answers2

0

Below I've added some code based on your question. Let's say you've a table called comments where you're inserting some values and as the id is an auto incremented field so it is inserted automatically with your passed data. So if you try with php and mysql then after your insert operation you can easily get the last inserted id without doing another extra query for inserted id using $conn->insert_id

$sql = "INSERT INTO comments(commenter, comment)
VALUES ('John', 'How are you?')";

if ($conn->query($sql) === TRUE) {
    $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

REF.: http://php.net/manual/en/mysqli.insert-id.php

A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
-1

You can use mysql_insert_id() function to get record inserd ID or similar function, depending on which driver you are using. But there should be a special function for this.

You do not need to run another query.

Sergej
  • 2,030
  • 1
  • 18
  • 28
  • 2
    The last thing to recommend I would have thought is a `mysql_` function, the whole API is deprecated and removed in PHP 7. – Nigel Ren Mar 15 '19 at 15:35
  • It is not a recommendation to use this driver. This is just as an example of this function. The author can use mysqli_ or PDO or any other driver he needs. I just let him know that there is a special function to retrieve the last insert ID. – Sergej Mar 15 '19 at 15:36