dynamicCoder: php

Showing posts with label php. Show all posts

Wednesday, December 18, 2013

how to export mysql table as csv using php?

Use the below code snippet as it is,just make sure to include your config file which has connection with the database.
make a file named generate.php .
<?php
include("config.php"); //config file containing connection to your database,you can name it whatever you want.
// Fetch Record from Database

$output            = "";
$table             = "mytable"; // Enter Your Table Name
$sql             = mysql_query("select * from $table");
$columns_total     = mysql_num_fields($sql);

// Get The Field Name

for ($i = 0; $i < $columns_total; $i++) {
    $heading    =    mysql_field_name($sql, $i);
    $output        .= '"'.$heading.'",';
}
$output .="\n";

// Get Records from the table

while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="\n";
}

// Download the file

$filename = "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo $output;
exit;

?>

If you want to generate csv file on click of any button or link.Just give path of this file in the link.Like:
<a href="generate.php">generate csv</a>

Tuesday, December 17, 2013

How to made/create a zip file using php.

class FlxZipArchive extends ZipArchive
{

/**
* Add a Dir with Files and Subdirs to the archive
*
* @param string $location Real Location
* @param string $name Name in Archive
* */
public function addDir($location, $name)
{
$this->addEmptyDir($name);
$this->addDirDo($location, $name);
}

/**
* Add Files & Dirs to archive.
*
* @param string $location Real Location
* @param string $name Name in Archive
* */
private function addDirDo($location, $name)
{
$name .= '/';
$location .= '/';

// Read all Files in Dir
$dir = opendir($location);
while ($file = readdir($dir)) {
if ($file == '.' || $file == '..')
continue;

// Rekursiv, If dir: FlxZipArchive::addDir(), else ::File();
$do = (filetype($location . $file) == 'dir') ? 'addDir' : 'addFile';
$this->$do($location . $file, $name . $file);
}
}

}

function create_zip($archive_folder, $archive_name){

    @unlink($archive_name);

    $za = new FlxZipArchive;

    $res = $za->open($archive_name, ZipArchive::CREATE);

    if($res === TRUE) {
    $za->addDir($archive_folder, basename($archive_folder));
    $za->close();
    }
    else
    echo 'Could not create a zip archive';
}//end of function

//now call the function where ever you want
$archive_folder="";//enter folder name here
$archive_name="";//enter name of zip file you want to create here
create_zip($archive_folder,$archive_name);

Feel free to comment ,if it works for you.Thanks!!

Tuesday, January 8, 2013

How to upload data from excel sheet to database using pHp

1.Save your excel sheet in .csv format.

2.add a form to upload file.

addfile.php

<html>
<body>

<form action="uploadfile.php" method="post"
enctype="multipart/form-data">
<label for="file">Csv Filename:</label>
<input type="file" name="file" id="file"><br>
<input type="submit" name="submit" value="Submit">
</form>

</body>
</html>

3.add a configuration file for connection with database.

config.php

<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "root";
$mysql_database = "your database name";
$prefix = "";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
mysql_select_db($mysql_database, $bd) or die("Could not select database");
?>

4.Now finally the upload script

uploadfile.php

<?
include('myconfig.php');

if (($handle = fopen($_FILES['file']['tmp_name'], "r")) !== FALSE) {
$counter = 0;
while (($data = fgetcsv($handle, 100000, ',','"')) !== FALSE) {
$counter++;
if($counter==1){
continue;
}
// echo '<pre>';
// print_r($data) ;
$str = implode('\',\'',$data);
$str1 = '\''.$str.'\'';

$insertQuery="INSERT into tb_patient values($str1)";
// echo $insertQuery;
$q=mysql_query($insertQuery);
if($q)
{
echo "data entered successfully";
}
else
{
echo "query failed";
}
}

}

?>