Check if value exist more than twice in database

Question

I want to make a restriction if lecID appears twice in the database, it will unable user to insert data into database. But the code has some problem which it will still insert into db even though lecID appears more than two time in my database. Can I know what is the problem. Below is my code:

     <?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));

            }


        ?>

Really appreciate if someone can point out the problem.

Answer 1

First thing, for solving your problem, IMO you should at least try to encapsulate your code in several functions that you can call for each step.

BUSINESS RULES for student course proposals:

1. A Student that is allowed to create two proposals. The student cannot select the same lecturer for both proposals (DB RULE).

2. A Lecturer can only accept two Student proposals (each proposal will be a different student).

   ISSUE Your rule here has ambiguity, so I am making an assumption that the proposal table could have more proposals than two that can be assigned to the same lecturer but that each proposal must be a different stuID. I am assuming you have a column called 'is_proposal_accepted' in that table that is set to true when a lecturer accepts a proposal.

3. student when creating a new proposal cannot choose a Lecturer if that Lecturer has "accepted" two proposals or the student has chosen that Lecturer in a previous proposal.

Step one Enforce the Data Integrity requirement of your Business Rules in the database not in your code:

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

Execute the above in your database using the Command Line or a tool such as Heidi SQL.

Step two Encapsulate your code (Some very rough pseudo code to help you with structure):

        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;
            }


        }

Now you can call the Proposal class and have it do the hard work of checking your business rules and saving your Student Proposals properly.

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