Unfortunately my webhoster has blocked the possibility to insert files to my mysql database using LOAD DATA INFILE
.
However he supplies the following script to upload a csv file to mysql.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> CSV-Import </TITLE>
</HEAD>
<BODY>
<?php
ini_set('max_execution_time',60);
foreach($_POST as $key => $value)
{
$_POST[$key]=trim($value);
}
if ( $_POST['submit'] AND $_POST['trenner'] )
{
$db = mysqli_connect("localhost",$_POST['dbuser'],$_POST['dbpass'],$_POST['datenbank']) or die(mysqli_error($db));
$spalten = mysqli_num_rows(mysqli_query($db, "SHOW COLUMNS FROM ".$_POST['dbtabelle']));
$datei = fopen($_POST['dateiname'],"r");
while( !feof($datei) )
{
$zeile = fgets($datei,1000);
if( strlen($zeile) > 3 )
{
$daten = explode($_POST['trenner'],$zeile);
$dbquery1 = "INSERT INTO `".$_POST['dbtabelle']."` VALUES (";
$dbquery2 = "";
$dbquery3 = ")";
for( $i=0; $i < $spalten; $i++ )
{
$dbquery2 = $dbquery2."'".rtrim(addslashes($daten[$i]))."', ";
}
$dbquery2 = substr($dbquery2,0,-2);
echo $dbquery1.$dbquery2.$dbquery3."<br>";
mysqli_query($db, $dbquery1.$dbquery2.$dbquery3) or die(mysqli_error($db));
}
}
mysqli_close($db);
}
else
{
?>
<form name="import" method="post" action="<? echo $PHP_SELF; ?>">
<table align="center" width="700px" border="1">
<tr>
<th colspan="3">CSV - Import</th>
</tr>
<tr>
<td colspan="2">
<p align="justify">
Tragen Sie hier den Namen der Datei und die Zugangsdaten zur Datenbank ein.
<br>
Die CSV-Datei wird in Ihrem aktuellen Verzeichnis <b>"<? echo dirname($_SERVER['PHP_SELF'])."/";?>"</b> gesucht.
<br>
<br>
Die Datenbank muss alle nötigen Voraussetzungen an vorhandenen Feldern und deren Eigenschaften erfüllen.
</p>
</td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td align="right">Dateiname: </td>
<td><input type="text" name="dateiname" value=""> (z.B. daten.csv)</td>
</tr>
<tr>
<td align="right">Datenbank: </td>
<td><input type="text" name="datenbank" value=""> (z.B. d0123456)</td>
</tr>
<tr>
<td align="right">Datenbankuser: </td>
<td><input type="text" name="dbuser" value=""> (z.B. d0123456)</td>
</tr>
<tr>
<td align="right">Datenbankpasswort: </td>
<td><input type="text" name="dbpass" value=""> (z.B. meinpasswort)</td>
</tr>
<tr>
<td align="right">Datenbanktabelle: </td>
<td><input type="text" name="dbtabelle" value=""> (z.B. meine_daten)</td>
</tr>
<tr>
<td align="right">CSV Trennzeichen: </td>
<td><input type="text" name="trenner" value=";" maxlength="1"> (z.B. ; oder ,)</td>
</tr>
<tr>
<td colspan="2"> </td></tr>
<tr>
<td align="center" colspan="2"><input type="submit" name="submit" value="Importieren!"></td>
</tr>
</table>
</form>
<?
The script works in a way where one should drop the csv file in a folder and where one should add the database values with every import.
I would like to use the script in parts to run it without the need to enter the credentials with any import again. It should be run by just calling the php file as a cronjob.
Could someone please help.
Thank you.