SELECT MAX() formatted string from a column using CodeIgniter's query builder

Question

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.

I would be sorely tempted to revise the schema, to store year? and no? separately — Strawberry
– Strawberry, Commented Jan 25, 2020 at 14:10
Use the query SELECT CAST(MAX(SUBSTRING(auto_no, -5)) AS UNSIGNED) AS max_no FROM auto_table SQL Fiddle — Madan Sapkota
– Madan Sapkota, Commented Jan 25, 2020 at 14:39
@ Madan. Its ok. But the function should be included into $this->db->.............. — MCIT Trends
– MCIT Trends, Commented Jan 25, 2020 at 14:41
You can do that :) $this->db->select(..., false). Use false as second paramter to accept raw SQL. — Madan Sapkota
– Madan Sapkota, Commented Jan 25, 2020 at 14:44
Related content: CodeIgniter database query with function calls in the SELECT clause and WHERE clause — mickmackusa
– mickmackusa ♦, Commented 4 hours ago

TsaiKoga · Accepted Answer · 2020-01-25 15:20:06Z

1

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

edited Jan 25, 2020 at 15:20

answered Jan 25, 2020 at 14:42

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

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

8 Comments

MCIT Trends Over a year ago

@ TsaiKoga. But it didn't include the table name. Assume table as letter_letter

TsaiKoga Over a year ago

@MCITTrends then use get(table_name)

MCIT Trends Over a year ago

@ TsaiKoga. But it reruns the min value as 2020-00001. All function as edited. Pls. see on edit

TsaiKoga Over a year ago

@MCITTrends I think you can just take the max auto_no and increase it like my post.

MCIT Trends Over a year ago

@ TsaiKoga. I think that is closed to the expected output. But outs the error, Message: Undefined offset: 1 Filename: models/Letter_model.php Line Number: 346

|

forpas · Accepted Answer · 2020-01-25 14:20:25Z

0

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:

answered Jan 25, 2020 at 14:20

forpas

165k10 gold badges51 silver badges85 bronze badges

2 Comments

MCIT Trends Over a year ago

@ forpas. Can you add your suggestion into $this->db->select_max()

MCIT Trends Over a year ago

@ forpas. No that is not. In Codeigniter, should be used $this->db->select_max in the left

Barbaros Özhan · Accepted Answer · 2020-01-25 14:29:26Z

0

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.

edited Jan 25, 2020 at 14:29

answered Jan 25, 2020 at 14:10

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

mickmackusa · Accepted Answer · 2025-11-30 04:07:35Z

First of all, this task has a smell. Why are you creating a formatted string comprised of two separate data points in your database? Why don't you have an auto-incremented id column and a year column?

Second, your formatted string appears to be "big-endian" (and consistently zero padded), so a straight MAX() should suffice.

Third, by manually generating an identifier outside of your database, your application will be more likely to fail due to a race condition.

If you simply must have a drop-in solution, then I suppose I recommend:

public function generate_auto_no(int $incBy = 1, int $start = 0, int $digits = 5): string
{
    if ($incBy < 1) {
        throw new Exception('$incBy parameter must be a minimum of 1');
    }
    $max = $this->db
        ->select_max('auto_no')
        ->get('letter_letter')
        ->row('auto_no');
    if ($max !== null) {
        sscanf($max, '%*d-%d', $minNext); // assuming date, hyphens, number
        ++$minNext;
    }
    $minNext ??= 0; // in case table is empty
    while ($start < $minNext) {
        $start += $incBy;
    }
    return date('Y-') . str_pad($start, $digits, '0', STR_PAD_LEFT);
}

Collectives™ on Stack Overflow

SELECT MAX() formatted string from a column using CodeIgniter's query builder

4 Answers 4

8 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related