将ID插入SQL表，具有相同ID号的问题

Question

I don't want to have an ID number of SQL Server table set to auto increment. So I set this number in my PHP program. The way I do this is that I get the current ID value of the table and then add+1 to it, and insert this number in the table.

EXAMPLE:

$query = "SELECT MAX(ID) FROM T01"; 
$result=sqlsrv_query($conn, $query);
$IDTable = sqlsrv_fetch_array($result);

$Column1= getPOST('Column1' . $column); 
$Column2= getPOST('Column2' . $Column);
$ID1 = $IDTable[0]+1;
$params = array(&$ID1,&$Column1,&$Column2);

# Statement
$sql = "INSERT INTO MyTable ([ID],[Column1],[Column2]) VALUES (?,?); SELECT SCOPE_IDENTITY();";

Now I have a form, and in that form, there can be inserted more than 1 row in SQL table(MyTable). The problem now happens that using the code below it gives all inserted rows same ID. And therefore I get an error saying duplicate ID values.

How can I overcome this?

Answer 1

1. Ensure you have a timestamp for each record you insert
2. Select the last record inserted.
3. Based on the id, it could be a string, so get the integer from the string.
4. Add 1 from the obtained string to get an incremental value.
5. Add the prefix to incremented integer, i.e T23.

6. Save it to a variable, and insert it as any other variable.

   //assume $dbc to be the variable referencing the database connection //fetch last id $select_last_id_sql = mysqli_query($dbc,"SELECT table_id,time_recorded FROM TableName ORDER BY time_recorded DESC LIMIT 1") or die(mysqli_error($dbc);

   $id_row = mysqli_fetch_array($select_last_id_sql); $id = $id_row['table_id']; $int = (int) filter_var($id, FILTER_SANITIZE_NUMBER_INT); $int = $int+1; $new_table_id = "T".$int;