• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

CSV into MySQL Using PHP

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
Ridgejp
Asked:
Ridgejp
  • 6
  • 5
  • 2
  • +1
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
Is 'csv' the name of the database and 'test' is the name of the table?
0
 
RidgejpManaging DirectorAuthor Commented:
Yes it is - sorry I should have said. J
0
 
Dave BaldwinFixer of ProblemsCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RidgejpManaging DirectorAuthor Commented:
<!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
 
RidgejpManaging DirectorAuthor Commented:
Form is above, below the php (full html enclosed) and csv attached too.
0
 
Julian HansenCommented:
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
 
RidgejpManaging DirectorAuthor Commented:
Using a DigitalOcean Server with a Lamp installation ... learning as I go - do you have examples on how it works?
0
 
Julian HansenCommented:
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
 
RidgejpManaging DirectorAuthor Commented:
Ok I'll look at that shortly ... is the php code easily fixed?
0
 
Julian HansenCommented:
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
 
Julian HansenCommented:
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
 
Julian HansenCommented:
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
 
Julian HansenCommented:
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
 
Kim WalkerWeb Programmer/TechnicianCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now