Converting CSV Files to MySQL Insert Queries Using PHP - The PHP script to process form inputs

(Page 4 of 4 )

You'll find the script discussion in the comments to the code.

<?php

//Check if the user inputs the correct security code/captcha and if the form has been submitted.

if($_POST[ref] == "csv2sql" && ((trim($_POST['captcha'])==$_SESSION['answer']))) {

//The form has been submitted, now check if the file type is CSV and the file size less than 50kB.

if (($_FILES["file"]["type"] == "text/csv") && ($_FILES["file"]["size"] < 50000))

{

if ($_FILES["file"]["error"] > 0)

{

echo "Return Code: " . $_FILES["file"]["error"] . "<br />";

}

else

{

//For special purposes, check if file does not exist in the server.

if (file_exists("upload/" . $_FILES["file"]["name"]))

{

echo $_FILES["file"]["name"] . " already exists. ";

}

else

{

//File still does not exist, save files to the folder name upload.

move_uploaded_file($_FILES["file"]["tmp_name"],

"upload/" . $_FILES["file"]["name"]);

}

}

}

else

{

echo "Invalid file";

}

//Now that the file is saved on the upload directory,

//Read the the CSV file contents into an array and put in the

//variable name as $filetoread

$filetoread="upload/" . $_FILES["file"]["name"];

//read file into array

$csv_array=file($filetoread);

//Display the header tag for the MySQL insert query output to the browser.

echo "<h2>SQL Query Output:</h2>";

echo '<font color="blue">';

//Get data from the form and parse it.

$table_name = stripslashes($_POST[table_name]);

$column_names = explode(";",$csv_array[0]);

// Generate the base MySQL query

$base_query = "INSERT INTO $table_name (";

$first = true;

foreach($column_names as $column_name)

{

if(!$first)

$base_query .= ", ";

$column_name = trim($column_name);

$base_query .= "`$column_name`";

$first = false;

}

$base_query .= ") ";

// Loop through all CSV data rows and generate separate

// INSERT queries based on base_query + the row information

$last_data_row = count($csv_array) - 1;

for($counter = 1; $counter < $last_data_row; $counter++)

{

$value_query = "VALUES (";

$first = true;

$data_row = explode(";",$csv_array[$counter]);

$value_counter = 0;

foreach($data_row as $data_value)

{

if(!$first)

$value_query .= ", ";

$data_value = trim($data_value);

$value_query .= "'$data_value'";

$first = false;

}

$value_query .= ")";

// Combine generated queries to generate final query

$query = $base_query .$value_query .";";

echo "$query<BR />";

}

//delete the temporary csv file in the server

, because the form has already been processed, and this CSV file is not anymore needed.

unlink($filetoread);

}

//Display error is the security is not correct.

elseif ($_POST[ref] == "csv2sql" && ((trim($_POST['captcha'])!= $_SESSION['answer']))) {

echo '<h3><font color="red">You have incorrectly entered the security code, please enter again, thanks.</font></h3>';

}

?>

</font>

</body>

</html>

<?php

//clear out array contents and session variables.

$_SESSION = array ();

session_destroy ();

?>

You can download the complete source code as a zip file.

You can also find a working example of this web application.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

blog comments powered by Disqus