Go Premium for a chance to win a PS4. Enter to Win

x
Solved

CSV into MySQL Using PHP

Posted on 2016-08-26
Medium Priority
101 Views
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'; } } ?>  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");

?>

0
Question by:Ridgejp
• 6
• 5
• 2
• +1

LVL 84

Expert Comment

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

Author Comment

ID: 41772307
Yes it is - sorry I should have said. J
0

LVL 84

Expert Comment

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

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>  a.csv 0 Author Comment ID: 41772315 Form is above, below the php (full html enclosed) and csv attached too. 0 LVL 60 Expert Comment 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 ID: 41772394 Using a DigitalOcean Server with a Lamp installation ... learning as I go - do you have examples on how it works? 0 LVL 60 Expert Comment 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 ID: 41772431 Ok I'll look at that shortly ... is the php code easily fixed? 0 LVL 60 Expert Comment 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  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  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)  0 LVL 60 Expert Comment 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  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();


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

$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); }  0 LVL 60 Assisted Solution Julian Hansen earned 1000 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')");  0 LVL 60 Expert Comment ID: 41772490 Your check for a successful db operation is also flawed if ($query) {
}

Should be
if (mysqli_query($link,$query)) {
echo "Query succeeded";
}
else {
echo "Query failed with error : " . mysqli_error($link); }  Testing on$query will always return TRUE as the $query has a string assigned to it. 0 LVL 22 Accepted Solution Kim Walker earned 1000 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];

0

Featured Post

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month11 days, 4 hours left to enroll