Nested tables with data from database - Angular, Vue, React, Web Components, Javascript, HTML5 Widgets

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

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

Author

Nested tables with data from database Posts

August 31, 2020 at 8:18 pm Nested tables with data from database #112887

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>

September 2, 2020 at 11:17 am 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

September 2, 2020 at 6:37 pm 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 */
?>

September 3, 2020 at 8:50 am 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

Author

Posts

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

You must be logged in to reply to this topic.