Import large csv data into Mysql within seconds.

Here is the PHP script that will import your large CSV file data within seconds .

Steps
1. Copy the below code and paste into your file and save your file with “.php” extension or you can download file directly from here.
2. Copy your csv file and paste into the same folder where your php file exists.
3. Now run your php file in browser.
4. Browser will show a form with some fields . you have to fill your database and table details and in “Name of the file” field you have to fill your csv file name.
5. Click on submit button.

It will take some seconds and your all csv data will import into your mysql table.

yourphpfile.php

<?php 
if(isset($_POST['username'])&&isset($_POST['mysql'])&&isset($_POST['db'])&&isset($_POST['username']))
{
$sqlname=$_POST['mysql'];
$username=$_POST['username'];
$table=$_POST['table'];
if(isset($_POST['password']))
{
$password=$_POST['password'];
}
else
{
$password= '';
}
$db=$_POST['db'];
$file=$_POST['csv'];
$cons= mysqli_connect("$sqlname", "$username","$password","$db") or die(mysql_error());
$result1=mysqli_query($cons,"select count(*) count from $table");
$r1=mysqli_fetch_array($result1);
$count1=(int)$r1['count'];
//If the fields in CSV are not seperated by comma(,)  replace comma(,) in the below query with that  delimiting character 
//If each tuple in CSV are not seperated by new line.  replace \n in the below query  the delimiting character which seperates two tuples in csv
// for more information about the query http://dev.mysql.com/doc/refman/5.1/en/load-data.html
mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE '.$table.'
        FIELDS TERMINATED by \',\'
        LINES TERMINATED BY \'\n\'
')or die(mysql_error());
$result2=mysqli_query($cons,"select count(*) count from $table");
$r2=mysqli_fetch_array($result2);
$count2=(int)$r2['count'];
$count=$count2-$count1;
if($count>0)
echo "Success";
echo "<b> total $count records have been added to the table $table </b> ";
}
else{
echo "Mysql Server address/Host name ,Username , Database name ,Table name , File name are the Mandatory Fields";
}
?>



<html>
<head>
<title> csv2 sql</title>
<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
</head>
<body>
<br>

</br>
<form class="form-horizontal" action="" method="post">
    <div class="form-group">
        <label for="mysql" class="control-label col-xs-2">Mysql Host name</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="mysql" id="mysql" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label for="username" class="control-label col-xs-2">Username</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="username" id="username" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label for="password" class="control-label col-xs-2">Password</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="password" id="password" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label for="db" class="control-label col-xs-2">Database name</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="db" id="db" placeholder="">
        </div>
    </div>
    
    <div class="form-group">
        <label for="table" class="control-label col-xs-2">table name</label>
        <div class="col-xs-3">
        <input type="name" class="form-control" name="table" id="table">
        </div>
    </div>
    <div class="form-group">
        <label for="csvfile" class="control-label col-xs-2">Name of the file</label>
        <div class="col-xs-3">
        <input type="name" class="form-control" name="csv" id="csv">
        </div>
        eg. yourfile.csv  (Your file must be in same folder where this script exist)
    </div>
    <div class="form-group">
    <label for="login" class="control-label col-xs-2"></label>
    <div class="col-xs-3">
    <button type="submit" class="btn btn-primary">Upload</button>
    </div>
    </div>
</form>
</div>

</body>
</html>
Share on Google+Share on FacebookTweet about this on TwitterEmail this to someone
 

Download table from mysql in .xls without Popup in Php

For download table from myslq into .xls form without asking have two basic steps:

  1. create connection to the database
  2. create index file to get the data from database, execute code and get desired output ( .xls file )

1.  connection to Database in connection.php

<?php $server="localhost"; 
$dbusername="root"; $dbPassword=""; 
$dbname="Database Name"; 
$connection_error= "your database is not connected";
 if(!mysql_connect($server,$dbusername,$dbPassword) || !mysql_select_db($dbname)) { die($connection_error); }
 ?>

 

2. Then in index.php add this code

<?php // connection connection file to index.php 
include ("connection.php");

 $setCounter = 0; $setData=""; 
$setExcelName = "download_excal_file";

// sql query which brings your table data
 $setSql = "SELECT * FROM `tablename` WHERE 1";

 // executing select query
 $setRec = mysql_query($setSql);

 // counting number of rows in the result of select query 
$setCounter = mysql_num_fields($setRec); $setMainHeader="";

 // creating header for .xls file

 for ($i = 0; $i &lt; $setCounter; $i++)
 {
 $setMainHeader .= mysql_field_name($setRec, $i)."\t"; 
}
 $setMainHeader.="\n"; 
while($rec = mysql_fetch_row($setRec))
 { $rowLine = ''; 

foreach($rec as $value)
 {
 if(!isset($value) || $value == "")
 { 
$value = "\t"; 
}
else {
 //It escape all the special charactor, quotes from the data. 
$value = strip_tags(str_replace('"', '""', $value));

$value = '"' . $value . '"' . "\t";
}
 $rowLine .= $value;
}
$setData .= trim($rowLine)."\n";
}

 $setData = str_replace("\r", "", $setData);

 if ($setData == "") 
{ 
$setData = "\nno matching records found\n";
 }

 $setCounter = mysql_num_fields($setRec);

 $myfile = fopen($setExcelName."_Reoprt.xls", "w") or die("Unable to open file!");

 $txt = $setMainHeader. $setData;

 fwrite($myfile, $txt);
 ?>

after adding this code in index file just load the file to the browser and check the root folder of your code you will get a xls file created with file name ending with “_Report.xls”

NOTE: You can give selected fields name if you want to get only some selected column instead of all the fields of table.

Share on Google+Share on FacebookTweet about this on TwitterEmail this to someone
 

Masonry Infinite Scroll With Mysql

<?php
ob_start();
$aHost = 'HOSTNAME';
$aUser = 'DBUSER';
$aPass = 'DBPASS';
$aDb   = 'DBNAME';
$aCon  = mysqli_connect($aHost,$aUser,$aPass);
mysqli_select_db($aCon,$aDb);


$limit = 15;
$page  = isset($_REQUEST['page']) ? $_REQUEST['page'] : 1;
$start = ($page - 1) * $limit;

$aLimit = "LIMIT {$start},{$limit}";
$aSql = "SELECT * FROM wp_posts WHERE 1 {$aLimit}";
$aResult = mysqli_query($aCon,$aSql);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Infinite Scroll With Mysql</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div id="gridConainer" class="">
<?php
	while($aRow = mysqli_fetch_assoc($aResult))
	{
	?>
		<div class="box">
			<h1><?php echo $aRow['post_title']; ?></h1>
			<h2><?php echo date("d/m/Y",strtotime($aRow['post_date'])); ?></h2>
			<p><?php echo $aRow['post_name']; ?></p> 
		</div>
	<?php
	}
?>
</div>
<nav id="page-nav">
  <a href="index.php?page=<?php echo $page; ?>"></a>
</nav>	

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script src="js/jquery.masonry.min.js"></script>
<script src="js/jquery.infinitescroll.min.js"></script>
<script>
$(function(){

	var $container = $('#gridConainer');
		
	$container.imagesLoaded(function(){
		$container.masonry({
			itemSelector: '.box',
		});
	});
	
	$container.infinitescroll({
	navSelector  : '#page-nav',    
	nextSelector : '#page-nav a', 
	itemSelector : '.box',     
	loading: {
	finishedMsg: 'No more pages to load.',
	img: 'http://i.imgur.com/6RMhx.gif'
	}
	},	
	function( newElements ) {
		var $newElems = $( newElements ).css({ opacity: 0 });
		$newElems.imagesLoaded(function(){	
			$newElems.animate({ opacity: 1 });
			$container.masonry( 'appended', $newElems, true );
		});
	});

});
</script>
</body>

</html>

Download source code Here

Share on Google+Share on FacebookTweet about this on TwitterEmail this to someone