AppBuilder's Replies

Forum Replies Created

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

Author

Posts

December 22, 2021 at 1:00 pm in reply to: Joomla 4 jqxGrid mysql database crud 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.

December 22, 2021 at 10:38 am in reply to: Joomla 4 jqxGrid mysql database crud 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',

March 1, 2021 at 2:49 pm in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114744

AppBuilder

Participant

No error message.

Have not debugged the jqxDataAdapter., because no clue how to.

I am trying to follow your CRUD example with MySQL, but I am facing difficulties transporting the data and debugging as it seems.

My PHP part looks like it is exporting the correct JSON Format. Had to struggle with this one as well, since your examples are all over the place. The JSON echo is valid.

I would basically like to get a piece of code to show the data from mysql and test how easy I can make adjustments to the grid layout/options and if CRUD really works right out of the box. The only thing I would adjust is the SELECT query and fields, just to see if it is working.

I am trying to rewrite your example to at least show the data, but it won’t work.

I am running Joomla 3.x with a Linux Server and I am injecting the SOURCE code directly inside Joomla pages. I am actually using a pagebuilder with Joomla, but I can make use of Regularlabs Sourcerer to inject the code. Therefore I can run HTML/JS/PHP anywhere.

February 28, 2021 at 12:38 am in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114724

AppBuilder

Participant

I have missed some lines and had to rewrite the sourcerer code.

current version of my jqwidget_source.php file, which actually does show the grid, but won’t show the json data yet:


{source}
<html lang="de">
<head>
	<link rel="stylesheet" href="/media/system/css/jqx.base.css" type="text/css" />
	<script type="text/javascript" src="/media/system/js/jqwidget-scripts/jquery-1.11.1.min.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxcore.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdata.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxbuttons.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxscrollbar.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxmenu.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxlistbox.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdropdownlist.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.selection.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.columnsresize.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.filter.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.sort.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.pager.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.grouping.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdata.export.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.export.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxexport.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxcheckbox.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxlistbox.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdropdownlist.js"></script>
	<script type="text/javascript">
		$(document).ready(function() {
		  // prepare the data
		  var data = {};
		  var theme = 'classic';
		  var source = {
		    datatype: "json",
		    cache: false,
		    datafields: [{
		        name: 'id'
		      },
		      {
		        name: 'title'
		      },
		      {
		        name: 'process'
		      }
		    ],
		    id: 'id',
		    url: 'app/jqwidget_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: 'app/jqwidget_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: 'app/jqwidget_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: 'app/jqwidget_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: 1366,
		    source: dataAdapter,
		    theme: theme,
		    columns: [{
		        text: 'id',
		        datafield: 'id',
		        width: 100
		      },
		      {
		        text: 'title',
		        datafield: 'title',
		        width: 100
		      },
		      {
		        text: 'process',
		        datafield: 'process',
		        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>
</head>
<body class='default'>
    <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>
</body>
</html>
{/source}

February 28, 2021 at 12:21 am in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114722

AppBuilder

Participant

EDIT 2 => Before initialize jqxGrid I forgot to add:

var dataAdapter = new $.jqx.dataAdapter(source);

Also added at the beginning:

var theme = ‘classic’;

Still no grid, only buttons; and buttons do not work.

This reply was modified 2 years, 9 months ago by AppBuilder.

February 27, 2021 at 8:12 pm in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114721

AppBuilder

Participant

EDIT => url: ‘data.php’ should be url: ‘app/jqwidget_data.php’

February 27, 2021 at 5:19 pm in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114718

AppBuilder

Participant

I have managed to connect to mysql and get json as a response, following some tutorials.
JSON output with 311 rows total shows:


[{"id":1,"title":"Reinweiß glänzend","process":1},
{"id":2,"title":"alpinweiß","process":1}...

Attached the code for:

1. jqwidget_data.php (called to generate json – working)
2. jqwidget_source.php (complete code is being injected inside Joomla page using Regularlabs sourcerer)

The Grid won’t show. Only the buttons are rendered.
I was able to build a simple grid without CRUD operation, which worked.

CRUD example:
https://www.jqwidgets.com/jquery-widgets-documentation/documentation/phpintegration/php-server-side-grid-crud.htm?search=

I don’t see any errors with Browser.
Does the primary_key “id” maybe cause conflicts?

jqwidget_data.php


<?php

// json
header("Content-Type: application/json");

// connect
include('connect.php');
$mysqli = new mysqli($mysqli_db_host, $mysqli_db_user, $mysqli_db_password, $mysqli_db_name);

if($mysqli)  
{  
  $query = "SELECT id, title, process FROM app_1_upload_1";  
  $result = mysqli_query($mysqli, $query); 
	
	// Set Charset to UTF8
	mysqli_set_charset($mysqli, "utf8");

	// get data and store in a json array
	$query = "SELECT id, title, process FROM app_1_upload_1";
	if (isset($_GET['insert']))
		{
		// INSERT COMMAND
		$query = "INSERT INTO <code>app_1_upload_1</code>(<code>title</code>, <code>process</code>) VALUES (?,?)";
		$result = $mysqli->prepare($query);
		$result->bind_param('si', $_GET['title'], $_GET['process']);
		$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>app_1_upload_1</code> SET <code>title</code>=?, <code>process</code>=? WHERE <code>id</code>=?";
		$result = $mysqli->prepare($query);
		$result->bind_param('sii', $_GET['title'], $_GET['process'], $_GET['id']);
		$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
		// printf ("Updated Record has id %d.\n", $_GET['EmployeeID']);
		echo $res;
		}
	  else if (isset($_GET['delete']))
		{
		// DELETE COMMAND
		$query = "DELETE FROM <code>app_1_upload_1</code> 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['EmployeeID']);
		echo $res;
		}
	  else
		{

		// SELECT COMMAND
		$result = $mysqli->prepare($query);
		$result->execute();
		/* bind result variables */
		$result->bind_result($id, $title, $process);
		/* fetch values */
		while ($result->fetch())
		{
			$output[] = array(
				'id' => $id,
				'title' => $title,
				'process' => $process
			);
		}
		print(json_encode($output,JSON_UNESCAPED_UNICODE));
	}
	
	$result->close();
	$mysqli->close();
	
}  

?>

jqwidget_source.php


{source}
<html lang="de">
<head>
	<link rel="stylesheet" href="/media/system/css/jqx.base.css" type="text/css" />
	<script type="text/javascript" src="/media/system/js/jqwidget-scripts/jquery-1.11.1.min.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxcore.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdata.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxbuttons.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxscrollbar.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxmenu.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxlistbox.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdropdownlist.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.selection.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.columnsresize.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.filter.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.sort.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.pager.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.grouping.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdata.export.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxgrid.export.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxexport.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxcheckbox.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxlistbox.js"></script>
	<script type="text/javascript" src="/media/system/js/jqxdropdownlist.js"></script>
	<script type="text/javascript">
		var source = {
		  datatype: "json",
		  cache: false,
		  datafields: [{
		      name: 'id'
		    },
		    {
		      name: 'title'
		    },
		    {
		      name: 'process'
		    }
		  ],
		  id: 'id',
		  url: 'app/jqwidget_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: 'app/jqwidget_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: 'app/jqwidget_data.php',
		      cache: false,
		      data: data,
		      success: function(data, status, xhr) {
		        // update command is executed.
		        commit(true);
		      },
		      error: function(jqXHR, textStatus, errorThrown) {
		        commit(false);
		      }
		    });
		  }
		};
	</script>
	<script type="text/javascript">
		// initialize jqxGrid
		$("#jqxgrid").jqxGrid({
		  width: 500,
		  height: 350,
		  source: dataAdapter,
		  theme: theme,
		  columns: [{
		      text: 'id',
		      datafield: 'id',
		      width: 100
		    },
		    {
		      text: 'title',
		      datafield: 'title',
		      width: 100
		    },
		    {
		      text: 'process',
		      datafield: 'process',
		      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>
</head>
<body class='default'>
	<div id='jqxWidget'>
		<div id="jqxgrid"></div>
			<div style="margin-left: 30px; float: left;">
				<div>
					<input id="addrowbutton" type="button" value="Neu" />
				</div>
				<div style="margin-top: 10px;">
					<input id="deleterowbutton" type="button" value="Löschen" />
				</div>
				<div style="margin-top: 10px;">
					<input id="updaterowbutton" type="button" value="Aktualisieren" />
				</div>
			</div>
	</div>
</body>
</html>
{/source}

This reply was modified 2 years, 9 months ago by AppBuilder.
This reply was modified 2 years, 9 months ago by AppBuilder.

February 26, 2021 at 10:44 pm in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114713

AppBuilder

Participant

So I am trying another example of yours and i get the following json echo:

[{“TotalRows”:311,”Rows”:null}]

There are 311 records with the table.
But why are rows returned as NULL?

The code actually looks simple.
I can’t spot a mistake.


<?php

// connect to the database
include('connect.php');
$mysqli = new mysqli($mysqli_db_host, $mysqli_db_user, $mysqli_db_password, $mysqli_db_name);

// check connection
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

// Initialize pagenum and pagesize
$pagenum = $_POST['pagenum'];
$pagesize = $_POST['pagesize'];		
$start = $pagenum * $pagesize;
if (isset($_POST['sortdatafield']))
{
	$sortfield = $_POST['sortdatafield'];
	$sortorder = $_POST['sortorder'];
	
	if ($sortorder != '')
	{			
		if ($sortorder == "desc")
		{
			$query = "SELECT id, title, process FROM app_1_upload_1 ORDER BY" . " " . $sortfield . " DESC LIMIT ?, ?";
		}
		else if ($sortorder == "asc")
		{
			$query = "SELECT id, title, process FROM app_1_upload_1 ORDER BY" . " " . $sortfield . " ASC LIMIT ?, ?";
		}			
		$result = $mysqli->prepare($query);
		$result->bind_param('ii', $start, $pagesize);
	}
	else
	{
		$result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS id, title, process FROM app_1_upload_1 LIMIT ?, ?");
		$result->bind_param('ii', $start, $pagesize);		
	}
}
else
{
	$result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS id, title, process FROM app_1_upload_1 LIMIT ?, ?");
	$result->bind_param('ii', $start, $pagesize);		
}

 /* execute query */
$result->execute();
/* bind result variables */
$result->bind_result($id, $title, $process);
/* fetch values */
while ($result->fetch()) {
	$output[] = array(
	'id' => $id,
	'title' => $title,
	'process' => $process
	);
}	

// get the total rows.
$result = $mysqli->prepare("SELECT FOUND_ROWS()");
$result->execute();
$result->bind_result($total_rows);
$result->fetch();
$data[] = array(
	'TotalRows' => $total_rows,
	'Rows' => $output
);
echo json_encode($data);
/* close statement */
$result->close();
/* close connection */
$mysqli->close();

?>

February 26, 2021 at 10:24 pm in reply to: qxgrid CRUD example, fetch mysqli data returns null rows qxgrid CRUD example, fetch mysqli data returns null rows #114711

AppBuilder

Participant

Oh, ok…those are the field types to bind: ‘sssssss’ and ‘ssssssi’ = string/int

February 26, 2021 at 6:13 pm in reply to: Export Excel and CSV Export Excel and CSV #114706

AppBuilder

Participant

Thank you

February 26, 2021 at 3:43 pm in reply to: Responsive Grid 100% Responsive Grid 100% #114705

AppBuilder

Participant

amazing. it pretty much worked right out of the box.

thank you.

February 26, 2021 at 2:44 pm in reply to: number of lines dependend on source and json format number of lines dependend on source and json format #114702

AppBuilder

Participant

Thank you for your tutorial.
As I can see, support is really good.

//===== OFFTOPIC

From what I have seen, I might becomde a pro customer.
Need to test first and check how the learning curve works out for me (never been into JS, but I can read your code and understand the basics).

So far, it looks well thought out.

I am currently facing a little firefox issue with this page:
https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/

When I click a link, I can see the sample I clicked.
When I try to click on another link, the URL changes, but the new link does not load.
I have to refresh the page and then I can go on to view the next link.
I have checked my browser extension, but everything looks ok.

Anyhow. Sorry for this long off topic.

//===== OFFTOPIC

My main problem with the code above is to get the data in a correct way.

You are using:
var sampleData =

I would like to fetch the data from mysql database (Joomla) with given table structure:
SELECT name, rank, value FROM app_1_analysis_1 WHERE name IN (PersonA,PersonB)

name | rank | value
Person A | 1 | 23
Person A | 2 | 44
Person A | 3 | 53
Person B | 1 | 12
Person B | 2 | 16
Person B | 3 | 19
Person C | 1 | 32
Person C | 2 | 66
Person C | 3 | 79

Database connection and fetching already works for me with jqxgrid.
But how can I create asuitable JSON with my structure.

Or do I need to alter my table in order to fetch data as json in a more “native”/workable way?

February 26, 2021 at 12:37 pm in reply to: Export Excel and CSV Export Excel and CSV #114698

AppBuilder

Participant

Firefox Error when I try to export:

unreachable code after return statement
jqxdata.export.js:8:67444

Author

Posts

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