acslater
asked on
quick question. Can i import records in Excel using PHP
I have reservation table with a number of fields. Could I somehow show those fields in an excel spreadsheet. How would i if it can be done
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ldbkutty is spot on, but I'd suggest you use the real MIME type for the download rather than the generic octet-stream, which would be:
header("Content-type: application/vnd.ms-excel")
Although strictly speaking, the format could also be correctly specified as text/plain or text/csv as it is really plain text and not a binary Excel file.
http://www.shaftek.org/publications/drafts/mime-csv/draft-shafranovich-mime-csv-03.html
header("Content-type: application/vnd.ms-excel")
Although strictly speaking, the format could also be correctly specified as text/plain or text/csv as it is really plain text and not a binary Excel file.
http://www.shaftek.org/publications/drafts/mime-csv/draft-shafranovich-mime-csv-03.html
My advice is to output the data as a CSV, using the header specified.
ASKER
That code work perfectly in Mozillia Guys but in IE 6 it comes up with an error....
Internet Explorer was not able to open this internet Site...
Is there a work around for this.
It would be greatly appreciated
Internet Explorer was not able to open this internet Site...
Is there a work around for this.
It would be greatly appreciated
I get the same error
1) If the Excel document is opened, close it and try again.
2) From my googling: Remove the "no-cache" header statement - try just for the sake if it works or not!
2) From my googling: Remove the "no-cache" header statement - try just for the sake if it works or not!
ASKER
Well i tried taking out that line but it never worked. Is it something with Internet Explorer. Can post code i have
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
well i tried that code but still didnt download: here is what i tried:
<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream")
header("Content-dispositio
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
include "db_connection.php";
$connect = db_connect();
mysql_select_db($hbc, $connect); // Select what database to use
// You do not need to change the function
echo "Col1,Col2,Col3,Col4,Col5\
$query = mysql_query("SELECT col1, col2, col3, col4, col5, col6 FROM tablename WHERE col7='$variable' AND col8='Y' AND col8 BETWEEN '$startingtime' AND '$endtime'");
while($row = mysql_fetch_array($query))
echo "$row[col1],$row[col2],$ro
}
?>
<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream")
header("Content-dispositio
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
include "db_connection.php";
$connect = db_connect();
mysql_select_db($hbc, $connect); // Select what database to use
// You do not need to change the function
echo "Col1,Col2,Col3,Col4,Col5\
$query = mysql_query("SELECT col1, col2, col3, col4, col5, col6 FROM tablename WHERE col7='$variable' AND col8='Y' AND col8 BETWEEN '$startingtime' AND '$endtime'");
while($row = mysql_fetch_array($query))
echo "$row[col1],$row[col2],$ro
}
?>
Here's the one which worked for me in Firefox and IE. It's been tested and verified.
<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream")
header("Content-dispositio
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
include ('../db.php');
// You do not need to change the function
$rs=sql_query("select author,post_text from hk_posts limit 10");
while (NULL!=$r=mysql_fetch_asso
echo implode('","',$r)."\n";
}
?>
<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream")
header("Content-dispositio
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
//Variables to connect to MySQL database
error_reporting (E_ALL);
include ('../db.php');
// You do not need to change the function
$rs=sql_query("select author,post_text from hk_posts limit 10");
while (NULL!=$r=mysql_fetch_asso
echo implode('","',$r)."\n";
}
?>
Are you using SSL? I'm having the same problem using SSL, but similar code works fine on the same server without SSL.
No. Straight through.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes we are using ssl so you dont think there is any way about it?
ASKER
ill post it in another open question to see if anyone knows anything bout it? thanks lads
ASKER
actually promethyl i didnt see your post for it working in IE ill try it now
Maybe it has something to do with SSL that wont work
Maybe it has something to do with SSL that wont work
"yes we are using ssl so you dont think there is any way about it?"
I certainly hope there is a way to do it with SSL, I just don't know what it is. :) We've found a common thread though, and hopefully someone will know how to fix it. I've tested the exact same code on a non SSL server, and it works fine.
I certainly hope there is a way to do it with SSL, I just don't know what it is. :) We've found a common thread though, and hopefully someone will know how to fix it. I've tested the exact same code on a non SSL server, and it works fine.
I think so. I believe there's some trick to doing it in SSL. If the program works, it's some workaround in the SSL. Get it working in regular HTTP and then go to the secure http.
Try this in Internet Explorer Options, Advanced Tab, under Security, make sure "Do not save encrypted pages to disk" is unchecked.
ASKER
yes checked that and it wasnt checked
ASKER
// You do not need to change the function
$rs=sql_query("select author,post_text from hk_posts limit 10");
while (NULL!=$r=mysql_fetch_asso
echo implode('","',$r)."\n";
}
?>
prmethyl i havnt tried the second one yet but see the echo line should it be:
echo implode('","',$rs)."\n"; and not
echo implode('","',$r)."\n";
$rs=sql_query("select author,post_text from hk_posts limit 10");
while (NULL!=$r=mysql_fetch_asso
echo implode('","',$r)."\n";
}
?>
prmethyl i havnt tried the second one yet but see the echo line should it be:
echo implode('","',$rs)."\n"; and not
echo implode('","',$r)."\n";
Negative.
while (NULL!=$r=mysql_fetch_asso
echo implode('","',$r)."\n";
}
In English pseduocode, for each record for the database, convert the array into a string seperated by quote comma quote, and add a newline break at the end.
You line would implode the recordset resource, which wouldn't work, since it's not an array, it's a stringed resource result.
while (NULL!=$r=mysql_fetch_asso
echo implode('","',$r)."\n";
}
In English pseduocode, for each record for the database, convert the array into a string seperated by quote comma quote, and add a newline break at the end.
You line would implode the recordset resource, which wouldn't work, since it's not an array, it's a stringed resource result.
ASKER
well lads got it working without SSL so it was SSL. ill look into it more and post again to see if anyone can help and let ye know if get it working
Do other sites work in SSL, besides this program?
This is horrible syntax:
while (NULL!=$r=mysql_fetch_asso
This will do the same thing because the while terminates on a non-true return value for the loop condition:
while ($r = mysql_fetch_assoc($rs)) {
while (NULL!=$r=mysql_fetch_asso
This will do the same thing because the while terminates on a non-true return value for the loop condition:
while ($r = mysql_fetch_assoc($rs)) {
You have your way, I have mine. Both work./
Yours works because the operator precedence happens to work out (it would break if you reversed the order) and you have picked a value to compare with that's not a legal return value for the function (great idea), but by a side-effect of the implicit coercion, happens to work. Used with expressions that can legitimately give rise to NULL values, this approach could fail. And this is meant to be guidance for someone less able than yourself?
while (false!=$r=mysql_fetch_ass
would be marginally more acceptable, but it's just wasteful, obfuscating code as that precise comparison is implicit in the definition of while. I mean, would you write garbage like this:
if(true==$a=1)
Comparing to NULL is almost never a good idea - that's why is_null() exists. It's right up there with using isset() to detect the presence of array keys; it's just not reliable.
Whether your syntax works is a secondary consideration - we're meant to be setting a good example here, not creating Zend certification puzzles.
while (false!=$r=mysql_fetch_ass
would be marginally more acceptable, but it's just wasteful, obfuscating code as that precise comparison is implicit in the definition of while. I mean, would you write garbage like this:
if(true==$a=1)
Comparing to NULL is almost never a good idea - that's why is_null() exists. It's right up there with using isset() to detect the presence of array keys; it's just not reliable.
Whether your syntax works is a secondary consideration - we're meant to be setting a good example here, not creating Zend certification puzzles.
>And this is meant to be guidance for someone less able than yourself?
Yes. Indeed. Mine works because the program works the way I wrote the code.
It is more than reliable in my usage.
This is not a flame forum, take your comments elsewhere.
Yes. Indeed. Mine works because the program works the way I wrote the code.
It is more than reliable in my usage.
This is not a flame forum, take your comments elsewhere.
This is neither a flame forum nor an yet-another-forum, its *experts exchange*. If something is unusual or not required and if an expert points out, please accept the fact or try to explain why it is not unusual. Squinky is been in EE for a long time setting up a good example for all PHP experts.
Roger.
I'm not flaming anyone. I've merely pointed out some legitimate technical issues with the way you've chosen to write something, and you have not provided any justification for your choices. It's a purely academic exercise, nothing personal. The code you've been submitting on here has been generally excellent and you're clearly very capable - I was surprised to see a construct like that from you.
> Mine works because the program works the way I wrote the code.
You're relying on a side effect and implicit execution order, directly against the advice of (to cite just one source) http://www.eecs.harvard.edu/~ellard/CS50-95/programming-style.html item 15. It's just not a good way to do it, and especially not a good thing to advise someone else to do.
> It is more than reliable in my usage.
But it's not for you. You're meant to be showing someone else the right way to do stuff.
> Mine works because the program works the way I wrote the code.
You're relying on a side effect and implicit execution order, directly against the advice of (to cite just one source) http://www.eecs.harvard.edu/~ellard/CS50-95/programming-style.html item 15. It's just not a good way to do it, and especially not a good thing to advise someone else to do.
> It is more than reliable in my usage.
But it's not for you. You're meant to be showing someone else the right way to do stuff.
I concede all your points. Last think I want to do is get into an argument with the PHP Wizard.
There are a couple things I want to say for clarity and giving credit where due.
I don't reinvent the wheel. That is, if I see a topic that can be answered by searching the web, I do so. Most of the code I posted here in this thread isn't mine. I defend it on the basis of sheer arrogance on my part.
There are a couple things I want to say for clarity and giving credit where due.
I don't reinvent the wheel. That is, if I see a topic that can be answered by searching the web, I do so. Most of the code I posted here in this thread isn't mine. I defend it on the basis of sheer arrogance on my part.
That's very gracious of you Promethyl. I look forward to working with you in future.
> please accept the fact or try to explain why
I'd say don't necessarily accept the 'fact' - it's perfectly reasonable to question what I, or any other expert says. I try to code in a logical way (at least most of the time), so that if asked, I can justify my choices from first principles. On the other hand, it's reasonable to take a proposition at face value if you don't have grounds to object to it. Also, asking why something is so is not the same as objecting to it. I'm also quite happy to concede a point in the face of logical counter-argument - I certainly don't know everything (I mean, this is just embarrassing - I clearly know nothing about WSDL: https://www.experts-exchange.com/questions/21372808/Building-a-web-service-proxy-class.html), but I'm always trying to get there...
> please accept the fact or try to explain why
I'd say don't necessarily accept the 'fact' - it's perfectly reasonable to question what I, or any other expert says. I try to code in a logical way (at least most of the time), so that if asked, I can justify my choices from first principles. On the other hand, it's reasonable to take a proposition at face value if you don't have grounds to object to it. Also, asking why something is so is not the same as objecting to it. I'm also quite happy to concede a point in the face of logical counter-argument - I certainly don't know everything (I mean, this is just embarrassing - I clearly know nothing about WSDL: https://www.experts-exchange.com/questions/21372808/Building-a-web-service-proxy-class.html), but I'm always trying to get there...
hope it helps