Exporting MYSQL table into excel

I have some PHP script for export MYSQL table into Excel format, but I get some problem after the table have exported. The data which get from db just show only one row.

This the following script:

$dbc=mysql_connect(_SRV,_ACCID,_PWD) or die(_ERROR15.": ".mysql_error());
$db=mysql_select_db("qdbase",$dbc) or die(_ERROR17.": ".mysql_error());

$sQuery = "SELECT id, Line, Model,Lot_no,
                  COUNT( Serial_number ) AS Qty,
                  SUM(S), SUM(A), SUM(B), SUM(C),
                  (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1) / COUNT(Serial_number) AS QP,
                  ROUND((SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1) / COUNT(Serial_number)*1000,2) AS PPM
            FROM  `inspection_report`";

$rResult = mysql_query( $sQuery) or die();
$count = mysql_num_fields($rResult);

// fetch table header
$header = '';
for ($i = 0; $i < $count; $i++){
       $header .= mysql_field_name($rResult, $i)."\t";
       }
// fetch data each row, store on tabular row data
while($row = mysql_fetch_row($rResult)){
       $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 = "\nno matching records found\n";
       }

header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

// output data
echo $header."\n".$data;
?>

beside that, how to make some border for this table?

---

if i want to do : echo ""; which the correct place to put it that cant make my script become error?

If youre goign to do it as an html table then i would jsut output liek you normally right a table... for example you might do:

$rResult = mysql_query( $sQuery) or die();
$count = mysql_num_fields($rResult);

$html = '<table border="1"><thead><tr>%s</tr><thead><tbody>%s</tbody></table>';
$thead = '';
$tbody = '';
$line = '<tr>%s</tr>';
for ($i = 0; $i < $count; $i++){      
  $thead .= sprintf('<th>%s</th>',mysql_field_name($rResult, $i));
}


while(false !== ($row = mysql_fetch_row($rResult))){
  $trow = '';

  foreach($row as $value){
   $trow .= sprintf('<td>%s</td>', $value);
  }

  $tbody .= sprintf($line, $trow);

}


header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

print sprintf($html, $thead, $tbody);
exit;

---

Youre only getting one row because youre using aggregate functions without a GROUP BY clause. Try adding a GROUP BY on on something that ties each of these "entries" to a specific group like the Lot_no or whatever youre trying to report on.

As far as the border, you cant do that with CSV which although readbale by Excel, is not actually an Excel format file with all the extras like formatting for borders. To use things like formatting you need to output native Excel or an html table that can be loaded into excel. Take a look at phpexcel if you need to do that.

---

You are not actually pulling anything else from the tables. You only have one query and it is pulling the header information. Run a sql statement like "select * from tableName" to get the rest and then do the while and for loop with that query. PHP/browsers do not normally work well ith items outside of programming languages, XML, and CSV. I would look more toward either one of those. XML is readable by the newer versions of excel (and openable in Internet Explorer in XML form), while csv is more widely used to share between different types of databases/spreadsheets. For an example of XML output goto http://www.ibm.com/developerworks/opensource/library/os-phpexcel/index.html and for CSV output you will have to use fputcsv() function on an array. For Examplle:

 $query = mysql_fetch_row($rResult);//the query of the table
             // use 'select * from table' to list everything
 $xls = fopen('file.csv', 'a'); //opens document for writing and attempts to create if file does not exist
 foreach ($list as $fields) {
      fputcsv($fp, $fields);
 }
 fclose($xls); //always close the file when done

Both are dirty ways of exporting but work.

---

try this code it works for me :

<?php
$db_name     = "db_name";
$db_password = "pass";
$db_link     = mysql_connect("localhost", "root", $db_password);
mysql_select_db($db_name, $db_link);
mysql_query("SET NAMES UTF8");
$table = "table_name";
function assoc_query_2D($sql, $id_name = false){
  $result = mysql_query($sql);
  $arr = array();
  $row = array();
  if($result){
    if($id_name == false){
      while($row = mysql_fetch_assoc($result))
        $arr[] = $row;
    }else{
      while($row = mysql_fetch_assoc($result)){
        $id = $row['id'];
        $arr[$id] = $row;
      }
    }
  }else return 0;

  return $arr;
}

function query_whole_table($table, $value = '*'){
    $sql = "SELECT $value FROM $table";
  return assoc_query_2D($sql);
}

$export_str = "";
$result = query_whole_table($table);

foreach($result as $record){
  $export_str .= implode(";",$record) . "\n";
}
// add time to fileName
$date = time(); 
file_put_contents($date.'_'.$table."_export.csv", $export_str);
?>