MySQL to Excel Sheet

[ded]

Is there a simple way for a user to download information from the database to an excel sheet?

 

Regards,

DED

[premiso]

Using phpMyAdmin you can do this rather easy in multiple formats.

[ded]

Agree....But i do not want the user to have ability to go into phpMyAdmin.

 

 

Regards,

DED

[premiso]

There are a bunch of options via google:

 

http://fundisom.com/phparadise/php/databases/mySQL_to_excel

 

http://www.phpclasses.org/browse/package/2038.html

 

http://www.google.com/search?hl=en&q=php+mysql+to+excel&btnG=Google+Search&aq=f&oq=

[dawsba]

This might be of some use

 

<?
  $dbserver = "127.0.0.1";
  $dbusername = "USERNAME GOES HERE";
  $dbpassword = "PASSWORD HERE";  
  $dbname ="DB NAME";
  $link=mysql_connect ($dbserver,$dbusername,$dbpassword) or die ('DB ERROR -> ' . mysql_error());
  mysql_select_db ($dbname); 
  header("Content-Type:  application/vnd.ms-excel"); 
  ?>
  <table>
    <tr>
  <td><strong>Company</strong></td>
  <td><strong>Proxy Name</strong></td>
  <td><strong>Address</strong></td>
  <td><strong>Address2</strong></td>
  <td><strong>Address3</strong></td>
  <td><strong>Post Code</strong></td>
</tr>
<?
        $j = 1; $k = 0;
	$sql2 = "SELECT * FROM `tbl_client_proxy`";
	$result5 = mysql_db_query($dbname,$sql2,$link) or die("Invalid Query<br>". mysql_error());
	while ($result4 = mysql_fetch_array($result5))
	{
	?>
	  <tr>
		<td>
		  <? echo mysql_result(mysql_query("SELECT `cname` FROM `tbl_clients` WHERE `id` = '$result4[cid]'",$link),0); ?>
		</td>
		<td>
		  <? echo $result4[name]; ?>
		</td>
		<td>
		  <? echo $result4[add1]; ?>
		</td>
		<td>
		  <? echo $result4[add2]; ?>
		</td>
		<td>
		  <? echo $result4[add3]; ?>
		</td>
		<td>
		  <? echo $result4[pcode]; ?>
		</td>
	  </tr>
	  <? 
	} 
?>
  </table>

[Mark Baker]

Using the PHPExcel library

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

$rResult = mysql_query('SELECT id FROM products');

$rowID = 0;
//  Loop through each returned record writing it to the worksheet
while ($row = mysql_fetch_assoc($sql)) {
   $rowID++;
   if ($rowID == 1) {
      $columnID = 'A';
      //  Set column headings (just before writing the first row)
      foreach($row as $key => $columnValue) {
         $cellID = $columnID.$rowID;
         $objPHPExcel->getActiveSheet()->setCellValue($cellID, $key);
         $columnID++;
      }
      $rowID++;
   }
   $columnID = 'A';
   //  Loop through each column in the current record writing it to the worksheet
   foreach($row as $columnValue) {
      $cellID = $columnID.$rowID;
      $objPHPExcel->getActiveSheet()->setCellValue($cellID, $columnValue);
      $columnID++;
   }
}

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Products');

// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));