Unable to submit csv file data into corresponding database fields using PHP

Question

Currently I am trying to upload a CSV file and enter each record into the database 1 by 1. The columns on the CSV have the same name as the field names in the database but sometimes the data will be in a different order within the CSV. When I say in a different order, I mean that instead of a list of names always being in the 1st column, they might be in the 3rd column.

Really what I'm asking is how will I do the above as I'm really stuck.

At the moment I doesn't insert into the database but it does get the array from the CSV file.

Code Below:

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>CSV Import</title>
</head>

<body>

<form action="upload.php" method="post" enctype="multipart/form-data">
    <input type="file" name="csv"/>
    <input type="submit" name="submit" value="Save" />
</form>

</body>

</html>

config.php

<?php

/* Database Connection */

$con = mysql_connect('xxxxxxxx', 'xxxxxxxx', 'xxxxxxxx');

if(! $con )
{
  die('Could not connect: ' . mysql_error());
}

$select_db = mysql_select_db('xxxxxxxx');

?>

upload.php

<?php

include('config.php');

$file = "test.csv";
$separator = ",";
$length = filesize($file);

$handle = fopen($file, "r");
    $csvData = fgetcsv($handle, $length, $separator);
fclose($handle);

$i = 0;

while($i >= 1){

$title = $csvData[0];
$firstName = $csvData[1];
$secondName = $csvData[2];
$emailAddress = $csvData[3];
$houseNumber = $csvData[4];
$mobileNumber = $csvData[5];
$address1 = $csvData[6];
$address2 = $csvData[7];
$address3 = $csvData[8];
$address4 = $csvData[9];
$postcode = $csvData[10];

mysql_query("INSERT csv SET title='$title', firstName='$firstName' ,secondName='$secondName', emailAddress='$emailAddress', houseNumber='$houseNumber' ,mobileNumber='$mobileNumber', address1='$address1', address2='$address2', address3='$address3' ,address4='$address4', postcode='$postcode'")

$i++;

}

?>

just for the record the use of mysql_* functions are discouraged due to deprecation. — SamHuckaby, Jul 08 '13 at 21:13
Yeah, you are right though lol do you have any ideas on what may be wrong given the code supplied? — AyeTry, Jul 08 '13 at 21:17
In the CSV file, how will you know what order the data is in? — , Jul 08 '13 at 21:18
That's what I mean. As I can't rely on a user to always have the CSV columns in the correct order, how would I do this? — AyeTry, Jul 08 '13 at 21:20
magic? sounds like an situation where you provide instructions, if the user breaks them it's their problem, not yours — , Jul 08 '13 at 21:23
Note that fgetcsv() only returns a single **ROW** Of data. if your csv has multiple lines, you'll be ignoring all but the first. You are also vulnerable to [SQL injection attacks](http://bobby-tables.com) via this file. — Marc B, Jul 08 '13 at 21:23
Can you rely on the header line being a) present, and b) correct? If so, you could match the headers from the first row to your database structure. — , Jul 08 '13 at 21:23
Well I would be able to rely on the header name always being correct but not always present as there may only be partial information. For eg. someone may include their name, email address but another may only include a column for their name. — AyeTry, Jul 08 '13 at 21:29

score 0 · Answer 1 · answered Jul 08 '13 at 21:19

0

The code you're using to insert the records will fail here:

$i = 0;

while($i >= 1){   // $i = 0. This test will fail.

// do stuff

}

answered Jul 08 '13 at 21:19

SebiF · Answer 2 · 2013-07-09T07:24:18.167

The correct upload.php, assuming that there is a header line which contains the column names:

<?php

include('config.php');

$file = "test.csv";
$separator = ",";
$length = 0; // size of the longest line(!), 0 = no limit
$fields = array('title', 'firstName', 'secondName', 'emailAddress', 'houseNumber', 'mobileNumber', 'address1', 'address2', 'address3', 'address4', 'postcode'); // use it as a white list

$handle = fopen($file, "r");

// get 1st line (header) and flip keys and values
// format like [title] --> 0, [firstName] --> 1, ...
$header = array_flip(fgetcsv($handle, $length, $separator));

$values = array();
// while we can read lines as csvData:
while(($csvData = fgetcsv($handle, $length, $separator)) !== false){
    foreach ($fields as $field) // put all values in an array in correct order
        $values[] = $csvData[$header[$field]];

    mysql_query("INSERT INTO csv (" . implode(',', array_keys($header)) . ") VALUES (" . implode(',', $values) . ")"); // only for demonstration - be careful with spaces and quotes in values - better switch to PDO!

}
fclose($handle);

Hope that helps! Untested, but might be working :).

Note: this will fail when someone omits values and you have declared them in your database as NOT NULL. Be aware that this sample does not do any counter-measures to SQL injections. Please forget about mysql_query and learn to use PDO.

I've tried this but unfortunately it doesn't seem to work, just shows a blank white screen and nothing inserts into the database. It would be great if you could have another look through the code, is that OK? — AyeTry, Jul 08 '13 at 22:30
That there is no output is normal, as there is no echo or something else. However, I corrected a small error, a missing `array_keys`. I tested it with some sample CSV and got a nice-looking SQL, which should've worked. You can replace `mysql_query(...)` with `echo ...` and see what it tries to insert. Don't forget to insert the aforementioned `array_keys`. — SebiF, Jul 09 '13 at 07:26

Unable to submit csv file data into corresponding database fields using PHP

2 Answers2