Well, I am just an amateur at this, but I have often received help here, so I will just tell you how I do it from my webpage. May be of some help.
I keep these files in mywebpage/admin/
I keep admin locked except when I want to change a table. I have 2 folders in admin: importcsv and createTable
I go to my webpage mysql and truncate the table I want to put the new figures in. I should figure out how to just use UPDATE
The csv files are uploaded to the webpage in ~/includes/mycsvfiles They change every school week.
First, you need an index.php this controls everything.
IGNORE 1 LINES ; tells mysql to forget the first line, the column names.
I never got the prepare part to work, but this is only me inserting studentsâ scores and attendance, so in this case not so important.
If some kind soul here can help me sort out that part, I shall be very grateful!!
<?php
// login to mysql
if (isset($_GET[âimportâ])) {
include $_SERVER[âDOCUMENT_ROOTâ] . â/includes/studentdb.inc.phpâ;
}
if (isset($_POST["tablename"])) {
include $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdbWriteto.inc.php';
try
{
// change the path here to suit
$pre = 'LOAD DATA LOCAL INFILE \'' . $_SERVER['DOCUMENT_ROOT'] . '/includes/mycsvfiles/';
$fileName = $_POST['filename'];
echo 'file name is ' . $fileName;
echo '<br>';
$tablename = $_POST['tablename'];
echo 'table name is ' . $tablename;
// followed by and html br tag you can't see here
echo '<br>';
$intotable = '\' INTO TABLE ';
$charset = ' CHARACTER SET utf8 ';
$rest = ' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES ;' ;
// without this, default is import all columns
// IGNORE 1 LINES (number, name, total, Week_2, Week_1);';
$sql = $pre . $fileName . $intotable . $tablename . $charset . $rest;
echo 'The command for mysql is: ' . $sql;
echo '<br>';
$pdo->exec($sql);
//$pdo->prepare($sql);
//$pdo->exec($sql);
//$s = $pdo->prepare($sql);
//$pdo->exec($s);
//$s->execute();
$output = 'The data from ' . $fileName . ' was successfully inserted in "' . $tablename . '"';
include 'output.html.php';
exit();
}
catch (PDOException $e)
{
$output = 'Error importing ' . $fileName . ' into ' . $tablename . ' This problem: ' . $e->getMessage();
include 'output.html.php';
exit();
}
}
// call the input form to start with
include 'form.html.php';
?>
Then you need a form.html.php This is the first page you see. Enter the table name and file name here
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Get csv</title>
</head>
<body>
<h1>Get the csv file </h1>
<h2> The table name should be the same as the csv file, like 19BE3 PHP will add .csv to it.</h2>
<form action="?" method="post" name="uploadCSV" enctype="text" >
<div class="input-row">
<label for="tablename">Enter the name of the table you want to put data in:</label>
<input type="text" name="tablename" placeholder="import into which table??"><br>
<label for="filename">Enter the name of the csv file you want to import:</label>
<input type="text" name="filename" placeholder="just write the file name."><br>
<!-- <button type="submit" id="submit" name="import" >Import csv file</button> -->
<!-- <button type="reset" id="submit" name="import" >Import csv file</button> -->
<div><input type="submit" value="Import csv file" name="import"><input type="reset" value="Clear Form"></div>
</div>
</form>
</body>
</html>
Lastly, you need an output.html.php just to tell you what happened, either error or success (works for me most times without error!)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>CSV import</title>
</head>
<body>
<p>
<h2>Did we manage to populate a table?</h2>
<?php echo $output; ?><br>
</p>
</body>
</html>
This works nicely for my amateur stuff. Security-wise itâs probably a nightmare, so any tips will be well received!!
Like I said, when Iâm finished, I lock the admin up, so it canât be used.
