[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

Pass session variable to PHP page

Hi all.

I'm working on a webpage that displays some Google Charts based on the date parameter selected by the end user. There are 3 files:

(1) Reports1.php: The end user selects the date parameter. They click the "Run" button and it directs them to ChartTest2.html

(2) ChartTest2.html displays the Google Charts. It calls the file GetChartData1.php to get the data to populate the charts.

(3) GetChartData1.php: Selects the data to display on the charts in ChartTest2.html

Below is the code I have so far, I am not able to display anything in the charts after the end user selects the date and clicks the Run button in Reports1.php. Is it that my "ReportDate" session is empty?

Reports1.php:
<?php 
require("common.php");
$_SESSION['ReportDate'] = $_POST['workscheduledate'];
?>
<!DOCTYPE html>
<head>
  <title>Web App</title>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <link rel="stylesheet" href="style.css" type="text/css" />
  <link href="iphone-icon1.png" rel="apple-touch-icon">
  <script type="text/javascript" src="js/calendarDateInput.js">
  </script>
  </head>
<body>
      				
<div class="wrapper">
    <div id="logo"></div>
    <form class="form4" action="Reports1.php" method="post">
<div class="formtitle4">Run Report</div>

        	
  
            
           <div class="input nobottomborder">
                 <div class="inputtext">New Schedule:</div>
                 

             
             <div style="width:80%;margin-left:40%;">
                <script>DateInput('workscheduledate', true, 'YYYY-MM-DD')</script>
                </div>
            </div>       
             
   
         
			<div class="buttons" align = center>

				<input class="button1"  type="submit" formaction="ChartTest2.html" value="Run" />
				<input class="button1"  type="submit" formaction="Menu.php" value="Menu" /> 

			</div>		

</form>
</body>
</html>

Open in new window


ChartTest2.html:
<html>
  <head>
    <!--Load the AJAX API-->
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type" />
    <meta content="utf-8" http-equiv="encoding" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">
google.load("visualization", "1.1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart1);

      // Callback that creates and populates a data table,
      // instantiates the chart, passes in the data and draws it.
       
      function drawChart1() {
        var jsonData1 = $.ajax({
          url: "GetChartData1.php",
          dataType:"json",
          async: false
          }).responseText;
        
        // Create our data table out of JSON data loaded from server
        var data = new google.visualization.DataTable(jsonData1);
     
        // Set chart options
        var options = {'title':'Super Slab Workload',
                       'width':1300,
                       'height':700};

                        
        var view = new google.visualization.DataView(data);
        
        view.setColumns([0, 1,
                       { calc: "stringify",
                         sourceColumn: 1,
                         type: "string",
                         role: "annotation" },
                         2,{ calc: "stringify",
                         sourceColumn: 2,
                         type: "string",
                         role: "annotation" },3,{ calc: "stringify",
                         sourceColumn: 3,
                         type: "string",
                         role: "annotation" }
                       ]);

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div1'));
        
        chart.draw(view, options);
        
      
      }
</script>
</head>
<body>
    <!--Div that will hold the pie chart-->
    <div id="chart_div1"></div> 
  </body>
</html>

Open in new window


GetChartData1.php:
<?php 
require("common.php"); 
 $query ='SELECT Concat(x.firstname ," " ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND CreateDate > ":reportdate"  group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND CreateDate > ":reportdate" group by firstname, lastname) x Group by Concat(x.firstname ," " ,x.lastname) order by Concat(x.firstname ," " ,x.lastname) ';
       $query_params = array( 
            ':reportdate' => $_SESSION['ReportDate']
           
            ); 
$stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params);
            $data = $stmt->fetchAll();
$rows = array();
    $table = array();


    $table['cols'] = array(
    array('label' => 'Name', 'type' => 'string'),
    array('label' => 'Dried In', 'type' => 'number'),
    array('label' => 'Slab', 'type' => 'number'),
    array('label' => 'Total', 'type' => 'number')
	);

    foreach($data as $row) {
      $temp = array();
      $temp[] = array('v' => (string) $row['Name']); 
      $temp[] = array('v' => (int) $row['DriedIn']); 
      $temp[] = array('v' => (int) $row['Slab']); 
      $temp[] = array('v' => (int) $row['Total']);
      $rows[] = array('c' => $temp);
    }

$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;


?>

Open in new window

0
Sim1980
Asked:
Sim1980
  • 8
  • 5
  • 3
  • +2
3 Solutions
 
Marco GasiFreelancerCommented:
Is the first line of common.php "session_start();"? if yes, then the problem is with Ajax,but if you don't have that line in common.php, put it at the very top and retry.
0
 
Sim1980Author Commented:
Yes, "session_start();" is in the common.php file.
0
 
Chris StanyonCommented:
In GetChartData1, try adding the session variable to table array and then examine it in your ajax call. At least that way, you'll know whether it's being read correctly. If not, then maybe post your common.php file for us to take a look at
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
This PHP instruction is almost always your best friend:
http://php.net/manual/en/function.var-dump.php
0
 
zappafan2k2Commented:
On a side note, you should always have the following line in any PHP script that gets called by AJAX (e.g. GetChartData1.php)
header("Cache-Control: no-cache, must-revalidate");

Open in new window

This avoids cached data being used (which might be causing your problem).

On another side note, remember that once you set a session variable, it remains until the session is ended or you use the unset() function.  

On a third side note, line 3 of Reports1.php will generate warnings in your log files every time a user loads the page for the first time.  I usually use something like
if (isset($_POST['workscheduledate'])) {  ... }

Open in new window


That said, are you using Firefox with the Firebug plugin by any chance?  It is immensely helpful in with debugging AJAX calls because it shows you what data is being sent and returned.

Have you looked in your server logs to see if there are any errors or warnings?
0
 
Ray PaseurCommented:
@zappafan2k2: Great suggestion about Firebug!
0
 
Sim1980Author Commented:
Thanks.

Ok, I'm trying to see where the issue may be so I commented out the session variable and instead changed the code to do a simple query based on the date parameter entered. Doing that it does not return any records, what am I doing wrong with the date parameters? I put # # on each end of the parameter but that didn't work. If I hard code the parameter to let's say '2013-10-28' then it displays data.

 $query_params = array( 
':reportdate' => $_POST['workscheduledate']
          );
   $query ="SELECT Concat(x.firstname ,'' ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND CreateDate > '#:reportdate#'  group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND CreateDate > '#:reportdate#' group by firstname, lastname) x Group by Concat(x.firstname ,'' ,x.lastname) order by Concat(x.firstname ,'' ,x.lastname) ";
    
$stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params);
$data = $stmt->fetchAll();

Open in new window

0
 
Chris StanyonCommented:
Firstly, you don't need the #. Secondly, you should cast the POST['workscheduledate'] to a valid date form. Thirdly, wrap your db bits in a try/catch block. Finally, you don't need the colon in the $data array (although I don't think it'll break by using it)

$date = new DateTime($_POST['workscheduledate']);
$query_params = array( 
   'reportdate' => $date->format('Y-m-d')
);

//use this to make sure your date is being parsed properly
var_dump($query_params);

$query = "...";

try {
   $stmt = $db->prepare($query); 
   $stmt->execute($query_params);
   $data = $stmt->fetchAll();
} catch (Exception $e) {
   //oops, we have an error
   var_dump($e->errorInfo);
}

Open in new window

0
 
Sim1980Author Commented:
Ok I tried the following, but no data is displayed.

require("common.php"); 
header("Cache-Control: no-cache, must-revalidate");

$date = new DateTime($_POST['workscheduledate']);
$query_params = array( 
':reportdate' => $date->format('Y-m-d')
          );

 var_dump($query_params);

 $query ="SELECT Concat(x.firstname ,'' ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND CreateDate > ':reportdate'  group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND CreateDate > ':reportdate' group by firstname, lastname) x Group by Concat(x.firstname ,'' ,x.lastname) order by Concat(x.firstname ,'' ,x.lastname) ";

 try {
   $stmt = $db->prepare($query); 
   $stmt->execute($query_params);
   $data = $stmt->fetchAll();
} catch (Exception $e) {
 var_dump($e->errorInfo);
}

Open in new window

0
 
Ray PaseurCommented:
It's a pretty complex query.  Are you certain (ie: tested and working) of the subqueries?  What kind of data do you get back from those?

Try var_dump($query) so you can see what  the variable actually contains.

Then try EXPLAIN SELECT.
0
 
Sim1980Author Commented:
When I do the following code, it displays the results. But when I remove the hardcoded date and put the parameter, that's when it displays nothing.

$query ='SELECT Concat(x.firstname ," " ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND CreateDate > "2013-10-01"  group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND CreateDate > "2013-10-01" group by firstname, lastname) x Group by Concat(x.firstname ," " ,x.lastname) order by Concat(x.firstname ," " ,x.lastname) ';

$stmt = $db->prepare($query); 
   $result = $stmt->execute();
   $data = $stmt->fetchAll();

Open in new window


I then did the var_dump($query); as Ray suggested and in the WHERE statement it shows ":reportdate" instead of the actual date. The parameter is not being placed in the query.
0
 
Ray PaseurCommented:
Try omitting the quote marks around the :reportdate
0
 
zappafan2k2Commented:
Okay.  You're using PDO, right?  Don't put quotes around the variable placeholder.

Please give this a try:
require("common.php"); 
header("Cache-Control: no-cache, must-revalidate");
if (isset($_POST['workscheduledate'])) {
    $date =date('Y-m-d', strtotime($_POST['workscheduledate']));
} else {
    die('Date not set');
}

 $query ="SELECT Concat(x.firstname ,'' ,x.lastname) as Name, sum(x.Slab) as Slab,
     SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from 
    (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn 
    FROM Users INNER JOIN FieldSuperDataEntry ON 
    Users.userid = FieldSuperDataEntry.UserID 
    Where Type = 1 AND CreateDate > :reportdate  
    group by firstname, lastname 
    union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn 
    FROM Users INNER JOIN FieldSuperDataEntry ON 
    Users.userid = FieldSuperDataEntry.UserID 
    Where Type = 2 AND CreateDate > :reportdate 
    group by firstname, lastname) x 
    Group by Concat(x.firstname ,'' ,x.lastname) 
    order by Concat(x.firstname ,'' ,x.lastname) ";

 try {
   $stmt = $db->prepare($query); 
   $stmt->bindParam(':reportdate', $date);
   $stmt->execute();
   $data = $stmt->fetchAll();
} catch (Exception $e) {
 var_dump($e->errorInfo);
}

Open in new window

0
 
Ray PaseurCommented:
Article edited to emphasize the importance of omitting the quotes.  
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

The colon-prefixed strings are placeholders, not quoted variable names.  The are sent to PDO::Prepare() where they are used to match keys in the inputs that are passed to PDOStatement::BindParam() and / or PDOStatement:: Execute()
0
 
Sim1980Author Commented:
Thanks zappa. I tried your code and got the following error:

array(1) { [0]=> string(5) "HY093" }
Warning: Invalid argument supplied for foreach() in /home/content/12/11231812/html/GetChartData1.php on line 104

It points to:

 foreach($data as $row) {
      $temp = array();
      $temp[] = array('v' => (string) $row['Name']); 
      $temp[] = array('v' => (int) $row['DriedIn']); 
      $temp[] = array('v' => (int) $row['Slab']); 
      $temp[] = array('v' => (int) $row['Total']);
      $rows[] = array('c' => $temp);
    }

Open in new window


Which is where I hold the output of the page.
0
 
Sim1980Author Commented:
Ok. I tried the code below but I get the following error:

Parse error: syntax error, unexpected ':', expecting ')' in /home/content/12/11231812/html/GetChartData1.php on line 39

Line 39 is:

:reportdate => $date->format('Y-m-d')

Open in new window


require("common.php"); 
header("Cache-Control: no-cache, must-revalidate");

 $date = new DateTime($_POST['workscheduledate']);

  $query ='SELECT Concat(x.firstname ," " ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND CreateDate > :reportdate  group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND CreateDate > :reportdate group by firstname, lastname) x Group by Concat(x.firstname ," " ,x.lastname) order by Concat(x.firstname ," " ,x.lastname) ';
    $query_params = array( 
  :reportdate => $date->format('Y-m-d')
    );
$stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params);
  $data = $stmt->fetchAll();

Open in new window

0
 
Sim1980Author Commented:
I got it to work. It turns out I cannot use the same parameter twice in the same query. So I did:

 $query_params = array(
 ':reportdate1' => $_POST['workscheduledate'],
     ':reportdate2' => $_POST['workscheduledate']

$query ='SELECT Concat(x.firstname ," " ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND CreateDate > :reportdate1  group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND CreateDate > :reportdate2 group by firstname, lastname) x Group by Concat(x.firstname ," " ,x.lastname) order by Concat(x.firstname ," " ,x.lastname) ';
   
 );

Open in new window


And that made the difference, I now see the data in the output.
0
 
Chris StanyonCommented:
Couple of things of note. The reason you get the parse error in this line:

:reportdate => $date->format('Y-m-d')

is because you haven't put the reportdate in quotes. You build the parameter array like this:

$query_params = array(
   'reportdate1' => $date->format('Y-m-d'),
   'reportdate2' => $date->format('Y-m-d')
);

Open in new window

When you var_dump($query) it will show the place holder (:reportdate). The named parameters don't get replaced - they get sent to the server separately.
0
 
Sim1980Author Commented:
I'll close this question since I derailed it from the original question. I'll post another question for the session portion since at least I know the query works when passing parameters.

Thank you all for your help!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now