CODEWITHSUNDEEP
phpmysqlsqlcodeignitermax
MCIT Trends
MCIT Trends

Reputation: 281

Codeigniter MySQL select max using varchar type value

I want to select max auto_no from the following varchar type column in my codeigniter model

+------------+
|  auto_no   |
+------------+
| 2020-00821 |
| 2020-00822 |
| 2020-00823 |
| 2020-00824 |
| 2020-00825 |
+------------+

In this example that the value is 825. I tried following option

public function generate_auto_no($count = 1, $start = 00000, $digits = 5)
    {
        $query = $this->db->get('letter_letter');
        $this->db->select("MAX(CAST(SUBSTRING_INDEX(auto_no, '-', -1) AS UNSIGNED)) AS auto_no", FALSE);        
        $count = ($query->num_rows() > 0) ? $query->row()->auto_no + 1 : 0;
        $result = array();
        for ($n = $start; $n <= $start + $count; $n++) {
            $result[] = str_pad($n, $digits, "0", STR_PAD_LEFT);
        }
        return date("Y").'-' . end($result);
    }

But didn't get the expected value. Can anyone help ?

Upvotes: 0

Views: 420

Answers (3)

TsaiKoga
TsaiKoga

Reputation: 13394

I thin you can just select the max string: 

$this->db->select_max("auto_no");
$query = $this->db->get('letter_letter');
if ($query->num_rows() > 0) {
    $last_auto_no = $query->row()->auto_no;
    $no = intval(explode('-', $last_auto_no)[1]) + 1;
    $auto_no = date("Y").'-' . str_pad($no, $digits, "0", STR_PAD_LEFT);
} else {
    $auto_no = (date("Y").'-' . str_pad(0, $digits, "0", STR_PAD_LEFT));
}
return $auto_no

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

One option would be using custom query :

$this->db->query(" SELECT MAX( CAST( SUBSTR(auto_no,INSTR(auto_no,'-')+1,LENGTH(auto_no)) AS SIGNED) ) FROM tab ");

including well-known string operators of MySQL DB.

Upvotes: 0

forpas
forpas

Reputation: 164184

The naximum value can be retrieved by using MAX() for the varchar values and then extracting the number of the last 5 chars:

select right(max(in_num), 5) + 0
from tablename

See the demo.
Result:

825

Upvotes: 0

Related Questions