Hello,
Im still very new to php and am having a problem.
I return a MySQL query to a form in my php page and I want to be able to export the form data to Excel when the users clicks a link (or a button).
I found some code and can apparently export the entire contents of a table in my database, but when i try to limit the export result using variables...i get nothing. I am hoping someone can point me in the right direction?
This is the query that returns the correct results to the form.
[PHP]$query="SELECT ts.TimeSheetID, ts.TaskDT, e.UserName, c.LastName, ts.HoursWorked, ts.Notes
FROM Emp_TimeSheet ts INNER JOIN Employee e ON ts.EmployeeID=e .EmployeeID
INNER JOIN Client c ON ts.ClientID=c.C lientID
WHERE e.EmployeeID IN ($select_ids) AND (ts.TaskDT >= '$Date' AND ts.TaskDT <='$Date2') ORDER BY ts.TaskDT, e.UserName";[/PHP]
In the same page I tried to save the variables to SESSION by:
[PHP]session_start() ;
// to change a variable, just overwrite it
$_SESSION['startDate']=$Date;
$_SESSION['endDate']=$Date2;
$_SESSION['select_ids']=$select_ids;
$_SESSION['querystring']=$query;
session_write_c lose();[/PHP]
There is a link on this same page that calls:mysql-php-xls.php (see code below)
I can export the results when I hard code in the EmployeeIDs and the dates, but I need to get the variables (passed through from the previous page to work).
[PHP]<?php
$Host = "localhost" ;
$User = "user";
$Password = "pswd";
$DBName = "TimeSheet" ;
$TableName = "Emp_TimeSheet" ;
$link = mysql_connect ($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
mysql_select_db ($DBName) or die('Could not select database');
$startDT=$_SESS ION['startDate'];
$endDT=$_SESSIO N['endDate'];
$Employees=$_SE SSION['select_ids'];
$querystring=$_ SESSION['querystring'];
//$select = '$querystring';
$select = "SELECT et.TaskDT, e.UserName, et.HoursWorked, et.Notes FROM Emp_TimeSheet et INNER JOIN Employee e ON et.EmployeeID=e .EmployeeID WHERE EmployeeID IN ($Employees) AND et.TaskDT >= $startDT AND et.TaskDT<=$end DT ORDER BY et.TaskDT, et.EmployeeID";
$export = mysql_query($se lect);
$fields = mysql_num_field s($export);
for ($i = 0; $i < $fields; $i++) {
$csv_output .= mysql_field_nam e($export, $i) . "\t";
}
while($row = mysql_fetch_row ($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value )) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"' , '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n ";
}
$data = str_replace("\r ","",$data) ;
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=databa se_dump.xls");
header("Pragma: no-cache");
header("Expires : 0");
print $csv_output."\n ".$data;
exit;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Downlo ad MySQL Table Code</title>
</head>
<body>
</body>
</html>[/PHP]
Im still very new to php and am having a problem.
I return a MySQL query to a form in my php page and I want to be able to export the form data to Excel when the users clicks a link (or a button).
I found some code and can apparently export the entire contents of a table in my database, but when i try to limit the export result using variables...i get nothing. I am hoping someone can point me in the right direction?
This is the query that returns the correct results to the form.
[PHP]$query="SELECT ts.TimeSheetID, ts.TaskDT, e.UserName, c.LastName, ts.HoursWorked, ts.Notes
FROM Emp_TimeSheet ts INNER JOIN Employee e ON ts.EmployeeID=e .EmployeeID
INNER JOIN Client c ON ts.ClientID=c.C lientID
WHERE e.EmployeeID IN ($select_ids) AND (ts.TaskDT >= '$Date' AND ts.TaskDT <='$Date2') ORDER BY ts.TaskDT, e.UserName";[/PHP]
In the same page I tried to save the variables to SESSION by:
[PHP]session_start() ;
// to change a variable, just overwrite it
$_SESSION['startDate']=$Date;
$_SESSION['endDate']=$Date2;
$_SESSION['select_ids']=$select_ids;
$_SESSION['querystring']=$query;
session_write_c lose();[/PHP]
There is a link on this same page that calls:mysql-php-xls.php (see code below)
I can export the results when I hard code in the EmployeeIDs and the dates, but I need to get the variables (passed through from the previous page to work).
[PHP]<?php
$Host = "localhost" ;
$User = "user";
$Password = "pswd";
$DBName = "TimeSheet" ;
$TableName = "Emp_TimeSheet" ;
$link = mysql_connect ($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
mysql_select_db ($DBName) or die('Could not select database');
$startDT=$_SESS ION['startDate'];
$endDT=$_SESSIO N['endDate'];
$Employees=$_SE SSION['select_ids'];
$querystring=$_ SESSION['querystring'];
//$select = '$querystring';
$select = "SELECT et.TaskDT, e.UserName, et.HoursWorked, et.Notes FROM Emp_TimeSheet et INNER JOIN Employee e ON et.EmployeeID=e .EmployeeID WHERE EmployeeID IN ($Employees) AND et.TaskDT >= $startDT AND et.TaskDT<=$end DT ORDER BY et.TaskDT, et.EmployeeID";
$export = mysql_query($se lect);
$fields = mysql_num_field s($export);
for ($i = 0; $i < $fields; $i++) {
$csv_output .= mysql_field_nam e($export, $i) . "\t";
}
while($row = mysql_fetch_row ($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value )) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"' , '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n ";
}
$data = str_replace("\r ","",$data) ;
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=databa se_dump.xls");
header("Pragma: no-cache");
header("Expires : 0");
print $csv_output."\n ".$data;
exit;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Downlo ad MySQL Table Code</title>
</head>
<body>
</body>
</html>[/PHP]
Comment