我怎样才能解决php（mssql）中主键的违规问题？

Question

I have a three field.

First Field is auto numbering.(PK)

Second Field is user id.

Last Field is user pw.

Anyway, This is my code. This page called by JSON. This page can call multiple user. so it should ensure conflict id. if id is conflict, id should be auto numbering to max.

<?php
$conn=mssql_connect('test_database', 'root', '1357');
if (!$conn) {
return -1;
}
header("Content-Type:application/json");
$id=($_POST['id']);
$userid=($_POST['userid']);
$userpw=($_POST['userpw']);

$sql="INSERT INTO dbo.testTable
           (id
           ,userid
           ,userpw)
     VALUES
           ('$id'
           ,'$userid'
           ,'$userpw')";

function customError($errno,$errstr)
{
    if (strpos($errstr,"Violation of PRIMARY KEY")!==false){
        $query_id="
SELECT id
FROM testTable
order by LEN(id) DESC, id DESC
";
        $result_id=mssql_query($query_id,$conn);
        $id=mssql_result($result_id,0,0)+1;
        $result= mssql_query($sql,$conn);
        echo json_encode($result);
    }
    else{
        echo $errstr;
        die();
    }   
}
set_error_handler("customError");

$result= mssql_query($sql,$conn);
echo json_encode($result);
?>

I Try control error message 'Violation of PRIMARY KEY" by using set_error_handler.

But $id,$userid,$userpw,$sql can not use in set_error_handler function.

Notice: Undefined variable: sql

Answer 1

I would suggest to remove that function which you have added to check that violation part. and change your first query like this,

$sql="INSERT INTO dbo.testTable
       (userid// i have removed the id here
       ,userpw)
 VALUES
       ('$userid'// i have removed $id 
       ,'$userpw')";

Or if you want to follow your method, do like this.

First replace this line, because you are storing the query in $query_id variable.

$result= mssql_query($sql,$conn);

to

$result= mssql_query($query_id,$conn);

in customError function.

Next, no need to pass autoincrement id to your database,

$sql="INSERT INTO dbo.testTable
       (id
       ,userid
       ,userpw)
 VALUES
       ('$id'
       ,'$userid'
       ,'$userpw')

I have removed first parameter since its an autoincrement in database.

This should solve both errors.