Solved

Download sql database in a php page in excel format

Posted on 2013-01-14
3
367 Views
Last Modified: 2013-01-15
Hello Experts.


I have a php page that displays a sql database table into an html <table>.
I want to add a link below the html table to download in an excel format the sql table.

How would I do this?
0
Comment
Question by:joao_c
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 250 total points
ID: 38777183
You may want to see this example:
http://snipplr.com/view/16324/
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 38777855
If it is one table, it's easy to do.  The SQL output will be a CSV file.  The CSV file extension is almost universally associated with Excel.

<?php // RAY_mysql_to_csv.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR DBA / HOSTING PROVIDER
$db_user = "??";
$db_word = "??";


// ESTABLISH A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// THE TABLE NAME IS NOT SPECIFIED - ASK CLIENT
if (empty($_GET["q"]))
{
    // RETURN A LIST OF TABLES
    $sql = "SHOW TABLES";
    $res = mysql_query($sql);
    if (!$res)
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }

    // DETERMINE HOW MANY ROWS OF RESULTS WE GOT (ONE FOR EACH TABLE)
    // MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
    $num = mysql_num_rows($res);
    if (!$num)
    {
        echo "<br/>$sql ";
        die("QUERY FOUND NO DATA");
    }
    else
    {
        echo "<br/>$sql ";
        echo "QUERY FOUND $num ROWS OF DATA ";
    }

    // ITERATE OVER THE RESULTS SET TO COLLECT THE TABLE NAMES
    $show_tables = array();
    while ($show_tables = mysql_fetch_array($res))
    {
        $my_tables[] = $show_tables[0];
    }

    // VISUALIZE THE LIST OF TABLES
    echo "<br/><pre>\n";
    print_r($my_tables);
    echo "</pre>\n";

    // SHOW A FORM - ASK CLIENT TO CHOOSE THE TABLE
    $form = <<<FORM
<form>
ENTER TABLE NAME:
<input name="q" />
<input type="submit" />
</form>
FORM;
    echo $form;
    die('CHOOSE NOW, PLEASE');
}


// THE CLIENT HAS SPECIFIED THE TABLE NAME IN THE FORM OR URL GET ARGUMENT
else
{
   $nom = trim($_GET["q"]);
}


// CONSTRUCT THE QUERY USING THE TABLE NAME IN THE URL GET STRING q=
$sql  = "SELECT * FROM ";
$sql .= "`" . mysql_real_escape_string($nom) . "`";

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
if (!$res = mysql_query($sql, $dbcx))
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql ";

    // MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
    if (mysql_errno() == 1146) echo "<br/>TABLE NAMES ARE CASE SENSITIVE ";
    die("<br/>" . $errmsg);
}

// HOW MANY ROWS DID WE GET?
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num) die("SQL $sql RETURNED NO ROWS");

// NOW THAT WE KNOW WE HAVE DATA, ESTABLISH THE PLACE TO PUT THE DATA!
// USE A FILE NAME WITH TABLE NAME, DATE AND TIME TO AVOID CONFUSION
$fname = $nom . date('Ymd\THis') . '.csv';
$fpc   = fopen($fname, 'w');
if (!$fpc) die("FOPEN FAILED FOR $fname");

// GET THE FIRST ROW OF THE RESULTS SET AND GET THE FIELD NAMES
$row = mysql_fetch_assoc($res);
$col = array_keys($row);

// MAN PAGE: http://php.net/manual/en/function.fputcsv.php
fputcsv($fpc, $col);

// WRITE THE FIRST ROW OF THE DATA
fputcsv($fpc, $row);

// ITERATE OVER THE REMAINING DATA
while ($row = mysql_fetch_assoc($res))
{
    fputcsv($fpc, $row);
}

// ALL DONE
fclose($fpc);

// SHOW THE LINK TO THE FILE
echo "<br/>CSV OF $nom CREATED HERE WITH COLUMN TITLES PLUS $num ROWS OF DATA:\n";
echo "<br/><a href=\"$fname\">$fname</a>\n";

Open in new window

Hope that helps, ~Ray
0
 

Author Closing Comment

by:joao_c
ID: 38778932
Thanks guys. Both comments helped me a lot.

Cheers
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question