dongshungai4857
dongshungai4857
2019-07-14 07:37
浏览 83
已采纳

为不同的请求生成相同的序列号

I am inserting a serial number in a table that is increment by one always but when multiple request is coming in same time it is inserting same serial number for different requests.I am using mysql database.

I know i am fetching the max serial number too early in the code and if request is come in same time so it will fetching same serial number for both. is it good idea to update serial number after all work done. what if inserting a record for new request and updating the serial number for previous one is in same time.

public function add(){
    $session          = $this->request->session();

    $company_id       = $session->read('Admin.company_id');
    $emp_id           = $session->read('Admin.emp_id');
    $user_email_id           = $session->read('Admin.email_id');
    $employee_name           = $session->read('Admin.employee_name');

    $conn = ConnectionManager::get('default');

    if ($this->request->is('post')) {

        try{

                $conn->begin();   
                $department = $this->request->data['department'];
                $data = $this->request->data;

                if(!array_key_exists('is_requisition_for_contractor', $data)){
                    $is_requisition_for_contractor = 0;
                } else {
                    $is_requisition_for_contractor = $data['is_requisition_for_contractor'];
                }

                if(!array_key_exists('is_requisition_for_employee', $data)){
                    $is_requisition_for_employee = 0;
                } else {
                    $is_requisition_for_employee = $data['is_requisition_for_employee'];
                }

                if(!array_key_exists('is_boulder_requisition', $data)){

                    $is_requisition_for_boulder = 0;
                } else {
                    if($data['is_boulder_requisition'] == ''){
                        $is_requisition_for_boulder = 0;
                    } else {
                        $is_requisition_for_boulder = $data['is_boulder_requisition'];
                    }
                }
                $is_requisition_for_plant = 0;
                if(!array_key_exists('is_plant_requisition', $data)){

                    $is_requisition_for_plant = 0;
                } else {
                    if($data['is_plant_requisition'] == ''){
                        $is_requisition_for_plant = 0;
                    } else {
                        $is_requisition_for_plant = $data['is_plant_requisition'];
                    }
                }

                if(array_key_exists("files",$this->request->data)) {
                    $files      = $this->request->data['files'];

                    if (count($files)) {
                        $files_uploading_response = $this->uploadMultipleFiles($files, 'files/requisitions/');
                    }
                }
                $last_material_insert_id = '';
                if($this->request->data('material_id')[0] == ''){
                    if($this->request->data('department') == 1){
                        $type = 1;
                    } elseif($this->request->data('department') == 3){
                        $type = 3;
                    } elseif($this->request->data('department') == 2){
                        $type = 2;
                    }
                    if($this->request->data('department') == 1 || $this->request->data('department') == 3){
                        $conn->execute("INSERT INTO material (material_name, material_type_id, company_id, status, is_approved_by_admin) VALUES (?,?,?,?,?)",[$this->request->data('material_name'), $type, $company_id, 1,0]);
                        $last_material_insert_id = $conn->execute("SELECT LAST_INSERT_ID() AS last_id")->fetchAll('assoc');
                    } elseif($this->request->data('department') == 2) {

                        //todo for unapproved material
                        $conn->execute("INSERT INTO material (part_no, material_type_id, company_id, status, is_approved_by_admin,unique_category_id) VALUES (?,?,?,?,?,?)",[$this->request->data('part_no')[0], $type, $company_id, 1,0,$this->request->data('unique_category_id')[0]]);
                        $last_material_insert_id = $conn->execute("SELECT LAST_INSERT_ID() AS last_id")->fetchAll('assoc');
                    }

                }
                // here i am fatching max serial number from table
                $requistion_number          = $conn->execute("SELECT IF(MAX(requisition_no) IS NULL, 0,MAX(requisition_no))  AS requisition_no FROM requisition WHERE site_id = ?",[$this->request->data('site_id')])->fetchAll('assoc');
                $Requisition                = TableRegistry::get('requisition');
                $requisition                = $Requisition->newEntity();
                $requisition->registered_on = $this->request->data['date'];
                $requisition->department_id = $this->request->data('department');
                $requisition->site_id = $this->request->data('site_id');
                $requisition->issues_to_id = $this->request->data['prepared_by_id'];
                $requisition->prepared_by_id = $this->request->data['prepared_by_id'];
                $requisition->approved_by_id = $this->request->data['hod_id'];
                $requisition->hod_id         = $this->request->data['hod_id'];
                $requisition->is_diesel_requisition_for_employee = $is_requisition_for_employee;
                $requisition->is_diesel_requisition_for_contractor = $is_requisition_for_contractor;
                $requisition->is_requisition_for_boulder         = $is_requisition_for_boulder;
                $requisition->is_requisition_for_plant = $is_requisition_for_plant;
                if(array_key_exists('for_tanker_stock', $this->request->data))    {
                    $requisition->for_tanker_stock         = 1;
                }
                if($last_material_insert_id != ''){
                    $requisition->is_material_approved_by_admin = 0;
                }
                $requisition->status         = 1;
                $site_id                     = $this->request->data['site_id'];
                $requisition->requisition_no = $requistion_number[0]['requisition_no'] + 1;
                $requistionnumber = $requistion_number[0]['requisition_no'] + 1;


                $saveRequsition = $Requisition->save($requisition);
                $conn->commit();
}

I am expecting the output different serial number for each request.any optimise way to do this. thanks in advance.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douao1854
    douao1854 2019-07-14 09:07
    已采纳

    Ok, how about the same strategy, setting the $requisition_number after the row has been inserted (see my other answer), but using a single query with the same method you use to determine the new requisition id:

    $conn->execute("UPDATE requisition 
                    SET requisition_no = (SELECT IF(MAX(requisition_no) IS NULL, 0,MAX(requisition_no)) AS requisition_no FROM requisition WHERE site_id = ?) + 1",
                   [$this->request->data('site_id')]);
    

    The idea here is that a single query will be executed in one step, without another, similar query, being able to interfere.

    点赞 评论
  • duanjiebian6712
    duanjiebian6712 2019-07-14 08:31

    What you currently do is to first get the old requistion number like this:

    $requistion_number = $conn->execute("SELECT IF(MAX(requisition_no) IS NULL, 0,MAX(requisition_no)) AS requisition_no 
                                         FROM requisition WHERE site_id = ?",[$this->request->data('site_id')])->fetchAll('assoc');
    

    and then increase it before you save and commit.

    My suggestion is to not set the $requistion_number at all before you save and commit the requisition row, but to determine the $requistion_number afterwards.

    You now wonder how?

    Well, you need to count the total number of requisition rows in the table for the site the requisition is for, and add one, like this:

    $last_requisition_id = $conn->execute("SELECT LAST_INSERT_ID() AS last_id")->fetchAll('assoc');
    $site_id = $this->request->data('site_id');
    $requisition_number = $conn->execute("SELECT COUNT(*) AS requisitionsCount 
                                          FROM requisition
                                          WHERE <primary_key> <= ? AND
                                                site_id = ?",
                                          [$last_requisition_id, $site_id]) + 1;
    $conn->execute("UPDATE requisition 
                    SET requisition_no = ?
                    WHERE <primary_key> <= ?",
                   [$requisition_number, $last_requisition_id]);
    

    I know this code is not working. The $requisition_number will probably contain an array with the requisitionsCount as a value, but you can correct that.

    Because you're using data that is already present in the database table you don't run the risk that two rows will get the same $requisition_number. The assumption here is that requisitions are never deleted.

    点赞 评论

相关推荐