PHP Custom ID generate help ekak

lilman

Well-known member
  • May 10, 2009
    40,048
    52,962
    113
    Colombo
    machan mata custom ID ekak generate karaganna help ekak one .

    no format ex: 210001,210002 etc

    21 - year eke last digit 2
    0001 - to foward auto increment nos

    meke first no eka db eke check karala , db eke natnam 210001 vidiyata enna one. eeta passe enter karana entries walata 210002,210003 wage digatama enna one. load weddi db eke last no eka check karala +1 wela new eka generate wenna one.

    new year ekak patan gattama new year eke first no eka 220001 wela enna one. eeta passe 220002,220003 widiyata continue wenna one.


    mekata help ekak denna puluwanda ? :)
     

    hasithayad

    Well-known member
  • Sep 28, 2011
    30,793
    1
    44,991
    113
    <?php $max_db_value = "210002"; //ex: select max(field) as name from tbl $year = substr(date("Y"), -2); $last_year = substr($max_db_value, 0,2); if ($year != $last_year) { // new year started $result = $year."0001"; } else { $max_value = intval(substr($max_db_value, 2,4)); $result = $year.str_pad(strval($max_value+1),4,"0",STR_PAD_LEFT); } echo $result; ?>
     

    ArnoDorian

    Well-known member
  • Jul 25, 2017
    890
    483
    63
    Did the same thing using PLSql few months back. The difference was we used to store a format. (Ex: YEAR00001)
    Approach:
    Check If a value exists(based on the YEAR):
    IF no record found:
    Get the format, and replace by Format by current year
    ELSE
    return value + 1

    Storing this a numeric would be easier, to increment
     

    Lakshan-Seram

    Well-known member
  • May 31, 2011
    24,714
    12,619
    113
    127.0.0.1:8080/Kandy
    Hadissiye liwwe podi awl athi :rolleyes:

    PHP:
    <?php
    
    /** Database **/
    $server = 'localhost';
    $user = 'root';
    $pass = '';
    $db = 'elakiri';
    $table = 'elakiri';
    
    /** Connection **/
    $conn = new mysqli( $server, $user, $pass, $db );
    
    /** If the connection is okay **/
    if ( $conn->connect_error ) {
     
        die( 'Connection error: ' . $conn->connect_error );
     
    }
    
    /** Start Id **/
    $id = 1;
    
    /** Gettings the last id **/
    $query = 'SELECT user_id FROM ' . $table . ' ORDER BY id DESC LIMIT 1';
    $result = $conn->query( $query );
    
    if( $result->num_rows > 0 ) {
     
        /** The selected row in database **/
        $row = $result->fetch_assoc();
     
        /** Extracting row **/
        extract( $row );
     
        /** Getting the Id in the database **/
        $id_last = substr( $user_id, -4 );
     
        /** Increment by 1 **/
        $id = $id_last + 1;
     
    }
    
    /** Last digits of the current year **/
    $year = date( 'y' );
    
    /** Format Id **/
    $id_formatted = $year . sprintf( '%04d', $id );
    
    /** Final Id **/
    var_dump( $id_formatted ); // Do whatever with this :)
    
    /** Colsing the connection **/
    $conn->close();
    
    ?>

    SQL:
    DROP TABLE IF EXISTS `elakiri`;
    CREATE TABLE IF NOT EXISTS `elakiri` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(6) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `elakiri`
    --
    
    INSERT INTO `elakiri` (`id`, `user_id`) VALUES
    (4, 210002),
    (3, 210001);
    COMMIT;

    ah podi awlak una.. new year ekak kiyana kella mataka una lol
    ------ Post added on Sep 13, 2021 at 7:09 PM

    Update

    PHP:
    <?php
    
    /** Database **/
    $server = 'localhost';
    $user = 'root';
    $pass = '';
    $db = 'elakiri';
    $table = 'elakiri';
    
    /** Connection **/
    $conn = new mysqli( $server, $user, $pass, $db );
    
    /** If the connection is okay **/
    if ( $conn->connect_error ) {
        
        die( 'Connection error: ' . $conn->connect_error );
        
    }
    
    /** Start Id **/
    $id = 1;
    
    /** Last digits of the current year **/
    $year = date( 'y' );
    
    /** Gettings the last id **/
    $query = 'SELECT user_id FROM ' . $table . ' ORDER BY id DESC LIMIT 1';
    $result = $conn->query( $query );
    
    if( $result->num_rows > 0 ) {
        
        /** The selected row in database **/
        $row = $result->fetch_assoc();
        
        /** Extracting row **/
        extract( $row );
        
        /** Getting the Id in the database **/
        $id_last = substr( $user_id, -4 );
        
        /** Getting the year in the database **/
        $year_last = substr( $user_id, 0, 2 );
        
        if( $year == $year_last ) {
            
            /** Increment by 1 **/
            $id = ( int ) $id_last + 1;
            
        }
        
    }
    
    /** Format Id **/
    $id_formatted = $year . sprintf( '%04d', $id );
    
    /** Final Id **/
    var_dump( ( int ) $id_formatted ); // Do whatever with this :)
    
    /** Colsing the connection **/
    $conn->close();
    
    ?>

    SQL:
    DROP TABLE IF EXISTS `elakiri`;
    CREATE TABLE IF NOT EXISTS `elakiri` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(6) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `elakiri`
    --
    
    INSERT INTO `elakiri` (`id`, `user_id`) VALUES
    (4, 200002),
    (3, 200001),
    (5, 210001);
    COMMIT;
    ------ Post added on Sep 13, 2021 at 7:17 PM
     
    Last edited: