Solved

CSV into MySQL Using PHP

Posted on 2016-08-26
14
72 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 83

Expert Comment

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

Author Comment

by:Ridgejp
ID: 41772307
Yes it is - sorry I should have said. J
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41772310
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Ridgejp
ID: 41772312
<!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
ID: 41772315
Form is above, below the php (full html enclosed) and csv attached too.
0
 
LVL 54

Expert Comment

by:Julian Hansen
ID: 41772379
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
ID: 41772394
Using a DigitalOcean Server with a Lamp installation ... learning as I go - do you have examples on how it works?
0
 
LVL 54

Expert Comment

by:Julian Hansen
ID: 41772417
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
ID: 41772431
Ok I'll look at that shortly ... is the php code easily fixed?
0
 
LVL 54

Expert Comment

by:Julian Hansen
ID: 41772476
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 54

Expert Comment

by:Julian Hansen
ID: 41772482
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 54

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
ID: 41772488
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 54

Expert Comment

by:Julian Hansen
ID: 41772490
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 22

Accepted Solution

by:
Kim Walker earned 250 total points
ID: 41772631
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql disables rename 4 69
Wordpress Taxonomy 2 28
when to use sequences in mysql 4 29
Sudden decrease in performance when updating mysql using classic asp 6 22
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

773 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