PHP/Mysql

If you want to export data from MySQL table to CSV file ,then don’t worry. I will provide you one script. When you run this script, data in table will be stored in CSV file.

Here is the PHP script. You have to just copy that code and paste in your PHP file and run that file.

$host = ‘hostaddress’;
$user = ‘username’;
$pass = ‘password’;
$db = ‘DB name’;
$table = ‘table name’;
$file = ‘export’;

$link = mysql_connect($host, $user, $pass) or die(“Can not connect.” . mysql_error());
mysql_select_db($db) or die(“Can not connect.”);

$result = mysql_query(“SHOW COLUMNS FROM “.$table.”");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].”; “;
$i++;
}
}
$csv_output .= “\n”;

$values = mysql_query(“SELECT * FROM “.$table.””);
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].”; “;
}
$csv_output .= “\n”;
}

$filename = $file.”_”.date(“Y-m-d_H-i”,time());
header(“Content-type: application/vnd.ms-excel”);
header(“Content-disposition: csv” . date(“Y-m-d”) . “.csv”);
header( “Content-disposition: filename=”.$filename.”.csv”);
print $csv_output;
exit;
?>

Export data from MS EXCEL sheet to MySQL table – PHP Script

2010-01-03T19:49:00.000-08:00

Export data from MS EXCEL sheet to MySQL table – PHP Script

Hello Friends !!

Today i write post on Export records from EXCEL sheet to MySQL table.I have PHP script for that. I already use that script and it’s working.

I will give you whole source code and explain you how to use this script.First of all you have to download excelreader file. Here this is a zip file and you have to extract it into your project directory.

After this make one PHP file called “excel_upload.php“. Copy this code and paste in this PHP file. You have to set the fields as your table.

PHP script shown below :

require_once(“excelreader/Excel/reader.php”);

$con = mysql_connect(‘HOSTNAME’, ‘USERNAME’, ‘PASSWORD’);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“YOUR DATABASE”, $con);

$edata = new Spreadsheet_Excel_Reader();

// Set output Encoding.
$edata->setOutputEncoding(‘CP1251′);

if($_FILES['file']['tmp_name'])
{

$edata->read($_FILES['file']['tmp_name']);
}

error_reporting(E_ALL ^ E_NOTICE);
$arr=array();
for ($i = 2; $i <= $edata->sheets[0]['numRows']; $i++)
{

for ($j = 1; $j <= $edata->sheets[0]['numCols']; $j++)
{
$arr[$i][$j]=$edata->sheets[0]['cells'][$i][$j];

}

$addsql = “insert into jos_users (`field1` ,`field1` ,`field1`,`field1`) “;
$addsql = $addsql.”VALUES (‘”.$arr[$i][1].”‘,’”.$arr[$i][2].”‘,’”.$arr[$i][3].”‘,’”.$arr[$i][4].”‘)”;

$ans=mysql_query($addsql);

}
mysql_close($con);

?>

In above code, first loop for rows, and second loop for columns. First loop starts from 2 because generally first row

contains title of column.

$arr is an array which fetch every row one bye one and store data in it. It is a two dimentional array.

If you have any problem with this code then comment on this post. If you have any new about any post then also comment me.