jQuery UI Widgets Forums Getting Started Joomla 4 jqxGrid mysql database crud

This topic contains 3 replies, has 2 voices, and was last updated by  AppBuilder 11 months, 1 week ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
  • Joomla 4 jqxGrid mysql database crud #121252

    AppBuilder
    Participant

    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 */
    ?>
    
    Joomla 4 jqxGrid mysql database crud #121253

    AppBuilder
    Participant

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

    
    url: 'data.php',
    
    Joomla 4 jqxGrid mysql database crud #121254

    ivanpeevski
    Participant

    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 11 months, 1 week ago by  ivanpeevski.
    • This reply was modified 11 months, 1 week ago by  ivanpeevski.
    Joomla 4 jqxGrid mysql database crud #121257

    AppBuilder
    Participant

    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.

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

You must be logged in to reply to this topic.