title: Get last number from unique ID from database SQL - PHP CodeIgniter
body: <p><em>scroll down for the answer<br/></em>
  <p>I have Registration table like this:
  <a href="http://i.stack.imgur.com/Utk7L.png" rel="nofollow">table</a>
  <br/>
  Now I'm hopping to get the last digit, for example:</p>
  
  <ul>
  <li>SG20160412001 >> 1</li>
  <li>SG20160412056 >> 56</li>
  <li>SG20160412121 >> 121<br/></li>
  </ul>
  
  <p>The purpose is for generating new Reg_ID, for example:</p>
  
  <ul>
  <li>If the returned data is = 1 then the new Reg_ID will be SG20160412002</li>
  <li>If the returned data is = 56 then the new Reg_ID will be SG20160412057</li>
  <li>If the returned data is = 0 then the new reg_ID will be SG20160412001</li>
  </ul>
  
  <p>Here's my current code:</p>
  
  <pre><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-&gt;db-&gt;query($query);
          $id = $lastid-&gt;num_rows()+1;
          $regCount = format_no_registrasi($id);
  
          $kode = $this-&gt;get_negara($nmNegara).$regCount;
          return $kode;
  
      }
  </code></pre>
  
  <p>And here's my current code for generating the last 3 digit:</p>
  
  <pre><code>function format_no_registrasi($no)
  {   
      $leadingzeros = '000';
      $no_reg = date('Y') . date('m') . substr($leadingzeros, 0, (-strlen($no))) . $no+1;
      return $no_reg;
  }
  </code></pre>
  
  <p>The problem I'm having from this code is:</p>
  
  <ol>
  <li>When i delete a row, the Primary Key will be doubled, example:
  
  <ul>
  <li>SG20160412001  &lt;&lt; I delete this one</li>
  <li>SG20160412002</li>
  </ul></li>
  <li>Now the data count is returning 1, and when I generate new one it will be: SG20160412002</li>
  </ol>
  
  <p>Any help is very much appreciated. Sorry for the problem.
  <p><em>scroll down for the answer<br/></em></p>