douyin6188 2015-05-24 00:34
浏览 37
已采纳

PHP / MySQLi函数,从3个表生成CSV,但需要单独生成一行

This is my final task on this project and unsure how I can adjust for what I need. I am new to all this, so rather happy I've got this far!

The following function gets data from 3 tables and generates a CSV file (great and works fine) however it splits the file up in 3 sections and ideally I would like all data per order per line.

As it stands, you see the 3 tables in 3 sections but I really need all data from the invoice id within 1 line.

Code:

// download invoice csv sheet
if ($action == 'download_csv'){

    header("Content-type: text/csv"); 

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('.$mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $tables = array('invoices', 'customers', 'invoice_items'); // array of tables need to export

    $file_name = 'invoice-export-'.date('d-m-Y').'.csv';   // file name
    $file_path = 'downloads/'.$file_name; // file path

    $file = fopen($file_path, "w"); // open a file in write mode
    chmod($file_path, 0777);    // set the file permission

    // loop for tables
    foreach($tables as $table) {
        $table_column = array();
        $query_table_columns = "SHOW COLUMNS FROM $table";

        // fetch table field names
        if ($result_column = mysqli_query($mysqli, $query_table_columns)) {
            while ($column = $result_column->fetch_row()) {
                $table_column[] = $column[0];
            }
        }

        // Format array as CSV and write to file pointer
        fputcsv($file, $table_column, ",", '"');

        $query_table_columns_data = "SELECT * FROM $table";

        if ($result_column_data = mysqli_query($mysqli, $query_table_columns_data)) {

            // fetch table fields data
            while ($column_data = $result_column_data->fetch_row()) {
                $table_column_data = array();
                foreach($column_data as $data) {
                    $table_column_data[] = $data;
                }

                // Format array as CSV and write to file pointer
                fputcsv($file, $table_column_data, ",", '"');
            }

        }
    }

    //if saving success
    if ($result_column_data = mysqli_query($mysqli, $query_table_columns_data)) {
        echo json_encode(array(
            'status' => 'Success',
            'message'=> 'CSV has been generated and is available in the /downloads folder for future reference, you can download by <a href="/downloads/'.$file_name.'">clicking here</a>.'
        ));

    } else {
        //if unable to create new record
        echo json_encode(array(
            'status' => 'Error',
            //'message'=> 'There has been an error, please try again.'
            'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
        ));
    }


    // close file pointer
    fclose($file);

    $mysqli->close();

}
  • 写回答

1条回答 默认 最新

  • dongtaidai0492 2015-05-24 00:59
    关注

    Assuming invoice is unique in the invoices table and the customers table, and that contains the common value we use to "match" rows in all of the tables...

    (A quick note here: this is where actual table definitions, including PRIMARY KEYs, UNIQUE KEYs, FOREIGN KEYs, datatypes of columns, and even column comments, would be invaluable in aiding communication, and us not having to make possibly erroneous assumptions...)

    Here's an example of the SQL query you would need to "combine" the rows from all of the tables, one row per invoice_item

    SELECT i.id
         , i.invoice
         , i.foo
         , i.bar
    
         , c.id        AS c_id
         , c.invoice   AS c_invoice
         , c.name      AS c_name
      -- , c.fee
      -- , c.fi
    
         , t.id        AS t_id
         , t.invoice   AS t_invoice
         , t.product   AS t_product
         , t.qty       AS t_qty
      -- , t.fo
      -- , t.fum       
    
      FROM invoice i
      LEFT
      JOIN customer c
        ON c.invoice = i.invoice
      LEFT
      JOIN invoice_item t
        ON t.invoice = i.invoice
     ORDER
        BY i.id
         , c.id
         , t.id
    

    Add whatever columns from each table you need, and make sure each column has a unique name in the returned row (using an AS column_alias after each expression in the SELECT list.)

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题