...

View Full Version : New to PHP, copied script to export to excel from sql



t-buck
11-02-2007, 05:32 PM
I found a script online that will export a SQL table and ask you to save as, and it's an excel spreadsheet.

It works and everything, but the data is crazy formatted. Is there a way to get the spreadsheet to look nice?

http://www.foodprotect.org/getfile.php

Like I said, I don't know anything about php, but I was able to get this script working, but I just want the spreadsheet to be formatted when exported.

Thanks for the help.

toddandrae
11-02-2007, 05:36 PM
Maybe post your table layout and the script? I can't tell too much from looking at the end result.

t-buck
11-02-2007, 05:39 PM
Oh yea sorry. That would help, duh.

here's the php

<?php

/*
This is where we declare all the database connection variables and their respective values


/*
This section builds the required query to fetch the data we need. The second line is calling or executing the query, and the third line is counting the number of fields in the database returned by the query we built above.
*/
$select = "SELECT * FROM registration07";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

/*
Here we loop and extract all the field names from our database.
*/
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "t";
}

/*
In this section we are exporting the values from database and writing them to correct columns of our excel spreadsheet.
*/
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\r\n";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\r\n";
}
$line .= $value;
}
$data .= trim($line)."\r\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=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$headern$data";

?>

And I'm not sure what you mean by table layout? like all of the header categories?

toddandrae
11-02-2007, 05:45 PM
First, you can remove the username and password from your post :)

I was just asking how your table was layed out in the database. I'll scan over this and get back to you.

edit: Also, never use root for a script. Set up a user with SELECT access.

t-buck
11-02-2007, 05:46 PM
sorry, I'm dumb. thanks.

toddandrae
11-02-2007, 05:51 PM
The problem comes in with the outputting of the file. The output looks like:

"42"
"1194021077"
"11-02-2007"
"Bob"
"Dole"
"bobby"
"Bob Dole Entepises"
"111 Bob Dole Way"
"Matin"
"SC"
"57551"
"555-555-1212"
That would be why there are no "lines" in the spreadsheet. Replace the "\r\n" with "\t" or "," depending if you want tab delimited or comma seperated.

t-buck
11-02-2007, 06:08 PM
Hey that helped! but now they are all split into cells, but every registrant is on one line. How do I tell the php to go to the next row after exporting 1 registrant?

t-buck
11-02-2007, 06:14 PM
oh, nevermind..I just messed around with it...and I got it.

Thanks.

t-buck
11-02-2007, 06:14 PM
Oh wait I spoke too soon. Can I get it to export the header categories at the beginning row?

toddandrae
11-02-2007, 06:22 PM
replace:


for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "t";
}

with:


$header = '';
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "t";
}


Try that out.

t-buck
11-02-2007, 06:25 PM
I replaced, but still no headers showing up.....hmmmmm

toddandrae
11-02-2007, 06:27 PM
Just noticed the last line:

print "$headern$data";
Change it to

print "$header\r\n$data";

t-buck
11-02-2007, 06:31 PM
Hey that exported the categories, but they are all in the same cell. Did I miss something that will make them distribute through the cells?

t-buck
11-02-2007, 06:33 PM
Hey I got it! I just needed to put a backslash before the t, in your extra code.

You rock! Ok one last question, and I'll leave you alone. Is there a line I can add to the code, that will only export today's registrations? the category label is date

toddandrae
11-02-2007, 06:50 PM
Change your SQL query to SELECT * FROM table WHERE date field is less than one day

It depends on what kind of time stamp you are using on how you would set that query up.

t-buck
11-02-2007, 06:56 PM
The time stamp is from this code:

$date=date("m-d-Y");

So I"m assuming that is some universal php code, that puts todays date on the data?

t-buck
11-02-2007, 07:00 PM
The time stamp is from this code:

$date=date("m-d-Y");

So I'm assuming that is some universal PHP date code?

toddandrae
11-02-2007, 07:29 PM
I can point you in the right direction but don't have anytime to test anything.

You will need to create a timestamp to compare against


$today = date('m-d-Y');


Your query would then look like
SELECT * FROM table WHERE date >= $today

Fumigator
11-02-2007, 09:12 PM
SELECT * FROM table WHERE date >= $today

Keep in mind this will only work reliably if your column named "date" is a "DATE" type column (as opposed to char, varchar, or int).



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum