PHP code to convert a MySQL query to CSV

Question

What is the most efficient way to convert a MySQL query to CSV in PHP please   It would be best to avoid temp files as this reduces portability  dir paths and setting file-system permissions required    The CSV should also include one top line of field names

User · Answer

Export to CSV if   GET  action       export         rsSearchResults   mysql query  sql   db  or die mysql error         out          fields   mysql list fields  database   table   db      columns   mysql num fields  fields         Put the name of all fields   for   i   0   i  lt   columns   i           l mysql field name  fields   i        out         l              out     n         Add all values in the table   while   l   mysql fetch array  rsSearchResults         for   i   0   i  lt   columns   i             out        l   i                    out     n            Output to browser with appropriate mime type  you choose      header  Content-type  text x-csv        header  Content-type  text csv        header  Content-type  application csv      header  Content-Disposition  attachment  filename search results csv      echo  out    exit

User · Answer

An update to  jrgns  with some slight syntax differences  solution    result   mysql query  SELECT   FROM  some table      if    result  die  Couldn  t fetch records      num fields   mysql num fields  result     headers   array     for   i   0   i  lt   num fields   i                    headers     mysql field name  result    i        fp   fopen  php   output    w     if   fp  amp  amp   result                 header  Content-Type  text csv           header  Content-Disposition  attachment  filename  export csv            header  Pragma  no-cache               header  Expires  0           fputcsv  fp   headers           while   row   mysql fetch row  result                       fputcsv  fp  array values  row              die

User · Answer

Look at the documentation regarding the SELECT     INTO OUTFILE syntax   SELECT a b a b INTO OUTFILE   tmp result txt    FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY       LINES TERMINATED BY   n    FROM test table

User · Answer

If you d like the download to be offered as a download that can be opened directly in Excel  this may work for you   copied from an old unreleased project of mine    These functions setup the headers    function setExcelContentType         if headers sent            return false       header  Content-type  application vnd ms-excel        return true     function setDownloadAsHeader  filename        if headers sent            return false       header  Content-disposition  attachment  filename      filename       return true      This one sends a CSV to a stream using a mysql result  function csvFromResult  stream   result   showColumnHeaders   true        if  showColumnHeaders             columnHeaders   array             nfields   mysql num fields  result           for  i   0   i  lt   nfields   i                   field   mysql fetch field  result   i                columnHeaders      field- gt name                    fputcsv  stream   columnHeaders               nrows   0      while  row   mysql fetch row  result             fputcsv  stream   row            nrows               return  nrows      This one uses the above function to write a CSV to a file  given by  filename  function csvFileFromResult  filename   result   showColumnHeaders   true         fp   fopen  filename   w         rc   csvFromResult  fp   result   showColumnHeaders       fclose  fp       return  rc      And this is where the magic happens     function csvToExcelDownloadFromResult  result   showColumnHeaders   true   asFilename    data csv         setExcelContentType        setDownloadAsHeader  asFilename       return csvFileFromResult  php   output    result   showColumnHeaders       For example    result   mysql query  SELECT foo  bar  shazbot FROM baz WHERE boo    foo     csvToExcelDownloadFromResult  result

User · Answer

Check out this question   answer  It s more concise than  Geoff s  and also uses the builtin fputcsv function    result    db con- gt query  SELECT   FROM  some table     if    result  die  Couldn  t fetch records     num fields   mysql num fields  result    headers   array    for   i   0   i  lt   num fields   i           headers     mysql field name  result    i      fp   fopen  php   output    w    if   fp  amp  amp   result        header  Content-Type  text csv        header  Content-Disposition  attachment  filename  export csv         header  Pragma  no-cache        header  Expires  0        fputcsv  fp   headers       while   row    result- gt fetch array MYSQLI NUM             fputcsv  fp  array values  row              die

User · Answer

SELECT   INTO OUTFILE  c  mydata csv  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY     LINES TERMINATED BY   n  FROM my table     the documentation for this is here  http   dev mysql com doc refman 5 0 en select html   or    select    SELECT   FROM table name     export   mysql query    select   or die    Sql error       mysql error         fields   mysql num fields    export     for    i   0   i  lt   fields   i            header    mysql field name   export    i       t      while   row   mysql fetch row   export            line           foreach   row as  value                                                             if      isset   value           value                                  value     t                     else                        value   str replace                value                 value          value           t                      line     value             data    trim   line       n      data   str replace    r          data     if    data                data     n 0  Records Found  n                              header  Content-type  application octet-stream    header  Content-Disposition  attachment  filename your desired name xls    header  Pragma  no-cache    header  Expires  0    print   header n data

[php] PHP code to convert a MySQL query to CSV

Examples related to php

Examples related to mysql

Examples related to csv