asked on

DataTables - 2 tables one renders and one does not.

I have a reporting website that I am working to implement DataTables formatting with server side processing. I have several reports that I'm trying to show. They all go through the same set of files to run them. I have a *ServerSide.php* file that gets the needed info and sends it to the server. Then I have a *FilterSort.class.php* file that actually processes the data and pulls from my MSSQL server and returns a JSON response to the client. Then I have a file that is included in the calling file (ReportPage.php) called *DataTableLinks.php* that has the DataTable initialization. Here they are:

ReportPage.php

<?php
    include('Helper/PageName.php');
?>
<!DOCTYPE html>
<html class="no-js" lang="en">
    <head>
        <meta charset="utf-8" />
        <title><?php echo $Title . " " . substr($_SERVER['COMPUTERNAME'],strlen($_SERVER['COMPUTERNAME'])-1,1); ?></title>
        <?php require 'StyleLinks.php'; ?>

        <?php include 'DataTableLinks.php'; include'array_column.php'; ?>

    </head>
    <body>
        <?php
            include 'Menu.php';
        ?>
        <br><br><br>
        <h1><?php echo $HeadingDesc; if(strpos($_SERVER['PHP_SELF'],"/",1)>0){echo "<br>Dev Site";} ?></h1>
        <?php            
            include 'DBConn.php';

            if ($Page == 'AdminPage')
            {
                echo "<script>location.href = 'http://engsys.corp.ftr.com/dev/FrontierReports/AdminPage.php?PageName=AdminPage'</script>";
            }
                
            $hsql = "select Headings from TableHeadings where TableName = '$TableName' and Headings != 'Edit' order by Id";

            $getHeadings = $conn->query($hsql);
            $rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
            $CountHeadings = count($rHeadings);
            $tsqlHeadings = '';
            for ($row = 0; $row < $CountHeadings; $row++)
            {
                $headings[$row] = $rHeadings[$row]["Headings"];
            }            
        ?>
        <table id="DataTable" class="display nowrap" style="width: 100%; border: 1px">
            <thead>
                <tr>
                    <?php
                    foreach($headings as $heading)
                    {?>
                    <th class="cell"><?php echo $heading; ?></th><?php
                    }?>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <?php
                    foreach($headings as $heading)
                    {?>
                    <th class="cell"><?php echo $heading; ?></th><?php
                    }?>
                </tr>
            </tfoot>
        </table>
        <?php //} ?>
        <table class="NormalTable">
            <tr>
                <td>
                    <a class="Help" id="Help" href="http://engsys.corp.ftr.com/dev/Frontier/HelpPage.php" onclick="javascript:void window.open('http://engsys.corp.ftr.com/dev/FrontierReports/helppage.php','1472831666662','width=825,height=280,toolbar=0,menubar=0,location=0,status=1,scrollbars=1,resizable=1,left=0,top=0');return false;">Help</a>
                </td>
            </tr>
        </table>
    </body>
</html>

Open in new window

ServerSide.php

<?php
include 'Helper/PageName.php';              //Pulls the page name and Table name and returns the $SQLTableName, $TableName, $Title and $HeadingDesc
include 'DBConn.php'; 

$headings = array();                        //Create the empty array for use later and so that it won't throw an error if not assinged later
$hsql = "select Headings from TableHeadings where TableName = '$TableName' order by Id";    //Get all the column headers from the TableHeadings table in SQL

$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);         //Count how many columns that there will be
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
    if($rHeadings[$row]["Headings"] <> "Edit")
    {
        $headings[$row] = $rHeadings[$row]["Headings"];     //fill the array of column headings for use in creating the DataTable
    }
}

foreach($headings as $index => $columnName)
{
    $ColumnHeader[] = array('db'=>$columnName,'dt'=>$index);    //creates the array for creating the DataTable column headers when sent to the FilterSort.class
}

//DB table to use
$table = $SQLTableName;

//Table's primary key
$primaryKey = 'id';

// Get data to display
$request = array();
$request = $_GET;
//print_r($_GET);
require('FilterSort.class.php');

echo json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));//,$whereResult,$whereAll));
?>

Open in new window

FilterSort.class.php

<?php
class FilterSort
{
    static function data_output($columns,$data)
    {
        $out = array();
        for($i=0,$ien=count($data);$i<$ien;$i++)
        {
            $row = array();
            for($j=0,$jen=count($columns);$j<$jen;$j++)
            {
                $column = $columns[$j];
                if(isset($column['Formatter']))
                {
                    $row[$column['dt']] = $column['Formatter']($data[$i][$column['db']],$data[$i]);
                }
                else
                {
                    $row[$column['dt']] = $data[$i][$columns[$j]['db']];
                }
            }
            $out[] = $row;
        }
        return $out;
    }

    //Database connection
    static function db ($conn)
    {
        if(is_array($conn))
        {
            return self::sql_connect($conn);
        }
        return $conn;
    }

    //Pagination
    static function limit ($request,$columns)
    {
        $limit = '';
        if(isset($request['start']) && $request['length'] != -1)
        {
            $limit = " OFFSET " . intval($request['start']) . " ROWS FETCH NEXT " . intval($request['length']) . " ROWS ONLY ";
        }
        return $limit;
    }

    //Ordering
    static function order ($request,$columns)
    {
        $order = '';
        if(isset($request['order']) && count($request['order']))
        {
            $orderBy = array();
            $dtColumns = self::pluck($columns,'dt');
            for($i=0,$ien=count($request['order']);$i<$ien;$i++)
            {
                //convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];
                $columnIdx = array_search($requestColumn['data'],$dtColumns);
                $column = $columns[$columnIdx];
                if($requestColumn['orderable'] == 'true')
                {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC':
                        'DESC';
                    $orderBy[] = $column['db']." ".$dir;
                }
            }
            $order = ' ORDER BY '.implode(', ',$orderBy);
        }
        return $order;
    }

	// Searching / Filtering
	// Construct the WHERE clause for server-side processing SQL query.
	static function filter ( $request, $columns, &$bindings )
	{
		$globalSearch = array();
		$columnSearch = array();
		$dtColumns = self::pluck( $columns, 'dt' );
		if (isset($request['search']) && $request['search']['value'] != '')
        {
			$str = $request['search']['value'];
			for ( $i=0, $ien=count($request['columns']); $i<$ien; $i++ )
            {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[ $columnIdx ];
				if ($requestColumn['searchable'] == 'true')
                {
					$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
					$globalSearch[] = "'".$column['db']."' LIKE ".$binding;
				}
			}
		}

		// Individual column filtering
		if ( isset( $request['columns'] ) )
        {
			for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ )
            {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];
				$str = $requestColumn['search']['value'];
				if ( $requestColumn['searchable'] == 'true' &&
				 $str != '' )
                 {
					$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
					$columnSearch[] = $column['db']." LIKE ".$binding;
				}
			}
		}
		// Combine the filters into a single string
		$where = '';
		if ( count( $globalSearch ) )
        {
			$where = ' ('.implode(' OR ', $globalSearch).') ';
		}

		if ( count( $columnSearch ) )
        {
			$where = $where === '' ?
				implode(' AND ', $columnSearch) :
				$where .' AND '. implode(' AND ', $columnSearch);
		}
		if ( $where !== '' )
        {
			$where = ' WHERE '.$where;
		}
		return $where;
	}

	// You can apply additional 'where' conditions to the SQL queries
	static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=NULL, $whereAll=NULL )
	{
		$bindings = array();
		$db = self::db( $conn );
		$localWhereResult = array();
		$localWhereAll = array();
		$whereAllSql = '';
        
		// Build the SQL query string from the request
		$limit = self::limit( $request, $columns );
		$order = self::order( $request, $columns );
		$where = self::filter( $request, $columns, $bindings );
        
		$whereResult = self::_flatten( $whereResult );
		$whereAll = self::_flatten( $whereAll );

		if ( $whereResult )
        {
			$where = $where ? $where .' AND '.$whereResult : ' WHERE '.$whereResult;
		}

		if ( $whereAll )
        {
			$where = $where ? $where .' AND '.$whereAll : ' WHERE '.$whereAll;
			$whereAllSql = ' WHERE '.$whereAll;
		}

		// Main query to actually get the data
		$data = self::sql_exec( $db, $bindings,"SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM $table$where$order$limit");

		// Data set length after filtering
		$resFilterLength = self::sql_exec( $db, $bindings,"SELECT COUNT({$primaryKey}) FROM $table $where" );
		$recordsFiltered = $resFilterLength[0][0];
		// Total data set length
		$resTotalLength = self::sql_exec( $db, "", "SELECT COUNT({$primaryKey}) FROM $table ".$whereAllSql);
		$recordsTotal = $resTotalLength[0][0];

		// Output
		return array(
			"draw"            => isset ( $request['draw'] ) ?
				intval( $request['draw'] ) :
				0,
			"recordsTotal"    => intval( $recordsTotal ),
			"recordsFiltered" => intval( $recordsFiltered ),
			"data"            => self::data_output( $columns, $data )
		);
	}

	// Connect to the database
	static function sql_connect ( $sql_details )
	{
		try {
	        $servername     = $sql_details['servername'];
	        $username       = $sql_details['username'];
	        $password       = $sql_details['password'];
	        $dbname         = $sql_details['dbname'];
			$db             = @new PDO("sqlsrv:server=$servername;database=$dbname", $username,$password);
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 10);
		}
		catch (PDOException $e)
        {
			self::fatal("An error occurred while connecting to the database. The error reported by the server was: ".$e->getMessage());
		}
		return $db;
	}

	// Execute an SQL query on the database
	static function sql_exec ( $db, $bindings, $sql=null )
	{
		// Argument shifting
		if ( $sql === null ) 
        {
			$sql = $bindings;
            $Newsql = $sql;
		}
		$stmt = $db->prepare( $sql );
		// Bind parameters
		if ( is_array( $bindings ) )
        {
			for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ )
            {
				$binding = $bindings[$i];
				$stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
			}
		}

		// Execute
		try
        {
			$stmt->execute();
		}
		catch (PDOException $e)
        {
			self::fatal( "A SQL error has occurred: ".$e->getMessage());
		}

		// Return all
		return $stmt->fetchAll( PDO::FETCH_BOTH );
	}

	//* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	// Internal methods

	// Throw a fatal error.
	static function fatal ( $msg )
	{
		echo json_encode(array("error" => $msg));
		exit(0);
	}

	// Create a PDO binding key which can be used for escaping variables safely when executing a query with execute()
	static function bind ( &$a, $val, $type)
	{
		$key = ':binding_'.count( $a );

		$a[] = array(
			'key' => $key,
			'val' => $val,
			'type' => $type
		);
		return $key;
	}

	// Pull a particular property from each assoc. array in a numeric array, returning and array of the property values from each item.
	static function pluck ( $a, $prop )
	{
		$out = array();
		for ( $i=0, $len=count($a) ; $i<$len ; $i++ )
        {
			$out[] = $a[$i][$prop];
		}
		return $out;
	}

	// Return a string from an array or a string
	static function _flatten ($a, $join = ' AND ')
	{
		if (!$a)
        {
			return '';
		}
		else if ($a && is_array($a))
        {
			return implode($join, $a);
		}
		return $a;
	}
}
?>

Open in new window

DataTableLinks.php

<?php    
echo '
        <link rel="stylesheet" type="text/css" href="DataTables/dataTables.css"/>
        <script type="text/javascript" src="DataTables/dataTables.js"></script>
        ';
?>

<script type="text/javascript" class="init">
	$(document).ready(function () {
		// Setup - add a text input to each footer cell
		$('#DataTable tfoot th').each(function () {
			var title = $(this).text();
			$(this).html('<input type="text" placeholder="Search ' + title + '" />');
		});

		var table = $('#DataTable').DataTable({
				"lengthMenu" : [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
				"ScrollX" : true,
				"dom" : '<"top"Bilp<"clear">>rt<"bottom"ip<"clear">>',
				"buttons" : [{
						extend : 'collection',
						text : 'Selection',
						buttons : ['selectAll', 'selectNone']
					}, {
						extend : 'collection',
						text : 'Export',
						buttons : ['excel', 'csv', 'pdf']
					}
				],
				"fixedHeader" : {
					header : true,
					footer : true
				},
				"select" : true,
				"processing" : true,
				"serverSide" : true,
				"ajax" : {
					"url" : "./ServerSide.php?PageName=<?php echo $Page; ?>"
				},
		initComplete: function() {
		  var api = this.api();

		  // Apply the search
		  api.columns().every(function() {
			var that = this;

			$('input', this.footer()).on('keyup change', function() {
			  if (that.search() !== this.value) {
				that
				  .search(this.value)
				  .draw();
			  }
			});
		  });
		}
	  });
	});
</script>

Open in new window

The problem is that I call two different reports, one will render and show the table filled with data and the other only shows the header and footer and nothing else for the table. When I check the console is shows that the ServerSide.php fould not be found. Which I don't understand since I'm using the same files for both calls. The order is:

ReportPage.php ->
ServerSide.php ->
FilterSort.class.php ->
ReportPage.php

zephyr_hex (Megan)

DataTableLinks.php makes reference to '#DataTable', which is the ID of an HTML element. You can't have more than one HTML element with the same ID on the same page. ID's must be unique. You can use a class to initialize '.DataTable' (all HTML elements with the DataTable class), but you may still need a way to identify each individual datatable. In the past, I've resolved this type of delimma by pushing each datatable into an array, and then I could reference the array index to access each individual datatable.

Mike in IT

ASKER

There is only one DataTable table on the page. The only other table has an ID ="NormalTable". I won't be showing more than one report at a time. The problem is that I can open one report, then when I try to open a different one it doesn't render. I have to select it from my menu and it should send new data to the ServerSide.php file, but simply doesn't, claiming that the file cannot be found.

When I select one report it is the only report that is on the page so there's never more than one table that has the ID=DataTable

Hope that makes more sense.

zephyr_hex (Megan)

That does make more sense. You'll want to consider destroying a datatable before loading a new one. But I don't think that's your problem if you're receiving a file not found error.

What error do you see in F12 developer tools (network tab)? Does it show that ServerSide.php is not found?

Mike in IT

ASKER

In dev tools it shows 404 file not found

zephyr_hex (Megan)

So, let me see if I understand... you have a drop down menu with options. You select option 1 and see the report. You select option 2 and get a file not found. And all files are in the same directory.

If that's a correct understanding, could you please show us the function that gets triggered when the option is selected from the drop down?

Mike in IT

ASKER

You are understanding correctly. I am using 2 reports as examples (Qdefs ad Approved Projects). It doesn't matter the order that I try running them, or if I restart the browser or try multiple browsers, Qdefs works and Approved Projects does not. The only difference in the way that they are called is the ?PageName= on the end of the URL. I've added some screenshots to show this.

So here's what happens.
You click on the menu item and you are taken to the ReportPage.php

This is where the DataTable is created with a header and footer

Then the jquery from the DataTableLinks.php kicks in and should attempt to format the table
At the same time the jquery is calling ServerSide.php

This is when the problems begin, the Qdefs report calls it just fine while the Approved Projects report does not.

I've attached a screen shot showing that the two files (ReportPage.php and ServerSide.php are in the same directory.

Here's the jquery from DataTableLinks.php

<script type="text/javascript" class="init">
	$(document).ready(function () {
		// Setup - add a text input to each footer cell
		$('#DataTable tfoot th').each(function () {
			var title = $(this).text();
			$(this).html('<input type="text" placeholder="Search ' + title + '" />');
		});

		var table = $('#DataTable').DataTable({
				"lengthMenu" : [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
				"ScrollX" : true,
				"dom" : '<"top"Bilp<"clear">>rt<"bottom"ip<"clear">>',
				"buttons" : [{
						extend : 'collection',
						text : 'Selection',
						buttons : ['selectAll', 'selectNone']
					}, {
						extend : 'collection',
						text : 'Export',
						buttons : ['excel', 'csv', 'pdf']
					}
				],
				"fixedHeader" : {
					header : true,
					footer : true
				},
				"select" : true,
				"processing" : true,
				"serverSide" : true,
				"ajax" : {
					"url" : "./ServerSide.php?PageName=<?php echo $Page; ?>"
				},
		initComplete: function() {
		  var api = this.api();

		  // Apply the search
		  api.columns().every(function() {
			var that = this;

			$('input', this.footer()).on('keyup change', function() {
			  if (that.search() !== this.value) {
				that
				  .search(this.value)
				  .draw();
			  }
			});
		  });
		}
	  });
	});
</script>

Open in new window

Then after calling ServerSide.php it should go to FilterSort.class.php which is the server side file that creates the JSON that DataTables uses to create the table.
Then it's sent back to ReportPage.php where it is formatted and put into the table based on the parameters that I have in the above code.
ApprovedProjects.JPG
QDEF.JPG
Files.JPG

zephyr_hex (Megan)

From F12 dev tools, on the network tab, copy the url that's being attempted. Paste that in a new browser and see if you can access the page.

Can you also post the url that works and the url that does not work here in this thread?

Usually, these 404 type errors are spot-on and there is a problem with the path / url or file location.

Mike in IT

ASKER

i would think that the error was spot on as well if it wasn't for both calls being the same. They both use the same files there's no difference in that.

This is an internal site so you won't be able to navigate to it.

I have tried copying and pasteing into another tab or into another browser. Still doesn't work.

Here is the url that works:

http://engsys.corp.ftr.com/dev/FrontierReports/ServerSide.php?PageName=QDefs&draw=1&columns[0][data]=0&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=1&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=true&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=2&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&columns[2][search][regex]=false&columns[3][data]=3&columns[3][name]=&columns[3][searchable]=true&columns[3][orderable]=true&columns[3][search][value]=&columns[3][search][regex]=false&columns[4][data]=4&columns[4][name]=&columns[4][searchable]=true&columns[4][orderable]=true&columns[4][search][value]=&columns[4][search][regex]=false&columns[5][data]=5&columns[5][name]=&columns[5][searchable]=true&columns[5][orderable]=true&columns[5][search][value]=&columns[5][search][regex]=false&columns[6][data]=6&columns[6][name]=&columns[6][searchable]=true&columns[6][orderable]=true&columns[6][search][value]=&columns[6][search][regex]=false&columns[7][data]=7&columns[7][name]=&columns[7][searchable]=true&columns[7][orderable]=true&columns[7][search][value]=&columns[7][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=0&length=25&search[value]=&search[regex]=false&_=1481043431145

Here's the url that doesn't work:

http://engsys.corp.ftr.com/dev/FrontierReports/ServerSide.php?PageName=ApprovedProjects&draw=1&columns[0][data]=0&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=1&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=true&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=2&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&columns[2][search][regex]=false&columns[3][data]=3&columns[3][name]=&columns[3][searchable]=true&columns[3][orderable]=true&columns[3][search][value]=&columns[3][search][regex]=false&columns[4][data]=4&columns[4][name]=&columns[4][searchable]=true&columns[4][orderable]=true&columns[4][search][value]=&columns[4][search][regex]=false&columns[5][data]=5&columns[5][name]=&columns[5][searchable]=true&columns[5][orderable]=true&columns[5][search][value]=&columns[5][search][regex]=false&columns[6][data]=6&columns[6][name]=&columns[6][searchable]=true&columns[6][orderable]=true&columns[6][search][value]=&columns[6][search][regex]=false&columns[7][data]=7&columns[7][name]=&columns[7][searchable]=true&columns[7][orderable]=true&columns[7][search][value]=&columns[7][search][regex]=false&columns[8][data]=8&columns[8][name]=&columns[8][searchable]=true&columns[8][orderable]=true&columns[8][search][value]=&columns[8][search][regex]=false&columns[9][data]=9&columns[9][name]=&columns[9][searchable]=true&columns[9][orderable]=true&columns[9][search][value]=&columns[9][search][regex]=false&columns[10][data]=10&columns[10][name]=&columns[10][searchable]=true&columns[10][orderable]=true&columns[10][search][value]=&columns[10][search][regex]=false&columns[11][data]=11&columns[11][name]=&columns[11][searchable]=true&columns[11][orderable]=true&columns[11][search][value]=&columns[11][search][regex]=false&columns[12][data]=12&columns[12][name]=&columns[12][searchable]=true&columns[12][orderable]=true&columns[12][search][value]=&columns[12][search][regex]=false&columns[13][data]=13&columns[13][name]=&columns[13][searchable]=true&columns[13][orderable]=true&columns[13][search][value]=&columns[13][search][regex]=false&columns[14][data]=14&columns[14][name]=&columns[14][searchable]=true&columns[14][orderable]=true&columns[14][search][value]=&columns[14][search][regex]=false&columns[15][data]=15&columns[15][name]=&columns[15][searchable]=true&columns[15][orderable]=true&columns[15][search][value]=&columns[15][search][regex]=false&columns[16][data]=16&columns[16][name]=&columns[16][searchable]=true&columns[16][orderable]=true&columns[16][search][value]=&columns[16][search][regex]=false&columns[17][data]=17&columns[17][name]=&columns[17][searchable]=true&columns[17][orderable]=true&columns[17][search][value]=&columns[17][search][regex]=false&columns[18][data]=18&columns[18][name]=&columns[18][searchable]=true&columns[18][orderable]=true&columns[18][search][value]=&columns[18][search][regex]=false&columns[19][data]=19&columns[19][name]=&columns[19][searchable]=true&columns[19][orderable]=true&columns[19][search][value]=&columns[19][search][regex]=false&columns[20][data]=20&columns[20][name]=&columns[20][searchable]=true&columns[20][orderable]=true&columns[20][search][value]=&columns[20][search][regex]=false&columns[21][data]=21&columns[21][name]=&columns[21][searchable]=true&columns[21][orderable]=true&columns[21][search][value]=&columns[21][search][regex]=false&columns[22][data]=22&columns[22][name]=&columns[22][searchable]=true&columns[22][orderable]=true&columns[22][search][value]=&columns[22][search][regex]=false&columns[23][data]=23&columns[23][name]=&columns[23][searchable]=true&columns[23][orderable]=true&columns[23][search][value]=&columns[23][search][regex]=false&columns[24][data]=24&columns[24][name]=&columns[24][searchable]=true&columns[24][orderable]=true&columns[24][search][value]=&columns[24][search][regex]=false&columns[25][data]=25&columns[25][name]=&columns[25][searchable]=true&columns[25][orderable]=true&columns[25][search][value]=&columns[25][search][regex]=false&columns[26][data]=26&columns[26][name]=&columns[26][searchable]=true&columns[26][orderable]=true&columns[26][search][value]=&columns[26][search][regex]=false&columns[27][data]=27&columns[27][name]=&columns[27][searchable]=true&columns[27][orderable]=true&columns[27][search][value]=&columns[27][search][regex]=false&columns[28][data]=28&columns[28][name]=&columns[28][searchable]=true&columns[28][orderable]=true&columns[28][search][value]=&columns[28][search][regex]=false&columns[29][data]=29&columns[29][name]=&columns[29][searchable]=true&columns[29][orderable]=true&columns[29][search][value]=&columns[29][search][regex]=false&columns[30][data]=30&columns[30][name]=&columns[30][searchable]=true&columns[30][orderable]=true&columns[30][search][value]=&columns[30][search][regex]=false&columns[31][data]=31&columns[31][name]=&columns[31][searchable]=true&columns[31][orderable]=true&columns[31][search][value]=&columns[31][search][regex]=false&columns[32][data]=32&columns[32][name]=&columns[32][searchable]=true&columns[32][orderable]=true&columns[32][search][value]=&columns[32][search][regex]=false&columns[33][data]=33&columns[33][name]=&columns[33][searchable]=true&columns[33][orderable]=true&columns[33][search][value]=&columns[33][search][regex]=false&columns[34][data]=34&columns[34][name]=&columns[34][searchable]=true&columns[34][orderable]=true&columns[34][search][value]=&columns[34][search][regex]=false&columns[35][data]=35&columns[35][name]=&columns[35][searchable]=true&columns[35][orderable]=true&columns[35][search][value]=&columns[35][search][regex]=false&columns[36][data]=36&columns[36][name]=&columns[36][searchable]=true&columns[36][orderable]=true&columns[36][search][value]=&columns[36][search][regex]=false&columns[37][data]=37&columns[37][name]=&columns[37][searchable]=true&columns[37][orderable]=true&columns[37][search][value]=&columns[37][search][regex]=false&columns[38][data]=38&columns[38][name]=&columns[38][searchable]=true&columns[38][orderable]=true&columns[38][search][value]=&columns[38][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=0&length=25&search[value]=&search[regex]=false&_=1481048354261

Mike in IT

ASKER

I tried testing it again today with pasting the URL from the dev tools network tab. Now it when I do that it shows the JSON response on the page. Which is what that file is supposed to be doing, the ServerSide.php file that is.

So I know that the file works, it's just not found when being called from the other file for the Approved Projects report.

zephyr_hex (Megan)

Wow. That is a long url. With that much data, have you considered using POST instead of GET ? There is a limit on the number of characters in an url. However, I can't say whether or not that's causing your problem. The server should return an "URL too long" type of error instead of "Page Not Found". But it's certainly a design consideration you should review.

Mike in IT

ASKER

I actually don't know exactly how it is sent to ServerSide.php since that never shows up in the address bar of the browser. I'm not sure how I'd send it through POST instead of GET, or if it's even going through GET.

This is how I'm initializing it now:

	$(document).ready(function () {

		//console.log("<?php //echo $_GET['PageName']; ?>");

		// Setup - add a text input to each footer cell
		$('#DataTable tfoot th').each(function () {
			var title = $(this).text();
			$(this).html('<input type="text" placeholder="Search ' + title + '" />');
		});

		var table = $('#DataTable').DataTable({
				"lengthMenu" : [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
				"ScrollX" : true,
				"dom" : '<"top"Bilp<"clear">>rt<"bottom"ip<"clear">>',
				"buttons" : [{
						extend : 'collection',
						text : 'Selection',
						buttons : ['selectAll', 'selectNone']
					}, {
						extend : 'collection',
						text : 'Export',
						buttons : ['excel', 'csv', 'pdf']
					}
				],
				"fixedHeader" : {
					header : true,
					footer : true
				},
				"select" : true,
				"processing" : true,
				"serverSide" : true,
				"ajax" : {
					"url" : "./ServerSide.php?PageName=<?php echo $Page; ?>"
				},
		initComplete: function() {
		  var api = this.api();

		  // Apply the search
		  api.columns().every(function() {
			var that = this;

			$('input', this.footer()).on('keyup change', function() {
			  if (that.search() !== this.value) {
				that
				  .search(this.value)
				  .draw();
			  }
			});
		  });
		}
	  });
	});

Open in new window

How would I change that so that it gets sent using POST?

ASKER CERTIFIED SOLUTION

zephyr_hex (Megan)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike in IT

ASKER

@zephyr_hex dude you are a genius!

I've been trying to figure that out for weeks now! I changed it to POST and now it finds the file (and now I have other problems, but at least it finds the file)!

zephyr_hex (Megan)

oh yay! glad that worked.

p.s. i'm a "dude-ette" ;)

Mike in IT

ASKER

Well, thanks for the help. I don't run into too many "dude-ettes" in the field. I do have one more question and since it has to do with the solution that you gave me I thought I'd ask here.

How can I send the PageName through POST?
I was doing it through GET because ServerSide.php is being called from the jquery when the DataTable is being initialized. I don't know enough yet about jquery personally to get the PageName added to the POST request.

zephyr_hex (Megan)

Yes, you can send PageName through POST. A common way to do something like this would be to add a hidden input with name = "PageName" to your form. Fill in the input with the page name when the drop down option is selected. That hidden field will get sent with POST just like any other field on the form.

Mike in IT

ASKER

Again, thank you for your help. I did get this working for me. Though I think that I didn't adequately describe my menu. The menu consists of links, there is no form. I ended up adding to my code a little, though. I have a file called PageName.php where I set the page name based on the menu item that is selected and the link that is in it. It now looks like this:

$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'];
}

if(!isset($Page) || $Page == "")
{
    $Page = $_SERVER['HTTP_REFERER'];
    $Page = str_replace('http://engsys.corp.ftr.com/dev/FrontierReports/ReportPage.php?PageName=','',$Page);
}

Open in new window

Now the $Page variable gets set every time and I've added it to POST in my Serverside.php file like this:

$_POST['PageName'] = $Page;

Open in new window

My menu is a bunch of nested ul and li like this:

<div class="menu-wrap">
    <nav class="menu">
        <ul class="clearfix" id="menu">
            <li><a href="index.php">Home</a></li>
            <li><a href="OPR Reports.php">OPCEN Reports</a>
                <ul class="sub-menu">
                    <li><a href="ReportPage.php?PageName=COEI_OPR">COEI OPR Report</a></li>
                    <li><a href="ReportPage.php?PageName=OSP_OPR">OSP OPR Report</a></li>
                    <li><a href="ReportPage.php?PageName=OPR_COEI">OPCEN COEI Report</a></li>
                    <li><a href="ReportPage.php?PageName=OPR_OSP">OPCEN OSP Report</a></li>                    
                    <li><a href="ReportPage.php?PageName=EOJ">EOJ Report</a></li>
                    <li><a href="MaterialTracking.php">Material Tracking</a>

Open in new window