Hi
I found a script on some page and want to get it work...
I have problem with the part when insert to database.
Please feel free to upload the script to your server and make a fast test of it.
More explanation of my problem is in code comments.
<?
/*
#############################################################
# >>> PHP CSV Importer #
#############################################################
# > Author: Matthew Lindley #
# > E-mail: sir_tripod@hotmail.com #
# > Verson: 1.1a (See change_log.txt for details) #
# > Date: 5th October 2002 #
# #
# This script will allow you to import CSV files and put #
# the results into a database. #
# #
#############################################################
# Please read read_me.txt first #
#############################################################
*/
ob_start();
function normalise($string) {
$string = str_replace("\r", "\n", $string);
return $string;
}
$serverName = "##";
$username = "##";
$password = "##";
$database = "###";
$table = "###";
$csvFile = $_POST['csvFile'];
$delimiter = $_POST['delimiter'];
//This is mac Cols in my table in database!
$maxCols = "16";
if ($_POST['stage'] == "")
{
$display_block .= "
<form action=\"csv_importer.php?stage=preview\" method=\"POST\">
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"55%\" align=\"center\">
<tr>
<td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
</tr>
<tr>
<td colspan=\"2\" class=\"tdSpacer\"> </td>
</tr>
<tr>
<td>Select CSV file:</td>
<td><input type=\"text\" name=\"csvFile\" size=\"100\" class=\"inputText\"> (relative to script)</td>
</tr>
<tr>
<td>How are the columns separated?</td>
<td><input type=\"text\" name=\"delimiter\" value=\",\" size=\"1\" class=\"inputText\"> If in doubt try a comma. (,)</td>
</tr>
<tr>
<td>How many lines do you want to preview?</td>
<td><input type=\"text\" name=\"previewLimit\" value=\"5\" size=\"1\" class=\"inputText\"></td>
</tr>
<tr>
<td colspan=\"2\" class=\"tdSpacer\"> </td>
</tr>
<tr>
<td colspan=\"2\" class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next »\" class=\"inputSubmit\"></td>
</tr>
</table>
<input type=\"hidden\" name=\"stage\" value=\"field_list\">
</form>";
}
if ($_POST['stage'] == "field_list"){
if (!$myFile = @fopen($csvFile, "r")) {
die("Can't open CSV file. Has it been moved/deleted?");
} else {
for ($index = 0; $index < $maxCols; $index++) {
$columnOptions .= "<option value=\"$index\">Column " . ($index+1) . "</option>";
}
$line = 0;
while (($line < 5) && ($data = fgetcsv($myFile, 1000, $delimiter))) {
$numOfCols = count($data);
$csv_block .= "\n\t\t\t\t\t\t\t<tr>";
for ($index = 0; $index < $numOfCols; $index++) {
if (strlen($data[$index]) > 100) {
$dots = "...";
} else {
$dots = "";
}
if ($data[$index] == "") {
$csv_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewContent\">"
. "\n\t\t\t\t\t\t\t\t\t"
. " "
. "\n\t\t\t\t\t\t\t\t</td>";
} else {
$csv_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewContent\">"
. "\n\t\t\t\t\t\t\t\t\t"
. substr($data[$index], 0, 100) . $dots
. "\n\t\t\t\t\t\t\t\t</td>";
}
}
$csv_block .= "\n\t\t\t\t\t\t\t\t</tr>";
$line++;
}
fclose($myFile);
$display_block .= "<form name=\"form1\" action=\"csv_importer.php?stage=insert\" method=\"POST\">
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">
<tr>
<td class=\"tdTitle\">PHP CSV Importer</td>
</tr>
<tr>
<td class=\"tdSpacer\"> </td>
</tr>
<tr>
<td>
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">
<tr>";
for ($index = 0; $index < $maxCols; $index++) {
$display_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewColHeader\">Col " . ($index+1) . "</td>";
}
$display_block .= "\n\t\t\t\t\t\t\t</tr>$csv_block</table>
</td>
</tr>
<tr>
<td class=\"tdSpacer\"> </td>
</tr>
<tr>
<td>
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">";
if (!$connection = mysql_connect($serverName, $username, $password)) {
die("Can't connect to database. Has MySQL stopped?");
} else {
$fields = mysql_list_fields($database, $table, $connection);
$columns = mysql_num_fields($fields);
for ($index = 0; $index < $columns; $index++) {
$display_block .= "\n\t\t\t\t\t\t<tr>
<td colspan=\"5\" class=\"tdPreviewAssignHeader\">" . mysql_field_name($fields, $index) . ": </td>
</tr>
<tr>
<td class=\"tdPreviewAssignContent\"><input type=\"radio\" name=\"useValue[$index]\" value=\"column\" checked>Use column: </td>
<td class=\"tdPreviewAssignContent\">
<select name=\"fieldColumn[$index]\" onClick=\"setAssignment('" . $index . "', 'column')\">
<option value=\"\">Select column</option>
<option value=\"none\">None</option>
$columnOptions
</select>
</td>
<td align=\"center\" class=\"tdPreviewAssignContent\"> OR </td>
<td class=\"tdPreviewAssignContent\">
<input type=\"radio\" name=\"useValue[$index]\" value=\"value\">
Use this value:
</td>
<td class=\"tdPreviewAssignContent\">
<input type=\"text\" name=\"fieldValue[$index]\" size=\"8\" class=\"inputText\" onClick=\"setAssignment('" . $index . "', 'value')\">
</td>
</tr>";
$js_field_array .= "fieldArray[$index] = \"" . mysql_field_name($fields, $index) . "\";";
}
}
$display_block .= "
<tr>
<td colspan=\"5\" class=\"tdSpacer\"> </td>
</tr>
<tr>
<td colspan=\"2\" align=\"center\"><input type=\"button\" name=\"sequence\" value=\"Sequence\" class=\"inputSubmit\" onClick=\"sequenceOptions()\"></td>
<td> </td>
<td colspan=\"2\"> </td>
</tr></table></td></tr>
<tr>
<td class=\"tdSpacer\"> </td>
</tr>
<tr>
<td>Would you like to import the first line of the CSV file?</td>
</tr>
<tr>
<td>
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">
<tr>
<td><input type=\"radio\" name=\"startLine\" value=\"0\" checked>Yes</td>
</tr>
<tr>
<td><input type=\"radio\" name=\"startLine\" value=\"1\">No</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class=\"tdSpacer\"> </td>
</tr>
<tr>
<td>Are you updating existing records or adding new ones?</td>
</tr>
<tr>
<td>
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">
<tr>
<td><input type=\"radio\" name=\"sqlType\" value=\"add\" checked onClick=\"setPrimaryKey(this.value)\">Adding</td>
</tr>
<tr>
<td><input type=\"radio\" name=\"sqlType\" value=\"update\" onClick=\"setPrimaryKey(this.value)\">Updating - Primary key: <select name=\"primaryKey\" class=\"inputCombo\"></select></td>
</tr>
<tr>
<td> If there are new records not in table, how are they marked? <input type=\"text\" name=\"noRecordMarker\" size=\"3\" class=\"inputText\"></td>
</tr>
</table>
</td>
</tr>
<tr>
<td class=\"tdSpacer\"> </td>
</tr>
<tr>
<td class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next »\" class=\"inputSubmit\" class=\"inputSubmit\"></td>
</tr>
</table>
<input type=\"hidden\" name=\"csvFile\" value=\"" . $csvFile . "\">
<input type=\"hidden\" name=\"delimiter\" value=\"" . $delimiter . "\">
<input type=\"hidden\" name=\"maxCols\" value=\"$maxCols\">
<input type=\"hidden\" name=\"serverName\" value=\"$serverName\">
<input type=\"hidden\" name=\"username\" value=\"$username\">
<input type=\"hidden\" name=\"password\" value=\"$password\">
<input type=\"hidden\" name=\"database\" value=\"$database\">
<input type=\"hidden\" name=\"table\" value=\"$table\">
<input type=\"hidden\" name=\"stage\" value=\"insert\">
</form>";
$js_block = "\n\nfunction sequenceOptions() {
var d = document.form1;
var total_elements = document.form1.length;
var selectedIndex = 2;
var regexp = /column/i;
for (var index = 0; index < total_elements; index++) {
element = d[index];
if ((element.type == \"select-one\") && (regexp.test(element.name))) {
if (element.options.length > selectedIndex) {
element.selectedIndex = selectedIndex;
selectedIndex++;
} else {
element.selectedIndex = 1;
}
}
}
}
function assignContent(field) {
alert(field.value);
}
function setAssignment(index, side) {
var df = document.form1;
var fieldList = \"\";
var name_regexp = eval('/\[' + index + '\]/');
var element;
for (var items = 0; items < df.length; items++) {
element = df[items];
if ((element.type == \"radio\")
&& (name_regexp.test(element.name))
&& (element.value == side)) {
element.checked = true;
}
}
}
var fieldArray = new Array;
$js_field_array
function setPrimaryKey(value) {
var element = document.form1.primaryKey;
if (value == \"update\") {
for (var index = 0; index < fieldArray.length; index++) {
element.options[index] = new Option(fieldArray[index], fieldArray[index]);
}
} else {
for (var index = fieldArray.length; index >= 0; index--) {
element.options[index] = null;
}
}
}\n\n\n";
}
}
if ($_POST['stage'] == "insert"){
//My own test :/
$noRecordMarker = $_POST['noRecordMarker'];
$primaryKey = $_POST['primaryKey'];
$sqlType = $_POST['sqlType'];
$startLine = $_POST['startLine'];
if (!$connection = mysql_connect($serverName, $username, $password)) {
die("Can't connect to database. Has MySQL stopped?");
} else {
$fields = mysql_list_fields($database, $table, $connection);
$columns = mysql_num_fields($fields);
$selectedFieldIndex = 0;
for ($index = 0; $index < $columns; $index++) {
$fieldArray[$selectedFieldIndex][0] = mysql_field_name($fields, $index);
//My own coment: Database fields shows if change below to this: "fieldColumn$index";
$fieldArray[$selectedFieldIndex][1] = $fieldColumn[$index];
$selectedFieldIndex++;
}
if ($sqlType == "add") {
if (!$myFile = @fopen(stripslashes($csvFile), "r")) {
die("Can't open CSV file. Has it been moved/deleted?");
} else {
$line = 0;
while ($data = fgetcsv($myFile, 2048, $delimiter)) {
if ($line >= $startLine){
$numOfCols = count($data);
$sql = "INSERT INTO
$table
(";
for ($index = 0; $index < count($fieldArray); $index++) {
if ((($fieldColumn[$index] != "") && ($fieldColumn[$index] != "none")) || ($useValue[$index] == "value")) {
$sql .= $fieldArray[$index][0] . ", ";
}
}
$sql = substr($sql, 0, -2) . ")\nVALUES\n(";
for ($index = 0; $index < count($fieldArray); $index++) {
if ($useValue[$index] == "value") {
$sql .= "\"" . $fieldValue[$index] . "\", ";
} else if (($fieldColumn[$index] != "none") && ($fieldColumn[$index] != "")) {
$sql .= "\"" . $data[$fieldArray[$index][1]] . "\", ";
}
}
$sql = substr($sql, 0, -2) . ")";
if ($update_db = mysql_query($sql, $connection)) {
$mysql_log .= "Ok: $sql\n<br>\n<br>";
} else {
$mysql_log .= "<strong>Failed:</strong> $sql\n<br>Reason: " . mysql_error() . "\n<br>\n<br>";
}
}
$line++;
}
}
fclose($myFile);
} else {
if (!$myFile = @fopen(stripslashes($csvFile), "r")) {
die("Can't open CSV file. Has it been moved/deleted?");
} else {
$line = 0;
while ($data = fgetcsv($myFile, 2048, $delimiter)) {
if ($line >= $startLine){
$numOfCols = count($data);
for ($index = 0; $index < count($fieldArray); $index++) {
if ($fieldArray[$index][0] == $primaryKey) {
if ($data[$fieldArray[$index][1]] == $noRecordMarker) {
$action = "insert";
} else {
$action = "update";
}
break;
}
}
if ($action == "insert") {
$sql = "INSERT INTO
$table (";
for ($index = 0; $index < count($fieldArray); $index++) {
if ($fieldArray[$index][0] != $primaryKey) $sql .= $fieldArray[$index][0] . ", ";
}
$sql = substr($sql, 0, -2) . ")\nVALUES\n(";
for ($index = 0; $index < count($fieldArray); $index++) {
if ($fieldArray[$index][0] != $primaryKey) {
$sql .= "\"" . $data[$fieldArray[$index][1]] . "\", ";
}
}
$sql = substr($sql, 0, -2) . ")";
} else {
$sql = "UPDATE
$table
SET ";
for ($index = 0; $index < count($fieldArray); $index++) {
if ($fieldArray[$index][0] == $primaryKey) {
if ($useValue[$index] == "column") {
$primaryKeyValue = $data[$fieldArray[$index][1]];
} else {
$primaryKeyValue = $fieldValue[$index];
}
}
if ($fieldArray[$index][0] != $primaryKey) {
if (($useValue[$index] == "column")
&& ($fieldColumn[$index] != "")
&& ($fieldColumn[$index] != "none")) {
$sql .= $fieldArray[$index][0] . " = \"" . $data[$fieldArray[$index][1]] . "\", ";
} else if ($useValue[$index] == "value") {
$sql .= $fieldArray[$index][0] . " = \"" . $fieldValue[$index] . "\", ";
}
}
}
$sql = substr($sql, 0, -2) . " WHERE $primaryKey = \"" . $primaryKeyValue . "\"";
}
if ($sql != "") {
if ($update_db = mysql_query($sql, $connection)) {
$mysql_log .= "Ok: $sql\n<br>\n<br>";
} else {
$mysql_log .= "<strong>Failed:</strong> $sql\n<br>Reason: " . mysql_error() . "\n<br>\n<br>";
}
$sql = "";
}
$sql = "";
}
$line++;
}
}
fclose($myFile);
}
$display_block = "
<form action=\"csv_importer.php\" method=\"POST\">
<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"55%\" align=\"center\">
<tr>
<td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
</tr>
<tr>
<td colspan=\"2\" class=\"tdSpacer\"> </td>
</tr>
<tr>
<td>" . $mysql_log . "</td>
</tr>
</table>
<input type=\"hidden\" name=\"stage\" value=\"preview\">
</form>";
}
} else {
echo "Bad stage";
}
?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Matt's CSV Reader</title>
<link rel="stylesheet" href="csv_importer.css" type="text/css">
<script>
<? echo $js_block ?>
</script>
</head>
<body>
<? echo $display_block ?>
</body>
</html>
Here is where the script not works as it should. From here and below i need help.
if ($_POST['stage'] == "insert"){
The fields from my $table is not showing untill i change this line:
$fieldArray[$selectedFieldIndex][1] = $fieldColumn[$index];
To this:
$fieldArray[$selectedFieldIndex][1] = "fieldColumn$index";
I just testet it and get it to show the cols from database.
Thanks and sorry 4 my eng
