php array routine, help please

[john]

Hello All,

I am reading a csv-file into an array and inserting it into a mysql
database.
The first 2 fields in the table are NOT NULL and required, and the
last field in the table may be empty, but in the csv-file there is no
indication of this, such as an empty "".

For example, the format of the csv-file is:
=============== =============== =====
"value1", "value2"
"value1", "value2", "value3"
"value1", "value2", "value3"
"value1", "value2"
"value1", "value2"
"value1", "value2", "value3"
=============== =============== =====

Value1 and Value2 will always be on each line, however value 3 may or
may not.

The following code works fine until it finds a line in the csv file
that only has the first 2 required values...
The error is obviously "Column count doesn't match value count at row
1"

How could I:
fix this routine to find out if each line only has 2 entries
and how to have my $sql reflect that?

BTW, the '' in the sql statement is for the first value field, which is
auto-increment...

thanks!

=============== =============== =============== =============== =============== ======

$fcontents = file ('./test_file.txt') ;
for ($i=0; $i<sizeof($fcon tents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode("\"", $line);

$sql = "insert into log_data values ('',". implode("'", $arr).")";

if(mysql_error( )) {
echo "<b>" . mysql_error() ."</b><br><br>\n";
}
}
=============== =============== =============== =============== =============== ======

[Ken Robinson]

Re: php array routine, help please



john wrote:[color=blue]
> Hello All,
>
> I am reading a csv-file into an array and inserting it into a mysql
> database.
> The first 2 fields in the table are NOT NULL and required, and the
> last field in the table may be empty, but in the csv-file there is no
> indication of this, such as an empty "".
>
> For example, the format of the csv-file is:
> =============== =============== =====
> "value1", "value2"
> "value1", "value2", "value3"
> "value1", "value2", "value3"
> "value1", "value2"
> "value1", "value2"
> "value1", "value2", "value3"
> =============== =============== =====
>
> Value1 and Value2 will always be on each line, however value 3 may or
> may not.
>
> The following code works fine until it finds a line in the csv file
> that only has the first 2 required values...
> The error is obviously "Column count doesn't match value count at row
> 1"
>
> How could I:
> fix this routine to find out if each line only has 2 entries
> and how to have my $sql reflect that?
>
> BTW, the '' in the sql statement is for the first value field, which is
> auto-increment...
>[/color]

First, I would re-think how to solve the problem.

Have you looked at the function fgetcsv()? This function will read csv
files into arrays. That way for each line read in you can tell how many
value you have by looking at the count of the generated array.

As for your second question, instead of using the insert format you
have used, I would use

insert into xyx set field1='value1' , field2='value2'

That way you can just set the fields you have. For example:

<?
$fields = array('field1', 'field2','field 3','field4');
$fp = fopen('yourcsv. file','r');
while (($tmp = fgetcsv($fp)) != FALSE) {
$q = "insert into xyz set ";
$tmpq = array();
for ($i=0;$i<count( $tmp);$i++) // $tmp holds the results from
fgetcsv()
$tmpq[] = $fields[$i] . "='" . $tmp[$i] . "'";
$q .= implode(',',$tm pq);
echo $q."<br>\n"; //debugging only
$rs = mysql_query($q) or die('Problems ... ' . mysql_error());
}
fclose($fp);
?>

Code not checked for errors.

Ken