通过复选框选择，通过PHP将MySQL表导出为CSV

Question

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

Answer 1

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;
?>