Exporting table to CSV via php button

Question

I am really new to php and everything I have learned is from my school textbook and online research. With that said I am trying to complete an assignment and I am stuck on the last part. for the final part the assignment says to "Create a PHP script that will dump the contents of the employee table into CSV text file that has comma separated values for each record. Each new record should begin on a new line". I have tried many online tutorials but none of them teach how to put this event in a button. I am including my code so you can see the mess I have. It's not to bad but I am sure I can do the same task with much less code. Again I am just starting out and this is the best I can do for now. Could anyone give any suggestions on how this could be done. I have my button on line 140. I also used a db_connect() function so i don't have to write it many times. I can peon use this to read the database before I save as a csv.

Any suggestions would be greatly appreciated. Be warned It's a lot of code to follow.

    <h2>Employee Search</h2>
    <form action="Draft.php" name="dbToCSV" method="GET">
        <input type="submit" name="dbToCSV" value="Export Database to CSV" <?php if     (isset($_GET['dbToCSV']))
                    {
                        //Do this code
                    }
                    else
                    {
                        echo "Error!";
                    } ?>><br />

    </form>
    <form action="Draft.php" method="GET">
        By name: <input type="text" name="searchName">
        <input type="submit" value="Search Name"><br />
    </form>
    <form action="Draft.php" method="GET">
        By language: <input type="text" name="searchLang">
        <input type="submit" value="Search Language"><br />
    </form>

Answer 1

It's 2017 and mysqli is more common than mysql now. So here's a mysqli version of Josh Liptzin's answer:

<?php

/* Attempt MySQL server connection. */  
$connection = mysqli_connect($database_server, $database_username, $database_password, $database_name);

// Check connection
if($connection === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$query = "SELECT * FROM Employee_data";
$result = mysqli_query($connection, $query);

$number_of_fields = mysqli_num_fields($result);
$headers = array();
for ($i = 0; $i < $number_of_fields; $i++) {
    $headers[] = mysqli_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

function mysqli_field_name($result, $field_offset)
{
    $properties = mysqli_fetch_field_direct($result, $field_offset);
    return is_object($properties) ? $properties->name : null;
}

?>

Answer 2

You are literally trying to put the PHP code inside the HTML button. The button can simply be a link to another page (like dump.php), which contains some PHP like the following:

Link:

<a href="dump.php" target="_blank">Download employee data</a>

dump.php:

<?php

$result = mysql_query('SELECT * FROM `employee_data`');
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

?>

Code source: PHP code to convert a MySQL query to CSV

For your own sake you shouldn't copy and paste the above code into your assignment - there's a world of difference between the code you posted and the code above that the grader will instantly pick up on.

Answer 3

So the class is over and I figure I would show how I resolved my issue. this made the most sense to me and it didn't take many lines of code. I am sure there are possibly even shorter ways to do this but for being very new to php i thing I did an ok job with the code.

I basically made an if statement that checks if there are any records in the database then it takes each record field and writes it to the cdv file while adding a comma. I did this code twice. The top code writes the database field names and the second half writes the values in those fields. Thanks for the advice everyone. I am glad I was able to figure it out in time to submit my assignment.

if (isset($_GET['dbToCSV']))
{
    // database connection
    db_connect();

    $query = mysql_query("SELECT * FROM employee_data") or die(mysql_error());
    $number_rows = mysql_num_rows($query);

    if ($number_rows >= 1)
    {
        $filename = "exported_db_" . date("m-d-Y_hia") . ".csv"; // filenme with date appended
        $fp = fopen($filename, "w"); // open file

        $row = mysql_fetch_assoc($query);

        $seperator = "";
        $comma = "";

        foreach ($row as $name => $value)
        {
            $seperator .= $comma . $name; // write first value without a comma
            $comma = ","; // add comma in front of each following value
        }
        $seperator .= "\n";

        echo "Database has been exported to $filename";

        fputs($fp, $seperator);

        mysql_data_seek($query, 0); // use previous query leaving out first row

        while($row = mysql_fetch_assoc($query))
        {
            $seperator = "";
            $comma = "";

            foreach ($row as $name => $value)
            {
                $seperator .= $comma . $value; // write first value without a comma
                $comma = ","; // add comma in front of each following value 
            }

            $seperator .= "\n";

            fputs($fp, $seperator);
        } 

        fclose($fp);

    }
    else
        echo "There are no records in the database to export.";

    mysql_close();   
}

Answer 4

Following is PHP script which I am using.

<?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$table_name = "employees";

$fp = fopen('php://output', 'w');
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$table_name.'.csv"');
header('Pragma: no-cache');
header('Expires: 0');

$result = mysql_query("SELECT * FROM $table_name");
if (!$result) die("Couldn't fetch records");
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = mysql_field_name($result , $i);
}

if ($fp && $result) {
    fputcsv($fp, $headers);
    while ($row = mysql_fetch_assoc($result)) {
        fputcsv($fp, $row);
    }
}

exit;
?>