Today i am going to explain how to import values form CSV to Mysql database.It is very useful to import bulk amount data's very easily.
Let's have look.
Step 1: You need a database connection Let's have look.
db.php
<?php
define ("DB_HOST", "localhost"); // set database host
define ("DB_USER", "root"); // set database user
define ("DB_PASS",""); // set database password
define ("DB_NAME","db"); // set database name
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");
?>
define ("DB_HOST", "localhost"); // set database host
define ("DB_USER", "root"); // set database user
define ("DB_PASS",""); // set database password
define ("DB_NAME","db"); // set database name
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");
?>
users.sql
CREATE TABLE IF NOT EXISTS `student` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`email` varchar(200) NOT NULL,
`city` varchar(200) NOT NULL,
`image` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`email` varchar(200) NOT NULL,
`city` varchar(200) NOT NULL,
`image` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Used functions and Definition:
is_file() — Checks wheather the specified file is a regular file.fopen - Used to open a file or url.
fgetcsv — Gets line from file pointer and parse for CSV fields
csv.php
<?php
if (isset($_POST['csvimport']))
{
$csv_file = $_FILES['csvfile']['tmp_name'];
if (!is_file($csv_file))
exit('File not found.');
$sql = '';
if (($handle = fopen( $csv_file, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql .= mysql_query("INSERT INTO `users` SET
`id` = '$data[0]',
`name` = '$data[1]',
`email` = '$data[2]',
`city` = '$data[3]',
`image` = '$data[4]'") or die("could not insert into DB");
}
fclose($handle);
}
exit( "Complete!");
}
?>
if (isset($_POST['csvimport']))
{
$csv_file = $_FILES['csvfile']['tmp_name'];
if (!is_file($csv_file))
exit('File not found.');
$sql = '';
if (($handle = fopen( $csv_file, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql .= mysql_query("INSERT INTO `users` SET
`id` = '$data[0]',
`name` = '$data[1]',
`email` = '$data[2]',
`city` = '$data[3]',
`image` = '$data[4]'") or die("could not insert into DB");
}
fclose($handle);
}
exit( "Complete!");
}
?>
csv.html
<form enctype="multipart/form-data" method="POST" action="">
<input name="csvfile" type="file">
<input type="submit" name="csvimport" value="Upload">
</form>
<input name="csvfile" type="file">
<input type="submit" name="csvimport" value="Upload">
</form>
No comments:
Post a Comment