doubi8512 2016-06-01 05:59 采纳率: 0%
浏览 37
已采纳

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

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

  • 写回答

2条回答 默认 最新

  • doupiao9318 2016-06-05 05:08
    关注

    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;
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥500 高有偿提问!求优化设计微信小程序
  • ¥15 matlab在安装时报错 无法找到入口 无法定位程序输入点
  • ¥15 收益高的广告联盟有哪些
  • ¥15 Android Studio webview 的使用问题, 播放器横屏全屏
  • ¥15 删掉jdk后重新下载,Java web所需要的eclipse无法使用
  • ¥15 uniapp正式环境中通过webapi将本地数据推送到设备出现的跨域问题
  • ¥15 xui建立节点,显示错误
  • ¥15 关于#单片机#的问题:开始、复位、十进制的功能可以实现,但是切换八进制的功能无法实现(按下按键也没有效果),把初始状态调成八进制,也是八进制可以实现但是切换到十进制不行(相关搜索:汇编语言|计数器)
  • ¥15 VINS-Mono或Fusion中feature_manager中estimated_depth是特征的深度还是逆深度?
  • ¥15 谷歌浏览器如何备份抖音网页数据