Leading zeros truncated for varchar column

peterbrowne

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();

peterbrowne

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 ;

colin

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

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

Colin

peterbrowne

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?

tangerine

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.

peterbrowne

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

kthorngren

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

peterbrowne

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

In the DB table:

kthorngren

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

peterbrowne

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.

peterbrowne

The server script is included above in my original question.

peterbrowne

There is the same issue in this thread.

peterbrowne

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

allan

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

Allan