Solved

CSV into MySQL Using PHP

Posted on 2016-08-26
14
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 57

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 57

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 57

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 57

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 57

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 57

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
can i read my emails on lamp ftp 4 71
Dump data from mysql to xls php 10 56
Bootstrap - BootBox - PHP - MYSQL - Response Trigger 6 62
Google Recaptcha 13 56
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

752 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