Cakephp save array data in MySql Database using loop with create

Question

I am facing strange problem first time. I am trying to insert simple records in database with Custom Auto Increment Values e.g 00001, 00002, 00003. but unable to get Incremented value. Each record get updated with same max number.

Controller Code

public function dobulk() {
    for($i=0;$i<5;$i++) {
        $data = array();
        $this->Tmp->create();
        $data['Tmp']['invoice_no'] = $this->Tmp->get_no();
        $data['Tmp']['invoice_date'] = '2013-12-11';
        $this->Tmp->save($data);
    }
   }

Model Code

public function get_no() {
    $strSql = "SELECT
                LPAD(IFNULL(RIGHT(MAX(invoice_no),5),0) + 1,5,'0') AS max_id
        FROM tmps
        ;";

    $result = $this->query($strSql);
    $invoice_no = $result[0][0]['max_id'];
    return $invoice_no;
}

Database Table

CREATE TABLE `tmps` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_no` varchar(20) DEFAULT NULL,
  `invoice_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

Thanks in advance.

Seems like this should be tagged as MySQL instead of CakePHP. You'll likely get more help that way, since it's a MySQL question. — Dave
– Dave, Commented Dec 12, 2013 at 5:45

Ben Hitchcock · Accepted Answer · 2013-12-12 22:59:33Z

1

You're probably running into the model cache.

From here: Bakery article on caching

If Model->cacheQueries == true, Cake will store the result of queries in memory. If it later sees an identical query, the result will be pulled from memory instead of hitting the database. This is only cached for the duration of a single page request. However, if a record is updated, the cache is not cleared. This is what gets most people unfamiliar with the cache.

So to make your code work, add this line to the top of the dobulk() function:

$this->Tmp->cacheQueries = false;

As you're running raw SQL inside your model, you might also have to change the query request inside get_no() to:

$result = $this->query($strSql, false);

edited Dec 12, 2013 at 22:59

answered Dec 12, 2013 at 22:06

Ben Hitchcock

1,37810 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sanjeev Divekar Over a year ago

Thanks Ben. Its really worked. I don't know how I missed Cache thing.

Anubhav · Accepted Answer · 2013-12-12 05:46:24Z

0

Hope below solution will help you!

   public function get_no() {
        $strSql = "SELECT MAX(invoice_no) AS max_id FROM tmps;";
        $result = $this->query($strSql);
        $invoice_no = $result['Tmp']['max_id'];
        return $invoice_no;
    }


    public function dobulk(){
      $data = array();
      $tempInvoiceNumber = $this->Tmp->get_no();
      for($i=1;$i<=5;$i++) {
        $data[$i]['Tmp']['invoice_no'] = tempInvoiceNumber+$i;
        $data[$i]['Tmp']['invoice_date'] = '2013-12-11';
       }
      $this->Tmp->saveAll($data);
    }

answered Dec 12, 2013 at 5:46

Anubhav

1,6234 gold badges18 silver badges32 bronze badges

1 Comment

Sanjeev Divekar Over a year ago

Thanks Anubhav, I will implement your solution, still I wonder what is root cause of this problem.

Collectives™ on Stack Overflow

Cakephp save array data in MySql Database using loop with create

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related