ParamQuery
General Category => Help for ParamQuery Grid (free version) => Topic started by: Dan95 on November 09, 2015, 07:46:39 pm
-
Hey I'm new here and I got 1 Error in my Code.
My English is not the best but I try to explain my Problem so good as I can.
The Plugin shows me all data from my database. That is not my Problem.
I implemented the remote Filterskript from the Paramquery Homepage.
Without any Problems.
If I try to search with keywords, I got the < Uncaught Error : SyntaxError: Unexpected token < pqgrid.min.js9
<xhr.h.ajax.error @ pqgrid.min.js:9
c @ jquery.min.js:3
p.fireWith @ jquery.min.js:3
k @ jquery.min.js:5
r @ jquery.min.js:5
Does anyone know what the Error means?
Here is some code from me.
table2.php
<!DOCTYPE html>
<html>
<head>
<!--jQuery dependencies-->
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/themes/base/jquery-ui.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>
<!--PQ Grid files-->
<!--PQ Grid Office theme-->
<link rel="stylesheet" href="grid/pqgrid.min.css" />
<script src="grid/pqgrid.min.js"></script>
<link rel="stylesheet" href="grid/themes/office/pqgrid.css" />
<script class="ppjs">
$(function () {
function filterhandler(evt, ui)
{
var $toolbar = $grid.find('.pq-toolbar-search'),
$value = $toolbar.find(".filterValue"),
value = $value.val(),
condition = $toolbar.find(".filterCondition").val(),
dataIndx = $toolbar.find(".filterColumn").val(),
filterObject;
if (dataIndx == "")
{
filterObject = [];
var CM = $grid.pqGrid("getColModel");
for(var i=0, len=CM.length; i<len; i++)
{
var dataIndx = CM[i].dataIndx;
filterObject.push({dataIndx: dataIndx, condition: condition, value: value});
}
}
else
{
filterObject = [{dataIndx: dataIndx, condition: condition, value: value}];
}
$grid.pqGrid("filter",
{
oper: 'replace',
data: filterObject
});
}
var colM = [
/*{ title: "ID", width: 100},*/
{ title: "Datum", width: 100},
{ title: "KW", width:100},
{ title: "Monteure", width: 100},
{ title: "Planung", width: 200},
{ title: "Notizen", width: 400}
];
var dataModel = {
recIndx: "datum",
location : "remote",
sorting: "local",
paging: "local",
dataType: "JSON",
method: "GET",
rPP: 10,
url: "get_data2.php",
getData: function (response) {
return {data: response.data};
}
}
var newObj = {
flexHeight: true,
flexWidth: true,
dataModel: dataModel,
bottomVisible: true,
colModel: colM,
pageModel: {type: "local", rPP: 20, strRpp: "{0}"},
selectionModel: {mode: 'single'},
filterModel: {mode: 'OR', type: "remote"},
toolbar: {
cls: "pq-toolbar-search",
items: [
{type: "<span style='margin:5px;'>Filter</span>"},
{type: 'textbox', attr: 'placeholder="Suche"', cls: "filterValue", listeners: [{'change': filterhandler}]},
{type: 'select', cls: "filterColumn",
listeners: [{'change': filterhandler}],
options: function(ui){
var CM=ui.colModel;
var opts = [{ '': '[Alle Felder]'}];
for(var i=0; i<CM.length; i++){
var column = CM[i];
var obj = {};
obj[column.dataIndx] = column.title;
opts.push(obj);
}
return opts;
}
},
{type : 'select', style: "margin:0px 5px;", cls: "filterCondition",
listeners: [{'change': filterhandler}],
options: [
{ "begin": "Beginnt mit" },
{ "contain": "Beinhaltet" },
{ "end": "Endet mit" },
{ "notcontain": "Beinhaltet nicht!" }
]
}
]
},
editable: false,
scrollModel: {horizontal: false},
title: "Montageplanung",
columnBorders: false
};
var $grid = $("#grid_php").pqGrid(newObj);
});
</script>
</head>
<body>
<div id="grid_php" style="margin:5px auto;"></div>
<!-- Die Ausgabetabelle nur als Grundgerüst definiert, weil die Ausgabe Serverseitig geschieht-->
<!--
<table id="grid_array" border="0" class="display" cellspacing="0" cellpadding="0" width="100%" style="text-align: center;">
<thead>
<tr>
<th>ID</th>
<th>Datum</th>
<th>KW</th>
<th>Monteure</th>
<th>Planung</th>
<th>Notiz</th>
</tr>
</thead>
</table>
-->
</body>
</html>
get_data2.php
<?php
class ColumnHelper
{
public static function isValidColumn($dataIndx)
{
if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
{
return true;
}
else
{
return false;
}
}
}
class FilterHelper
{
public static function deSerializeFilter($pq_filter)
{
$filterObj = json_decode($pq_filter);
$mode = $filterObj->mode;
$filters = $filterObj->data;
$fc = array();
$param= array();
foreach ($filters as $filter)
{
$dataIndx = $filter->dataIndx;
if (ColumnHelper::isValidColumn($dataIndx) == false)
{
throw new Exception("Invalid column name");
}
$text = $filter->value;
$condition = $filter->condition;
if ($condition == "contain")
{
$fc[] = $dataIndx . " like CONCAT('%', ?, '%')";
$param[] = $text;
}
else if ($condition == "notcontain")
{
$fc[] = $dataIndx . " not like CONCAT('%', ?, '%')";
$param[] = $text;
}
else if ($condition == "begin")
{
$fc[] = $dataIndx . " like CONCAT( ?, '%')";
$param[] = $text;
}
else if ($condition == "end")
{
$fc[] = $dataIndx . " like CONCAT('%', ?)";
$param[] = $text;
}
else if ($condition == "equal")
{
$fc[] = $dataIndx . " = ?";
$param[] = $text;
}
else if ($condition == "notequal")
{
$fc[] = $dataIndx . " != ?";
$param[] = $text;
}
else if ($condition == "empty")
{
$fc[] = "ifnull(" . $dataIndx . ",'')=''";
}
else if ($condition == "notempty")
{
$fc[] = "ifnull(" . $dataIndx . ",'')!=''";
}
else if ($condition == "less")
{
$fc[] = $dataIndx . " < ?";
$param[] = $text;
}
else if ($condition == "great")
{
$fc[] = $dataIndx . " > ?";
$param[] = $text;
}
}
$query = "";
if (sizeof($filters) > 0)
{
$query = " where " . join(" ".$mode." ", $fc);
}
$ds = new stdClass();
$ds->query = $query;
$ds->param = $param;
return $ds;
}
}//end of class
$filterQuery = "";
$filterParam = array();
if(isset($_GET["pq_filter"]))
{
$pq_filter = $_GET["pq_filter"];
$dsf = FilterHelper::deSerializeFilter($pq_filter);
$filterQuery = $dsf->query;
$filterParam = $dsf->param;
}
$dbserver='localhost';
$dbusername='root';
$dbpassword='';
$dbname='monteureinsatz';
$db=mysql_connect("$dbserver","$dbusername","$dbpassword");
$currDb=mysql_selectdb($dbname) or die (mysql_error());
$sqlstring="Select datum,kw,name,bezeichnung,notiz from uebersichtsplan UP
JOIN uebersichtsplan_has_mitarbeiter UHM ON (UP.ID_UP = UHM.uebersichtsplan_ID)
JOIN mitarbeiter M ON (UHM.mitarbeiter_ID = M.ID_M)
JOIN uebersichtsplan_has_planung UHP ON(UP.ID_UP = UHP.uebersichtsplan_ID)
JOIN planung P ON(UHP.planung_ID = P.ID_P) JOIN notizen ON (UP.notizen_ID = notizen.ID_N) order by ID_UP". $filterQuery;
$result=mysql_query($sqlstring) or die (mysql_error());
$php_total=mysql_num_rows($result);
$daten = array();
while($row=mysql_fetch_array($result))//while($row=mysql_fetch_array($result))
{
$daten[] = array(date('d.m.y', strtotime($row['datum'])),$row['kw'],$row['name'],$row['bezeichnung'],$row['notiz']);
}
$json_arr = array('data'=>$daten);
$php_json = json_encode($json_arr);
echo $php_json;
?>
Best Regards
Daniel
-
It seems to be error related to improper JSON format from the server while filtering.
Please check the response from server in your browser network tab or open the url in a different tab ( since it's a GET request in your dataModel ) to look at the response format.
-
Thank you for your reply.
I uploaded a File which shows you the response from the server in my browser network tab.
-
That is the error itself, not the response from the server. You have to right click on url and open it in new tab to find response from the server which would be helpful to you to troubleshoot the issue in your PHP script.
get_data2.php?pq_datatype=JSON&..
-
Sorry. Hope that is right now.
-
There you go. The error suggests you to correct some missing column name.
-
But which column name I have to correct?
I have all necessary columns in my SQL-Statement.
-
Ok I see, there is underscore "_" in your column names, so you need to modify the regular expression to include underscore in isValidColumn function.
if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
-
In my SQL-Statement I only use one column name with an underscore "ID_UP".
I tried to to modify the regular expression with an underscore like this
if (preg_match('/^[a-z,A-Z,_]*$/', $dataIndx))
but the error still exist.
-
You are missing dataIndx in column definitions in the colModel in javascript code, just include them.
-
I included the dataIndx in colModel and now I don't get any rows displayed in the table.
var colM = [
/*{ title: "ID", width: 100},*/
{ title: "Datum", width: 100, dataIndx:"datum"},
{ title: "KW", width:100, dataIndx:"kw"},
{ title: "Monteure", width: 100, dataIndx:"name"},
{ title: "Planung", width: 200, dataIndx:"bezeichnung"},
{ title: "Notizen", width: 400, dataIndx:"notiz"}
];
The dataIndx("datum","kw","name","bezeichnung","notiz") are the column names from my database.
Now I get some other Error in my network tab:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where uebersichtsplan like CONCAT( ?, '%') OR uebersichtsplan like CONCAT( ?, '%' at line 5
"uebersichtsplan" is the name of one of my tables.
-
I resolved by code :
if (preg_match('/^[a-zA-Z0-9_-]*$/', $dataIndx))