Solved

CSV into MySQL Using PHP

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

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 52

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
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.

 
LVL 52

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 52

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 52

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 52

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 52

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Blog posts not showing up on Blog page 7 50
Paypal 502 Error 3 63
Store image url and display it for user profile 6 23
mysql joining from the same table 6 29
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

937 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

4 Experts available now in Live!

Get 1:1 Help Now