Mike Mike - 19 days ago 5
HTML Question

How to parse JSON into HTML Menu in PHP

I have a website that I've been working on, updating each time I find a better way of doing things. Right know I've started using DataTables, but some of the HTML tables that I have are too large and time out.

To fix this I'm trying to convert to using server-side processing, which requires JSON. I've never used JSON so I'm learning as I go. What I'm trying to do first is convert the menu to JSON. Only the menu has several levels and I can't seem to get anything to work.

Here's what I've got so far:

TestFile: with the JSON I'm trying to convert:

{"NavMenu":"ul","class":"clearfix","id":"menu","html":[
{"NavMenu":"li","html":[{"NavMenu":"a","href":"default.aspx","html":"Home"}]},
{"NavMenu":"li","html":[{"NavMenu":"a","href":"MaterialTracking.php","html":"Material Tracking"}]},
{"NavMenu":"li","html":[{"NavMenu":"a","href":"OPR Reports.php","html":"OPR Reports"}]},
{"NavMenu":"li","html":[{"NavMenu":"a","href":"#","html":"CAFII Reports"}]},
{"NavMenu":"li","html":[{"NavMenu":"a","href":"#","html":"ENG Reports"}]},
{"NavMenu":"li","html":[{"NavMenu":"a","href":"#","html":"Admin"}]}
]}


PHP file that I'm using to do the converting with a modified script that I found here

<div id="id1"></div>
<script>
var xmlhttp = new XMLHttpRequest();
var url = "TestFile.txt";

xmlhttp.onreadystatechange = function ()
{
if (xmlhttp.readyState == 4 && xmlhttp.status == 200)
{
alert('Made it to a good file');
var myArr = JSON.parse(xmlhttp.responseText);
myfunction(myarr);
}
}

xmlhttp.open("GET", url, true);
xmlhttp.send();
alert('Right before the function');
function myFunction(arr)
{
var out = "";
var i;
alert('Made it into the function');
for (i = 0; i < arr.length; i++)
{
out += '<' + arr[i].NavMenu + '>';
for (a = 0; arr[i].html.length; a++)
{
out += '<' + arr[i].html[a].NavMenu + ' href="' + arr[i].html[a].href + '">' + arr[i].html[a].html + '</' + arr[i].html[a].NavMenu + '>';
}
out += '</' + arr[i].NavMenu + '><br>';
}
document.getElementById("id1").innerHTML = out;
}
</script>


I got the JSON from a converter I found here under the "Builder" tab.

I'm sure I'm doing something wrong, I just can't figure out what.

Also if this isn't the best way to convert these tables into JSON for server side processing let me know that too so I can move on to something that will be more productive. I just have a bunch of reports that are too large to handle client side.

EDIT

I've changed the I'm doing things to try and only pull the tables back using JSON based on the recommendation below. However, I only get a blank screen with this:

<?php
/* Enter the column names which you want you retrieve
I use a table in my DB to hold all column names for the several
tables that I will pull back.
*/
$Page = '';
if (isset($_GET['PageName']))
{
//echo "<br>Page = Get<br>";
$Page = $_GET['PageName'];
}
elseif (isset($_POST['PageName']))
{
//echo "<br>Page = Post<br>";
$Page = $_POST['PageName'];
}

$hsql = "select Headings from TableHeadings where TableName = '$Page' order by Id";
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);
//print_r($hsql);
$tsqlHeadings = '';
for ($row = 0; $row < $CountHeadings; $row++)
{
$headings[$row] = $rHeadings[$row]["Headings"];
$tsqlHeadings = $tsqlHeadings . "[" . $headings[$row] . '],';
}

$aColumns = $headings; //array( 'engine', 'browser', 'platform', 'version', 'grade' );

/* Indexed column (Ex: id or employee_id etc.) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = $Page;
include 'DBConn.php';

/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}

/*
* MySQL connection
* I don't use MySQL so I had to convert that to what I do use PDO SqlSrv
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}

if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
}
}

$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}


/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
{
$sWhere .= $aColumns[$i]." LIKE '%".quote( $_GET['sSearch'] )."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".quote($_GET['sSearch_'.$i])."%' ";
}
}


/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = $conn->query($sQuery);// or fatal_error( 'MySQL Error: ' . mysql_errno() );

/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = $conn->query($sQuery);// or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = $rResultFilterTotal->fetchALL(PDO::FETCH_ASSOC);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = $conn->query($sQuery);// or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = $rResultTotal->fetchALL(PDO::FETCH_ASSOC);
$iTotal = $aResultTotal[0];


/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = sqlsrv_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}

echo json_encode( $output );
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
</head>
<body>
<script>
var table=$('#datatable').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "ajax-handler.php",
pagingType: "bootstrap_full_number",
"aaSorting": [[0, "desc"]],
"language": {
"lengthMenu": "",
"zeroRecords": "No Messages!",
"infoEmpty": "Empty"
}
});
</script>
</body>
</html>


And here's my connection info from DBConn.php:

$conn = new PDO("sqlsrv:server=$servername;database=$dbname", $username,$password);
//print_r($conn);
//set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 10);


EDIT 2

I have gotten it to kind of work. I have based my code on what Sarath has below in his answer. I also have a page where I'm trying what Press has in his answer. For both I get something that looks like this as the output:

Sarath:

{"iTotalRecords":{"":280},"iTotalDisplayRecords":false,"aaData":[[1,"TableUpdates","Id"],[2,"TableUpdates","TableName"],[3,"TableUpdates","UpdateDate"],[1,"ApprovedProjects","Id"],[2,"ApprovedProjects","Company"],[3,"ApprovedProjects","State"],[4,"ApprovedProjects","Project"],[5,"ApprovedProjects","SubProject"],[6,"ApprovedProjects","Division"],[7,"ApprovedProjects","PlantNum"],[8,"ApprovedProjects","Exchange"],[9,"ApprovedProjects","DeptNum"],[10,"ApprovedProjects","ClassOfPlant"],[11,"ApprovedProjects","StatusCode"],[12,"ApprovedProjects","JustCode"],[13,"ApprovedProjects","Description"],[14,"ApprovedProjects","InfProjType"],


Press:

{"NavMenu":"ul","class":"clearfix","id":"menu","html":[ {"NavMenu":"li","html":[{"NavMenu":"a","href":"default.aspx","html":"Home"}]}, {"NavMenu":"li","html":[{"NavMenu":"a","href":"MaterialTracking.php","html":"Material Tracking"}]}, {"NavMenu":"li","html":[{"NavMenu":"a","href":"OPR Reports.php","html":"OPR Reports"}]}, {"NavMenu":"li","html":[{"NavMenu":"a","href":"#","html":"CAFII Reports"}]}, {"NavMenu":"li","html":[{"NavMenu":"a","href":"#","html":"ENG Reports"}]}, {"NavMenu":"li","html":[{"NavMenu":"a","href":"#","html":"Admin"}]} ]}


How do I turn the first one into a table and second one into a menu?

Answer

You don't need parse json and use in DataTable simply use datatable server side script. Just replace column names, indexed column, database connection settings. Don't change anything.

Ajax-handler.php :

/* Enter the column name which you want you retrieve   
 */
$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );

/* Indexed column (Ex: id or employee_id etc.) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "ajax";

/* Database connection information */
$gaSql['user']       = "";
$gaSql['password']   = "";
$gaSql['db']         = "";
$gaSql['server']     = "localhost";



/*
 * Local functions
 */
function fatal_error ( $sErrorMessage = '' )
{
    header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
    die( $sErrorMessage );
}


/*
 * MySQL connection
 */
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
{
    fatal_error( 'Could not open connection to server' );
}

if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
    fatal_error( 'Could not select database ' );
}


/*
 * Paging
 */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
        intval( $_GET['iDisplayLength'] );
}


/*
 * Ordering
 */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
    {
        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
        {
            $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
        }
    }

    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" )
    {
        $sOrder = "";
    }
}


/*
 * Filtering
 * NOTE this does not match the built-in DataTables filtering which does it
 * word by word on any field. It's possible to do here, but concerned about efficiency
 * on very large tables, and MySQL's regex functionality is very limited
 */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
        {
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }
        $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    }
}


/*
 * SQL queries
 * Get data to display
 */
$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
    FROM   $sTable
    $sWhere
    $sOrder
    $sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );

/* Data set length after filtering */
$sQuery = "
    SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
    SELECT COUNT(".$sIndexColumn.")
    FROM   $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];


/*
 * Output
 */
$output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $aColumns[$i] == "version" )
        {
            /* Special output formatting for 'version' column */
            $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
        }
        else if ( $aColumns[$i] != ' ' )
        {
            /* General output */
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}

echo json_encode( $output );

Datatable funtion using jQuery :

var table=$('#datatable').dataTable({
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "ajax-handler.php",
        pagingType: "bootstrap_full_number",
        "aaSorting": [[0, "desc"]],
        "language": {
            "lengthMenu": "",
            "zeroRecords": "No Messages!",
            "infoEmpty": "Empty"
        }
    });

I have made few changes for PDO Sqlserver Change:

<?php
/* Enter the column names which you want you retrieve
   I use a table in my DB to hold all column names for the several 
   tables that I will pull back.   
 */
 

 
$Page = '';
if (isset($_GET['PageName']))
{
    //echo "<br>Page = Get<br>";
    $Page = $_GET['PageName'];
}
elseif (isset($_POST['PageName']))
{
    //echo "<br>Page = Post<br>";
    $Page = $_POST['PageName'];
}

$hsql = "select Headings from TableHeadings where TableName = '$Page' order by Id";
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);
//print_r($hsql);
$tsqlHeadings = '';
for ($row = 0; $row < $CountHeadings; $row++)
{
    $headings[$row] = $rHeadings[$row]["Headings"];
    $tsqlHeadings = $tsqlHeadings . "[" . $headings[$row] . '],';
}

$aColumns = $headings; //array( 'engine', 'browser', 'platform', 'version', 'grade' );

/* Indexed column (Ex: id or employee_id etc.) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = $Page;
include 'DBConn.php';

/*
 * Local functions
 */
function fatal_error ( $sErrorMessage = '' )
{
    header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
    die( $sErrorMessage );
}

 /* DATABASE CONNECTION */
$serverName = "serverName\instanceName";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}


/*
 * Paging
 */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
        intval( $_GET['iDisplayLength'] );
}


/*
 * Ordering
 */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
    {
        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
        {
            $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
        }
    }

    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" )
    {
        $sOrder = "";
    }
}


/*
 * Filtering
 * NOTE this does not match the built-in DataTables filtering which does it
 * word by word on any field. It's possible to do here, but concerned about efficiency
 * on very large tables, and MySQL's regex functionality is very limited
 */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
        {
            $sWhere .= $aColumns[$i]." LIKE '%".quote( $_GET['sSearch'] )."%' OR ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }
        $sWhere .= $aColumns[$i]." LIKE '%".quote($_GET['sSearch_'.$i])."%' ";
    }
}


/*
 * SQL queries
 * Get data to display
 */
$sQuery = "
    SELECT count($sIndexColumn) over() as Row_Count ".str_replace(" , ", " ", implode(", ", $aColumns))."
    FROM   $sTable
    $sWhere
    $sOrder
    $sLimit
";

$rResult = sqlsrv_query( $conn, $sQuery );
if( $rResult === false) {
    die( print_r( sqlsrv_errors(), true) );
}

/* Data set length after filtering */
$iFilteredTotal = sqlsrv_num_rows( $rResult );

/* Total data set length */
$sQuery = "SELECT COUNT($sIndexColumn)
    FROM $sTable
";
$rResultTotal = sqlsrv_query( $conn, $sQuery );
$aResultTotal = sqlsrv_fetch_array( $rResultTotal, SQLSRV_FETCH_ASSOC);
$iTotal = $aResultTotal[0];


/*
 * Output
 */
$output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
);

while ( $aRow = sqlsrv_fetch_array( $rResult,SQLSRV_FETCH_ASSOC ) )
{
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $aColumns[$i] == "version" )
        {
            /* Special output formatting for 'version' column */
            $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
        }
        else if ( $aColumns[$i] != ' ' )
        {
            /* General output */
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}

echo json_encode( $output );
?>

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>		
    </head>
    <body>
	
	<table id="datatable">
		<thead>
			<!-- Number of columns should be same as $aColumns -->
		</thead>
	</table>
        <script>
            var table=$('#datatable').dataTable({
                    "bProcessing": true,
                    "bServerSide": true,
                    "sAjaxSource": "ajax-handler.php",
                    pagingType: "bootstrap_full_number",
                    "aaSorting": [[0, "desc"]],
                    "language": {
                        "lengthMenu": "",
                        "zeroRecords": "No Messages!",
                        "infoEmpty": "Empty"
                    }
                });
        </script>
    </body>
</html>

Comments