[SOLVED] How a can show Data From MYSQL in PHP Grid

[Xavier Reyes]

I got this code:

Conexion PHP file (db_select_seccion.php)

<?php

function getData(){ 

require("../config/conexion.php");

$emparray=array();

$sql = "SELECT id_seccion, nombre, activo FROM mm_c_secciones";

$result = $mysqli->query($sql);

if ($result->num_rows > 0) {

    // output data of each row

    while($row = $result->fetch_assoc()) {

        $emparray[]=$row;

    }

} else {

    echo "0 results";

}

echo json_encode($emparray);

}

if(isset($_GET['action'])){

if(function_exists($_GET['action'])) {    

$_GET['action']();

}

}

?>

this page return:

[{"id_seccion":"1","nombre":"10","activo":"1"},{"id_seccion":"2","nombre":"20","activo":"1"},{"id_seccion":"3","nombre":"30","activo":"1"},{"id_seccion":"4","nombre":"40","activo":"1"},{"id_seccion":"5","nombre":"50","activo":"1"},{"id_seccion":"6","nombre":"60","activo":"1"}]

Layout File PHP :

<?php

    $Json = json_decode(file_get_contents('http://127.0.0.1/morena/navigation/db_select_seccion.php/?action=getData'), true);

    $col1 = new EJ\Grid\Column();

    $col1->field('id_seccion')->headerText('Id de la Seccion')->textAlign('center')->isPrimaryKey(true)->width(100);

    $col2 = new EJ\Grid\Column();

    $col2->field('nombre')->headerText('Nombre')->width(100);

    $col3 = new EJ\Grid\Column();

    $col3->field('activo')->headerText('Activa')->textAlign('center')->width(100);

    $gridColumns = array($col1,$col2,$col3);

    $grid =  new EJ\Grid('Grid');

    $edit =new EJ\Grid\EditSetting();

    $toolbarItems = array('add','edit','delete','update','cancel');

    $toolbar= new EJ\Grid\ToolbarSetting();

    echo $grid -> dataSource($Json)->allowPaging(true)->columns($gridColumns)->editSettings($edit->allowEditing(true)->allowDeleting(true)->allowAdding(true))->toolbarSettings($toolbar->showToolbar(true)->toolbarItems($toolbarItems))->render();

    ?>

but i do sonthing wrong why don't show the rows

Could you please give me some guidance?

[Jayaprakash Kamaraj]

Hi Xavier,  

Thank you for contacting Syncfusion support. 

To overcome this problem, we suggest you to use ejDatamanager to bind the dataSource in Grid. Please refer to the below code example, 

                                                                                                                                                                 

<?php 

require_once '../EJ/AutoLoad.php'; 

?> 

<div class="cols-sample-area"> 

    <?php 

    

    $col1 = new EJ\Grid\Column(); 

    $col1->field("OrderID")->headerText("OrderID")->textAlign("right")->width(100); 

    $col2 = new EJ\Grid\Column(); 

    $col2->field("CustomerID")->headerText("CustomerID")->width(70); 

    $col3 = new EJ\Grid\Column(); 

    $col3->field("EmployeeID")->headerText("EmployeeID")->textAlign("right")->width(70); 

    $col4 = new EJ\Grid\Column(); 

    $col4->field("ShipCountry")->headerText("ShipCountry")->width(70); 

    $col5 = new EJ\Grid\Column(); 

    $col5->field("Freight")->headerText("Freight")->textAlign("right")->format("{0:C}")->width(70); 

    $gridColumns = array($col1,$col2,$col3,$col4,$col5); 

    $dataManager  = new EJ\DataManager();  

    $dataManager->url('//mvc.syncfusion.com/Services/Northwnd.svc/Orders/')->offline(true); 

    $grid =  new EJ\Grid("Grid"); 

    echo $grid -> dataSource( $dataManager)->columns($gridColumns)->allowPaging(true)->render(); 

    ?> 

 

</div> 

<style> 

    .cols-sample-area {         

        margin:0 auto; 

        float:none; 

    } 

</style> 

If you still facing the issue, please share the following information to serve you better    

1.       Essential studio and browser version details. 

2.       Please open the console window in browser and check whether any script error throws. 

3.       An issue reproducing sample if possible or hosted link 

Regards, 

Jayaprakash K. 

 

[Xavier Reyes]

SOLVED!!

Already solved it, it lacked to include the file "jsrender.min.js" in the heading, for that it did not show the rows.

Conexion PHP file (db_select_seccion.php)

<?php

function getData(){ 

require("../config/conexion.php");

$emparray=array();

$sql = "SELECT id_seccion, nombre, activo FROM mm_c_secciones";

$result = $mysqli->query($sql);

if ($result->num_rows > 0) {

    // output data of each row

    while($row = $result->fetch_assoc()) {

        $emparray[]=$row;

    }

} else {

    echo "0 results";

}

echo json_encode($emparray);

}

if(isset($_GET['action'])){

if(function_exists($_GET['action'])) {    

$_GET['action']();

}

}

?>

this page return:

[{"id_seccion":"1","nombre":"10","activo":"1"},{"id_seccion":"2","nombre":"20","activo":"1"},{"id_seccion":"3","nombre":"30","activo":"1"},{"id_seccion":"4","nombre":"40","activo":"1"},{"id_seccion":"5","nombre":"50","activo":"1"},{"id_seccion":"6","nombre":"60","activo":"1"}]

Layout File PHP :

<!DOCTYPE html>

<html>

<head>

<link rel='nofollow' href="Content/bootstrap.min.css" rel="stylesheet" />

<link rel='nofollow' href="Content/ejthemes/ej.widgets.core.bootstrap.min.css" rel="stylesheet" />

<link rel='nofollow' href="Content/ejthemes/bootstrap-theme/ej.web.all.min.css" rel="stylesheet" />

<!--Dependency files references-->

<!--[if lt IE 9]>

<script src="Scripts/jquery-1.11.3.min.js" type="text/javascript" ></script>

<![endif]-->

<!--[if IE 9]><!-->

<script src="Scripts/jquery-3.1.1.min.js" type="text/javascript"> </script>

<!--<![endif]-->

<script src="Scripts/jsrender.min.js" type="text/javascript"></script>

<script src="Scripts/ej.web.all.min.js" type="text/javascript"></script>

</head>

<?php

require_once '/EJ/AutoLoad.php';

?>

    <?php

   

    $Json = json_decode(file_get_contents('http://127.0.0.1/morena/navigation/db_select_seccion.php/?action=getData'), true);

    $col1 = new EJ\Grid\Column();

    $col1->field('id_seccion')->headerText('Id de la Seccion')->textAlign('center')->isPrimaryKey(true)->width(100);

    $col2 = new EJ\Grid\Column();

    $col2->field('nombre')->headerText('Nombre')->width(100);

    $col3 = new EJ\Grid\Column();

    $col3->field('activo')->headerText('Activa')->textAlign('center')->width(100);

    $gridColumns = array($col1,$col2,$col3);

    $grid =  new EJ\Grid('Grid');

    $edit =new EJ\Grid\EditSetting();

    $toolbarItems = array("add","edit","delete","update","cancel");

    $toolbar= new EJ\Grid\ToolbarSetting();

    echo $grid -> dataSource($Json)->allowPaging(true)->columns($gridColumns)->editSettings($edit->allowEditing(true)->allowDeleting(true)->allowAdding(true))->toolbarSettings($toolbar->showToolbar(true)->toolbarItems($toolbarItems))->render();

    

    ?>

</body>

</html>

[Xavier Reyes]

Hi Xavier,  

Thank you for contacting Syncfusion support. 

To overcome this problem, we suggest you to use ejDatamanager to bind the dataSource in Grid. Please refer to the below code example, 

                                                                                                                                                                 

<?php 

require_once '../EJ/AutoLoad.php'; 

?> 

<div class="cols-sample-area"> 

    <?php 

    

    $col1 = new EJ\Grid\Column(); 

    $col1->field("OrderID")->headerText("OrderID")->textAlign("right")->width(100); 

    $col2 = new EJ\Grid\Column(); 

    $col2->field("CustomerID")->headerText("CustomerID")->width(70); 

    $col3 = new EJ\Grid\Column(); 

    $col3->field("EmployeeID")->headerText("EmployeeID")->textAlign("right")->width(70); 

    $col4 = new EJ\Grid\Column(); 

    $col4->field("ShipCountry")->headerText("ShipCountry")->width(70); 

    $col5 = new EJ\Grid\Column(); 

    $col5->field("Freight")->headerText("Freight")->textAlign("right")->format("{0:C}")->width(70); 

    $gridColumns = array($col1,$col2,$col3,$col4,$col5); 

    $dataManager  = new EJ\DataManager();  

    $dataManager->url('//mvc.syncfusion.com/Services/Northwnd.svc/Orders/')->offline(true); 

    $grid =  new EJ\Grid("Grid"); 

    echo $grid -> dataSource( $dataManager)->columns($gridColumns)->allowPaging(true)->render(); 

    ?> 

 

</div> 

<style> 

    .cols-sample-area {         

        margin:0 auto; 

        float:none; 

    } 

</style> 

If you still facing the issue, please share the following information to serve you better    

1.       Essential studio and browser version details. 

2.       Please open the console window in browser and check whether any script error throws. 

3.       An issue reproducing sample if possible or hosted link 

Regards, 

Jayaprakash K. 

 

$dataManager  = new EJ\DataManager();  

    $dataManager->url('//mvc.syncfusion.com/Services/Northwnd.svc/Orders/')->offline(true); 

Could you provide an example of how to use the DataManager() with Mysql?

[Jayaprakash Kamaraj]

Hi Xavier, 

Based on your request, we have created a sample using ejDataManager with UrlAdaptor and Mysql  that can be downloaded from the following location. 

Sample : http://www.syncfusion.com/downloads/support/forum/130239/ze/sample-988407926 

Index.php 

<?php 

require_once 'EJ/AutoLoad.php'; 

?> 

 

    <title>ejGrid with PHP</title> 

    <meta name="viewport" content="width=device-width, initial-scale=1.0" /> 

 

    <link rel='nofollow' href="http://cdn.syncfusion.com/15.1.0.41/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" /> 

 

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery-1.10.2.min.js"></script> 

 

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery.easing.1.3.min.js"></script> 

 

    <script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script> 

 

    <script src="https://cdn.syncfusion.com/15.1.0.41/js/web/ej.web.all.min.js"></script> 

 

<div class="cols-sample-area"> 

    <?php 

    

    $col1 = new EJ\Grid\Column(); 

    $col1->field("help_category_id")->headerText("OrderID")->textAlign("right")->width(100); 

    $col2 = new EJ\Grid\Column(); 

    $col2->field("name")->headerText("CustomerID")->width(70); 

    $col3 = new EJ\Grid\Column(); 

    $col3->field("parent_category_id")->headerText("EmployeeID")->textAlign("right")->width(70); 

    $gridColumns = array($col1,$col2,$col3); 

    $dataManager  = new EJ\DataManager();  

    $dataManager->url('http://localhost:8080/sample/data.php')->adaptor('UrlAdaptor'); 

    $grid =  new EJ\Grid("Grid"); 

    echo $grid -> dataSource( $dataManager)->columns($gridColumns)->allowPaging(true)->render(); 

    ?> 

 

</div> 

<style> 

    .cols-sample-area {         

        margin:0 auto; 

        float:none; 

    } 

</style> 

 

data.php 

<?php 

 

//session_start(); 

 

//include("connection.php"); 

 

?> 

 

<?php 

/** 

* Created by PhpStorm. 

* User: christopheweibel 

* Date: 16/01/15 

* Time: 12:20 

*/ 

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

$link = mysqli_connect('localhost', 'root', '', 'mysql'); 

 

$json_param = file_get_contents("php://input"); 

$params = json_decode($json_param,true); 

/* check connection */ 

if (!$link) { 

    printf("Connect failed: %s\n", mysqli_connect_error()); 

    exit(); 

} 

$skip = $params['skip']; 

$take = $params['take']; 

$query =$sql = "SELECT * FROM help_category"; 

 

if($take!=null){ 

    $query = $query." limit ".$skip.",".$take;  //perform skip and take operation 

} 

 

$result = mysqli_query($link,$query); 

 

$json=array(); 

 

while ($row = mysqli_fetch_array($result)) { 

    array_push($json,array( 

                           'help_category_id' => $row{'help_category_id'}, 

                           'name' => $row{'name'}, 

                           'parent_category_id' => $row{'parent_category_id'}, 

                            

                          )); 

} 

 

$countquery = mysqli_query($link,$sql); 

$count=mysqli_num_rows($countquery);  //return total records count 

 

$response=array("result"=>$json,"count"=>(int)$count); 

echo json_encode($response); 

 

?> 

 

For curd operations, please refer to the below link.   

 

https://www.syncfusion.com/blogs/post/Grid-Control-for-JavaScript-in-PHP.aspx 

Regards, 

Jayaprakash K. 

[Xavier Reyes]

Hi Xavier, 

Based on your request, we have created a sample using ejDataManager with UrlAdaptor and Mysql  that can be downloaded from the following location. 

Sample : http://www.syncfusion.com/downloads/support/forum/130239/ze/sample-988407926 

Index.php 

<?php 

require_once 'EJ/AutoLoad.php'; 

?> 

 

    <title>ejGrid with PHP</title> 

    <meta name="viewport" content="width=device-width, initial-scale=1.0" /> 

 

    <link rel='nofollow' href="http://cdn.syncfusion.com/15.1.0.41/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" /> 

 

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery-1.10.2.min.js"></script> 

 

    <script src="http://cdn.syncfusion.com/js/assets/external/jquery.easing.1.3.min.js"></script> 

 

    <script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script> 

 

    <script src="https://cdn.syncfusion.com/15.1.0.41/js/web/ej.web.all.min.js"></script> 

 

<div class="cols-sample-area"> 

    <?php 

    

    $col1 = new EJ\Grid\Column(); 

    $col1->field("help_category_id")->headerText("OrderID")->textAlign("right")->width(100); 

    $col2 = new EJ\Grid\Column(); 

    $col2->field("name")->headerText("CustomerID")->width(70); 

    $col3 = new EJ\Grid\Column(); 

    $col3->field("parent_category_id")->headerText("EmployeeID")->textAlign("right")->width(70); 

    $gridColumns = array($col1,$col2,$col3); 

    $dataManager  = new EJ\DataManager();  

    $dataManager->url('http://localhost:8080/sample/data.php')->adaptor('UrlAdaptor'); 

    $grid =  new EJ\Grid("Grid"); 

    echo $grid -> dataSource( $dataManager)->columns($gridColumns)->allowPaging(true)->render(); 

    ?> 

 

</div> 

<style> 

    .cols-sample-area {         

        margin:0 auto; 

        float:none; 

    } 

</style> 

 

data.php 

<?php 

 

//session_start(); 

 

//include("connection.php"); 

 

?> 

 

<?php 

/** 

* Created by PhpStorm. 

* User: christopheweibel 

* Date: 16/01/15 

* Time: 12:20 

*/ 

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

$link = mysqli_connect('localhost', 'root', '', 'mysql'); 

 

$json_param = file_get_contents("php://input"); 

$params = json_decode($json_param,true); 

/* check connection */ 

if (!$link) { 

    printf("Connect failed: %s\n", mysqli_connect_error()); 

    exit(); 

} 

$skip = $params['skip']; 

$take = $params['take']; 

$query =$sql = "SELECT * FROM help_category"; 

 

if($take!=null){ 

    $query = $query." limit ".$skip.",".$take;  //perform skip and take operation 

} 

 

$result = mysqli_query($link,$query); 

 

$json=array(); 

 

while ($row = mysqli_fetch_array($result)) { 

    array_push($json,array( 

                           'help_category_id' => $row{'help_category_id'}, 

                           'name' => $row{'name'}, 

                           'parent_category_id' => $row{'parent_category_id'}, 

                            

                          )); 

} 

 

$countquery = mysqli_query($link,$sql); 

$count=mysqli_num_rows($countquery);  //return total records count 

 

$response=array("result"=>$json,"count"=>(int)$count); 

echo json_encode($response); 

 

?> 

 

For curd operations, please refer to the below link.   

 

https://www.syncfusion.com/blogs/post/Grid-Control-for-JavaScript-in-PHP.aspx 

Regards, 

Jayaprakash K. 

It does not work in local mode, but online it's great.

I send you the code including CURD operations, I hope can help someone.

Data file (data.php):

[CODE]

<?php

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

function getData(){ 

require("../config/conexion.php");

$json_param = file_get_contents("php://input");

$params = json_decode($json_param,true);

/* check connection */

if (!$mysqli) {

printf("Connect failed: %s\n", mysqli_connect_error());

exit();

}

$skip = $params['skip'];

$take = $params['take'];

$query =$sql = "SELECT * FROM mm_c_secciones";

if($take!=null){

$query = $query." limit ".$skip.",".$take;  //perform skip and take operation

}

$result = mysqli_query($mysqli,$query);

$json=array();

while ($row = mysqli_fetch_array($result)) {

array_push($json,array(

  'id_seccion' => $row{'id_seccion'},

  'nombre' => $row{'nombre'},

  'activo' => $row{'activo'}                           

 ));

}

$countquery = mysqli_query($mysqli,$sql);

$count=mysqli_num_rows($countquery);  //return total records count

$response=array("result"=>$json,"count"=>(int)$count);

echo json_encode($response);

}

function delData(){

require("../config/conexion.php");

$json_del=file_get_contents("php://input");

    $obj_del = json_decode($json_del,true);

$key=(int)$obj_del['key'];

    $sql = "DELETE FROM mm_c_secciones WHERE id_seccion = $key";

if ($mysqli->query($sql) === TRUE) {

$success=array("Deleted"=>$key);

echo json_encode($success);

} else {

echo "Error updating record: " . $conn->error;

}

}

function insertData(){

require("../config/conexion.php");

    $json_insert=file_get_contents("php://input");

    $obj_insert = json_decode($json_insert,true);

$insertvalue=array();

$insertvalue=$obj_insert['value'];

$oid=(int)$insertvalue['id_seccion'];$cid=$insertvalue['nombre'];$eid=(int)$insertvalue['activo'];

$sql = "INSERT INTO mm_c_secciones(nombre,activo) VALUES('$cid',$eid)";

if ($mysqli->query($sql) === TRUE) {

$success=array("Inserted"=>array("id_seccion"=>$oid,"nombre"=>$cid,"activo"=>$eid));

echo json_encode($sucess);

} else {

echo "Error updating record: " . $conn->error;

}

}

function updateData(){

require("../config/conexion.php");

    $json_update=file_get_contents("php://input");

    $obj_update = json_decode($json_update,true);

$value=array();

$key=(int)$obj_update['key'];

$value=$obj_update['value'];

$oid=(int)$value['id_seccion'];

$cid=$value['nombre'];

$eid=(int)$value['activo'];

$sql="UPDATE mm_c_secciones SET nombre='$cid',activo=$eid WHERE id_seccion=$key";

if ($mysqli->query($sql) === TRUE) {

$success=array("Updated"=>array("id_seccion"=>$oid,"nombre"=>$cid,"activo"=>$eid));

echo json_encode($success);

} else {

echo "Error updating record: " . $conn->error;

}

}

if(isset($_GET['action'])){

if(function_exists($_GET['action'])) {    

$_GET['action']();

}

}

?>

[/CODE]

Code in layout file PHP :

<?php

require 'src/AutoLoad.php';

    $col1 = new EJ\Grid\Column();

    $col1->field('id_seccion')->headerText('Id de la Seccion')->textAlign('center')->isPrimaryKey(true)->width(100);

    $col2 = new EJ\Grid\Column();

    $col2->field('nombre')->headerText('Nombre')->width(100);

    $col3 = new EJ\Grid\Column();

    $col3->field('activo')->headerText('Activa')->textAlign('center')->width(100);

    $gridColumns = array($col1,$col2,$col3);

    $grid =  new EJ\Grid('Grid');

    $edit =new EJ\Grid\EditSetting();

    $toolbarItems = array("add","edit","delete","update","cancel");

    $toolbar= new EJ\Grid\ToolbarSetting();

    $dataManager  = new EJ\DataManager();

$url = "http://mywebsite.com/navigation/data.php";

    $dataManager->url($url.'?action=getData')->removeurl($url.'?action=delData')->updateurl($url.'?action=updateData')->inserturl($url.'?action=insertData')->adaptor('UrlAdaptor');

    echo $grid -> dataSource($dataManager)->allowPaging(true)->columns($gridColumns)->editSettings($edit->allowEditing(true)->allowDeleting(true)->allowAdding(true))->toolbarSettings($toolbar->showToolbar(true)->toolbarItems($toolbarItems))->render();

    ?>

[Jayaprakash Kamaraj]

Hi Xavier, 

Query : It does not work in local mode 

Before proceeding please share us the following details. 

                               

1.       Share the video or screenshot to show the issue. 

2.       In which scenario you have facing the issue.   

3.       Essential studio and browser version details. 

4.       Please open the console window in browser and check whether any script error throws. 

5.       An issue reproducing sample if possible 

Regards, 

Jayaprakash K.