dongruo0909
2018-07-26 04:09
浏览 72
已采纳

数据未通过PHP从MySQL完全导出到CSV

Context:

I'm working with the following stack: HTML/CSS, PHP, MySQL, and XAMPP to build a web app that stores and retrieves data entered to a form.

Problem:

I have two files (i) export.php and (ii) export_index.php. As you can see from the picture below the export_index.php file indexes the data and is a complete representation of the data in my MySQL database when viewed locally in the web browser. The export.php file is supposed to output to the local downloads folder the exact same thing you see in the export_index.php file to a .csv format file to be easily opened in Excel.

After reading the documentation and trying to debug it I still can't figure out where the error is but I think that it is coming from either/both of $data[] and $row. Appreciate any help I can get on this bug. Thanks for your time!

Images:

output of index_export.php file

output of export.php file

Code:

index_export.php

<?php
ob_start();

/*
* this file is used to index the data stored in 
* the MySQL database. it will display the data
* on the screen that is in the db but DOESNT
* format the data for excel files. export.php
* does that.
*/


// dn login credentials
include("dbconfig.php");


// connect with credentials held in dbconfig file
$con = new mysqli($server, $user, $pass, $db);


if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
} 


session_start();



// List Data
$query = "SELECT * FROM comp";
if (!$result = mysqli_query($con, $query)) {
    exit(mysqli_error($con));
}


if (mysqli_num_rows($result) > 0) {
    $data = '<table class="table table-bordered">
        <tr>
            <th>Title</th>
            <th>Overview</th>
            <th>Threat Details</th>
            <th>Customer Name</th>
            <th>Field Insight</th>
            <th>Competitor</th>
            <th>ID</th>
            <th>Revenue Damage</th>
            <th>Technology</th>
            <th>Geography</th>
        </tr>
    ';
    while ($row = mysqli_fetch_assoc($result)) {
        $data .= '<tr>
        <td>'.$row['titleBinded'].'</td>
        <td>'.$row['overviewBinded'].'</td>
            <td>'.$row['threatDetailsBinded'].'</td>
            <td>'.$row['customerNameBinded'].'</td>
            <td>'.$row['fieldInsightBinded'].'</td>
            <td>'.$row['competitorBinded'].'</td>
            <td>'.$row['idBinded'].'</td>
            <td>'.$row['revDamageBinded'].'</td>
            <td>'.$row['techBinded'].'</td>  
            <td>'.$row['geoBinded'].'</td>
        </tr>';
    }
    $data .= '</table>';
}

?>



<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Export Database Data</title>
    <!-- Bootstrap CSS File  -->
    <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
    <!--  Header  -->
    <div class="row">
        <div class="col-md-12">
            <h2>Export Data from MySQL to CSV</h2>
        </div>
    </div>
    <!--  /Header  -->

    <!--  Content   -->
    <div class="form-group">
        <?php echo $data ?>
    </div>
    <div class="form-group">
        <button onclick="Export()" class="btn btn-primary">Export to CSV File</button>
    </div>
    <!--  /Content   -->

    <script>
        function Export()
        {
            var conf = confirm("Export users to CSV?");
            if(conf == true)
            {
                window.open("export.php", '_blank');
            }
        }
    </script>
</div>
</body>
</html>

export.php

<?php
ob_start();


// passes in the login credentials
include("dbconfig.php");

// !!!CAUSED A MEMORY LEAK!!!!
// includes the index file
//include("export.php");


// connect with credentials held in dbconfig file
$con = new mysqli($server, $user, $pass, $db);
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}


session_start();   


// list all data in the db
$query = "SELECT * FROM comp";
if (!$result = mysqli_query($con, $query)) {
     exit(mysqli_error($con));
}


$data = array();
if (mysqli_num_rows($result) > 0) {

    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
}


// header labels for the CSV file
header('Content-Type: text/csv; charset=utf-8');

header('Content-Disposition: attachment; filename = comp.csv');

$output = fopen('php://output', 'w');

fputcsv($output, array('Title', 'Overview', 'Threat Details', 'Customer Name', 'Field Insight', 'Competitor', 'ID', 'Revenue Damage', 'Technology', 'Geography') );



// iteratively exports row by row
if (count($comp) > 0) {
    foreach ($data as $row) {
    fputcsv($output, $row);
    }
}

?>

create.sql

drop database if exists comp;
create schema comp;
use comp;

create table comp (
    titleBinded VARCHAR(50),
    overviewBinded VARCHAR(255),
    threatDetailsBinded VARCHAR(255),
    customerNameBinded VARCHAR(30),
    fieldInsightBinded VARCHAR(255),
    competitorBinded VARCHAR(30),
    idBinded INT,
    revDamageBinded INT,
    techBinded VARCHAR(225),
    geoBinded VARCHAR(255),
    PRIMARY KEY (idBinded)
);
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dqan70724 2018-07-26 05:57
    已采纳

    You have done small mistake in export.php file:

    // iteratively exports row by row
     if (count($comp) > 0) {
       foreach ($data as $row) {
          fputcsv($output, $row);
       }
     }
    

    In if condition of above code use

    if(count($data) > 0)
    

    Instead of

     if(count($comp) > 0)
    

    Best of luck!!!

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题