Honesty Rocks! truth rules.

Php Screipt To Export Sql To Excel

HOME      >>       Websites and Web Designing

karlosantana

Hi guys title says everything really, googled it none of the scripts work, tried doing my own and I suck too! I'm running a local server using wampp. Basically it's a phone book I need an "admin" page where the user can go and export all contacts to excel spreadsheet. I'm sure I'm just being the idiot I usually am and am missing something very simple. Surely there is a simple script to do that???CheersKyle


rvalkass

Why not just export the data as a CSV file? Using comma separated values is much easier than generating an Excel file, and would allow the file to be opened in pretty much any application you feel like. You'd also be able to read it back in with PHP fairly easily.


karlosantana

The only problem wiuth that is when it's opened will it be opened as a spreadsheet or one long line? Or does excel deal with all that? If it does, we're in business :) Where could I find a script that'll do that, that I can add as a simple button from an Admin area as explained in my last post?


rvalkass

The only problem wiuth that is when it's opened will it be opened as a spreadsheet or one long line? Or does excel deal with all that?

It'll be open with each value in a different cell. Each comma represents the next column, each new line a new row. For example, this is a CSV file that has the cell label for each value:

A1,B1,C1,D1,E1A2,B2,C2,D2,E2A3,B3,C3,D3,E3

Those values, if you open that with a spreadsheet program, will end up in their relevant cells (ie. "B2" will be in cell B2).

If it does, we're in business :) Where could I find a script that'll do that, that I can add as a simple button from an Admin area as explained in my last post?

Have a look at the PHP function fputcsv().

karlosantana

Mate you are the man! It worked I built it and it's awsum! Is there a way that I could filter it out through timestamp? So It'd only show todays submissions?Your still amazing! Thanks againKyle


rvalkass

Is there a way that I could filter it out through timestamp? So It'd only show todays submissions?

Assuming you store the timestamp in the database, it would be fairly easy to do. If you want submissions in the last 24 hours, simply get the current timestamp, subtract 24 hours and only select rows from the database with a timestamp greater than that. For ones submitted during the current day you'd want to fetch the timestamp for either 23:59:59 yesterday or 00:00:00 today and check the timestamp for the row is (equal to or) greater than that value.

If you don't store the timestamp in the database, then no :) If you don't store the time there's no way to filter by it!

karlosantana

Yep it's in the database :). Without sounding really cheaky is there a tutotial you can point me in the direction of or a code that you know?Thanks in advanceKyle


rvalkass

To get the current time:

$current_time = time();

To get the timestamp for "24 hours ago":
$timestamp = time() - (24*60*60);

To get the timestamp for the last occurrence of midnight:
$timestamp = mktime(0, 0, 0);

Then add a WHERE clause to your SQL, such as "WHERE timestamp > $timestamp" to only select those after the timestamp you specify.

Quatrux

I would like to add my two cents, due to people might come from google to this post:

A database can be exported through PHPMyAdmin in a lot of formats.. including Excel spreadsheets.

Also here's a good PHP Class anyone can use: http://www.phpclasses.org/package/1590-PHP-Export-database-table-records-in-Excel-XLS-format.html