I have this csv file:
NrSAT,StatoSAT,TipoServ,DataAtt,ImeiG,ImeiC,MarcaTer,ModelloTer,FamigliaG,DescrizioneG
SAT100000002572,in lavorazione,21/07/2014,8294121141143,8294121141143,Samsung,Nexus 4,Audio, Microfono Rott
SAT100000002573,in lavorazione,21/07/2014,8294121141143,8294121141143,Samsung,Nexus 4,Audio, Microfono Rot
SAT100000002574,in lavorazione,21/07/2014,8294121141143,8294121141143,Samsung,Nexus 4,Audio, Microfono Ro
SAT100000002575,in lavorazione,21/07/2014,8294121141143,8294121141143,Samsung,Nexus 4,Audio, Microfono R
SAT100000002576,in lavorazione,21/07/2014,8294121141143,8294121141143,Samsung,Nexus 4,Audio, Microfono Roto
I want to copy precisely this csv file in a database table. I tried this code but i have an error:
<?php
$conn=mysql_connect("localhost","root","")or die (mysql_error());
mysql_select_db("sat",$conn);
if (isset($_POST['submit']))
{
$file = $_FILES ['file']['tmp_name'];
$handle = fopen ($file,"r");
while(( $fileop = fgetcsv($handle ,10000 ,",")) !== false)
{
$NumeroSAT = $fileop[0];
$StatoSAT = $fileop[1];
$TipoServizio = $fileop[2];
$DataAttivazione = $fileop[3];
$ImeiGuasto = $fileop[4];
$ImeiConsegnato = $fileop[5];
$MarcaTerminale = $fileop[6];
$ModelloTerminale = $fileop[7];
$FamigliaGuasto = $fileop[8];
$DescrizioneGuasto = $fileop[9];
echo $NumeroSAT;
echo $StatoSAT;
echo $TipoServizio;
echo $DataAttivazione;
echo $ImeiGuasto;
echo $ImeiConsegnato;
echo $MarcaTerminale;
echo $ModelloTerminale;
echo $FamigliaGuasto;
echo $DescrizioneGuasto;
$sql = mysql_query ("INSERT INTO gestite (NrSAT,StatoSAT,TipoServ,DataAtt,ImeiG,ImeiC,MarcaTer,ModelloTer,FamigliaG,DescrizioneG) VALUES ('$NumeroSAT ','$StatoSAT ','$TipoServizio','$DataAttivazione','$ImeiGuasto','$ImeiConsegnato,'$MarcaTerminale','$ModelloTerminale','$FamigliaGuasto','$DescrizioneGuasto')");
}
}
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import a CSV File with PHP & MySQL</title>
</head>
<body>
<div id="mainWrapper">
<form action="lol.php" method="post" enctype="multipart/form-data" >
<input type="file" name="file" />
<br>
<input type="submit" name="submit" value="submit" />
</form>
</div>
</body>
</html>
This is the error that i have after thaht i loaded the csv file :
Notice: Undefined offset: 9 in C:\xampp\htdocs\Hop\lol.php on line 32
Some of you would know tell me why my code does not work? I have tried many things to fix it but I can not!
The CSV you are loading indicates 10 columns when looking at the first line. However the following lines only hold 9 values.
So, either you add a comma at the end of these lines, or if the last column is optional, use the following code:
$DescrizioneGuasto = isset($fileop[9]) ? $fileop[9] : '';
Thus, you would at least get rid of the error.
However, it looks like in fact "StatoSAT" or "TipoServ" is missing in the data.
Your code and sample data are really messy. Your CSV sample has a header listing 10 columns, but the rows beneath it have 9 columns. Your code expects 10 (0 - 9). Consider rewriting your code to a more manageable version, like so:
$header = array(
"NrSAT", "StatoSAT", "TipoServ", "DataAtt", "ImeiG",
"ImeiC", "MarcaTer", "ModelloTer", "FamigliaG", "DescrizioneG"
);
This $header now contains both your SQL field names and variable names in a more consistent manner. You don't really need the variable names, but you can retain them by using array_combine() or list:
while( $fileop = fgetcsv($handle...)) {
// fix: guarantees csv lines will have 10 columns
$fileop = array_pad( $fileop, 10, "");
$row = array_combine( $header, $fileop );
}
Rather than manually assign each variable name and print them, you can just do print_r( $row ); and get a very readable output. You can refer to individual columns as $row["NrSAT"] and get whatever you need. The print_r output will look like this:
Array (
[NrSAT] => SAT100000002572
[StatoSAT] => in lavorazione
[TipoServ] => 21/07/2014
[DataAtt] => 8294121141143
[ImeiG] => 8294121141143
[ImeiC] => Samsung
[MarcaTer] => Nexus 4
[ModelloTer] => Audio
[FamigliaG] => Microfono Rott
[DescrizioneG] =>
)
The MySQL functions you're using are deprecated, and you should switch to PDO. But, if you really want to stick to the old stuff, here's how you can make use of the rewrite (inside the while loop).
foreach( $row as $k => $v ) {
$row[$k] = mysql_real_escape_string($v); // prevents SQL injection + errors
}
$sql = sprintf(
"insert into gestite (%s) values ('%s')",
implode(", ", $header),
implode("', '", $row )
);
mysql_query( $sql );
Your SQL line becomes programmatically generated without any manual construction, eliminating errors from oversight and typos. If you echo $sql, you'll see this:
insert into gestite (NrSAT, StatoSAT, TipoServ, DataAtt, ImeiG, ImeiC, MarcaTer,
ModelloTer, FamigliaG, DescrizioneG) values ('SAT100000002572', 'in lavorazione',
'21/07/2014', '8294121141143', '8294121141143', 'Samsung', 'Nexus 4', 'Audio',
'Microfono Rott', '');
From here, it's just a short leap into PDO. I suggest this fine tutorial: http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059