If the table does not exist, insert into the table

Question

I have an application where the user can add serial numbers (units) to a circuit. I'm trying to modify an insert query for two tables to check if a cell ID already exists. If it doesn't exist, I want to insert it, but if it does exist, I don't want to insert a new record. I've searched and tried to apply answers I've found on SO related to this without any success.

这是我的代码，以 控制器

public function AddNewCell()
    {
        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            $circuitId = $_POST["circuitId"];
            $cellNum = filter_var($_POST["cellNum"], FILTER_SANITIZE_STRING);
            $toteId = $_POST["toteId"];
            $posId = $_POST["posId"];

            $stageCheckId = $this->GetStageIdByBatId($cellNum);
            if (empty($stageCheckId)) {
                echo json_encode("0");
            } else {
                $cellId = $this->form->InsertNewCell($circuitId, $stageCheckId, $toteId, $posId);
                $this->wk->InsertCell($circuitId, $cellId, $cellNum, $toteId, $posId);
                echo json_encode($cellId);
            }
        }
    }

编队模型

public function InsertNewCell($circuitId, $stageCheckId, $toteId, $posId)
    {
        $this->db->query("INSERT INTO tbl_Cell_Tote_Track (Circuit_Id, Stage_Check_Id, Tote_Id, Position_Id) VALUES (:cid, :scid, :tid, :pid)");
        $this->db->bind(":cid", $circuitId);
        $this->db->bind(":scid", $stageCheckId);
        $this->db->bind(":tid", $toteId);
        $this->db->bind(":pid", $posId);
        $this->db->execute();

        $this->db->query("SELECT TOP(1) Cell_Id FROM tbl_Cell_Tote_Track ORDER BY Cell_Id DESC");
        return $this->db->single()->Cell_Id;
    }

工作表模型

public function InsertCell($circuitId, $cellId, $cellNum, $toteId, $posId)
    {
        $this->db->query("SELECT Circuit_Num FROM tbl_Circuit_Track WHERE Circuit_Id = :cid");
        $this->db->bind(":cid", $circuitId);
        $circuitNum = $this->db->single()->Circuit_Num;

        $position = $this->GetCellPos($toteId, $posId);

        $this->db->query("INSERT INTO tbl_OCV_Worksheet (Cell_Id, Circuit_Id, Circuit_Num, Position_Num, Serial_Num) VALUES (:clid, :cirid, :cn, :pn, :cnum)");
        $this->db->bind(":clid", $cellId);
        $this->db->bind(":cirid", $circuitId);
        $this->db->bind(":cn", $circuitNum);
        $this->db->bind(":pn", $position);
        $this->db->bind(":cnum", $cellNum);
        $this->db->execute();
    }

我尝试通过添加在表的 Cell_Id 列上添加唯一约束 $this->db->query("更改表 tbl_Cell_Tote_Track 添加唯一的 (Cell_Id); 到模型函数，但当使用现有序列号输入单元格时仍然收到重复项。I also tried

public function InsertNewCell($circuitId, $stageCheckId, $toteId, $posId)
{
     $this->db->query("INSERT INTO tbl_Cell_Tote_Track (Circuit_Id, Stage_Check_Id, Tote_Id, Position_Id) SELECT $circuitId, $stageCheckId, $toteId, $posId
     WHERE NOT EXISTS(SELECT Cell_Id FROM tbl_Cell_Tote_Track)");
     $this->db->execute();
     $this->db->query("SELECT TOP(1) Cell_Id FROM tbl_Cell_Tote_Track ORDER BY Cell_Id DESC");
     return $this->db->single()->Cell_Id;
}

This seemed to prevent duplicates in the table, but a senior colleague told me this was incorrect. Apologies in advance as I'm new to SQL and php. Any help is greatly appreciated. If more code needs to be included, please let me know.

Answer 1

If you set where on the select statement, you can select the last code (with select), e.g.

"NOT EXISTS (SELECT Cell_Id FROM tbl_Cell_Tote_Track WHERE Cell_id = $cellId)"

And change the function parameters of the sending unit ID.

If Cell_Id is auto-incremental, then you need to define the constraint with a different column.