SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Exporting Data in a CSV format - via website is this possible

    Hi All,

    I am currently learning about SQL (via some sitepoint books) and I was wondering if the following is possible.

    I have a requirement to pull data from an SQL database which needs to be compiled into a .csv file. Currently I manually do this via phpMyAdmin which is a bit messy.

    Ideally this would be coded into a page on the website so at a click of a button the file with the relevant data will be exported and saved locally. (Ie save file as..)

    From my understanding you can query the database and have the data represented in a webpage (with some php help) but can it be set to export as a csv file?

    If you can advise if this is possible or not, I can then focus on learning how to do it and trying it out.

    Thanks

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Yes that's possible.
    Simple example: you want to export the name and age of all users
    Create a file users.php with the following:

    PHP Code:
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="users.csv"');

    $db mysql_connect('your_host''your_username''your_password');
    mysql_select_db('database_name'$db);

    echo 
    'Name;Age'"\n"// CSV headers

    $res mysql_query('SELECT name,age FROM users');
    while (
    $row=mysql_fetch_assoc($res))
    {
       echo 
    '"'$row['name'], '"; '$row['age'], "\n";

    The name needs to be in double quotes because it's a string and can contain ; that would mess up the whole csv. Quoting the values keeps the data "sane".

    Content-Disposition: attachment forces the browser to download the file (show the download dialog to the user) instead of displaying it.

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ScallioXTX,

    Firstly thanks for your conformation and WOW thanks for the sample code.

    I'll have a play with this and see how it performs.

    I really appreciate your assistance and marking the sitepoint forums a valuable resource and learning tool.

    I’ll report back my results.

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ScallioXTX,

    Well a long time since I last wrote but this project has been on the sidelines for a little while (now back into focus)

    I have had a look through your example (which I appreciate, thank you) and whilst a little over my head I’m working my way through it (SQL is very new to me)

    As the output table needs to be very specific in format, there will need to be some blank cells so the data corresponds with the correct cell. Can this be generated by the query, or should I creat a "temp" table for this?

    Many thanks for your assistance.

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,598
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Dan_ View Post
    As the output table needs to be very specific in format, there will need to be some blank cells so the data corresponds with the correct cell. Can this be generated by the query, or should I creat a "temp" table for this?
    Just add extra commas where the blank cells are required.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi felgall,

    Many thanks for your assistance, I will implement your solution!

    I have started playing with the code provided by ScallioXTX and it seems to be putting some of the data into IE, however it does not prompt you to save the data as a CSV, I must have missed something so I will play with this to see.

    One other question I have regarding security, as this piece of script has the database username and password in it, how safe (or secure) is it in it's current form?

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    unfortunately I'm unable to edit the above,

    However i have fixed up the auto-saving of the file. It was a spacing error.

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,598
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Dan_ View Post
    One other question I have regarding security, as this piece of script has the database username and password in it, how safe (or secure) is it in it's current form?
    When you access a database using PHP it is quite normal to put the login data in a php file. THe only other alternative is to get each user to enter it which then requires that you publish it where all your users can see it and thus makes it less secure.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •