mysql - 检查值是否在数据库中存在两次以上

Question

我想做一个限制，如果lecID在数据库中出现两次，用户将无法向数据库中插入数据。但是代码有一些问题，它仍然会插入到数据库中，即使 lecID 在我的数据库中出现了两次以上。我能知道是什么问题吗。下面是我的代码:

     <?php

            require ("config1.php");

            if(!empty($_POST)){

       //UPDATED  
           $query="SELECT lecID, COUNT(*)  FROM proposal GROUP BY 
                      lecID=:lecID HAVING COUNT(*)>2 ";
                         $query_params= array(
                          ':lecID'=>$_POST['lecID']
                     );


       try {
         $stmt   = $db->prepare($query);
         $result = $stmt->execute($query_params);
       }catch (PDOException $ex) {
         $response["success"] = 0;
         $response["message"] = $ex->getMessage();
        die(json_encode($response));
}

$row= $stmt->fetch();
    if($row){
    $response["success"] = 0;
    $response["message"] = "This supervisor has reached maximum students.";
    die(json_encode($response));

    }



            $query="SELECT 1  FROM proposal WHERE stuName= :stuName ";
                $query_params= array(
                    ':stuName'=>$_POST['stuName']
                );

        try {
            $stmt   = $db->prepare($query);
            $result = $stmt->execute($query_params);
        }catch (PDOException $ex) {
            $response["success"] = 0;
            $response["message"] = "Database Error!";
            die(json_encode($response));
        }

        $row= $stmt->fetch();
            if($row){
            $response["success"] = 0;
            $response["message"] = "You have already choose supervisor.";
            die(json_encode($response));

            }


        ?>

如果有人能指出问题，我将不胜感激。

Answer 1

首先，为了解决您的问题，IMO 您至少应该尝试将您的代码封装在几个函数中，您可以为每个步骤调用这些函数。

学生类(class)提案的商业规则:

1. 允许创建两个提案的学生。 学生不能为两个提案选择相同的讲师(DB RULE)。

2. 讲师只能接受两个学生提案(每个提案将由不同的学生提出)。

   问题 你这里的规则有歧义，所以我假设提案表可以有比两个可以分配给同一个讲师的提案更多的提案，但每个提案必须是一个不同的学生。我假设您在该表中有一个名为“is_proposal_accepted”的列，当讲师接受提案时该列设置为 true。

3. 如果讲师已“接受”两个提案，或者学生在之前的提案中选择了该讲师，则学生在创建新提案时不能选择该讲师。

第一步在数据库而不是代码中强制执行业务规则的数据完整性要求:

 ALTER TABLE `proposal` ADD UNIQUE `unique_index`(`stuID`, `lecID`);

使用命令行或 Heidi SQL 等工具在您的数据库中执行上述操作。

第二步封装您的代码(一些非常粗略的伪代码可以帮助您构建结构):

        class Proposal
        {
            private $dataArr = [];
            private $response = [];

            public function doit($postArrayData)
            {
                //Clean up data if there are issues reject
                if (!$this->clean_up_data($postArrayData)) return $this->get_response();

                /**
                 * Before even trying to do any data inserts check the business rules
                 * Check if the lecturers
                 */
                if (!$this->check_if_lecturer_is_open()) return $this->get_response();
                if (!$this->check_if_student_has_available_proposals()) return $this->get_response();


                /**
                 *    
                If you have reached here in the method your Application based Business rules have been achieved


                NOW save the reocord, if the data violates the
                DB Rules your response will be set to an error,
                otherwise it will show a success message

                 */
                $this->add_proposal();
                return $this->get_response();
            }

            public function clean_up_data($postArrayData)
            {
               /**
                 * Apply any validation checks for data quality here then assign to array
                 EXAMPLE:  Set each field parameter like so:
                 */
                $this->dataArr['stuID'] = $postArrayData['stuID'];

                if (DataHasIssues) {
                    $this->set_response(0, "Crappy Data try again");
                    return false;
                }
                return true;
            }

            //Keep this method private, we want our POST data to be processed first and the in
            private function add_proposal()
            {



                /**
                 * 
                 * 
                 * 
                 * Your DB calls here.....
                 *
                 *  $query = "INSERT INTO proposal (lecName,lecID,stuName,stuID,proposalTitle,proposalObjective,proposalDesc)
                 * VALUES (:proposalID,:lecName,:lecID,:stuName,:stuID,:proposalTitle,:proposalObjective,:proposalDesc)";
                 */
                $query_params = $this->dataArr;
                /**
                 * Execute query with error control here.....
                 * if(ExecuteQuery){
                 *         * return true;  //Student proposal has been added
                 *}
                 * set errors here
                 */
                $this->set_response(0, DB_errors_msg);
                return false;

            }

            private function set_response($success, $message)
            {


                $this->response["success"] = $success;
                $this->response["message"] = $message;


            }

            public function get_response()
            {


                return $this->response;


            }

            public function check_if_lecturer_is_open()
            {

                /**
                 *
                 * $countOfAcceptedProposals ... Select SQL code here to count if the lecID has had two proposals flagged as accepted
                 * */
                if (CountOfAccepptedProsalsByLecID < 2) {
                    return true;
                }
                $this->set_response(0, "This Lecturer is full and is not available");
                return false;
            }


            public function check_if_student_has_available_proposals()
            {

                //USE the dataArr to get the StuID you want to check


                $CountOfProsalsByStuID = returned_count;// select count from where stuID = $this->dataArr['stuID'];

                /**
                 *
                 * $countOfStudentProposals ... Select SQL code here to count if the lecID has had two proposals flagged as accepted
                 * */
                if ($CountOfProsalsByStuID < 2) {
                    return true;
                }
                $this->set_response(0, "Student has reached the maximum number of proposals allowed");
                return false;
            }


        }

现在您可以调用 Proposal 类并让它完成检查业务规则和正确保存学生提案的艰苦工作。

    $proposal = new Proposal();
    echo json_encode($proposal->doit($_POST));  //This returns your response object