Phil Phil - 5 months ago 43
PHP Question

Export MySQL table to CSV via PHP by checkbox selection

I need to display a list of results form a survey on a PHP page then export them to a CSV file. The list also acts as a summary that can be clicked thorugh to the full result.

I have all that sorted but now I need to have the CSV export by a check bx selection so that we dont need to download the entire databse each time just the ones we need.

My code so far below.

<div class="resultsList">

<h1>PEEM Results List</h1>
<a class="exportCSV" href="https://domain.com/downloadcsv.php">Export to CSV</a>
<!-- Export CSV button -->
<h3 class="resultsbydate">All results by date</h3>


<div class="resultsListHeader">
<div class="clientidTab">Agency</div>
<div class="clientidTab">Family ID</div>
<div class="clientidName">Name</div>
<div class="clientidTab">Date</div>
<div class="clientidTab"></div>
</div>
<div class="entriesListMain">
<?php
$connection = mysql_connect("localhost", "username", "password"); // Establishing Connection with Server
$db = mysql_select_db("database_name", $connection); // Selecting Database
//MySQL Query to read data
$query = mysql_query("select * from results ORDER BY peemdate DESC", $connection);
while ($row = mysql_fetch_array($query)) {
echo "<div><input type=\"checkbox\" name=\"xport\" value=\"export\"><span>{$row['client_id']}</span> <span>{$row['family_id']}</span> <span>{$row['firstname']} {$row['lastname']}</span> <span>".date("d F Y", strtotime($row['peemdate']))."</span>";
echo "<span><a class=\"parents-button\" href=\"peem-parent-repsonses.php?id={$row['survey_id']}\"><strong>Parent&rsquo;s Review</strong></a></span>";
echo "<span><a href=\"peem-repsonses.php?id={$row['survey_id']}\"><strong>View Results</strong></a></span>";
echo "</div>";
}
?>
</div>
</div>

<?php
if (isset($_GET['id'])) {
$id = $_GET['id'];
$query1 = mysql_query("select * from results where survey_id=$id", $connection);
while ($row1 = mysql_fetch_array($query1)) {
?>

<?php
}
}
?>

<?php
mysql_close($connection); // Closing Connection with Server
?>


And the downloadcsv.php

<?php
$conn = mysql_connect("localhost","username","password");
mysql_select_db("databasename",$conn);

$filename = "peem_results.csv";
$fp = fopen('php://output', 'w');

$query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='realwell_peemfinal' AND TABLE_NAME='results'";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)) {
$header[] = $row[0];
}

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $header);

$query = "SELECT * FROM results";
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) {
fputcsv($fp, $row);
}
exit;
?>


Any help with this would be great, cheers

Updated with a screenshot of what I am trying to achieve
enter image description here

Answer

The initial result set needs to be wrapped in a form which POST to the next page. The Checkbox must send an array of ids to the export script.

<input type='checkbox' name='xport[]' value='ID_OF_THE_ROW_HERE'>

The [ ] after xport means that $_POST['xport'] will be an array of values.


The export page can collapse that array of ids into a comma separated string and to be used the query:

SELECT * FROM results WHERE id IN (4,7,11,30)

<form method="POST" action="downloadcsv.php">

        <h1>PEEM Results List</h1>
        <a class="exportCSV" href="https://domain.com/downloadcsv.php">Export to CSV</a>
        <!-- Export CSV button -->
        <h3 class="resultsbydate">All results by date</h3>


            <div class="resultsListHeader">
                <div class="clientidTab">Agency</div>
                <div class="clientidTab">Family ID</div>
                <div class="clientidName">Name</div>
                <div class="clientidTab">Date</div>
                <div class="clientidTab"></div>
            </div>
            <div class="entriesListMain">
                <?php
                $connection = mysql_connect("localhost", "username", "password"); // Establishing Connection with Server
                $db = mysql_select_db("database_name", $connection); // Selecting Database
                //MySQL Query to read data
                $query = mysql_query("select * from results ORDER BY peemdate DESC", $connection);
                while ($row = mysql_fetch_array($query)) {
                echo "<div><input type='checkbox' name='xport[]' value='{$row['client_id']}'><span>{$row['client_id']}</span> <span>{$row['family_id']}</span> <span>{$row['firstname']} {$row['lastname']}</span> <span>".date("d F Y", strtotime($row['peemdate']))."</span>";
                echo "<span><a class=\"parents-button\" href=\"peem-parent-repsonses.php?id={$row['survey_id']}\"><strong>Parent&rsquo;s Review</strong></a></span>";
                echo "<span><a href=\"peem-repsonses.php?id={$row['survey_id']}\"><strong>View Results</strong></a></span>";
                echo "</div>";
                }
                ?>
            </div>

         </form>

Change $row['client_id'] to the correct value

Then in the export script:

<?php
/*
Expecting $_POST['xport'] array of row ids
*/
if( !isset($_POST['xport']) OR !is_array($_POST['xport']) ) {
    exit('No rows selected for export');
}

$conn = mysql_connect("localhost","username","password");
mysql_select_db("databasename",$conn);

$filename = "peem_results.csv";
$fp = fopen('php://output', 'w');

$query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='realwell_peemfinal' AND TABLE_NAME='results'";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)) {
    $header[] = $row[0];
}   

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $header);

//Cast all ids to integer
$ids = $_POST['xport'];
array_walk($ids, function(&$value, $key) {
    $value = (int)$value;
});

$ids = implode(', ', $ids);

$query = "SELECT * FROM results WHERE id IN ($ids)";
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) {
    fputcsv($fp, $row);
}
exit;
?>
Comments