I want to use a csv file to update the mysql scv table. how to code ? I have no experiece doing this job.
<p>please select a scv file to upload</p>
<form action="index.php" method="post">
<input type="file" name="scv" />
<input type="submit" value="submit" />
</form>
<?php
mysql_connect('localhost','root','admin');
mysql_select_db('linjuming');
// how to upload a scv file and insert or update the "csv" table?
?>
Your upload file:
<form action="upload_target.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file"><br>
<input type="submit" name="submit" value="Submit">
</form>
Your upload_target.php
$ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
if ($ext == "csv" && $_FILES["file"]["error"] == 0)
{
$target = "upload/" . $_FILES["file"]["name"];
move_uploaded_file($_FILES["file"]["tmp_name"], $target);
if (($handle = fopen($target, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
{
print_r($data);
}
fclose($handle);
}
}
Very basic and with very few checks / validations. The print_r($data) contains one line of your csv that you can now insert in your database.
However, I would recommend using PDO or MySQLi for that task, since the mysql functions of PHP will be deprecated in the future.
There's several parts of this:
First, your form MUST have the enctype set, as follows:
<form enctype="multipart/form-data" action="index.php" method="post">
Otherwise, it will not accept file uploads.
Once you've done that, then you can access the file using the $_FILES variable. After the file has been uploaded, then you can access it like so:
if (isset($_FILES["scv"])) {
$file = $_FILES["scv"];
$file_name = $file["name"];
$ext = pathinfo($file_name, PATHINFO_EXTENSION);
if ($ext!="CSV" && $ext!="TXT") {
die('The file must be csv or txt format.');
}
$saveto_path_and_name = '/path/to/file.csv'; // Where you want to save the file
move_uploaded_file($file["tmp_name"], $saveto_path_and_name);
}
Once you've saved the file, you then can open it and import it. That's not trivial to do, but here's some primer code:
// Open the file for reading
$handle = @fopen($saveto_path_and_name, "r") or die(__("Unable to open uploaded file!", "inventory"));
// Grab the first row to do some checks
$row = fgets($inv_file, 4096);
// See if it's comma or tab delimited
if (stripos($inv_row, "\t")) {
$sep = "\t";
} else {
$sep = ",";
}
while ( ! feof($handle)) {
$rowcount = 0;
// Get the individual fields
$inv_fields = explode($sep, $inv_row);
$fields = array();
// Iterate through the fields to do any sanitization, etc.
foreach ($inv_fields as $field) {
// Highly recommended to sanitize the variable $field here....
$fields[] = $field;
$rowcount++;
}
// This is where you would write your query statement to insert the data
// This is just EXAMPLE code. Use the DB access of your choice (PDO, MySQLi)
$sql = vsprintf('INSERT INTO `table` (`column`, `column2`, ...) VALUES (%s, %d, ...)', $fields);
// Get the next row of data from the file
$row = fgets($inv_file, 4096);
}
mysql_*functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.