Read data from array

weraw · January 22, 2012

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

AyKay47 · January 22, 2012

1. mysql_list_fields is deprecated and should no longer be relied upon. The link will suggest you use mysql_query instead.

2. Where is the $fieldColumn array coming from?

weraw · January 22, 2012

Hi

I Don´t know where the fieldColumn array is!

Maybe it is missing from the begin in the script?

If the fieldColumn array is needed then please tell me how and where it should be... Working with array is not my strong side :/

I will replace mysql_list_fields() when i got it to work...

AyKay47 · January 22, 2012

Its not my code, so I have no clue what that variable is supposed to store. Really, for now I would remove it until you figure it out if you are only worried about storing the field names.

$fieldArray[$index] = mysql_field_name($fields, $index);

Then to view all of the field names you would print_r($fieldArray);

weraw · January 22, 2012

Hi

First of all thank u four your time.

The field names from my table in database is display corretly in my querry.

Here is the output on the querry:

Ok: INSERT INTO test_imports (id, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16) VALUES ("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")

Here i can confirm that the fields from table in database are correct!

The value from the csv file should be mapped in this field1,field2,field3 etc with the code below:

$sql .= "\"" . $data[$fieldArray[$index][1]] . "\", ";

						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]] . "\", ";
						}
					}

Problem is that the value is empty!

If i tray to change

$sql .= "\"" . $data[$fieldArray[$index][1]] . "\", ";

to this

$sql .= "\"" . $data[$index] . "\", ";

then the value is coming from csv file but not mapped to correct field in database table.

When run

print_r($fieldArray);

i get this result:

Array ( [0] => field1 [1] => ) Array ( [0] => field2 [1] => ) Array ( [0] => field3 [1] => )

and so on!

As you can see

Array ( [0] => field1 [1] => $HERE_SHOULD_THE_VALUE_FROM_CSV_FIELD_BE_!_BUT_THIS_IS_EMPTY)

I maby wrong abaut the last line i wrote above this.

Sign In

Read data from array

Recommended Posts

weraw

Link to comment

Share on other sites

AyKay47

Link to comment

Share on other sites

weraw

Link to comment

Share on other sites

AyKay47

Link to comment

Share on other sites

weraw

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information