CODEWITHSUNDEEP
phpmysqlcodeigniter
yodann
yodann

Reputation: 355

Query using MAX

I have multiple user accounts and each of them running incremental set of integer for their own transactions.

So, everytime a user open a transaction, I will query the last max digit within his transaction and plus 1.

But, sometimes I found the result is returning the maximum ID from another user transaction. Supposedly user A has the last ID = 5402, and user Z has last ID = 19201. Sometimes, the user A gets the 19202 instead of 5403.

This is my query:

SELECT MAX(CAST(id AS UNSIGNED)) as max_id FROM `transaction` WHERE `user_id` = 'A'

The transaction table is like: id INT PK user_id INT ... etc

This is a web application and multiple users connect simultaneously and I'm using mysql as database, and php as the programming language.

I'm using CI, here is the code I use to 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 AS UNSIGNED)) as max_$max_col");
    $query = $this->db->get($table);

    if ($query->num_rows() > 0) {
        return intval($query->row_array()["max_$max_col"]);
    }
    return 0;
}

Once I obtained the id, I insert as 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 this is the 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;
}

Upvotes: 1

Views: 68

Answers (1)

always-a-learner
always-a-learner

Reputation: 3794

Codeigniter has a query function to get max value from column from database table,$this->db->select_max()

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

And for database transaction add this before when you start any query and end it at the last query.

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete();

Note: Database transaction is useful when you use it when you are doing many database related query like you are inserting something and then updating just before it.

Here is the reference link

Edit:

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

//Error Line1
 $this->db->select("MAX(CAST($max_col AS UNSIGNED)) as max_$max_col");

//Error Line 2
 return intval($query->row_array()["max_$max_col"]);

Upvotes: 1

Related Questions