Joomla 4 jqxGrid mysql database crud

[AppBuilderParticipant]

Hello,

I have build a simple Joomla 4 module which should output a CRUD grid (default.php) and show values (data.php) from mysql database .

I have tried to follow this tutorial:

Build CRUD Web App with jqxGrid using PHP and MySQL

I think you missed “s” with “firtnameindex” at some lines.

Well, I can see an empty grid with three buttons and I have no clue why I can’t fetch any data.
My database connection should be fine. I can fetch and echo user_id of logged in user.

I have tried this a year ago and failed to get it working.
Maybe someone is willing to help with this first step.

All I need is an editable grid with database source.
I am a noob when it comes to JavaScript, but I have started to learn some.

default.php

<script type="text/javascript">
   $(document).ready(function () {
       // prepare the data
       var data = {};
       var theme = 'summer';
       var firstname = ["Nancy", "Andrew", "Janet", "Margaret", "Steven", "Michael", "Robert", "Laura", "Anne"];
       var lastname = ["Davolio", "Fuller", "Leverling", "Peacock", "Buchanan", "Suyama", "King", "Callahan", "Dodsworth"];
       var generaterow = function (id) {
           var row = {};
           var firstnameindex = Math.floor(Math.random() * firstname.length);
           var lastnameindex = Math.floor(Math.random() * lastname.length);
           var k = firstnameindex;
           row["id"] = id;
           row["firstname"] = firstname[firstnameindex];
           row["lastname"] = lastname[lastnameindex];
           return row;
       }
       var source =
       {
           datatype: "json",
           cache: false,
           datafields: [
						{ name: 'id' },
						{ name: 'firstname' },
						{ name: 'lastname' }
           ],
           id: 'id',
           url: 'data.php',
           addrow: function (rowid, rowdata, position, commit) {
               // synchronize with the server - send insert command
               var data = "insert=true&" + $.param(rowdata);
               $.ajax({
                   dataType: 'json',
                   url: 'data.php',
                   data: data,
                   cache: false,
                   success: function (data, status, xhr) {
                       // insert command is executed.
                       commit(true);
                   },
                   error: function (jqXHR, textStatus, errorThrown) {
                       commit(false);
                   }
               });
           },
           deleterow: function (rowid, commit) {
               // synchronize with the server - send delete command
               var data = "delete=true&" + $.param({ id: rowid });
               $.ajax({
                   dataType: 'json',
                   url: 'data.php',
                   cache: false,
                   data: data,
                   success: function (data, status, xhr) {
                       // delete command is executed.
                       commit(true);
                   },
                   error: function (jqXHR, textStatus, errorThrown) {
                       commit(false);
                   }
               });
           },
           updaterow: function (rowid, rowdata, commit) {
               // synchronize with the server - send update command
               var data = "update=true&" + $.param(rowdata);
               $.ajax({
                   dataType: 'json',
                   url: 'data.php',
                   cache: false,
                   data: data,
                   success: function (data, status, xhr) {
                       // update command is executed.
                       commit(true);
                   },
                   error: function (jqXHR, textStatus, errorThrown) {
                       commit(false);
                   }
               });
           }
       };
       var dataAdapter = new $.jqx.dataAdapter(source);
       // initialize jqxGrid
       $("#jqxgrid").jqxGrid(
       {
           width: 500,
           height: 350,
           source: dataAdapter,
           theme: theme,
           columns: [
                 { text: 'id', datafield: 'id', width: 100 },
                 { text: 'firstname', datafield: 'firstname', width: 100 },
                 { text: 'lastname', datafield: 'lastname', width: 100 }
             ]
       });
       $("#addrowbutton").jqxButton({ theme: theme });
       $("#deleterowbutton").jqxButton({ theme: theme });
       $("#updaterowbutton").jqxButton({ theme: theme });
       // update row.
       $("#updaterowbutton").bind('click', function () {
           var datarow = generaterow();
           var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
           var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
           if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
               var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
               $("#jqxgrid").jqxGrid('updaterow', id, datarow);
           }
       });
       // create new row.
       $("#addrowbutton").bind('click', function () {
           var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
           var datarow = generaterow(rowscount + 1);
           $("#jqxgrid").jqxGrid('addrow', null, datarow);
       });
       // delete row.
       $("#deleterowbutton").bind('click', function () {
           var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
           var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
           if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
               var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
               $("#jqxgrid").jqxGrid('deleterow', id);
           }
       });
   });
</script>
<div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;">
   <div style="float: left;" id="jqxgrid">
   </div>
   <div style="margin-left: 30px; float: left;">
      <div>
         <input id="addrowbutton" type="button" value="Add New Row" />
      </div>
      <div style="margin-top: 10px;">
         <input id="deleterowbutton" type="button" value="Delete Selected Row" />
      </div>
      <div style="margin-top: 10px;">
         <input id="updaterowbutton" type="button" value="Update Selected Row" />
      </div>
   </div>
</div>

data.php

<?php
/**
 * @package    [PACKAGE_NAME]
 *
 * @author     [AUTHOR] <[AUTHOR_EMAIL]>
 * @copyright  [COPYRIGHT]
 * @license    GNU General Public License version 2 or later; see LICENSE.txt
 * @link       [AUTHOR_URL]
 */

/* Connect */
$db = JFactory::getDbo();
$user = JFactory::getUser();
$user_id = $user->get('id');
if(empty($user_id)) {$user_id = 0;}

echo "This is the user id: " . $user_id;

/* Connect to the database */
// $mysqli = new mysqli($hostname, $username, $password, $database);
$mysqli = $db;

/* 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 id, firstname, lastname FROM xdata_test";
if (isset($_GET['insert']))
	{
	// INSERT COMMAND
	$query = "INSERT INTO <code>xdata_test</code>(<code>firstname</code>, <code>lastname</code>) VALUES (?,?)";
	$result = $mysqli->prepare($query);
	$result->bind_param('ss', $_GET['firstname'], $_GET['lastname']);
	$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
	// printf ("New Record has id %d.\n", $mysqli->insert_id);
	echo $res;
	}
  else if (isset($_GET['update']))
	{
	// UPDATE COMMAND
	$query = "UPDATE <code>xdata_test</code> SET <code>firstname</code>=?, <code>lastname</code>=? WHERE <code>id</code>=?";
	$result = $mysqli->prepare($query);
	$result->bind_param('ssi', $_GET['firstname'], $_GET['lastname'], $_GET['id']);
	$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
	// printf ("Updated Record has id %d.\n", $_GET['id']);
	echo $res;
	}
  else if (isset($_GET['delete']))
	{
	// DELETE COMMAND
	$query = "DELETE FROM xdata_test WHERE id=?";
	$result = $mysqli->prepare($query);
	$result->bind_param('i', $_GET['id']);
	$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
	// printf ("Deleted Record has id %d.\n", $_GET['id']);
	echo $res;
	}
  else
	{
	// SELECT COMMAND
	$result = $mysqli->prepare($query);
	$result->execute();
	/* bind result variables */
	$result->bind_result($id, $firstname, $lastname);
	/* fetch values */
	while ($result->fetch())
		{
		$employees[] = array(
			'id' => $id,
			'firstname' => $firstname,
			'lastname' => $lastname
		);
		}
	echo json_encode($employees);
	}
$result->close();
$mysqli->close();
/* close connection */
?>

[AppBuilderParticipant]

Should I use the absolute path here, since the data.php file is located in modules folder and not root?

url: 'data.php',

[ivanpeevskiParticipant]

Hi AppBuilder,

default.php seems correct to me and the problem comes from data.php
The issue comes from echo $user_id. JavaScript expects to receive a valid JSON object($employees in this case), but $user_id disrupts the object and makes it invalid. By removing the line, the app should work.

Using the Network tab (F12 -> Network) you can check if the page can find data.php and what is the response from it.
If the two pages are in different directories, then yes, you need to change it to the correct one.
If it still doesn’t work, try to open the file and see what is the content of $employees, since it is difficult to say without seeing the DB.

I hope this helps!
If you have any other questions, please do not hesitate to contact us again
Best regards,
Ivan Peevski
jQWidgets Team
https://www.jqwidgets.com

• This reply was modified 1 year, 11 months ago by  ivanpeevski.
• This reply was modified 1 year, 11 months ago by  ivanpeevski.

[AppBuilderParticipant]

Thank you.

I think I need a different Code in order to connect to Joomla 4 database.
This “JFactory::getDbo();” seems to be deprecated.
Currently testing.

[Author]

Posts