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]
7  3906 
I cannot see it, but you must start script 'mysql-php-xls.php' with the session_start() right at the top, otherwise you cannot access the $_SESISON variables you stored there in the calling script.
Ronald
Hi,
sorry i had problems with my connection. it was really fustrating cos i had a solution but couldn't post it!!!
any way, the following will generate a excel file....
[PHP]<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=databa se_dump.xls");
?>
<!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>
<table>
<tr>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
</tr>
<tr>
<td>q</td>
<td>w</td>
<td>e</td>
<td>r</td>
</tr>
</table>
</body>
</html>[/PHP]
If you populate the HTML table into a proper table with the data how you want it to look in excel it should work fine.
hope that helps
Here's an example of how it can be done from an MSKB article. I just tried this page but it only runs from IE (because of ActiveX) and you must have Excel installed on the client machine. Generated Excel Content
I don't like this solution myself because it's too browser and product dependent. There were some slick Javascript classes that did graphs written by some guy at Netscape years ago. In hindsight, that approach was the most viable and long lasting and I prefer it because the data prep is done at the server and the graphing is done at the client.
cool thanks! ill try em out.
i can get the export to work (thanks!) but is there a way to call it through a button click? it would be nice to be able to display the table first and allow the user the choice to export or not.
Actually, I got side tracked by the title. The title probably should have been--
Having Problems with MySQL Where Clause
The error is on line 17 of the third block of code:
$startDT AND et.TaskDT<=$end DT
The PHP variables $startDT and $endDT are not surrounded by single quotation marks.
I think this should solve it.
Markus 6,050
Recognized Expert Expert
Actually, I got side tracked by the title. The title probably should have been--
Having Problems with MySQL Where Clause
The error is on line 17 of the third block of code:
$startDT AND et.TaskDT<=$end DT
The PHP variables $startDT and $endDT are not surrounded by single quotation marks.
I think this should solve it.
Clearly this isn't the problem as the OP wouldn't have thanked hsriat for providing a code that got the
export to work (thanks!)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: D |
last post by:
I've created a report with many subreports of aggregate data. I want my
client to be able to export this data to Excel to make her charts, etc. Only
one problem: one of the fields is a "SchoolYear" TEXT field that contains
data such as 2000/01, 2001/02, etc. If I export a Query with this kind of
data to Excel, it gives me the text value of this field; however, when I
export a Report bound to this TEXT field, Excel gives me the values 36526,...
|
by: Kenneth |
last post by:
How do I remove the limitation in Access that deny me from exporting 24000
rows and 17 columns (in a query) into Excel?
Kenneth
|
by: Suffrinmick |
last post by:
Hello Everyone
I've built a database using Access 2000 which includes a query which is
built using a form containing filters. No problem. When I export the
results of the query to excel, (File > Export > Save as type: Microsft
Excel 97-2000) one of the fields, which is a memo field type, loses any
data over the first 255 characters. How do I get all the data into
excel?
Thanks
|
by: Matt |
last post by:
I have code that generates a dataset and sends the results to the user
and automatically opens Excel when the link is clicked. It works fine on
my own machine and one server but I get the following error on another
server:
Problems cam up in the following areas during load: Missing file
http://abc.com/app/mystylesheet.css
The style sheet is present on the website so it isn't missing. It's some
kind of permission problem but I don't...
|
by: bobh |
last post by:
Hi All,
In Access97 I have a table that's greater than 65k records and I'm
looking for a VBA way to export the records to Excel.
Anyone have vba code to export from access to excel and have the code
use multiple excel tabs within a workbook????
Anyone have vba code that would create a temp table write 65,000
records to it, export those to excel, clean the temp table, append the
next 65,000 records, export it to excel with a different...
| | |
by: Snozz |
last post by:
The short of it:
If you needed to import a CSV file of a certain structure on a regular
basis(say 32 csv files, each to one a table in 32 databases), what
would be your first instinct on how to set this up so as to do it
reliably and minimize overhead? There are currently no constraints on
the destination table. Assume the user or some configuration specifies
the database name, server name, and filename+fullpath. The server is
SQL...
|
by: TARHEELS721 |
last post by:
I am trying to send the results of a query that runs when I click a button on my form that is based on a parameter query. The code runs without any errors but nothing is exported into my excel spreadsheet and I can't figure out why does anyone see where I went wrong in my code. I am currently using DAO Recordset. Here is what my code looks like:
Private Sub btnJE_Click() 'Exports qryJE results into excel
On Error GoTo Err_btnJE_Click
...
|
by: myemail.an |
last post by:
Hi all,
I use Access 2007 and have the following problems: when exporting
banal select queries (either to Excel or to a csv file) I find that
exporting often doesn't work and creates a file with the WHOLE
dataset, i.e. including those rows which the criteria of the query
excluded.
For example: let's say I have a database with sales by region. I
create a select query to only show sales from Europe. The query runs
|
by: deedi |
last post by:
Hi,
I've a problem with exporting my query results into excel. When the rows returned are less, export to excel is happening without any issues. When there is large number of rows, IE is showing an error message like "Internet Explorer cannot download file from site. IE was not able to open the internet site. The requested site is either unavailable or cannot be found. Please try again later."
Kindly help me in this regard.
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
