Ridgejp
asked on
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?
My Connection php is as follows: -
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");
?>
Is 'csv' the name of the database and 'test' is the name of the table?
ASKER
Yes it is - sorry I should have said. J
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?
ASKER
<!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
ASKER
Form is above, below the php (full html enclosed) and csv attached too.
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.
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.
ASKER
Using a DigitalOcean Server with a Lamp installation ... learning as I go - do you have examples on how it works?
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.
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.
ASKER
Ok I'll look at that shortly ... is the php code easily fixed?
You have two options
1. Use the LOAD DATA command
2. PHP script
Here is some sample code for LOAD DATA
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
SQLLOAD 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)
PHP Solution - two approaches. One using file() the other using fgetcsv()
Source CSV [t1513.csv]
Solution 2 - get lines one by one with 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('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);
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your check for a successful db operation is also flawed
Testing on $query will always return TRUE as the $query has a string assigned to it.
if ($query) {
echo 'data uploaded';
}
Should beif (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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.