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
acslaterAsked:
Who is Participating?
 
ldbkuttyConnect With a Mentor Commented:
For writing to a simple CSV format file:

<?php

// DB Connection here

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$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 ) ) || ( trim( $value ) == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}

$data = str_replace( "\r" , "" , $data );

if ( trim( $data ) == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>

Make sure you dont output any HTML or echo tags before "header" function is called. (ie) header function should be called before any output is sent to the browser!

For exporting the results into Excel with formatted display and/or additional Excel related tasks, try one of these:

http://www.web-aware.com/biff/
http://sourceforge.net/projects/php-doc-xls-gen/
http://www.apg88.com/apgForm/
0
 
alextr2003frCommented:
yes you can, try to look here : http://www.zend.com/codex.php?id=542&single=1
hope it helps
0
 
Marcus BointonCommented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
PromethylCommented:
My advice is to output the data as a CSV,  using the header specified.
0
 
acslaterAuthor Commented:
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
0
 
djs120Commented:
I get the same error
0
 
ldbkuttyCommented:
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!
0
 
acslaterAuthor Commented:
Well i tried taking out that line but it never worked. Is it something with Internet Explorer. Can post code i have
0
 
PromethylConnect With a Mentor Commented:
HEre' s an example I'm told works. I can't find my copy of when I made it work cross-browser.

My web application allows user to select certain part of my MySQL table for export and the route I want to offer is either XLS file or CSV.

Exporting and saving works just fine in both formats, but ... Excel does not want to show data correctly: instead of the value "441234567890" in one column it displays "4.41234E+11". The routes that I've already explored are:
1. Adding " ' " in front of the numbers to tell excel to treat them as text -- Excel treats info as text BUT displays the '-symbol and confuses users
2. Putting brackets around the value -- Excel ignores them and treats info as numbers and does the E+11 trick again

Is there a way to pass to excel column formats with the data -- number formats, column width, etc?

Or has someone found a way to tell Excel that values should be treated as text without changing information displayed in Excel?

Thanks for your help,
Oleg
PS ... here is the current code:
<?php
// Here we tell the browser that this is an excel file.
header("Content-type: application/octet-stream");
header("Content-disposition: attachment; filename=SMSRealty-Sales-Leads-".date("Y-m-d").".csv");
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);
$user = "user"; //MySQL Username
$password = "password"; // MySQL Username's Password
$dbname= "dbname"; //Database Name
$tablename ="tablename"; // DB Table Name
$server="server"; //MySQL server name

$global_dbh = mysql_connect("localhost", $user, $password); // This establishes the database connection

mysql_select_db($dbname, $global_dbh); // Select what database to use

// You do not need to change the function
echo "Col1,Col2,Col3,Col4,Col5\n";
$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],$row[col3],$row[col4],$row[col5],$row[col6]\n";
}
?>
0
 
acslaterAuthor Commented:
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-disposition: attachment; filename=SMSRealty-Sales-Leads-".date("Y-m-d").".csv");
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\n";
$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],$row[col3],$row[col4],$row[col5],$row[col6]\n";


}
?>

0
 
PromethylCommented:
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-disposition: attachment; filename=MyData-".date("Y-m-d").".csv");
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_assoc($rs)) {
    echo implode('","',$r)."\n";
}
?>
0
 
djs120Commented:
Are you using SSL?  I'm having the same problem using SSL, but similar code works fine on the same server without SSL.
0
 
PromethylCommented:
No. Straight through.
0
 
djs120Connect With a Mentor Commented:
I was asking acslater actually, I think we're both having this problem for the same reason.  I just logged into my bank website and I can't right click->Save target as... on any pages in IE, I get that same error.  But it works in Firefox.  It has something to do with IE and SSL I think.
0
 
acslaterAuthor Commented:
yes we are using ssl so you dont think there is any way about it?
0
 
acslaterAuthor Commented:
ill post it in another open question to see if anyone knows anything bout it? thanks lads
0
 
acslaterAuthor Commented:
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
0
 
djs120Commented:
"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.
0
 
PromethylCommented:
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.
0
 
djs120Commented:
Try this in Internet Explorer Options, Advanced Tab, under Security, make sure "Do not save encrypted pages to disk" is unchecked.
0
 
acslaterAuthor Commented:
yes checked that and it wasnt checked
0
 
acslaterAuthor Commented:
// 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_assoc($rs)) {
    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";
0
 
PromethylCommented:
Negative.

while (NULL!=$r=mysql_fetch_assoc($rs)) {
    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.
0
 
acslaterAuthor Commented:
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
0
 
PromethylCommented:
Do other sites work in SSL, besides this program?
0
 
Marcus BointonCommented:
This is horrible syntax:

while (NULL!=$r=mysql_fetch_assoc($rs)) {

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)) {
0
 
PromethylCommented:
You have your way, I have mine. Both work./
 
0
 
Marcus BointonCommented:
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_assoc($rs)) {

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.
0
 
PromethylCommented:
>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.

 
0
 
ldbkuttyCommented:
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.


0
 
PromethylCommented:
Roger.
0
 
Marcus BointonCommented:
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.
0
 
PromethylCommented:
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.
0
 
Marcus BointonCommented:
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: http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_21372808.html), but I'm always trying to get there...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.