Nicole Foster Nicole Foster - 5 months ago 13
Javascript Question

Session Variables in Datatables Plugin

I am creating a PHP/MySQL based application and it need to be able to carry over session variables into the DataTable plugin I use on a page in my app. The application is rather complicated, so I will explain how it works before I ask specific questions.

On index.php, there is a dropdown menu that shows the departments that use this application within my organization. The department list is generated by a mySQL table that has their department name and department code. The

$dept
variable stores the department code value from the selected option in the dropdown menu on submit. In turn, the
$_SESSION["department"]
variable stores
$dept
and redirects to the checkin page if successful.

<?php
require_once('connection.php');
session_start();

if (isset($_POST['submit']))
{
$dept = $_POST['dept'];
$_SESSION["department"] = $dept;
header("Location: checkin.php");
}
?>
<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta http-equiv="x-ua-compatible" content="ie=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Visitor Management</title>
<link rel="stylesheet" href="css/foundation.min.css" />
<link rel="stylesheet" href="css/app.css" />
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/24365087-b739-4314-af6e-741946b60bef.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/b05259d9-ca62-44a8-8a19-d3facdbd64df.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/2603d516-f938-4b52-ae3a-11d25bb4c555.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/510266cf-74ab-4fa8-9b39-dd37b90d6ab0.css"/>
</head>
<body>

<!-- nav -->
<div class="top-bar">
<div class="top-bar-left">
<ul class="menu">
</ul>
</div>
<div class="top-bar-right">
</div>
</div>

<div class="row text-center" style="margin-top: 5%;">

<h1>Syracuse University</h1>
<h2>Visitor Management</h2>
<br/>
<form id="dept" method="post" name="dept">
<?php

echo "<select name='dept'>";
echo '<option>'.'Please select a department'.'</option>';
$query = mysqli_query($VisitorManagement, "SELECT * FROM departments");

while($row=mysqli_fetch_array($query))
{
echo "<option value='". $row['code']."'>".$row['name']
.'</option>';


}

echo '</select>';



?>

<input type="submit" class="button" value="Submit" name="submit">
</form>

</div>

<script src="js/vendor/jquery.min.js"></script>
<script src="js/vendor/what-input.min.js"></script>
<script src="js/foundation.min.js"></script>
<script src="js/app.js"></script>
</body>
</html>


The session variable is then carried throughout the site and is used to determine which table needs to be shown. For instance, in checkin.php, we need to display staff members in a dropdown list. We have multiple tables based on the departments using the application. One table we have is called
ts_staff
If the session variable is stored as the string
ts
, we do the following steps to make sure the app is connecting to the right database:


  1. We store the session variable from index.php into a global variable on checkin.php
    $dept = $_SESSION[department];

  2. We create another new variable to concatenate the global variable and the _staff string which is used in all our mySQL staff tables:
    $staffTable = $dept . "_staff";

  3. Lastly, we use the
    $staffTable
    variable as the database table that needs to be displayed:
    $query = mysqli_query($VisitorManagement, "SELECT * FROM {$staffTable}");



Here's the full checkin.php code:

<?php
// connect to database
require_once('connection.php');
session_start();

//get session variable, if empty, unset and logout
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: index.php");
} else {
$dept = $_SESSION[department];
}

//submit values on submit
if (isset($_POST['submit']))
{
// store form data values
$suid = mysqli_real_escape_string($VisitorManagement, $_POST['suid']);
$staff = mysqli_real_escape_string($VisitorManagement, $_POST['staff']);

$checkinTable = $dept . "_checkin";
// insert varaibles into table rows
$sql = "INSERT INTO {$checkinTable} (suid, staffMember) VALUES ('$suid', '$staff')";

// check if row was inserted correctly
if (mysqli_query($VisitorManagement, $sql)) {
header('Location: thank-you.php');
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($VisitorManagement);
}

}


?>

<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta http-equiv="x-ua-compatible" content="ie=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Visitor Management</title>
<link rel="stylesheet" href="css/foundation.min.css" />
<link rel="stylesheet" href="css/app.css" />
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/24365087-b739-4314-af6e-741946b60bef.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/b05259d9-ca62-44a8-8a19-d3facdbd64df.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/2603d516-f938-4b52-ae3a-11d25bb4c555.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/510266cf-74ab-4fa8-9b39-dd37b90d6ab0.css"/>
</head>
<body>

<!-- nav -->
<div class="top-bar">
<div class="top-bar-left">
<ul class="menu">
<li><a href="checkin.php" class="active">Check-In</a></li>
</ul>
</div>
<div class="top-bar-right">
<ul class="menu">
<li><a href="login.php">Admin Login</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</div>

<div class="row text-center" style="margin-top: 5%;">

<h1>Syracuse University</h1>
<!-- replace with whatever department they select -->
<h2>Technical Services</h2>
</div>

<div class="row">
<form id="checkin" method="post" name="checkin">
<div class="row">
<div class="medium-12 columns">
<label>Please Swipe Your SUID Card
<input type="text" placeholder="SUID Number Here" id="suid" name="suid" required>
</label>
</div>
<div class="medium-12 columns">
<label>Who Are You Here to See?
<?php

$staffTable = $dept . "_staff";

echo "<select name='staff'>";
echo '<option value="">'.'Please select a staff member'.'</option>';
$query = mysqli_query($VisitorManagement, "SELECT * FROM {$staffTable}");

while($row=mysqli_fetch_array($query))
{
echo "<option value='". $row['fullName']."'>".$row['fullName']
.'</option>';

}

echo '</select>';

// close connection
mysqli_close($VisitorManagement);
?>
</label>
</div>
<div class="medium-12 columns">
<input type="submit" class="button" value="Submit" name="submit">
</div>
</div>
</form>


</div>

<script src="js/vendor/jquery.min.js"></script>
<script src="js/vendor/what-input.min.js"></script>
<script src="js/foundation.min.js"></script>
<script src="js/app.js"></script>
</body>
</html>


The problem is that on reports.php, I am using the DataTables plugin to dynamically organize and filter a table, but I need to be able to carry the session variable over into the plugin so it knows to organize the specific table based on what the user selected on the index.php screen.

reports.php just calls DataTables and using another page, response.php to turn the data from the table into JSON to be displayed. Here's reports.php:

<?php session_start();
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: index.php");
} else {
$dept = $_SESSION[department];
}
$checkinTable = $dept . "_checkin";
?>
<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta http-equiv="x-ua-compatible" content="ie=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Visitor Management</title>
<link rel="stylesheet" href="../css/foundation.min.css" />
<link rel="stylesheet" href="../css/app.css" />
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/24365087-b739-4314-af6e-741946b60bef.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/b05259d9-ca62-44a8-8a19-d3facdbd64df.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/2603d516-f938-4b52-ae3a-11d25bb4c555.css"/>
<link type="text/css" rel="stylesheet" href="http://fast.fonts.net/cssapi/510266cf-74ab-4fa8-9b39-dd37b90d6ab0.css"/>
<link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.css"/>
</head>
<body>
<?php
if (!isset($_SESSION['user'])) {
header("Location: ../login.php"); // If session is not set that redirect to Login Page
}
?>
<div class="top-bar admin">
<div class="top-bar-left">
<ul class="menu">
<li class="menu-text">Visitor Management</li>
</ul>
</div>
<div class="top-bar-right">
<ul class="menu">
<li><a href="logout.php">Logout</a></li>
<li><a href="#">Help</a></li>
</ul>
</div>
</div>

<div class="medium-2 columns dash">
<ul>
<li><a href="dashboard.php">Dashboard</a></li>
<li><a href="staff.php">Staff</a></li>
<li class="active"><a href="reports.php">Reports</a></li>
</ul>
</div>

<div class="medium-10 columns">
<div class="row checkin">
<h2>Reports</h2>

<table class="checkin" id="checkin">
<thead>
<tr>
<th>ID</th>
<th>SUID #</th>
<th>Staff Member</th>
<th>Student Name</th>
<th>Student Email</th>
<th>Check In Date/Time</th>
</tr>
</thead>


</table>

<!--<div class="float-left">
<a href="export.php" class="button success">Export to Excel</a>
</div>
<div class="float-right">
</div>-->

</div>
</div>

<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
<script src="../js/vendor/what-input.min.js"></script>
<script src="../js/foundation.min.js"></script>
<script src="../js/app.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.js"></script>

<script>
$(document).ready(function() {

$('#checkin').DataTable({
"bProcessing": true,
"serverSide": false,
"dom": 'lBfrtip',
"buttons": [
{
extend: 'collection',
text: 'Export',
buttons: [
'copy',
'excel',
'csv',
'pdf',
'print'
]
}
],
"ajax":{
url :"response.php", // json datasource
type: "post", // type of method ,GET/POST/DELETE
data: {}
}
});
});
</script>
</body>
</html>


Here's response.php:

<?php
//include connection file
include_once("../connection.php");

// initilize all variable
$params = $columns = $totalRecords = $data = array();

$params = $_REQUEST;

//define index of column
$columns = array(
0 => 'id',
1 => 'suid',
2 => 'staffMember',
3 => 'studentName',
4 => 'studentEmail',
5 => 'checkinDateTime'
);

$where = $sqlTot = $sqlRec = "";

// check search value exist
if( !empty($params['search']['value']) ) {
$where .=" WHERE ";
$where .=" ( studentName LIKE '".$params['search']['value']."%' ";
$where .=" OR staffMember LIKE '".$params['search']['value']."%' ";
$where .=" OR studentEmail LIKE '".$params['search']['value']."%' ";
$where .=" OR suid LIKE '".$params['search']['value']."%' ";
$where .=" OR checkinDate LIKE '".$params['search']['value']."%' )";
}

// getting total number records without any search
$sql = "SELECT id, suid, staffMember, studentName, studentEmail, date_format(checkinDateTime, '%b %d, %Y, %h:%i %p') as checkinDateTime FROM `ts_checkin`";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {

$sqlTot .= $where;
$sqlRec .= $where;
}


//$sqlRec .= " ORDER BY ". $columns[$params['order'][0]['column']]." ".$params['order'][0]['dir']." LIMIT ".$params['start']." ,".$params['length']." ";

$queryTot = mysqli_query($VisitorManagement, $sqlTot) or die("database error:". mysqli_error($VisitorManagement));


$totalRecords = mysqli_num_rows($queryTot);

$queryRecords = mysqli_query($VisitorManagement, $sqlRec) or die("error to fetch check-in data");

//iterate on results row and create new index array of data
while( $row = mysqli_fetch_row($queryRecords) ) {
$data[] = $row;
}

$json_data = array(
"draw" => intval( $params['draw'] ),
"recordsTotal" => intval( $totalRecords ),
"recordsFiltered" => intval($totalRecords),
"data" => $data // total data array
);

echo json_encode($json_data); // send data as json format
?>


In response.php, I need to able to replace
ts_checkin
, which is shown on this line:

$sql = "SELECT id, suid, staffMember, studentName, studentEmail, date_format(checkinDateTime, '%b %d, %Y, %h:%i %p') as checkinDateTime FROM `ts_checkin`";`


with a variable called
$checkinTable
that would concatenate similar to how checkin.php does with the staff table. So essentially I would like to have
$checkinTable = $dept . "_checkin"
with
$dept
equaling the value of the session variable.

When I go to do this, I get this error from DataTables:
DataTables warning: table id=checkin - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1


Is it possible to get this working? I would like to keep DataTables as its the best way to achieve on-the-fly table filtering, searching, and sorting (plus it's a highly requested feature by our departments). But I need to be able to set the table based on the session variable.

I apologize for the length of this question. If there needs to be clarification, let me know.

Answer

I'm not certain to understand the question...

Are you saying this is not working ?
Did you try it written like this?
(notice the space after FROM. It is necessary!)

$checkinTable = $dept . "_checkin";

$sql = "SELECT id, suid, staffMember, studentName, studentEmail, date_format(checkinDateTime, '%b %d, %Y, %h:%i %p') as checkinDateTime FROM " . $checkinTable;

For sure, $dept has also to be defined.
And the resulting $checkinTable has to be an existing table name.

Ok...
This looks to be missing in your response.php:

session_start();
$dept = $_SESSION[department];
$checkinTable = $dept . "_checkin";
Comments