Upload CSV into Database Using PHP and MYSQL

Upload CSV into Database Using PHP and MYSQL

May 2, 2015
This script can be use to update data in database from local CSV file on user(admin) computer
Create table in users in the reg database

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(20) NOT NULL,
  `name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `pass` varchar(25) NOT NULL,
  `gen` varchar(5) NOT NULL,
  `phno` varchar(12) NOT NULL,
  `dob` varchar(10) NOT NULL,
  `street` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `nation` varchar(20) NOT NULL,
  `country` varchar(25) NOT NULL,
  `photo` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 1 – Data Base Connection

At first we need to connect to database…
File Name: connection.php

?
1
2
3
4
5
6
7
8
<?php
$db = mysql_connect("localhost", "root", "vertrigo") or
die("Could not connect.");
if(!$db)
    die("no db");
if(!mysql_select_db("reg",$db))
     die("No database selected.");
?>

Step 2 – upload page

Making connection to data base by calling the connection.php, clean the table of its old data, insert new uploaded data into table…
File Name: upload.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
<!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>Upload page</title>
<style type="text/css">
body {
    background: #E3F4FC;
    font: normal 14px/30px Helvetica, Arial, sans-serif;
    color: #2b2b2b;
}
a {
    color:#898989;
    font-size:14px;
    font-weight:bold;
    text-decoration:none;
}
a:hover {
    color:#CC0033;
}
 
h1 {
    font: bold 14px Helvetica, Arial, sans-serif;
    color: #CC0033;
}
h2 {
    font: bold 14px Helvetica, Arial, sans-serif;
    color: #898989;
}
#container {
    background: #CCC;
    margin: 100px auto;
    width: 945px;
}
#form             {padding: 20px 150px;}
#form input     {margin-bottom: 20px;}
</style>
</head>
<body>
<div id="container">
<div id="form">
<?php
include "connection.php"; //Connect to Database
//$deleterecords = "TRUNCATE TABLE users"; //empty the table of its current records
//mysql_query($deleterecords);
//Upload File
 
if (isset($_POST['submit'])) {
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
        echo "<h2>Displaying contents:</h2>";
        readfile($_FILES['filename']['tmp_name']);
    }
    //Import uploaded file to Database
    $handle = fopen($_FILES['filename']['tmp_name'], "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $import="INSERT INTO `users`(`id`, `name`, `email`, `pass`, `gen`, `phno`, `dob`
        , `street`, `city`, `nation`, `country`, `photo`) values('$data[0]','$data[1]'
       ,'$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]',
        '$data[9]','$data[10]','$data[11]')";
        mysql_query($import) or die(mysql_error());
    }
    fclose($handle);
    print "Import done";
    //view upload form
}else {
    print "Upload new csv by browsing to file and clicking on Upload<br />\n";
    print "<form enctype='multipart/form-data' action='#' method='post'>";
    print "File name to import:<br />\n";
    print "<input size='50' type='file' name='filename'><br />\n";
    print "<input type='submit' name='submit' value='Upload'></form>";
}
?>
</div>
</div>
</body>
</html>

Download
* If you like this post please don’t forget to subscribe Techies Badi - programming blog for more useful stuff

Related Posts

Subscribe to: Post Comments (Atom)