miércoles, 18 de marzo de 2015

Upload CSV and Insert into Database Using PHP/MYSQL


This script can be use to update data in database from local CSV file on user(admin) computer

Step 1 – Data Base Connection

At first we need to connect to database…
File Name: connection.php
1<?php
2$db = mysql_connect("Database", "username", "password") or die("Could not connect.");
3 
4if(!$db)
5 
6    die("no db");
7 
8if(!mysql_select_db("Databasename",$db))
9 
10    die("No database selected.");
11?>

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
view sourceprint?
1<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
2<html xmlns="http://www.w3.org/1999/xhtml">
3<head>
4<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
5<title>Upload page</title>
6<style type="text/css">
7body {
8    background: #E3F4FC;
9    font: normal 14px/30px Helvetica, Arial, sans-serif;
10    color: #2b2b2b;
11}
12a {
13    color:#898989;
14    font-size:14px;
15    font-weight:bold;
16    text-decoration:none;
17}
18a:hover {
19    color:#CC0033;
20}
21 
22h1 {
23    font: bold 14px Helvetica, Arial, sans-serif;
24    color: #CC0033;
25}
26h2 {
27    font: bold 14px Helvetica, Arial, sans-serif;
28    color: #898989;
29}
30#container {
31    background: #CCC;
32    margin: 100px auto;
33    width: 945px;
34}
35#form           {padding: 20px 150px;}
36#form input     {margin-bottom: 20px;}
37</style>
38</head>
39<body>
40<div id="container">
41<div id="form">
42 
43<?php
44 
45include "connection.php"; //Connect to Database
46 
47$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
48mysql_query($deleterecords);
49 
50//Upload File
51if (isset($_POST['submit'])) {
52    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
53        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
54        echo "<h2>Displaying contents:</h2>";
55        readfile($_FILES['filename']['tmp_name']);
56    }
57 
58    //Import uploaded file to Database
59    $handle = fopen($_FILES['filename']['tmp_name'], "r");
60 
61    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
62        $import="INSERT into tablename(item1,item2,item3,item4,item5) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
63 
64        mysql_query($import) or die(mysql_error());
65    }
66 
67    fclose($handle);
68 
69    print "Import done";
70 
71    //view upload form
72}else {
73 
74    print "Upload new csv by browsing to file and clicking on Upload<br />\n";
75 
76    print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
77 
78    print "File name to import:<br />\n";
79 
80    print "<input size='50' type='file' name='filename'><br />\n";
81 
82    print "<input type='submit' name='submit' value='Upload'></form>";
83 
84}
85 
86?>
87 
88</div>
89</div>
90</body>
91</html>



http://coyotelab.org/php/upload-csv-and-insert-into-database-using-phpmysql.html
Publicado por en

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)