jQuery UI Widgets Forums Grid Nested tables with data from database

This topic contains 3 replies, has 2 voices, and was last updated by  Hristo 3 years, 8 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
  • Nested tables with data from database #112887

    paulcobben
    Participant

    Hi,

    I had the Master Details working with CSV files according the example. https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm#demos/jqxgrid/masterdetails.htm

    Now I have moved the data to a database and changed the datasources to the database tables.

    When I click a row in the first column, it does not show the same info in the second table as it worked in the CSV example.

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <title id="Description">DUO Dashboard</title>
        <link rel="stylesheet" href="jqwidgets/styles/jqx.base.css" type="text/css" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1 maximum-scale=1 minimum-scale=1" />
        <script type="text/javascript" src="scripts/jquery-1.11.1.min.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxdata.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxdata.export.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxpanel.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.sort.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.filter.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.edit.js"></script>  
        <script type="text/javascript" src="jqwidgets/jqxgrid.export.js"></script>  
        <script type="text/javascript" src="jqwidgets/jqxgrid.selection.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxcheckbox.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.columnsresize.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
        <script type="text/javascript" src="scripts/demos.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var source =
                {
                    datatype: "json",
                    cache: false,
                    datafields: [
    					 { name: 'samaccount', type: 'string' },
    					 { name: 'gebruikersnaam', type: 'string' },
    					 { name: 'percgereeduser', type: 'number' },
    					 { name: 'gemigreerd' , type: 'bool' },
    					 { name: 'notities', type: 'string' }
                    ],
                    id: 'samaccount',
                    url: 'getdata1.php',
                };
    
                var dataAdapter = new $.jqx.dataAdapter(source);
                // initialize jqxGrid
                $("#userGrid").jqxGrid(
                {
                    width: 1060,
                    height: 400,
                    source: dataAdapter,
                    // theme: 'material',
                    columnsresize: true,
                    showfilterrow: true,
                    sortable: true,
                    filterable: true,
                    editable: true,
                    keyboardnavigation: true,
                    columns: [
                          { text: 'samaccount', datafield: 'samaccount', editable: false, hidden: true },
                          { text: 'Gebruikersnaam', datafield: 'gebruikersnaam', editable: false, width: 275 },
                          { text: 'User % gereed', datafield: 'percgereeduser', editable: false, width: 125 },
                          { text: 'Gemigreerd', datafield: 'gemigreerd', columntype: 'checkbox', editable: true, width: 100 },
                          { text: 'Notities', datafield: 'notities', editable: true }
                      ]
                });
    
                // events
    
                $('#userGrid').on('cellclick', function (event) {
                    $("#log").html("A cell has been clicked:" + event.args.rowindex + ":" + event.args.datafield);
                });
    
                // Packages Grid
                // prepare the data
                var	dataFields = [
                            { name: 'samaccount' },
                            { name: 'gebruikersnaam' },
    			{ name: 'applicatienaam' },
                            { name: 'percgereedpackage' },
                            { name: 'omschrijving' },
                            { name: 'afdeling' },
                            { name: 'actief' },
                            { name: 'typewerkplek' }
                        ];
    			var source =
                {
                    datatype: "json",
                    cache: false,
                    datafields: dataFields,
                    id: 'samaccount',
                    url: 'getdata2.php',
                };
                var dataAdapter = new $.jqx.dataAdapter(source);
                dataAdapter.dataBind();
                $("#userGrid").on('rowselect', function (event) {
                    var samaccount = event.args.row.samaccount;
                    var records = new Array();
                    var length = dataAdapter.records.length;
                    for (var i = 0; i < length; i++) {
                        var record = dataAdapter.records[i];
                        if (record.samaccount == samaccount) {
                            records[records.length] = record;
                        }
                    }
                    var dataSource = {
                        datafields: dataFields,
    					localdata: records
                    }
                    var adapter = new $.jqx.dataAdapter(dataSource);
    
                    // update data source.
                    $("#packageGrid").jqxGrid({ source: adapter });
                });
                
                var exportInfo;
                $("#userGrid").on("cellvaluechanged", function (event) {
                    var theKey = $("#userGrid").jqxGrid("getcellvalue", event.args.rowindex, "key", columnDelimiter=';');
                    // doSomething(theKey, event.args.newvalue);
                });
                
                $("#packageGrid").jqxGrid(
                {
                    width: 1060,
                    autoheight: true,
                    keyboardnavigation: false,
                    columnsresize: true,
                    sortable: true,
                    columns: [
                        { text: 'Gebruikersnaam', columntype: 'textbox', datafield: 'gebruikersnaam' },
                        { text: 'Applicatienaam', columntype: 'textbox', datafield: 'applicatienaam' },
                        { text: 'App % gereed', columntype: 'textbox', datafield: 'percgereedpackage' },
                        { text: 'Omschrijving', columntype: 'textbox', datafield: 'omschrijving' },
                        { text: 'SAM Account naam', columntype: 'textbox', datafield: 'samaccount' },
                        { text: 'Afdeling', columntype: 'textbox', datafield: 'afdeling' },
                        { text: 'Actief', columntype: 'textbox', datafield: 'actief' },
                        { text: 'Type werkplek', columntype: 'textbox', datafield: 'typewerkplek' }
                    ]
                });
                $("#userGrid").jqxGrid('selectrow', 0);
            });
            
        </script>
    </head>
    <body class="default">
        <div id="jqxWidget" style="font-size: 13px; font-family: Verdana; float: left;">
            <h3>
                Gebruikers</h3>
            <div id="userGrid">
            </div>
            <h3>
                Details</h3>
            <div id="packageGrid">
            </div>
        </div>
    </body>
    </html>
    • This topic was modified 3 years, 8 months ago by  paulcobben.
    • This topic was modified 3 years, 8 months ago by  paulcobben.
    Nested tables with data from database #112920

    Hristo
    Participant

    Hello paulcobben,

    Could you provide us with more details?
    Also, I would like to ask you is there any error message in the console?

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

    Nested tables with data from database #112935

    paulcobben
    Participant

    Hello Hristo,

    There’s no error message in the console.
    When I select a row in the 1st table, It only shows one line of details in the 2nd table, but must show more rows.

    When I load the above code in Visual Studio Code, I get the following message in the debug console:
    Uncaught TypeError: Cannot read property ‘samaccount’ of undefined userresult.html:97
    No debug adapter, can not send ‘variables’

    Userresult.html is the above code and line 97 is: var samaccount = event.args.row.samaccount;

    Contents of getdata1.php

    <?php
    #Include the connect.php file
    include ('connect.php');
    // Connect to the database
    $mysqli = new mysqli($hostname, $username, $password, $database);
    /* check connection */
    if (mysqli_connect_errno())
    	{
    	printf("Connect failed: %s\n", mysqli_connect_error());
    	exit();
    	}
    // get data and store in a json array
    $query = "SELECT samaccount, gebruikersnaam, percgereeduser, gemigreerd, notities FROM sfuser";
     	{
     	// SELECT COMMAND
     	$result = $mysqli->prepare($query);
     	$result->execute();
     	/* bind result variables */
     	$result->bind_result($samaccount, $gebruikersnaam, $percgereeduser, $gemigreerd, $notities);
     	/* fetch values */
     	while ($result->fetch())
     		{
     		$sfuser[] = array(
     			'samaccount' => $samaccount,
     			'gebruikersnaam' => $gebruikersnaam,
     			'percgereeduser' => $percgereeduser,
     			'gemigreerd' => $gemigreerd,
     			'notities' => $notities
     		);
     		}
     	echo json_encode($sfuser);
     	}
    $result->close();
    $mysqli->close();
    /* close connection */
    ?>

    Contents of getdata2.php

    <?php
    #Include the connect.php file
    include ('connect.php');
    // Connect to the database
    $mysqli = new mysqli($hostname, $username, $password, $database);
    /* check connection */
    if (mysqli_connect_errno())
    	{
    	printf("Connect failed: %s\n", mysqli_connect_error());
    	exit();
    	}
    // get data and store in a json array
    $query = "SELECT samaccount, gebruikersnaam, applicatienaam, percgereedpackage, omschrijving, afdeling, actief, typewerkplek FROM sfpackage";
     	{
     	// SELECT COMMAND
     	$result = $mysqli->prepare($query);
     	$result->execute();
     	/* bind result variables */
     	$result->bind_result($samaccount, $gebruikersnaam, $applicatienaam, $percgereedpackage, $omschrijving, $afdeling, $actief, $typewerkplek);
     	/* fetch values */
     	while ($result->fetch())
     		{
     		$sfpackage[] = array(
    			'samaccount' => $samaccount,
    			'gebruikersnaam' => $gebruikersnaam,
     			'applicatienaam' => $applicatienaam,
    			'percgereedpackage' => $percgereedpackage,
    			'omschrijving' => $omschrijving,
    			'afdeling' => $afdeling,
    			'actief' => $actief,
    			'typewerkplek' => $typewerkplek
     		);
     		}
     	echo json_encode($sfpackage);
     	}
    $result->close();
    $mysqli->close();
    /* close connection */
    ?>
    • This reply was modified 3 years, 8 months ago by  paulcobben.
    • This reply was modified 3 years, 8 months ago by  paulcobben.
    Nested tables with data from database #112951

    Hristo
    Participant

    Hello paulcobben,

    About how many rows to show in the table it depends on the source or if you set some restrictions there.
    It seems the issue becomes from the database because with the local data it works fine (also the rowselect event).
    I would like to suggest you try to use the hardcoded data from the database.
    In that way, you will check it (because you already mention that it works on the Client-Side with localdata).
    After that, you could check one by one of the fields (especially the “samaccount” field in the database).
    One more thing, you could wrap the whole code in the if” statement for the mentioned event:
    if (event.args.row) { /* Add your logic here */ }

    Also, I saw that you use the getcellvalue method with the key” value for the datafield but there is no such field in your example.
    I only notice it because it could cause any issue.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

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

You must be logged in to reply to this topic.