CODEWITHSUNDEEP
phpmysqlcodeigniter
HealMee
HealMee

Reputation: 85

Get last number from unique ID from database SQL - PHP CodeIgniter

scroll down for the answer

I have Registration table like this: table
Now I'm hopping to get the last digit, for example:

The purpose is for generating new Reg_ID, for example:

Here's my current code:

public function generate_no_reg($nmNegara)
    {
        $dtNow = date('Y-m-d');
        $query = 'SELECT COUNT(Reg)ID) FROM Registratrion WHERE Reg_Date="$dtNow"';
        $lastid = $this->db->query($query);
        $id = $lastid->num_rows()+1;
        $regCount = format_no_registrasi($id);

        $kode = $this->get_negara($nmNegara).$regCount;
        return $kode;

    }

And here's my current code for generating the last 3 digit:

function format_no_registrasi($no)
{   
    $leadingzeros = '000';
    $no_reg = date('Y') . date('m') . substr($leadingzeros, 0, (-strlen($no))) . $no+1;
    return $no_reg;
}

The problem I'm having from this code is:

  1. When i delete a row, the Primary Key will be doubled, example:
    • SG20160412001 << I delete this one
    • SG20160412002
  2. Now the data count is returning 1, and when I generate new one it will be: SG20160412002

Any help is very much appreciated. Sorry for the problem.

scroll down for the answer

Upvotes: 0

Views: 1316

Answers (4)

Max P.
Max P.

Reputation: 5679

This query cuts 3 last signs from Reg_ID and returns max value. 

select max(cast(substr(Reg_ID, -3) as UNSIGNED))
from FROM Registratrion
WHERE Reg_Date="$dtNow"

So id numeration supports ids till 999

Upvotes: 2

HealMee
HealMee

Reputation: 85

My answer is helped by Markus and Max

This is the function that i used for leading characters, since the 'SG' is from database:

public function get_negara($nmNegara)
    {
        $this->db->select('Kode_Negara');
        $this->db->from('tb_negara_tujuan');
        $this->db->where('Nama_Negara',$nmNegara);
        $query = $this->db->get();
        $result = $query->row();
        return $result->Kode_Negara . date('Ymd');
    }
 // this will returning 'SG20160413'

This is the helper function used to convert 1 to 001:

function format_no_registrasi($no)
{   
    $leadingzeros = '000';
    $no_reg = substr($leadingzeros, 0, (-strlen($no))) . $no;
    return $no_reg;
}

This is the function used to generate the full Reg_ID:

public function generate_no_reg($nmNegara)
    {
        $leadingChar = $this->get_negara($nmNegara);
        $this->db->select_max('No_Registrasi');
        $this->db->like('No_Registrasi',$leadingChar);
        $query = $this->db->get('tb_registrasi');
        $result = $query->row();
        $noreg = (int) substr($result->No_Registrasi,-3);
        $noreg++;
        return $kode = $leadingChar.format_no_registrasi($noreg);
    }

Upvotes: 0

Arth
Arth

Reputation: 13110

Have you considered splitting out your reg_id into three columns?

  • prefix CHAR(2)
  • reg_date DATE
  • postfix SMALLINT UNSIGNED

You can use a surrogate or composite PRIMARY if you need and concat the columns on select:

SELECT CONCAT(prefix, reg_date, LPAD(post_fix,3,'0')) reg_id 
  FROM registration

Keeping separate data in separate fields saves a lot of headache with operations like this.

Upvotes: 0

Markus M&#252;ller
Markus M&#252;ller

Reputation: 2641

Max' solution is much cleaner.

Don't use the count, use the highest number.

Create the leading characters of the Reg_ID

$leadingCharacters = 'SG' + date('Ymd');

Then select the hightes Reg_ID that starts with this string:

select Reg_ID from Registration where Reg_ID like '$leadingCharacters%' order by Reg_ID desc limit 0,1

Then extract the last three digits from the result, convert it to int and increment it. This will be your next ID.

$number = (int) substr($result,-3);
$number++;

NOTICE: Typed the code from memory, so there might be a little error in it. But the concept should work though

Upvotes: 0

Related Questions