Mysql select and insert safely and quickly with php
I have a simple table, the logic is that before inserting a new row, I have to extract one column from that table. Let me explain:
table
id key groupId Note
1 00001 1 abd
2 00002 1 aasdas
3 00003 1 aasdas
4 00001 2 q2eqwd
5 00002 2 qwdvvd
6 00003 2 qwrqw
7 00004 2 qwdqdqw
You see, the key increases, for example Auto Increment for each group. When a group with id 2 wants to add a new note, it must know the last key. Once found, php adds +1 to the last key and inserts a new line. I am doing it as shown below:
$groupId = 2; //for example
$note = $_POST['note'];
$select = $db -> prepare("SELECT key FROM table where groupId = :groupId ORDER BY id DESC limit 1");
$select -> bindValue(":groupId", $groupId, PDO::PARAM_INT);
$select -> execute();
$fetch = $select -> fetch(PDO::FETCH_ASSOC);
$lastKey = $fetch['key']+1;
$insert = "INSERT INTO table (key, groupId, note) VALUES(:key, :groupId, :note)";
$ins = $db -> prepare($insert);
$insert -> bindValue(":key", $lastKey, PDO::PARAM_INT);
$insert -> bindValue(":groupId", $groupId, PDO::PARAM_INT);
$insert -> bindValue(":note", $note, PDO::PARAM_STR);
This method works well for me, but I'm afraid there will be some kind of conflict here when getting the last key from the table? Because, at the same time, 10 users with the same groupId can add a new row. Can php pick the same key for 3 users with group id 2 at the same time?
Is there a quick and safe way?
source to share
You can do it AUTO_INCREMENT
using MyISAM.
From MySQL Docs :
For MyISAM and BDB tables, you can point AUTO_INCREMENT to a secondary column in a multi-column index .... This is useful when you want to put data in ordered groups.
Otherwise, you must set the value in your insert request using a subquery, for example SELECT MAX(key) + 1 FROM table WHERE groupID = 1
, and read the value.
source to share