无法将CSV文件上传到数据库
I have an uploads page where a user can upload CSV files only. However, when i hit the upload button, nothing is uploaded to my database and no error messages are displayed. Only the go back button shows which means that my code must not be executing my code properly. How would i resolve this?
CREATE TABLE `Jobs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`order_ref` varchar(250) NOT NULL,
`first_name` varchar(250) NOT NULL,
`last_name` varchar(250) NOT NULL,
`postcode` varchar(250) NOT NULL,
`country` varchar(250) NOT NULL,
`quantity` varchar(250) NOT NULL,
`scott_packing` varchar(250) NOT NULL,
`packing_price` varchar(250) NOT NULL,
`courier_price` varchar(250) NOT NULL,
`dispatch_type` varchar(250) NOT NULL DEFAULT '',
`tracking_number` varchar(250) NOT NULL,
`job_status` varchar(250) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
HTML:
<?
session_start();
if(!session_is_registered(myusername))
{
header("location:../index.php");
}
?>
<?
include("../template/header.php");
?>
<!DOCTYPE html>
<head>
<title></title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="content">
<div class="toggle">
<div class="header"><img style="float: right; padding: 10px;" src="../img/show.gif"><h4>Upload New File:</h4></div>
<form id="upload" action="upload_file.php" method="post" enctype="multipart/form-data">
<input type="file" name="uploaded_file"><br>
<input type="submit" value="Upload file">
</form>
<!--<p>
<a href="list_files.php" style="padding: 20px;">See all files</a>
</p>-->
</div>
</div>
</body>
</html>
upload_file.php:
<?php
if(isset($_FILES['uploaded_file'])) {
if($_FILES['uploaded_file']['error'] == 0) {
$database = "spineless";
$dblink = mysql_connect("localhost", "root", "vario007")
or die("Could not connect");
$db = mysql_select_db($database)
or die("Could not select database");
if(mysql_errno()) {
die("MySQL connection failed: ". mysql_error());
}
$file = $_FILES ['uploaded_file']['tmp_name'];
$handle = fopen($file, "r");
$row = 1;
while (($data = fgetcsv($handle, 0, ",","'")) !== FALSE)
{
if($row == 1)
{
// skip the first row
}
else
{
//csv format data like this
//$row[0] = date
//$row[1] = order_ref
//$row[2] = postcode
//$row[3] = country
//$row[4] = quantity
//$row[5] = packing_price
//$row[6] = dispatch_type
$query = "
INSERT INTO `Jobs` (
`date`, `order_ref`, `postcode`, `country`, `quantity`, `packing_price`, `dispatch_type`, `created`
)
VALUES (
'".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', NOW()
)";
$result = mysql_query($query);
// Check if it was successfull
if(!$result) {
echo 'Success! Your file was successfully added!';
}
else {
echo 'Error! Failed to insert the file';
}
}
$row++;
}
}
else {
echo 'An error accured while the file was being uploaded. '
. 'Error code: '. intval($_FILES['uploaded_file']['error']);
}
// Close the mysql connection
mysql_close();
}
else {
echo 'Error! A file was not sent!';
}
// Echo a link back to the main page
echo '<p>Click <a href="joblist.php">here</a> to go back</p>';
?>
This is how the CSV looks like: http://i754.photobucket.com/albums/xx182/rache_R/Screenshot2014-04-24at151348_zps807cedb0.png
Ok, I set up a DB as per your info and created a CSV file using the data you supplied. The good news is that your code works absolutely fine (for me) as is, but for two small issues.
Your success message is the wrong way around, remove the !:
if($result){
echo 'Success! Your file was successfully added!';
}else{
echo 'Error! Failed to insert the file';
}
You are also trying to insert a date string 03/02/2014 into a DATE datatype. This won't work and you'll need to format this string accordingly. As a temporary measure I just changed the datatype in the DB to a VARCHAR(20).
With those two small things fixed:
So i have managed to solve my own question. After looking through my code i noticed that my code was fine up until the while loop. PHP was not properly recognizing the line endings when reading the file so enabling the auto_detect_line_endings run-time configuration helped solve my issue. NOTE that auto_detect_line_endings should be set before fopen and not after.
<?php
if(isset($_FILES['uploaded_file'])) {
if($_FILES['uploaded_file']['error'] == 0) {
// Connect to database
$database = "spineless";
$link = mysql_connect("localhost", "root", "vario007") or die("Could not connect");
$db = mysql_select_db($database,$link) or die("Could not select database");
// Upload file
$file = $_FILES['uploaded_file']['tmp_name'];
ini_set("auto_detect_line_endings", true); // auto_detect_line_endings added here
$handle = fopen($file, "r");
$row = 1;
while (($data = fgetcsv($handle, 0, ",","'")) !== FALSE)
{
if($row == 1)
{
// skip the first row
}
else
{
//csv format data like this
//$row[0] = date
//$row[1] = order_ref
//$row[2] = postcode
//$row[3] = country
//$row[4] = quantity
//$row[5] = packing_price
//$row[6] = dispatch_type
$query = "
INSERT INTO `Jobs` (
`date`, `order_ref`, `postcode`, `country`, `quantity`, `packing_price`, `dispatch_type`, `created`
)
VALUES (
'".$data[1]."', '".$data[2]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[9]."', '".$data[11]."', NOW()
)";
$result = mysql_query($query);
// Check if it was successfull
if($result) {
echo 'Success! Your file was successfully added!';
}
else {
echo 'Error! Failed to insert the file';
}
}
$row++;
}
}
else {
echo 'An error accured while the file was being uploaded. ';
}
// Close the mysql connection
mysql_close();
}
else {
echo 'Error! A file was not sent!';
}
// Echo a link back to the main page
echo '<p>Click <a href="joblist.php">here</a> to go back</p>';
?>
<?
session_start();
You cannot start a session after output has been sent to the browser, in this case, a few blankspaces.
Adjust this and better not use any sort of short_open_tags:
<?php
session_start();