Loading a tab-delimited file into MYSQL via php?

Hi All,

I'm trying to write a parser in php which takes a simple tab-delimited file and uploads it to a table in mysql. Here's what I effectively want to do:

1) Take the tab delimited file

http://www.inspireaway.co.uk/td/parse.jpg

2] And upload the data row by row in the table.jpg (note the other column is set in the below code as you'll see)

http://www.inspireaway.co.uk/td/table.jpg

Now, here's my code for trying to upload this data row-by-row:



<?php
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];

$data = file ($tmpName);

foreach ( $data as $line ) {
$val = trim ($line);
$arr = explode ("\t", $val);
$val = "'" . implode ("','", $arr) . "'";
$query = "INSERT INTO tab_del (colour,other) VALUES (".$val.",'sometext')";
mysql_query($query) or die(mysql_error());
}

if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
}

echo "<br>File $fileName uploaded<br>";

}
?>
<h2>Tab testing</h2>

<form method="post" enctype="multipart/form-data">
<table width="350" border="0" cellpadding="1" cellspacing="1" class="box">
<tr>
<td width="246">
<input type="hidden" name="MAX_FILE_SIZE" value="2000000">
<input name="userfile" type="file" id="userfile">
</td>
<td width="80">
<input name="upload" type="submit" class="box" id="upload" value=" Upload ">
</td>
</tr>
</table>
</form>


But when I try and upload the data I get the message Column count doesn't match value count at row 1
Can anyone help?
Thanks

two problems:
in your file, the colors are in single row. From the image, each of the items should go in separate record? but your iteration is per line foreach ( $data as $line )

if each line in the file should go to one record each, you have to assume that each line contains a fixed number of items and adjust the sql query:

$id= $arr[0];//do error checks
$color = $arr[1];
"INSERT INTO tab_del (id,color,other) VALUES ('".$id."','".$color."','sometext')";

Thanks,

I've changed my script to this now as suggested:


<?php
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];

$data = file ($tmpName);

$id= $arr[0];//do error checks
$color = $arr[1];
$query = "INSERT INTO tab_del (id,colour,other) VALUES ('".$id."','".$color."','sometext')";
mysql_query($query) or die(mysql_error());

if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
}

echo "<br>File $fileName uploaded<br>";

}
?>
<h2>Tab testing</h2>

<form method="post" enctype="multipart/form-data">
<table width="350" border="0" cellpadding="1" cellspacing="1" class="box">
<tr>
<td width="246">
<input type="hidden" name="MAX_FILE_SIZE" value="2000000">
<input name="userfile" type="file" id="userfile">
</td>
<td width="80">
<input name="upload" type="submit" class="box" id="upload" value=" Upload ">
</td>
</tr>
</table>
</form>


Now i'm told that the file is uploaded, but it now only uploads one record with no colour from the tab delimited file like so:

id |colour |other
------------------------
5 | | sometext |#

From this file:

http://www.inspireaway.co.uk/td/parse.jpg

Any ideas?