duanqianmou4661 2014-11-13 15:59
浏览 30
已采纳

MySQL查询到CSV格式

I have a database with two tables: core_members and core_fields_content.

core_members has the columns: member_id, name, email core_pfields_content has the columns: member_id, field_1, field_2, etc.

I'd like to output the results of a query which selects the names and emails of the members, according to the custom field value provided in the query and export this to a csv file.

I want the output to appear in columns, but when opened in excel, the code below gives me the following output all in one cell: name;email;test name;testemail@test.com;

How can I change the code to output into columns, with the column name as a heading?

<?php

    $csv_fileName = 'forumuserlist_'.date('Y-m-d').'.csv';

    // database variables
    $hostname = "localhost";
    $user = "";
    $password = "";
    $database = "";

    // Database connecten voor alle services
    mysql_connect($hostname, $user, $password)
    or die('Could not connect: ' . mysql_error());

    mysql_select_db($database)
    or die ('Could not select database ' . mysql_error());

    $csv_export = '';

    $query = mysql_query("SELECT m.name, m.email FROM core_members m, core_pfields_content p WHERE m.member_id=p.member_id AND p.field_4='LPC'");
    $field = mysql_num_fields($query);

    // create line with field names
    for($i = 0; $i < $field; $i++) {
      $csv_export.= mysql_field_name($query,$i).';';
    }
    $csv_export.= '';
    while($row = mysql_fetch_array($query)) {
      // create line with field values
      for($i = 0; $i < $field; $i++) {
        $csv_export.= $row[mysql_field_name($query,$i)].';';
      } 
      $csv_export.= ''; 
    }

    // Export the data and prompt a csv file for download
    header("Content-type: text/x-csv");
    header("Content-Disposition: attachment; filename=".$csv_fileName."");
    echo($csv_export);
?>
  • 写回答

1条回答 默认 最新

  • duanpacan2583 2014-11-13 16:01
    关注

    change the separator from ; to , (COMMA separated values => csv) and enclose the columnnames and values between double quotes

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

报告相同问题?

悬赏问题

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