Downloading data for use in Excel (from PHP / MySQL)

For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))

Requirement: To allow a visitor to your web site to click on a link that downloads data from the server into a Microsoft Excel spreadhseet.

Solutions - I'm going to offer you two:

a) To send the output as a text file - delimited by tabs between columns, and with new lines for each new line. Add appropriate content type headers (such as octet-stream) to save the file to local disc rather than displaying it, and pass back a file name with a .xls extension to provide direction to the operating system as to how to handle the file. In most environments, Excel will do the import for you.

I have written an example that does this, grabbing data from one of our MySQL databases that logs web site traffic Run the example or see the source of the example

or b) To send the output as an HTML table, in which case Excel will interpret the rows and columns of the tables as its data cells. I've written an example of this too - using an alternative content type to show the flexibility that's available. And I have also put a formula into one of the cells so that you can get excel to fill in the extra boxes for you.

This example just populates a spread sheet with some numbers as a "proof of concept" but it's still a series of useful pointers, I hope. You can Run the example or see the source code

You'll notice in the source code of both examples that I've added in extra headers in my response to ensure that the browser doesn't cache the results - that's done to make sure that you get a fresh download each time you run the scripts, rather than being given stored old data.
(written 2008-01-25)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles

S156 - Interfacing Applications to MySQL Databases
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [663] Python to MySQL - (2006-03-31)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [2381] Checking the database connection manually - (2009-08-28)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)

H112 - PHP - Further Web Page and Network Handling
  [220] When to use Frames - (2005-02-19)
  [314] What language is this written in? - (2005-05-17)
  [345] Spotting a denial of service attack - (2005-06-12)
  [356] Sudoku helper or sudoku cheat - (2005-06-23)
  [372] Time calculation in PHP - (2005-07-08)
  [376] What brings people to my web site? - (2005-07-13)
  [410] Reading a news or blog feed (RSS) in your PHP page - (2005-08-12)
  [425] Caching an XML feed - (2005-08-26)
  [443] Server side scripting of styles to suit the browser - (2005-09-12)
  [451] Accessing a page via POST from within a PHP script - (2005-09-26)
  [484] Setting the file name for a downloaded document - (2005-11-03)
  [537] Daily Image Santafied - (2005-12-22)
  [542] Morning image, afternoon image - (2005-12-26)
  [565] Using PHP to output images, XML, Style sheets, etc - (2006-01-15)
  [603] PHP - setting sort order with an associative array - (2006-02-13)
  [675] Adding PHP tags to an old cgi program - (2006-04-08)
  [767] Finding the language preference of a web site visitor - (2006-06-18)
  [789] Hot answers in PHP - (2006-07-02)
  [847] Image maps for navigation - a straightforward example - (2006-08-28)
  [904] Of course I'll tell you by email - (2006-10-25)
  [936] Global, Superglobal, Session variables - scope and persistance in PHP - (2006-11-21)
  [1009] Passing GET parameters through Apache mod_rewrite - (2006-12-27)
  [1114] PHP Image upload script - (2007-03-21)
  [1183] Improving searches - from OR to AND? - (2007-05-11)
  [1187] Updating a page strictly every minute (PHP, Perl) - (2007-05-14)
  [1210] PHP header() function - uses and new restrictions - (2007-05-30)
  [1355] .php or .html extension? Morally Static Pages - (2007-09-17)
  [1379] Simple page password protection - PHP - (2007-10-04)
  [1485] Copyright and theft of images, bandwidth and members. - (2007-12-26)
  [1495] Single login and single threaded models - Java and PHP - (2008-01-04)
  [1496] PHP / Web 2 logging - (2008-01-06)
  [1505] Script to present commonly used images - PHP - (2008-01-13)
  [1515] Keeping staff up to date on hotel room status - (2008-01-22)
  [1549] http, https and ajp - comparison and choice - (2008-02-22)
  [2632] Shipping a test harness with your class in PHP - (2010-02-12)
  [2679] How to build a test harness into your PHP - (2010-03-16)
  [2729] Uploading a document or image to its own URL via a browser - (2010-04-18)
  [2918] Downloading a report from the web for further local analysis - (2010-08-13)
  [3036] Sending out an email containing HTML from within a PHP page - (2010-11-07)
  [3432] 3 digit HTTP status codes - what are they, which are most common, which should be a concern? - (2011-09-11)
  [3540] Easy session example in PHP - keeping each customers data apart - (2011-12-06)
  [3568] Telling which ServerAlias your visitor used - useful during merging domains - (2012-01-04)
  [3918] Multiple page web applications - maintaining state - PHP - (2012-11-10)
  [4070] Passing variable between PHP pages - hidden fields, cookies and sessions - (2013-04-26)
  [4483] Moving from mysql to mysqli - simple worked example - (2015-05-03)

Back to
Python - formatting objects

Previous and next
or
Horse's mouth home

Forward to
Flipping images on your web page

Some other Articles

Paging Martin Noke, who petitions for more trains from Trowbridge to Swindon
Evening drive across the roof of Wiltshire
Terms and Conditions - Hotel, England - plain English
Flipping images on your web page
Downloading data for use in Excel (from PHP / MySQL)
Python - formatting objects
Pictures you can use - for free - from our library
Autovivification - the magic appearance of variables in Perl
Perl, PHP or Python? No - Perl AND PHP AND Python!

4759 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Like this? ??