CODEWITHSUNDEEP
phpmysqlcakephp
techie_28
techie_28

Reputation: 2133

Incorrect record in MYSQL Max in cakephp query 1.1

I have a scenario where I need to find a number higher then the last highest number and then insert that number in a new row and it has to be done twice depending on 2 if statements I was using mysql max for this like:

if(condtn1){
    $maxNum = $this->model->query('SELECT SQL_NO_CACHE max(qte_number) + 1 as newQuoteNumber FROM qtes LIMIT 1');
 $this->model->save($data) // the max number caluclated above inserts here and is visible in DB
}
if(condtn2){
    $maxNum = $this->model->query('SELECT SQL_NO_CACHE max(qte_number) + 1 as newQuoteNumber FROM qtes LIMIT 1');
 $this->model->save($data); //simillar case with some values different in data array 
}

I was assuming that the number saved in first condition will now act as a base for the number that I need in second condition. Like first one generated 200 and it got saved in database so it should be 201 in second condition but it remains 200 in both of them. I tried some caching options to disable cache here like

$this->cacheQueries = false;

and also manually deleted it from the tmp folder but to no effect.

Thanx all.

Upvotes: 0

Views: 231

Answers (2)

thaJeztah
thaJeztah

Reputation: 29137

Apparently you're trying to create what's called a 'gapless sequence'.

However, because you're using two separate queries to realise this (first calculate max(x) + 1, then use that value to insert the new record), you're risking the chance that the calculated value is no longer the right value; it's possible that someone else created a new record between the moment you calculate the new number and insert the new record.

A better option might be to have the database calculate the value for you during insert. You can do so using a 'trigger'; this question on Stackoverflow does something similar and may give you the right pointers to achieve this;

MySQL trigger to set column to max + 1 not working

Leaving the responsibility to create the new ID to the database also has the advantage that, no matter what tool you're using to insert the data, the database will always make sure that a correct ID/sequence is generated.

So, even if you insert a new record via the MySQL shell or using PhpMyAdmin, the database will automatically generate the new number.

Upvotes: 1

techie_28
techie_28

Reputation: 2133

I have found a way of making that work. It was a caching issue and there is a second param in query function which needs to be set to not cache the query Like

$this->model->query($qry,false);

Thanx all.

Upvotes: 0

Related Questions