Re: [PHP] generate xls file on fly
On Fri, 8 Feb 2008, Per Jessen wrote: Hiep Nguyen wrote: let say that user searched and found 10 records, in the meantime, other users may change any of these 10 records, so if we saved mysql statement and re-run mysql statement again, the result might be different. to prevent this problem, i only want to download records that returned on this page only. This is more of a caching issue - then you determine how long you want to keep the results for, and only re-run the mysql query when the results have gone stale. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php in the last couple days, i've looked into php $_SESSION and kinda get the concept. my question is can i use $_SESSION to store mysql statement? what is the pro/con to store mysql statement in $_SESSION? with $_COOKIE, i can use setrawcookie to avoid urlencoding. is ther anything similar in $_SESSION? thanks, t. hiep -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
--- Hiep Nguyen [EMAIL PROTECTED] wrote: On Fri, 8 Feb 2008, Per Jessen wrote: Hiep Nguyen wrote: let say that user searched and found 10 records, in the meantime, other users may change any of these 10 records, so if we saved mysql statement and re-run mysql statement again, the result might be different. to prevent this problem, i only want to download records that returned on this page only. This is more of a caching issue - then you determine how long you want to keep the results for, and only re-run the mysql query when the results have gone stale. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php in the last couple days, i've looked into php $_SESSION and kinda get the concept. my question is can i use $_SESSION to store mysql statement? what is the pro/con to store mysql statement in $_SESSION? with $_COOKIE, i can use setrawcookie to avoid urlencoding. is ther anything similar in $_SESSION? thanks, t. hiep Hiep, There is no need to use setraw in case of session. Because every session variable keeps its value as it is assign to it. Where as in case of cookie by default browser encode the cookie in urlencoding format. To stop this feature of browser we use setrawcookie() function. --- Nirmalya Lahiri [+91-9433113536] Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Thu, Feb 14, 2008 at 9:23 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: On Fri, 8 Feb 2008, Per Jessen wrote: Hiep Nguyen wrote: let say that user searched and found 10 records, in the meantime, other users may change any of these 10 records, so if we saved mysql statement and re-run mysql statement again, the result might be different. to prevent this problem, i only want to download records that returned on this page only. This is more of a caching issue - then you determine how long you want to keep the results for, and only re-run the mysql query when the results have gone stale. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php in the last couple days, i've looked into php $_SESSION and kinda get the concept. my question is can i use $_SESSION to store mysql statement? what is the pro/con to store mysql statement in $_SESSION? with $_COOKIE, i can use setrawcookie to avoid urlencoding. is ther anything similar in $_SESSION? thanks, t. hiep You can easily store a SQL statement in $_SESSION since the statement is just a string. Are you asking if you can store the *result* of the statement execution in $_SESSION? You shouldn't store the SQL statement in cookies. It gives the end user way too much insight into your DB implementation if they can see the actual statement that you will be issuing to the database and it's an even bigger security risk for SQL injection than simply using raw, unescaped form input in a statement without validation! The attacker doesn't even have to think how to create a parameter to escape out of your statement - they can send you DELETE FROM mysql.user or any other wonderful thing they like. Granted, your script should not be using a db user account that has privileges to execute such a statement, but that should give you a clue that this would be a VERY bad idea. URL encoding/decoding isn't really an issue with sessions since the session data is stored internally on the server and does not have to be urlencoded to be sent between the server and the browser in an HTTP header. Andrew
Re: [PHP] generate xls file on fly
On Feb 11, 2008 7:41 AM, Hiep Nguyen [EMAIL PROTECTED] wrote:
any suggestion for a tutorial on session in php??? thanks.
http://www.google.com/search?q=working+with+php+sessions
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Fri, 8 Feb 2008, Jason Pruim wrote:
On Feb 8, 2008, at 10:14 AM, Hiep Nguyen wrote:
On Fri, 8 Feb 2008, Per Jessen wrote:
Hiep Nguyen wrote:
is there anyway to generate this into xls file w/o using fopen
fwrite to the server? my goal is to have a link after the table and
user can click on that link and a save window pop up to allow user to
save to local disk.
Yes - have a link like this:
a href=yourscript.php?parametersGet XLS file/a
in yourscript.php, you evaluate the parameters given and build the XSL
file as output:
header(Content-Type: application/excel);
header(Content-Disposition: attachment; filename=\filename\);
print
.
.
.
.
.
done.
i already got this method, but the problem that i have is the parameters is
mysql statement and it's very long. i don't think a good idea to pass this
in the url. also, the page that i'm working on is a search page, therefore
the mysql statement is very complicate and long. that's why i don't want
to pass via url.
is there way to do within this page???
I have actually done what you are looking for... Here is how I do it:
On the search page, write the search phrase into a session variable so you
can access it from another page and then run this code:
?PHP
$sortOrder = $_SESSION['order'];
$search = $_SESSION['search'];
$select = SELECT * FROM .$table. WHERE blah blah blah blah blah;
why not store the whole SQL statement in a session variable, so it can
work with any table??? i never use session before, but i'll look into it
to see if it works for me.
any suggestion for a tutorial on session in php??? thanks.
$export = mysql_query($select);
$fields = mysql_num_fields($export);
for ($i = 0; $i $fields; $i++) {
$header .= mysql_field_name($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);
if ($data ==) {
$data =\n(0) Records Found!\n;
}
header(Content-type: application/x-msdownload);
header(Content-Disposition: attachment; filename=Export.xls);
header(Pragma: no-cache);
header(Expires: 0);
print $header\n$data;
?
What that does is it writes the current search pattern into an excel file
called Export.xls and downloads it to your harddrive. I am in the process of
re-writing it to work as a function, but have had mixed results so far. I'm
learning functions right now :)
If you know functions, and want to take a stab at re-writing it, I can
provide you with the code I have so far :)
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Fri, 8 Feb 2008, Per Jessen wrote: Hiep Nguyen wrote: is there anyway to generate this into xls file w/o using fopen fwrite to the server? my goal is to have a link after the table and user can click on that link and a save window pop up to allow user to save to local disk. Yes - have a link like this: a href=yourscript.php?parametersGet XLS file/a in yourscript.php, you evaluate the parameters given and build the XSL file as output: header(Content-Type: application/excel); header(Content-Disposition: attachment; filename=\filename\); print . . . . . done. i already got this method, but the problem that i have is the parameters is mysql statement and it's very long. i don't think a good idea to pass this in the url. also, the page that i'm working on is a search page, therefore the mysql statement is very complicate and long. that's why i don't want to pass via url. is there way to do within this page??? t. hiep -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Fri, 8 Feb 2008, Jason Pruim wrote:
On Feb 8, 2008, at 10:14 AM, Hiep Nguyen wrote:
On Fri, 8 Feb 2008, Per Jessen wrote:
Hiep Nguyen wrote:
is there anyway to generate this into xls file w/o using fopen
fwrite to the server? my goal is to have a link after the table and
user can click on that link and a save window pop up to allow user to
save to local disk.
Yes - have a link like this:
a href=yourscript.php?parametersGet XLS file/a
in yourscript.php, you evaluate the parameters given and build the XSL
file as output:
header(Content-Type: application/excel);
header(Content-Disposition: attachment; filename=\filename\);
print
.
.
.
.
.
done.
i already got this method, but the problem that i have is the parameters is
mysql statement and it's very long. i don't think a good idea to pass this
in the url. also, the page that i'm working on is a search page, therefore
the mysql statement is very complicate and long. that's why i don't want
to pass via url.
is there way to do within this page???
I have actually done what you are looking for... Here is how I do it:
On the search page, write the search phrase into a session variable so you
can access it from another page and then run this code:
?PHP
$sortOrder = $_SESSION['order'];
$search = $_SESSION['search'];
$select = SELECT * FROM .$table. WHERE blah blah blah blah blah;
$export = mysql_query($select);
$fields = mysql_num_fields($export);
for ($i = 0; $i $fields; $i++) {
$header .= mysql_field_name($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);
if ($data ==) {
$data =\n(0) Records Found!\n;
}
header(Content-type: application/x-msdownload);
header(Content-Disposition: attachment; filename=Export.xls);
header(Pragma: no-cache);
header(Expires: 0);
print $header\n$data;
?
What that does is it writes the current search pattern into an excel file
called Export.xls and downloads it to your harddrive. I am in the process of
re-writing it to work as a function, but have had mixed results so far. I'm
learning functions right now :)
If you know functions, and want to take a stab at re-writing it, I can
provide you with the code I have so far :)
thank you for the suggestion, but here is another problem that i try to
avoid saving mysql statement to query from the table again.
let say that user searched and found 10 records,
in the meantime, other users may change any of these 10 records,
so if we saved mysql statement and re-run mysql statement again, the
result might be different. to prevent this problem, i only want to
download records that returned on this page only.
thanks,
t. hiep
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Feb 8, 2008 10:14 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: On Fri, 8 Feb 2008, Per Jessen wrote: Hiep Nguyen wrote: is there anyway to generate this into xls file w/o using fopen fwrite to the server? my goal is to have a link after the table and user can click on that link and a save window pop up to allow user to save to local disk. Yes - have a link like this: a href=yourscript.php?parametersGet XLS file/a in yourscript.php, you evaluate the parameters given and build the XSL file as output: header(Content-Type: application/excel); header(Content-Disposition: attachment; filename=\filename\); print . . . . . done. i already got this method, but the problem that i have is the parameters is mysql statement and it's very long. i don't think a good idea to pass this in the url. also, the page that i'm working on is a search page, therefore the mysql statement is very complicate and long. that's why i don't want to pass via url. is there way to do within this page??? t. hiep How are you passing the parameters to THIS page? The solution Per presented would either require the same parameter list as the current page (if going to a URL dedicated to sending the output in the format you are using for the xls) or one additional parameter to the current page if you the current page is written to vary the style of the output based on that parameter's value. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Feb 8, 2008 8:41 AM, Hiep Nguyen [EMAIL PROTECTED] wrote:
hi friends,
i have a php page with the following logic:
html
head
titleDownload/title
/head
table
trtdTitle/tdtdAuthor/td/tr
? $sql = select title,author from book where title != null and author !=
null; ?
? $rs = mysql_query($sql) or die(mysql_error()); ?
? while($row = mysql_fetch_array($rs)) { ?
Hiep,
You should also put all of the code you can into one block. Each
time you ? open and close ? it will take longer to load. Not
noticeably on a single page with one user accessing it, but with full
sites and many simultaneous users, it will be noticeable. This is
because PHP is stepping in and out of parse mode. So simply
concatenate that code as follows:
trtdTitle/tdtdAuthor/td/tr
?
$sql = select title,author from book where title != null and
author != null;
$rs = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($rs)) {
echo trtd.$row[0]./tdtd.$row[1];./td/tr\n;
}
?
trtda href=Download Into Excel File/a/td/tr
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
Hiep Nguyen wrote: is there anyway to generate this into xls file w/o using fopen fwrite to the server? my goal is to have a link after the table and user can click on that link and a save window pop up to allow user to save to local disk. Yes - have a link like this: a href=yourscript.php?parametersGet XLS file/a in yourscript.php, you evaluate the parameters given and build the XSL file as output: header(Content-Type: application/excel); header(Content-Disposition: attachment; filename=\filename\); print . . . . . done. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
Hiep Nguyen wrote:
hi friends,
i have a php page with the following logic:
html
head
titleDownload/title
/head
table
trtdTitle/tdtdAuthor/td/tr
? $sql = select title,author from book where title != null and
author != null; ?
? $rs = mysql_query($sql) or die(mysql_error()); ?
? while($row = mysql_fetch_array($rs)) { ?
trtd?=$row[0];?/tdtd?=$row[1];?/td/tr
? } ?
trtda href=Download Into Excel File/a/td/tr
/table
/html
is there anyway to generate this into xls file w/o using fopen
fwrite to the server? my goal is to have a link after the table and
user can click on that link and a save window pop up to allow user to
save to local disk.
appreciate very much for any input.
t. hiep
Somthing like this works ok, you can further refine it for the purpse
you want.
?php
$q= mysql_query(select * from table);
$count = mysql_num_fields($q);
//print field names
for ($i = 0; $i $count; $i++) {
$header .= mysql_field_name($export, $i).\t;
}
//print data
while($row = mysql_fetch_array($q, MYSQL_NUM))
{
$table[] = implode(\t, $row);
}
$table= $header . \r\n . implode(\r\n, $table);
//set headers that prompt user for download
header(Content-Type: application/vnd.ms-excel);
header(Expires: 0);
header(Cache-Control: must-revalidate, post-check=0, pre-check=0);
header(Content-Disposition: attachment; filename=fileName);
echo $table;
?
Ade
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
On Feb 8, 2008, at 10:14 AM, Hiep Nguyen wrote:
On Fri, 8 Feb 2008, Per Jessen wrote:
Hiep Nguyen wrote:
is there anyway to generate this into xls file w/o using fopen
fwrite to the server? my goal is to have a link after the table and
user can click on that link and a save window pop up to allow user
to
save to local disk.
Yes - have a link like this:
a href=yourscript.php?parametersGet XLS file/a
in yourscript.php, you evaluate the parameters given and build the
XSL
file as output:
header(Content-Type: application/excel);
header(Content-Disposition: attachment; filename=\filename\);
print
.
.
.
.
.
done.
i already got this method, but the problem that i have is the
parameters is mysql statement and it's very long. i don't think a
good idea to pass this in the url. also, the page that i'm working
on is a search page, therefore the mysql statement is very
complicate and long. that's why i don't want to pass via url.
is there way to do within this page???
I have actually done what you are looking for... Here is how I do it:
On the search page, write the search phrase into a session variable so
you can access it from another page and then run this code:
?PHP
$sortOrder = $_SESSION['order'];
$search = $_SESSION['search'];
$select = SELECT * FROM .$table. WHERE blah blah blah blah blah;
$export = mysql_query($select);
$fields = mysql_num_fields($export);
for ($i = 0; $i $fields; $i++) {
$header .= mysql_field_name($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);
if ($data ==) {
$data =\n(0) Records Found!\n;
}
header(Content-type: application/x-msdownload);
header(Content-Disposition: attachment; filename=Export.xls);
header(Pragma: no-cache);
header(Expires: 0);
print $header\n$data;
?
What that does is it writes the current search pattern into an excel
file called Export.xls and downloads it to your harddrive. I am in the
process of re-writing it to work as a function, but have had mixed
results so far. I'm learning functions right now :)
If you know functions, and want to take a stab at re-writing it, I can
provide you with the code I have so far :)
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] generate xls file on fly
Hiep Nguyen wrote: let say that user searched and found 10 records, in the meantime, other users may change any of these 10 records, so if we saved mysql statement and re-run mysql statement again, the result might be different. to prevent this problem, i only want to download records that returned on this page only. This is more of a caching issue - then you determine how long you want to keep the results for, and only re-run the mysql query when the results have gone stale. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
