Solved

CSV into MySQL Using PHP

Posted on 2016-08-26
14
57 Views
Last Modified: 2016-08-27
Hi Guys'

I'm looking at how you take data from a CSV file into MySql, but my php isn't working once I get to the mysqli part - can anyone see why?

<?php include("connection_1.php"); 


    if(isset($_POST['submit']))
        
    {
        
        $file = $_FILES['file']['tmp_name'];
        
        $handle = fopen($file,"r");
        
        while(($fileop = fgetcsv($handle,1000,",")) !== false)
        
            {
            
            $first_name = fileop[0];
            $last_name = fileop[1];
            $email = fileop[2];
            
            $query = "INSERT into test (first_name,last_name,email) VALUES ('$first_name', '$last_name', '$email')");
            mysqli_query($link, $query);
            
            }
                
            if ($query)
                
            { 
                echo 'data uploaded';
            }
    }
    
?>

Open in new window


My Connection php is as follows: -

<?php


	/* Access to the databse via link commands requires the $link statement
	in order for them to be successful, also the term "localhost" stops the $link
	failing in the event of a change in your IP Address.*/
	
	$link = mysqli_connect("localhost" , "root", "*******", "csv");


?>

Open in new window

0
Comment
Question by:Ridgejp
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Is 'csv' the name of the database and 'test' is the name of the table?
0
 

Author Comment

by:Ridgejp
Comment Utility
Yes it is - sorry I should have said. J
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Can you post a short version of the CSV file?  And I'm assuming that you have a file upload form page that is feeding the PHP page?
0
 

Author Comment

by:Ridgejp
Comment Utility
<!DOCTYPE html>
<?php include("connection_1.php"); 


    if(isset($_POST['submit']))
        
    {
        
        $file = $_FILES['file']['tmp_name'];
        
        $handle = fopen($file,"r");
        
        while(($fileop = fgetcsv($handle,1000,",")) !== false)
        
            {
            
            $first_name = fileop[0];
            $last_name = fileop[1];
            $email = fileop[2];
            
            $query = "INSERT into test (first_name,last_name,email) VALUES ('$first_name', '$last_name', '$email')");
            mysqli_query($link, $query);
            
            }
                
            if ($query)
                
            { 
                echo 'data uploaded';
            }
    }
    
?>



<html lang="en">
<head>
			<meta charset="utf-8">
			<meta http-equiv="X-UA-Compatible" content="IE=edge">
			<meta name="viewport" content="width=device-width, initial-scale=1">
			<title>Goods In</title>

			<!-- Bootstrap -->
			<link href="css/bootstrap.min.css" rel="stylesheet">
			<link rel="stylesheet" href="../assets/bootstrap/css/bootstrap.min.css">
    		<link rel="stylesheet" href="../assets/bootstrap-table/src/bootstrap-table.css">
    		<link rel="stylesheet" href="../assets/examples.css">
    		
    		<script src="../assets/jquery.min.js"></script>
    		<script src="../assets/bootstrap/js/bootstrap.min.js"></script>
    		<script src="../assets/bootstrap-table/src/bootstrap-table.js"></script>
    		<script src="../assets/bootstrap-table/src/extensions/filter-control/bootstrap-table-filter-control.js"></script>
    		<script src="../ga.js"></script>
    		
    		<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
			<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
			<!-- Include all compiled plugins (below), or include individual files as needed -->

</head>


	
<body>
<!-- Start of Container Class (1) -->
<div class="container">

<form method="post" action="csv_import.php" enctype="multipart/form-data">
    
        <input type="file" name="file" />
        <br />
        <input type="submit" name="submit" value="Submit" />
    
    </form>

<div class="container">
    
    <br />
    <br />
    <br />
    <br />
    

    

</div>


		<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
		<!-- Include all compiled plugins (below), or include individual files as needed -->
		<script src="js/bootstrap.min.js"></script>
		<script src="dynamitable.jquery.min.js"></script>
		
		<script> 
		
		
		</script>

</body>
</html>




	

Open in new window

a.csv
0
 

Author Comment

by:Ridgejp
Comment Utility
Form is above, below the php (full html enclosed) and csv attached too.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Are you able to use LOAD DATA INFILE ?

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

If so then you can get MySQL to do all the work for you. Depending on where your server is this may or may not be enabled. Some ISP's disable this on shared servers - if this is a local server then you can potentially use it.
0
 

Author Comment

by:Ridgejp
Comment Utility
Using a DigitalOcean Server with a Lamp installation ... learning as I go - do you have examples on how it works?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
There are examples on that link I posted earlier.

What does your file look like
What is the separator - assume ','
Are fields enclosed in " "
What separates lines \n or \r\n

To give a relevant example would need the above.
0
 

Author Comment

by:Ridgejp
Comment Utility
Ok I'll look at that shortly ... is the php code easily fixed?
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
You have two options

1. Use the LOAD DATA command
2. PHP script

Here is some sample code for LOAD DATA
CREATE TABLE `ee1513` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(50) DEFAULT NULL,
  `surname` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Open in new window

CSV [t1513.csv]
Name,Surname,Email,DOB
Fred, Smith, fred@domain.com, 1969-07-16
John, Doe, john@somewhere.com, 1981-04-12
Mary, Jane, mary@overthere.com, 1998-11-20

Open in new window

SQL
LOAD DATA LOCAL INFILE 'C:\\Path\\to\\t1513.csv' INTO TABLE ee1513 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES 
(firstname, surname, email, dob)

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
PHP Solution - two approaches. One using file() the other using fgetcsv()
Source CSV [t1513.csv]
Name,Surname,Email,DOB
Fred, Smith, fred@domain.com, 1969-07-16
John, Doe, john@somewhere.com, 1981-04-12
Mary, Jane, mary@overthere.com, 1998-11-20

Open in new window

Solution 1 - using file() to read the file into an array of lines and then exploding the lines into an array of fields
<?php
define('host','localhost');
define('user','username');
define('password','password');
define('database','ee');

// Read file into array
$file = file('t1513.csv');
// Base query
$query = <<< QUERY
  INSERT INTO ee1513 (`firstname`,`surname`,`email`,`dob`) 
  VALUES
QUERY;

// Loose the header row
array_shift($file);
// Loop through file line by line
foreach($file as $line) {
  // Explode the line on ','
  $fields = explode(',', $line);
  // Build the query
  $query .= "('" . implode("','", $fields) . "'),";
}
// Remove last ','
$query = trim($query, ',');
echo $query;

// Connect to DB and run query
$mysqli = new mysqli(host, user, password, database);
if ($mysqli) {
  $mysqli->query($query);
}
else {
  echo "No Connect";
}
$mysqli->close();

Open in new window


Solution 2 - get lines one by one with fgetcsv()
<?php
define('host','localhost');
define('user','username');
define('password','password');
define('database','ee');

// Read file into array
$handle = fopen('t1513.csv','r');
if ($handle) {
  // Base query
  $query = <<< QUERY
  INSERT INTO ee1513 (`firstname`,`surname`,`email`,`dob`) 
  VALUES
QUERY;
  // get first line - header
  fgets($handle);
  
  while(($fields = fgetcsv($handle,100, ',')) !== false) {
    // Build the query
    $query .= "('" . implode("','", $fields) . "'),";
  }
  
  $query = trim($query, ',');
  echo $query;
  
  $mysqli = new mysqli(host, user, password, database);
  
  if ($mysqli) {
    $mysqli->query($query);
  }
  else {
    echo "No Connect";
  }
  
  $mysqli->close();
  fclose($handle);
}

Open in new window

0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
Comment Utility
As to why your code does not work you have an unmatched closing ')' at the end of the string.
$query = "INSERT into test (first_name,last_name,email) VALUES ('$first_name', '$last_name', '$email')");

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Your check for a successful db operation is also flawed
if ($query) { 
  echo 'data uploaded';
}

Open in new window

Should be
if (mysqli_query($link, $query)) {
   echo "Query succeeded";
}
else {
  echo "Query failed with error : " . mysqli_error($link);
}

Open in new window


Testing on $query will always return TRUE as the $query has a string assigned to it.
0
 
LVL 21

Accepted Solution

by:
Kim Walker earned 250 total points
Comment Utility
Going back to the code in your original question, you've left out the $ before the $fileop variable. It should be:
            $first_name = $fileop[0];
            $last_name = $fileop[1];
            $email = $fileop[2];

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Jquery Autocomplete PHP script 3 20
Phone Dialer 5 33
uploading multiple image with php 14 15
Wordpress Body Class 5 7
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now