Wednesday, May 12, 2010

Import CSV File using PHP

Below is the code to import csv file to MySQL .

It allows you to specify the delimiter in this csv file, whether it is a comma, a tab etc. It also allows you to chose the line separator.

It also permits you to include an empty field at the beginning of each row, which is usually an auto increment integer primary key.

This script is useful mainly if you don't have or you are a command prompt guy.
Just make sure the table is already created before trying to dump the data.
Kindly post your comments if you got any bug report.



/* Created By : Parth Trivedi
Created On : 11th May, 2010
Functionality : Add News User.
*/
@ob_start();
@session_start();
//include dbconnection and query class
include_once('include/userfunction.php');
include('include/dbconfig.php');

//Database connection
$dbconnect = new DbConnect(HOST,USER,PASS,DATABASE);
$dbconnect->open();

$databasetable = NEWSUSER;
$fieldseparator = ",";
$lineseparator = "\n";
//change the name of the file you want

$csvfile = 'filename';

/********************************************************************************************/
/* Would you like to add an empty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************************************************************************/
$addauto = 0;
/********************************************************************************************/
if(!file_exists($csvfile)) {
$msg = "File not found. Make sure you specified the correct path.
";
}
$file = fopen($csvfile,"r");

if(!$file) {
$msg = "Error opening data file.
";
}

$size = filesize($csvfile);

if(!$size) {
$msg = "File is empty.
";
}
$csvcontent = fread($file,$size);

fclose($file);
$lines = 0;
$queries = "";
$inserted = 0;
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {
$lines++;
$line = trim($line," \t");
$line = str_replace("\r","",$line);
/************************************************************************************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************************************************************************************/
$line = str_replace("'","\'",$line);
/***********************************************************************************************************/

$linearray = explode($fieldseparator,$line);

if($lines == 1)
{
//first line as the name of the fields
$first = explode($fieldseparator,$line);
}
if($lines !=1)
{
$linemysql = implode("','",$linearray);

if($addauto)
$query = "insert into $databasetable ($user) values('','$linemysql');";
else
$query = "insert into $databasetable ($user) values('$linemysql');";

$queries .= $query . "\n";

$dbquery = new DbQuery($query);
$resinsert = $dbquery->insertquery();
if($resinsert)
{
$inserted ++;
}
}
}

$rec = $lines-1;
if($inserted == $rec)
{
$msg = "Found a total of $rec records in this csv file.
Data inserted successfully";
header('location:addnewsuser.php?msg='.urlencode($msg));
exit;
}
else
{
$msg .= " Data not inserted successfully";
header('location:addnewsuser.php?msg='.urlencode($msg));
exit;
}
?>

Database connection Class

Sometimes i was looking for common database layer or common database class for php.I decided to make some common pages that i can reuse in every project and acts as common module to interact with database.

Put below classes in your includes folder of your project and use them.

//include dbconnection and query class
include_once('include/connection.php');
//Database connection
$dbconnect = new DbConnect(HOST,USER,PASS,DATABASE);
$dbconnect->open();

$sql = 'select * from '.TABLENAME;
//instantiate object of query class
$dbquery = new DbQuery($sql);
$result = $dbquery->query();
$fetchrow = $dbquery->fetcharray();

and so on.
If any bugs please contact me.....
/*
Created By : Parth Trivedi
Created On : 11th May,2010
Name : connection.php
Functionality : Connection and query class.
*/

Class DbConnect
{
var $host = '' ;
var $user = '';
var $password = '';
var $database = '';
var $persistent = false;
var $conn;

var $error_reporting = false;

/*constructor function this will run when we call the class */

function DbConnect ($host1, $user1, $password1,$database1, $error_reporting=true, $persistent=false) {


//pass the hostname, user, password, database names here if static
$this->host = $host1;
$this->user = $user1;
$this->password = $password1;
$this->database = $database1;
$this->persistent = $persistent;
$this->error_reporting = $error_reporting;
}

function open(){
if ($this->persistent) {
$func = 'mysql_pconnect';
} else {
$func = 'mysql_connect';
}

/* Connect to the MySQl Server */

$this->conn = $func($this->host, $this->user, $this->password);
if (!$this->conn) {
return false;
}
/* Select the requested DB */

if (@!mysql_select_db($this->database, $this->conn)) {
return false;
}
return true;
}

/*close the connection */

function close() {
return (@mysql_close($this->conn));
}

/* report error if error_reporting set to true */

function error() {
if ($this->error_reporting) {
return (mysql_error()) ;
}

}
}
/* Class to perform query*/
class DbQuery extends DbConnect
{
var $result = '';
var $sql;
function DbQuery($sql1)
{
$this->sql = $sql1;
}

function query() {

return $this->result = mysql_query($this->sql);
//return($this->result != false);
}

function affectedrows() {
return(@mysql_affected_rows($this->conn));
}

function numrows() {
return(@mysql_num_rows($this->result));
}
function fetchobject() {
return(@mysql_fetch_object($this->result, MYSQL_ASSOC));
}
function fetcharray() {
return(@mysql_fetch_array($this->result));
}

function fetchassoc() {
return(@mysql_fetch_assoc($this->result));
}

function freeresult() {
return(@mysql_free_result($this->result));
}

}

Architecture of Zend Engine

Overview

Debugging can begin via your browser or directly from the IDE Client. Communication to the Debug Server is by way of your Web server and the Zend EngineTM. Thereafter, interaction between the Debug Server and the IDE Client takes place throughout the course of a Debug Session.



The IDE Client
The IDE Client is Zend's desktop application that enables you to develop your scripts and to interact with the Debug Server. It is comprised of the Zend IDE Desktop (GUI), an editor, Debug window, Filesystem browser, debug module, and a customization tool.

Debug Via Browser
A Debug Session is a structured process of debugging one or more script files. Debug sessions are not limited to independent scripts. Beginning with a root script, a Session can continue through one or more nested scripts until it is terminated.

You can start a Debug Session directly from your browser GUI, by manually defining a set of parameters to the root script's URL.

However, the Zend IDE enables you to access your browser from within the IDE Client. In doing so, the assignment of parameters and definition of a cookie will be done automatically.

Debug Server
The Debug Server is the server-side module that controls the Zend Engine's execution over the course of a Debug Session. The Debug Server oversees a Debug Session process from beginning to end,meaning that it:

Initiates the Session at your command.
Executes debugging methods that you choose to apply.
Terminates the Debug Session at your command
The Zend Engine is the internal compiler and runtime engine used by
PHP4. Developed by Zeev Suraski and Andi Gutmans, the Zend Engine is an
abbreviation of their names. In the early days of PHP4, it worked as
follows:
The PHP script was loaded by the Zend Engine and compiled into Zend opcode. Opcodes, short for operation codes, are low level binary instructions. Then the opcode was executed and the HTML generated sent to the client. The opcode was flushed from memory after execution.
Today, there are a multitude of products and techniques to help you speed up this process.




How to download file using PHP

The following is the code to download file using PHP



//Author : Parth Trivedi
if(isset($_GET['Action']) && $_GET['Action'] =='Download')
{
$speed = 100;
$export_file = "upload/".$_REQUEST['file'];
$file_extension = strtolower(substr(strrchr(basename($export_file),"."),1));
if(file_exists($export_file) && is_file($export_file) )
{
switch( $file_extension ) {
case "exe":$ctype="application/octet-stream";
break;
case "zip":$ctype="application/zip";
break;
case "mp3":$ctype="audio/mpeg";
break;
case "mpg":$ctype="video/mpeg";
break;
case "avi":$ctype="video/x-msvideo";
break;
// (sensitive stuff, like php files)
case "doc":$ctype="application/msword";break;
case "css":$ctype="text/plain";break;
case "htm":$ctype="application/force-download";break;
case "html":$ctype="application/force-download";break;
case "txt":$ctype="application/force-download";break;
default:
$ctype="application/force-download";
}
$filespaces = str_replace("_", " ", $export_file);
header("Cache-Control:");
header("Cache-Control: public");
header("Content-Type: $ctype");
header("Accept-Ranges: bytes");
$size = filesize($export_file);

header("Content-Length: ".$size);
header('Content-Disposition: attachment; filename="'.basename($filespaces).'"');
//open the file
$fp = fopen($export_file,"rb");
//seek to start of missing part
//start buffered download
while(!feof($fp)) {
//reset time limit for big file
set_time_limit(0);
print(fread($fp,1024*$speed));
flush();
sleep(1);
}
fclose($fp);
exit;
}
else
{
die("404 File not found!");
}
}