snipt

Ctrl+h for KB shortcuts

PHP

A way to export data from MySQL table with proper encoding

<?php                                                                                                                                                                                                                                       
/*                                                                                                                                                                                                                                          
 * PHP code to export MySQL data to CSV                                                                                                                                                                                                     
 * http://911-need-code-help.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html                                                                                                                                                   
 *                                                                                                                                                                                                                                          
 * Sends the result of a MySQL query as a CSV file for download
 * Easy to convert to UTF-8.
 */                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                            
 /*                                                                                                                                                                                                                                         
 * establish database connection                                                                                                                                                                                                            
 */                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                            
$conn = mysql_connect('localhost', 'login', 'pass') or die(mysql_error());                                                                                                                                                              
mysql_select_db('database_name', $conn) or die(mysql_error($conn));                                                                                                                                                                             
mysql_query("SET NAMES CP1252");                                                                                                                                                                                                            
                                                                                                                                                                                                                                            
/*                                                                                                                                                                                                                                          
 * execute sql query                                                                                                                                                                                                                        
 */                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                            
$query = sprintf('SELECT field1,field2 FROM table_name');                                                                                                                                                                  
$result = mysql_query($query, $conn) or die(mysql_error($conn));                                                                                                                                                                            
                                                                                                                                                                                                                                            
/*                                                                                                                                                                                                                                          
 * send response headers to the browser                                                                                                                                                                                                     
 * following headers instruct the browser to treat the data as a csv file called export.csv                                                                                                                                                 
 */                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                            
header('Content-Type: text/csv; charset=cp1252');                                                                                                                                                                                           
header('Content-Disposition: attachment;filename=output.csv');                                                                                                                                                                             
                                                                                                                                                                                                                                            
/*                                                                                                                                                                                                                                          
 * output header row (if atleast one row exists)                                                                                                                                                                                            
 */                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                            
$row = mysql_fetch_assoc($result);                                                                                                                                                                                                          
if ($row) {                                                                                                                                                                                                                                 
    echocsv(array_keys($row));                                                                                                                                                                                                              
}                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                            
/*                                                                                                                                                                                                                                          
 * output data rows (if atleast one row exists)                                                                                                                                                                                             
 */                                                                                                                                                                                                                                                                                       
while ($row) {                                                                                                                                                                                                                              
    echocsv($row);                                                                                                                                                                                                                          
    $row = mysql_fetch_assoc($result);                                                                                                                                                                                                      
}                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                            
/*                                                                                                                                                                                                                                          
 * echo the input array as csv data maintaining consistency with most CSV implementations                                                                                                                                                   
 * - uses double-quotes as enclosure when necessary                                                                                                                                                                                         
 * - uses double double-quotes to escape double-quotes                                                                                                                                                                                      
 * - uses CRLF as a line separator                                                                                                                                                                                                          
 */                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                            
function echocsv($fields)                                                                                                                                                                                                                   
{                                                                                                                                                                                                                                           
    $separator = '';                                                                                                                                                                                                                        
    foreach ($fields as $field) {                                                                                                                                                                                                           
        if (preg_match('/\\r|\\n|,|"/', $field)) {                                                                                                                                                                                          
            $field = '"' . str_replace('"', '""', $field) . '"';                                                                                                                                                                            
        }                                                                                                                                                                                                                                   
        echo $separator . $field;                                                                                                                                                                                                           
        $separator = ',';                                                                                                                                                                                                                   
    }                                                                                                                                                                                                                                       
    echo "\r\n";                                                                                                                                                                                                                            
}                                                                                                                                                                                                                                           
?>                                                                                                                                                                                                                                          
                                   
https://snippets.siftie.com/embed/5f9b52b46fdd945d34177b55dcded306/
/raw/5f9b52b46fdd945d34177b55dcded306/
5f9b52b46fdd945d34177b55dcded306
php
PHP
70
2019-03-19T17:26:18
True
False
False
Sep 22, 2012 at 02:50 AM
/api/public/snipt/47496/
-863
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a> <a href="#L-35">35</a> <a href="#L-36">36</a> <a href="#L-37">37</a> <a href="#L-38">38</a> <a href="#L-39">39</a> <a href="#L-40">40</a> <a href="#L-41">41</a> <a href="#L-42">42</a> <a href="#L-43">43</a> <a href="#L-44">44</a> <a href="#L-45">45</a> <a href="#L-46">46</a> <a href="#L-47">47</a> <a href="#L-48">48</a> <a href="#L-49">49</a> <a href="#L-50">50</a> <a href="#L-51">51</a> <a href="#L-52">52</a> <a href="#L-53">53</a> <a href="#L-54">54</a> <a href="#L-55">55</a> <a href="#L-56">56</a> <a href="#L-57">57</a> <a href="#L-58">58</a> <a href="#L-59">59</a> <a href="#L-60">60</a> <a href="#L-61">61</a> <a href="#L-62">62</a> <a href="#L-63">63</a> <a href="#L-64">64</a> <a href="#L-65">65</a> <a href="#L-66">66</a> <a href="#L-67">67</a> <a href="#L-68">68</a> <a href="#L-69">69</a> <a href="#L-70">70</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="cp">&lt;?php</span> </span><span id="L-2"><a name="L-2"></a><span class="cm">/* </span> </span><span id="L-3"><a name="L-3"></a><span class="cm"> * PHP code to export MySQL data to CSV </span> </span><span id="L-4"><a name="L-4"></a><span class="cm"> * http://911-need-code-help.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html </span> </span><span id="L-5"><a name="L-5"></a><span class="cm"> * </span> </span><span id="L-6"><a name="L-6"></a><span class="cm"> * Sends the result of a MySQL query as a CSV file for download</span> </span><span id="L-7"><a name="L-7"></a><span class="cm"> * Easy to convert to UTF-8.</span> </span><span id="L-8"><a name="L-8"></a><span class="cm"> */</span> </span><span id="L-9"><a name="L-9"></a> </span><span id="L-10"><a name="L-10"></a> <span class="cm">/* </span> </span><span id="L-11"><a name="L-11"></a><span class="cm"> * establish database connection </span> </span><span id="L-12"><a name="L-12"></a><span class="cm"> */</span> </span><span id="L-13"><a name="L-13"></a> </span><span id="L-14"><a name="L-14"></a><span class="nv">$conn</span> <span class="o">=</span> <span class="nb">mysql_connect</span><span class="p">(</span><span class="s1">&#39;localhost&#39;</span><span class="p">,</span> <span class="s1">&#39;login&#39;</span><span class="p">,</span> <span class="s1">&#39;pass&#39;</span><span class="p">)</span> <span class="k">or</span> <span class="k">die</span><span class="p">(</span><span class="nb">mysql_error</span><span class="p">());</span> </span><span id="L-15"><a name="L-15"></a><span class="nb">mysql_select_db</span><span class="p">(</span><span class="s1">&#39;database_name&#39;</span><span class="p">,</span> <span class="nv">$conn</span><span class="p">)</span> <span class="k">or</span> <span class="k">die</span><span class="p">(</span><span class="nb">mysql_error</span><span class="p">(</span><span class="nv">$conn</span><span class="p">));</span> </span><span id="L-16"><a name="L-16"></a><span class="nb">mysql_query</span><span class="p">(</span><span class="s2">&quot;SET NAMES CP1252&quot;</span><span class="p">);</span> </span><span id="L-17"><a name="L-17"></a> </span><span id="L-18"><a name="L-18"></a><span class="cm">/* </span> </span><span id="L-19"><a name="L-19"></a><span class="cm"> * execute sql query </span> </span><span id="L-20"><a name="L-20"></a><span class="cm"> */</span> </span><span id="L-21"><a name="L-21"></a> </span><span id="L-22"><a name="L-22"></a><span class="nv">$query</span> <span class="o">=</span> <span class="nb">sprintf</span><span class="p">(</span><span class="s1">&#39;SELECT field1,field2 FROM table_name&#39;</span><span class="p">);</span> </span><span id="L-23"><a name="L-23"></a><span class="nv">$result</span> <span class="o">=</span> <span class="nb">mysql_query</span><span class="p">(</span><span class="nv">$query</span><span class="p">,</span> <span class="nv">$conn</span><span class="p">)</span> <span class="k">or</span> <span class="k">die</span><span class="p">(</span><span class="nb">mysql_error</span><span class="p">(</span><span class="nv">$conn</span><span class="p">));</span> </span><span id="L-24"><a name="L-24"></a> </span><span id="L-25"><a name="L-25"></a><span class="cm">/* </span> </span><span id="L-26"><a name="L-26"></a><span class="cm"> * send response headers to the browser </span> </span><span id="L-27"><a name="L-27"></a><span class="cm"> * following headers instruct the browser to treat the data as a csv file called export.csv </span> </span><span id="L-28"><a name="L-28"></a><span class="cm"> */</span> </span><span id="L-29"><a name="L-29"></a> </span><span id="L-30"><a name="L-30"></a><span class="nb">header</span><span class="p">(</span><span class="s1">&#39;Content-Type: text/csv; charset=cp1252&#39;</span><span class="p">);</span> </span><span id="L-31"><a name="L-31"></a><span class="nb">header</span><span class="p">(</span><span class="s1">&#39;Content-Disposition: attachment;filename=output.csv&#39;</span><span class="p">);</span> </span><span id="L-32"><a name="L-32"></a> </span><span id="L-33"><a name="L-33"></a><span class="cm">/* </span> </span><span id="L-34"><a name="L-34"></a><span class="cm"> * output header row (if atleast one row exists) </span> </span><span id="L-35"><a name="L-35"></a><span class="cm"> */</span> </span><span id="L-36"><a name="L-36"></a> </span><span id="L-37"><a name="L-37"></a><span class="nv">$row</span> <span class="o">=</span> <span class="nb">mysql_fetch_assoc</span><span class="p">(</span><span class="nv">$result</span><span class="p">);</span> </span><span id="L-38"><a name="L-38"></a><span class="k">if</span> <span class="p">(</span><span class="nv">$row</span><span class="p">)</span> <span class="p">{</span> </span><span id="L-39"><a name="L-39"></a> <span class="nx">echocsv</span><span class="p">(</span><span class="nb">array_keys</span><span class="p">(</span><span class="nv">$row</span><span class="p">));</span> </span><span id="L-40"><a name="L-40"></a><span class="p">}</span> </span><span id="L-41"><a name="L-41"></a> </span><span id="L-42"><a name="L-42"></a><span class="cm">/* </span> </span><span id="L-43"><a name="L-43"></a><span class="cm"> * output data rows (if atleast one row exists) </span> </span><span id="L-44"><a name="L-44"></a><span class="cm"> */</span> </span><span id="L-45"><a name="L-45"></a><span class="k">while</span> <span class="p">(</span><span class="nv">$row</span><span class="p">)</span> <span class="p">{</span> </span><span id="L-46"><a name="L-46"></a> <span class="nx">echocsv</span><span class="p">(</span><span class="nv">$row</span><span class="p">);</span> </span><span id="L-47"><a name="L-47"></a> <span class="nv">$row</span> <span class="o">=</span> <span class="nb">mysql_fetch_assoc</span><span class="p">(</span><span class="nv">$result</span><span class="p">);</span> </span><span id="L-48"><a name="L-48"></a><span class="p">}</span> </span><span id="L-49"><a name="L-49"></a> </span><span id="L-50"><a name="L-50"></a><span class="cm">/* </span> </span><span id="L-51"><a name="L-51"></a><span class="cm"> * echo the input array as csv data maintaining consistency with most CSV implementations </span> </span><span id="L-52"><a name="L-52"></a><span class="cm"> * - uses double-quotes as enclosure when necessary </span> </span><span id="L-53"><a name="L-53"></a><span class="cm"> * - uses double double-quotes to escape double-quotes </span> </span><span id="L-54"><a name="L-54"></a><span class="cm"> * - uses CRLF as a line separator </span> </span><span id="L-55"><a name="L-55"></a><span class="cm"> */</span> </span><span id="L-56"><a name="L-56"></a> </span><span id="L-57"><a name="L-57"></a><span class="k">function</span> <span class="nf">echocsv</span><span class="p">(</span><span class="nv">$fields</span><span class="p">)</span> </span><span id="L-58"><a name="L-58"></a><span class="p">{</span> </span><span id="L-59"><a name="L-59"></a> <span class="nv">$separator</span> <span class="o">=</span> <span class="s1">&#39;&#39;</span><span class="p">;</span> </span><span id="L-60"><a name="L-60"></a> <span class="k">foreach</span> <span class="p">(</span><span class="nv">$fields</span> <span class="k">as</span> <span class="nv">$field</span><span class="p">)</span> <span class="p">{</span> </span><span id="L-61"><a name="L-61"></a> <span class="k">if</span> <span class="p">(</span><span class="nb">preg_match</span><span class="p">(</span><span class="s1">&#39;/\\r|\\n|,|&quot;/&#39;</span><span class="p">,</span> <span class="nv">$field</span><span class="p">))</span> <span class="p">{</span> </span><span id="L-62"><a name="L-62"></a> <span class="nv">$field</span> <span class="o">=</span> <span class="s1">&#39;&quot;&#39;</span> <span class="o">.</span> <span class="nb">str_replace</span><span class="p">(</span><span class="s1">&#39;&quot;&#39;</span><span class="p">,</span> <span class="s1">&#39;&quot;&quot;&#39;</span><span class="p">,</span> <span class="nv">$field</span><span class="p">)</span> <span class="o">.</span> <span class="s1">&#39;&quot;&#39;</span><span class="p">;</span> </span><span id="L-63"><a name="L-63"></a> <span class="p">}</span> </span><span id="L-64"><a name="L-64"></a> <span class="k">echo</span> <span class="nv">$separator</span> <span class="o">.</span> <span class="nv">$field</span><span class="p">;</span> </span><span id="L-65"><a name="L-65"></a> <span class="nv">$separator</span> <span class="o">=</span> <span class="s1">&#39;,&#39;</span><span class="p">;</span> </span><span id="L-66"><a name="L-66"></a> <span class="p">}</span> </span><span id="L-67"><a name="L-67"></a> <span class="k">echo</span> <span class="s2">&quot;</span><span class="se">\r\n</span><span class="s2">&quot;</span><span class="p">;</span> </span><span id="L-68"><a name="L-68"></a><span class="p">}</span> </span><span id="L-69"><a name="L-69"></a><span class="cp">?&gt;</span><span class="x"> </span> </span><span id="L-70"><a name="L-70"></a><span class="x"> </span> </span></pre></div> </td></tr></table>
encoding, export, mysql, php