jQuery UI Widgets Forums Grid Paging with jqxGrid using PHP and PDO postgresql

Tagged: ,

This topic contains 7 replies, has 4 voices, and was last updated by  Yavor Dashev 3 years, 1 month ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author

  • robin
    Participant

    I am having trouble rendering my data into the grid layout. I am using PDO postgresql as my database call and I am able to successfully retrieve the data via a PHP call. However, I am having difficulty storing that data into my jqwidgets/jquery grid. The documentation on “Server Side Paging with jqxGrid using PHP” is outdated and not sure how to proceed.

    The specific issue I am having is in my index.php file where it is able to retrieve the data via PHP but I was unable to store that data into the grid.

    Here is an image without the data since I am not sure how to store that data into the grid: image

    This is my code thus far:

    <?php
    require 'vendor/autoload.php';
    
    use PostgreSQLTutorial\Connection as Connection;
    use PostgreSQLTutorial\CustomerDB as CustomerDB;
    
    try {
        $pdo = Connection::get()->connect();
        $customerDB = new CustomerDB($pdo);
        $customers = $customerDB->findCustomer(1, 5);
    } catch (\PDOException $e) {
        echo $e->getMessage();
    }
    ?>
    <!DOCTYPE html>
    <html>
        <head>
            <title>PostgreSQL PHP Querying Data Demo</title>            
            <link rel="stylesheet" href="../jqwidgets/styles/jqx.base.css" type="text/css"/>
            <link rel="stylesheet" href="../jqwidgets/styles/jqx.classic.css" type="text/css"/>
            <meta name="viewport" content="width=device-width, initial-scale=1" />
            <script type="text/javascript" src="../jqwidgets/jqxcore.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxbuttons.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxscrollbar.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxmenu.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxcheckbox.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxlistbox.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxdropdownlist.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxgrid.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxgrid.pager.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxgrid.selection.js"></script>
            <script type="text/javascript" src="../jqwidgets/jqxdata.js"></script>
    
            <script type="text/javascript">
                $(document).ready(function () {
                    // prepare the data
                    var theme = 'classic';
                    var source =
                    {
                        datatype: "json",
                        datafields: [
                            { name: 'CompanyName' },
                            { name: 'ContactName' },
                            { name: 'ContactTitle' },
                            { name: 'City' },
                            { name: 'Country' }
                        ],
                        cache: false,
                        url: 'CustomerDB.php',
                        root: 'Rows',
                        beforeprocessing: function (data) {                        
                            var data = <?php echo json_encode($customers); ?>;
                            source.totalrecords = data[0].TotalRows;
                        }
                    };
                    var dataadapter = new $.jqx.dataAdapter(source);
                    // initialize jqxGrid
                    $("#jqxgrid").jqxGrid(
                    {
                        width: 600,
                        source: dataadapter,
                        theme: theme,
                        autoheight: true,
                        pageable: true,
                        virtualmode: true,
                        rendergridrows: function (params) {
                            return params.data;
                        },
                        columns:
                        [
                            { text: 'Company Name', datafield: 'CompanyName', width: 250 },
                            { text: 'Contact Name', datafield: 'ContactName', width: 200 },
                            { text: 'Contact Title', datafield: 'ContactTitle', width: 200 },
                            { text: 'City', datafield: 'City', width: 100 },
                            { text: 'Country', datafield: 'Country', width: 140 }
                        ]
                    });
                });
            </script>
        </head>
        <body class='default'>
            <div id="jqxgrid"></div>
        </body>    
    </html>

    <?php
    
    namespace PostgreSQLTutorial;
    /**
     * Create table in PostgreSQL from PHP demo
     */
    class CustomerDB {
    
        private $pdo;
    
        /**
         * init the object with a \PDO object
         * @param type $pdo
         */
        public function __construct($pdo) {
            $this->pdo = $pdo;
        }
        
        /**
         * Return customer rows during pagnation
         * @return array
         */
        public function findCustomer($pagenum, $pagesize) {
            $starts = $pagenum * $pagesize;
            $stmt = $this->pdo->prepare('SELECT "CompanyName", "ContactName", "ContactTitle", "City", "Country" FROM customers LIMIT :starts OFFSET :pagesize');
            $stmt->bindValue(':starts', $starts);
            $stmt->bindValue(':pagesize', $pagesize);
            $stmt->execute();
    
            $customers = [];
            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                $customers[] = [
                    'CompanyName' => $row['CompanyName'],
                    'ContactName' => $row['ContactName'],
                    'ContactTitle' => $row['ContactTitle'],
                    'City' => $row['City'],
                    'Country' => $row['Country']
                ];
            }
            return $customers;
        }
    }
    • This topic was modified 3 years, 2 months ago by  robin.

    robin
    Participant

    Does anyone have any suggestions please?


    Yavor Dashev
    Participant

    Hi robin,

    Would you like to share whats the data returned from the server in order to help you solve your problem and to check if it comes from the ‘grid’ component more particular ‘beforeprocessing’ and ‘rendergridrows’.

    Please, do not hesitate to contact us if you have any additional questions.Best regards,
    Yavor Dashev
    jQWidgets Team
    https://www.jqwidgets.com/


    LastmansBadBoy
    Participant

    No Idea


    timmeehle
    Participant

    I am having a similar issue. I’m new with jqxGrip, and am adapting the demos in the download to fit my needs. I am attempting to load records from a mySQL database which is returning JSON data with both the ROWS and TOTALROWS arrays (as JSON):

    [{“TotalRows”:10,”Rows”:[{“id”:”435″,”unityID”:”rCFfbiNcqQhp8SG3″,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”174″,”unityID”:”xTbCM6zZ25et1l7v”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”315″,”unityID”:”z7AwmbZMUJ36lDC8″,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”703″,”unityID”:”MD4LEXmuJxlW1fTC”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”672″,”unityID”:”fBR9Wx7GlM6SQiTp”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”529″,”unityID”:”7slfbgeKpA6VmiN9″,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”854″,”unityID”:”ASngFwK8M9cxtPeB”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”584″,”unityID”:”CfB9hRFoMDpeWuOm”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”212″,”unityID”:”DH9a6qklbwyRAYjx”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null},{“id”:”427″,”unityID”:”9G4R306DsMyvgBjS”,”displayName”:null,”dateUpdated”:”2021-02-10 14:26:47″,”City”:null,”Country”:null}]}]

    I can see the grid displaying, but no data is loaded in the grid. Here is my code for the grid:

    $(document).ready(function () {
    // prepare the data
    var source =
    {
    datatype: “json”,
    datafields: [
    { name: ‘id’, type:’int’},
    { name: ‘unityID’,type: ‘string’},
    { name: ‘displayName’,type: ‘string’},
    { name: ‘dateUpdated’,type: ‘string’},
    { name: ‘City’,type: ‘string’},
    { name: ‘Country’,type: ‘string’}
    ],
    url: ‘serverpaging_data.php’,
    root: ‘Rows’,
    cache: false,
    beforeprocessing: function(data)
    {
    source.totalrecords = data[0].TotalRows;
    }
    };

    var dataadapter = new $.jqx.dataAdapter(source, {
    formatData: function (data) {
    return JSON.stringify(data);
    },
    });

    // initialize jqxGrid
    $(“#jqxgrid”).jqxGrid(
    {
    width: 800,
    source: dataadapter,
    autoheight: true,
    pageable: true,
    columns: [
    { text: ‘ID’, datafield: ‘id’, width: 250 },
    { text: ‘Unity ID’, datafield: ‘unityID’, width: 200 },
    { text: ‘Display Name’, datafield: ‘displayName’, width: 200 },
    { text: ‘Last Updated’, datafield: ‘dateUpdated’, width: 180 },
    { text: ‘City’, datafield: ‘City’, width: 100 },
    { text: ‘Country’, datafield: ‘Country’, width: 100 }
    ]
    });
    });
    </script>
    </head>
    <body class=’default’>
    <div id=”jqxgrid”></div>
    </body>
    </html>

    (this is the demo ‘demos/php/serverpaging.php’), and I’ve modified the serverpaging_data.php to load my data (sample data above).

    I’m not quite clear where I should be checking for a return from the php page. I’m also seeing a weird error 404 with jquery and jqx.light.css, although both files are in their expected locations.


    Yavor Dashev
    Participant

    Hi timmeehle,

    I have tested the grid with the data you provided and the grid works as intended.
    I would suggest you to to check your php(if its connected to the database and etc) configuration because the problem is not coming from the grid and also have a look at this demo :
    https://www.jqwidgets.com/jquery-widgets-demo/demos/php/index.htm#demos/php/grid.htm .

    Please, do not hesitate to contact us if you have any additional questions.Best regards,
    Yavor Dashev
    jQWidgets Team
    https://www.jqwidgets.com/


    robin
    Participant

    The pagenum and pagesize still isn’t being sent. Here is the link that you can inspect the behavior. I am using localdata instead of url: https://cosmos.physast.uga.edu/pdo_test/postgresqlphpconnect/index.php

    • This reply was modified 3 years, 1 month ago by  robin.

    Yavor Dashev
    Participant

    Hi robin,

    From the link you shared the virtual mode of the grid was indeed not working properly(loading the same rows in each page) and the problem was coming from the function in the renderingrows.
    When using virtual mode it is necessary to have the renderingrows property set properly so that the virtual mode displays the right rows for each page.
    I have also prepared a code snippet for you to see how to set up the renderingrows property correctly.

    
     <script type="text/javascript">
             $(document).ready(function () {
                 var data ='[{"SID":2315189034144182400,"ra":"5.04669000845","de":"-34.27166836323","Tau":"0.002267810880946083","tstar":"4330"},{"SID":2729864278282260992,"ra":"338.94622128939","de":"11.70427469845","Tau":"0.04774015411894417","tstar":"3070"},{"SID":2563976526593374592,"ra":"17.43582240456","de":"4.46453118755","Tau":"0.029339244758389747","tstar":"3140"},{"SID":3290126127637212672,"ra":"76.63243572764","de":"8.4257027557","Tau":"0.07641494375537786","tstar":"2780"},{"SID":5401822669314874240,"ra":"165.5406281171","de":"-34.50994086261","Tau":"0.05345680248166983","tstar":"2360"},{"SID":5467714064704570112,"ra":"157.19050989322","de":"-28.51047788348","Tau":"0.017894844664470933","tstar":"2860"},{"SID":3478940625208241920,"ra":"174.89050470955","de":"-30.666879521","Tau":null,"tstar":"3040"},{"SID":3466327989885650176,"ra":"181.7951296958","de":"-32.5150287772","Tau":"0.0449809514","tstar":"3020"},{"SID":6227865036339982336,"ra":"225.94060358218","de":"-24.0182111581","Tau":"0.006210822659891296","tstar":"4260"},{"SID":6044879015558339072,"ra":"249.20585416166","de":"-26.50839452808","Tau":"0.038542679441451004","tstar":"2870"}]';
                 var data = JSON.parse(data);
                 var source =
                 {
                     datatype: "json",
                     datafields: [
                         { name: 'SID' },
                         { name: 'ra' },
                         { name: 'de' },
                         { name: 'Tau' },
                         { name: 'tstar' }
                     ],
                     cache: false,
                     localdata: data,
                     totalrecords: data.length
                   
                 };
                 var rendergridrows = function(obj) {
                        var rowData= {};
                        for(var i = obj.startindex ; i < obj.endindex ; i ++){
                            var row = {}
                            row['SID'] = data[i].SID
                            row['ra'] =  data[i].ra
                            row['de'] =  data[i].de
                            row['Tau'] =  data[i].Tau
                            row['tstar'] =  data[i].tstar
                            rowData[i]= row;
                        }
                       
                        return rowData;     
                     }
                 var dataadapter = new $.jqx.dataAdapter(source);
                 $("#jqxgrid").jqxGrid(
                 {
                     width: 600,
                     source: dataadapter,
                     autoheight: true,
                     pageable: true,
                     virtualmode: true,
                     pagesize: 5 ,
                     rendergridrows: rendergridrows,
                     columns: [
                       { text: 'SID', datafield: 'SID', width: 250 },
                       { text: 'ra', datafield: 'ra', width: 200 },
                       { text: 'de', datafield: 'de', width: 200 },
                       { text: 'Tau', datafield: 'Tau', width: 100 },
                       { text: 'tstar', datafield: 'tstar' }
                   ]
                 });
             });
         </script>
    
    </head>
    <body class='default'>
            <div id="jqxgrid"></div>
    </body>
    

    Also I would advice you to have a look at this demo:
    https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm#demos/jqxgrid/virtualdata.htm

    Please, do not hesitate to contact us if you have any additional questions.Best regards,
    Yavor Dashev
    jQWidgets Team
    https://www.jqwidgets.com/

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.