New to PHP, copied script to export to excel from sql

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.

Maybe post your table layout and the script? I can't tell too much from looking at the end result.

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?

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.

sorry, I'm dumb. thanks.

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.

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?

oh, nevermind..I just messed around with it...and I got it.

Thanks.

Oh wait I spoke too soon. Can I get it to export the header categories at the beginning row?

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.

I replaced, but still no headers showing up.....hmmmmm

Just noticed the last line:
print "$headern$data";
Change it to
print "$header\r\n$data";

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?

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

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.

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?

The time stamp is from this code:

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

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

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

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).