Leading zeros truncated for varchar column

Leading zeros truncated for varchar column

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
edited November 2021 in Editor

Leading zeros are being truncated for a varchar column user.user_id. How can I retain the leading zeros? E.g. 00123456 is selected, inserted and updated as 123456. I need to keep the leading zeros as they are staff IDs...

columns: [ {
        data: "user.user_id"
        },...

and

ar editor = new $.fn.dataTable.Editor( {
                ajax: "user_data.php",
                table: "#user_table",
                template: '#user_form',
                fields: [ {
                    label: "User ID:",
                    name: "user.user_id"
                },

and

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'user', 'user_pk' )
    ->fields(
    Field::inst( 'user.user_id' ),
    Field::inst( 'user.first_name' )
    ->get( $_SESSION['cm_user']['permission'] ),
    Field::inst( 'user.last_name' ),
    Field::inst( 'user.email' ),
    Field::inst( 'user.user_type' ),
    Field::inst( 'user.modified' ),
    Field::inst( 'user.modified_by' )->setValue( $user )
)
    ->join(
        Mjoin::inst( 'program' ) 
            ->link( 'user.user_pk', 'user_program_lookup.user_fk' )
            ->link( 'program.program_pk', 'user_program_lookup.program_fk' )
           ->order( 'program.program_name asc' )
            ->fields(
                Field::inst( 'program_pk' )
                    ->options( Options::inst()
                        ->table( 'program' )
                        ->value( 'program_pk' )
                        ->label( 'program_name' )         
                    ),
                Field::inst( 'program_name' )
            )
    )
    ->process( $_POST )->json();

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    This is the table:

    CREATE TABLE IF NOT EXISTS `user` (
      `user_pk` int(4) NOT NULL AUTO_INCREMENT,
      `user_id` varchar(20) NOT NULL,
      `first_name` varchar(50) NOT NULL,
      `last_name` varchar(100) NOT NULL,
      `email` varchar(100) NOT NULL,
      `user_type` varchar(20) NOT NULL DEFAULT 'read',
      `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `modified_by` varchar(50) NOT NULL,
      PRIMARY KEY (`user_pk`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    It's probably because Editor thinks they're numeric so is being 'helpful'. You can force the field to be a string with :smile:

                    fields: [ {
                        label: "User ID:",
                        name: "user.user_id",
                        type: 'text'
                    },
    

    Colin

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited November 2021

    Thanks Colin. That might be OK for the Editor, but the retrieval from the DB is still truncating the zeros.

    So that when the editor updates or inserts, the field is still missing the zeros, unless the user adds the zeros manually each time. I think the type needs to be added in other places, e.g. the data script above?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    but the retrieval from the DB is still truncating the zeros.

    In that case it's not really a DT issue. You need to fix it at the db level. Google has a lot to say about varchar leading zeros.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    I don't believe the issue is with the DB column. I can manually add the zeros directly via PhpMyAdmin and they are retained.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited November 2021

    but the retrieval from the DB is still truncating the zeros.

    Do you mean the display in the Datatables table truncates the leading zeros?

    Have you used the browser's network inspector to validate what is returned when Datatables fetches the table data and the request and response when editing a row?

    Basically we need to understand where the leading zeros are truncated. Can you post a link to your page or a test case showing the issue?
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    This is what is shown in datatables. The leading zeros are missing.

    In the DB table:

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    OK, so what is in the JSON response? Use the browser's network inspector to look at the response of Datatables XHR request.

    If the leading zeros or in the JSON response then try setting columns.type to string. If the leading zeros aren't in the response then the problem is with the server script and will need to be debugged there.

    Kevin

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    The JSON contains no leading zeros. You can see my ID in the JSON is 82563. In the database it is 00082563, which is correct.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    The server script is included above in my original question.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    There is the same issue in this thread.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Just updated from Editor-PHP-1.9.5 to Editor-PHP-1.9.6. Leading zeros now retained fine. Fixed.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    Thanks for the update - good to hear that the update fixed it!

    Allan

Sign In or Register to comment.