php - Query using MAX -

i have multiple user accounts , each of them running incremental set of integer own transactions.

so, everytime user open transaction, query last max digit within transaction , plus 1.

but, found result returning maximum id user transaction. supposedly user has last id = 5402, , user z has last id = 19201. sometimes, user gets 19202 instead of 5403.

this query:

select max(cast(id unsigned)) max_id `transaction` `user_id` = 'a' 

the transaction table like: id int pk user_id int ... etc

this web application , multiple users connect simultaneously , i'm using mysql database, , php programming language.

i'm using ci, here code use obtain max id

function get_max($table, $max_col, $col_id = null, $id = null) {     if (!empty($col_id) && !empty($id)) {         $this->db->where($col_id, $id);     }     $this->db->select("max(cast($max_col unsigned)) max_$max_col");     $query = $this->db->get($table);      if ($query->num_rows() > 0) {         return intval($query->row_array()["max_$max_col"]);     }     return 0; } 

once obtained id, insert below:

$new_data['id'] = $this->model_share->get_max('transaction', 'id', 'user_id', $user_id) + 1; $new_data['user_id'] = $user_id;  $this->model_share->insert('transaction', $new_data); // save data 

and detail of insert function

function insert($table, $data) {     $this->db->insert($table, $data);      $id = $this->db->insert_id();     if ($id <= 0)         return $this->db->affected_rows() > 0;     else return $id; }  

codeigniter has query function max value column database table,$this->db->select_max()

$this->db->select_max('your_column'); $query = $this->db->get('table');  

and database transaction add before when start query , end @ last query.

$this->db->trans_start(); $this->db->query('an sql query...'); $this->db->query('another query...'); $this->db->query('and yet query...'); $this->db->trans_complete(); 

note: database transaction useful when use when doing many database related query inserting , updating before it.

here reference link

• query_builder
• transactions

edit:

you have syntax error in lines because of max_$max_col this.

//error line1  $this->db->select("max(cast($max_col unsigned)) max_$max_col");  //error line 2  return intval($query->row_array()["max_$max_col"]);