0

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?

Zain Aftab
  • 703
  • 7
  • 21
mnm
  • 9
  • 5
  • 2
    Any reason to not using `IDENTITY()`? – Ilyes Jul 04 '19 at 12:24
  • If you always increment the id with one, you could use an auto incrementing id – Ebski Jul 04 '19 at 12:25
  • 1
    Don't get the `MAX` value and insert +1. Use the `IDENTITY` property or a `SEQUENCE.`. – Thom A Jul 04 '19 at 12:25
  • 1
    so if 2 instances of this app do this at the same time, the same key can be generated ? Maybe that is why `identity` and `sequences` where created ? why not use them ? – GuidoG Jul 04 '19 at 13:04
  • I could use IDENTITY() for my ID column in table, but i had some problems with autoincermenting ID, and i want to try it inside program. – mnm Jul 04 '19 at 13:55

1 Answers1

0
  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;
Dharman
  • 30,962
  • 25
  • 85
  • 135
Abdalla Maro
  • 98
  • 1
  • 6
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Oct 30 '19 at 22:09