Archive

Posts Tagged ‘csv/exel’

Export MYSQL data into Excel/CSV via php

July 10, 2011 3 comments

Hello Friends,

Today i came across a functionality where i need to Export the MYSQL data into CSV/Excel file via PHP function/script. There are such requirement where client needs to Export the MYSQL data (Order data,Member data,Newsletter emails etc) into Excel sheet or CSV file for future reference or need to send to other team for future work. You can give a button or link from where client can click on it and get a Excel or CSV file with all data from MYSQL database tables using(through) PHP.

Here i am sharing a function using which you can easily export the MYSQL data into Excel/CSV with a single click on button or link. If you are looking to export the filtered data than you can pass parameters into function and make a sql query accordingly. Do you know how to Import CSV/Excel data into MYSQL ?

<?php
function export_excel_csv()
{
$conn = mysql_connect(“localhost”,”root”,””);
$db = mysql_select_db(“database”,$conn);

$sql = “SELECT * FROM table”;
$rec = mysql_query($sql) or die (mysql_error());

$num_fields = mysql_num_fields($rec);

for($i = 0; $i < $num_fields; $i++ )
{
$header .= mysql_field_name($rec,$i).”\t”;
}

while($row = mysql_fetch_row($rec))
{
$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 No Record Found!n”;
}

header(“Content-type: application/octet-stream”);
header(“Content-Disposition: attachment; filename=reports.xls”);
header(“Pragma: no-cache”);
header(“Expires: 0”);
print “$header\n$data”;
}
?>

What you need to do is…
1) Copy above function and paste it into your file.
2) Change MYSQL connection settings in mysql_connect(“localhost”,”root”,””).
3) Change database name in mysql_select_db(“database”,$conn)
4) Change table name in $sql = “SELECT * FROM table”.
5) Thats it.

Let me know your thoughts for the same. If you face any problem in this than let me know via comment or contact us form.

To know more about programming,JavaScript issues,jQuery,Expression Engine,MYSQL database and Open-source, enter your email address below. We will send you free tutorials.

Advertisements

Import CSV/Excel data into MYSQL database via PHP

July 10, 2011 4 comments

Hello Friends,

Today i came across a functionality where i need to import the CSV/Excel file in to MYSQL database via PHP script. There is a special requirement from client where he can upload the CSV/Excel file in file upload field inHTML form and all data from CSV/Excel must import into MYSQLdatabase table through PHP.

You can import data of CSV/Excel into MYSQL via PHP using fgetcsv() function along with some file handling functions. Here i would like to share that script with all of you. I hope that in future this article will be helpful to you when you need to implement this type of functionality.

if(isset($_POST[‘SUBMIT’]))
{
$fname = $_FILES[‘sel_file’][‘name’];

$chk_ext = explode(“.”,$fname);

if(strtolower($chk_ext[1]) == “csv”)
{

$filename = $_FILES[‘sel_file’][‘tmp_name’];
$handle = fopen($filename, “r”);

while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE)
{
$sql = “INSERT into user(name,email,phone) values(‘$data[0]’,’$data[1]’,’$data[2]’)”;
mysql_query($sql) or die(mysql_error());
}

fclose($handle);
echo “Successfully Imported”;
}
else
{
echo “Invalid File”;
}
}

<form action='<?php echo $_SERVER[“PHP_SELF”];?>’ method=’post’>

Import File : <input type=’text’ name=’sel_file’ size=’20’>
<input type=’submit’ name=’submit’ value=’submit’>

</form>

Above code will first check for valid csv file. If it is valid csv file than with the use of fopen() function , uploaded file will be opened in read mode. Now using fgetcsv() function , you will have a line by line data from csv file. Each line you will get is an array with all column values.  So now you have all data from csv file. You can play with them according to your needs. I have shown an example to insert 3 data in database table user. If your csv file contains more data than you will get in $data[0] , $data[1], $data[2],$data[3] and so on..

Thats it. Let me know your thoughts for the same. If you face any problem in this than let me know via comment or contact us form.

To know more about programming,JavaScript issues,jQuery,Expression Engine,MYSQL database and Open-source, enter your email address below. We will send you free tutorials.

%d bloggers like this: