novice using fgetcsv

[kristinac]

I am a web designer/PHP hack trying to solve a problem for our HR department. We have a quarterly newsletter in a Wordpress site I developed and they want to have a dynamically updated service anniversary page updated through excel. The solution to this problem is clearly fgetcsv, but I'm having trouble understanding how to implement it within the wordpress page.

 

Here is a really nice function from the php manual page

 

<?php
function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) {
    if (($handle = fopen("$source_file", "r")) !== FALSE) {
        $columns = fgetcsv($handle, $max_line_length, ",");
        foreach ($columns as &$column) {
            $column = str_replace(".","",$column);
        }
        $insert_query_prefix = "INSERT INTO $target_table (".join(",",$columns).")\nVALUES";
        while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
            while (count($data)<count($columns))
                array_push($data, NULL);
            $query = "$insert_query_prefix (".join(",",quote_all_array($data)).");";
            mysql_query($query);
        }
        fclose($handle);
    }
}

function quote_all_array($values) {
    foreach ($values as $key=>$value)
        if (is_array($value))
            $values[$key] = quote_all_array($value);
        else
            $values[$key] = quote_all($value);
    return $values;
}

function quote_all($value) {
    if (is_null($value))
        return "NULL";

    $value = "'" . mysql_real_escape_string($value) . "'";
    return $value;
}
?> 

 

But this leaves me with a few questions:

1. is this both function AND query? or do i need extra code to run the function and spit out the results?

2. where do i input my actual values?

3. does this have to mirror a mysql table somewhere else? i feel like i'm missing a crucial piece of info here.

 

the path to the csv file needs to be absolute, due to HR needing to manage the file themselves without the help of yours truly. the intended output will look like this:

 

5 YEARS

FirstName1 LastName1, FirstName2 LastName2, FirstName3 LastName3, etc

 

6 YEARS

FirstName1 LastName1, FirstName2 LastName2, FirstName3 LastName3, etc

 

and so on. so in addition to the FirstName, LastName and Year columns, the loop will need to include a conditional statement. something like:

 

<h1>5 YEAR</h1>
<ul>
<?php 
     if (quote_all_array(5) ) : ?>
          echo "<li>(FirstName, LastName), </li>";
<?php endif; ?> 
</ul> 

<h1>6 YEAR</h1>
<ul>
<?php 
     if (quote_all_array(6) ) : ?>
          echo "<li>(FirstName, LastName), </li>";
<?php endif; ?>
</ul> 

 

I'm sure my syntax is horribly wrong because I have virtually no idea what I'm doing. Which is why I'm asking you all for help

 

to help my understanding i'll supply a set of variables:

 

CSV Source: http://resource.mysite.com/anny.csv

Table Columns: FirstName, LastName, Year

External MySql database (should it apply): AnnySql

 

Thanks in advance for any help. I'm over my pay grade on this one.

[kristinac]

Bueller?

[jcbones]

The problem is that you will have to know the exact CSV format.  Is there any way you could give a sample CSV file.  This way we could see if the first line contains column names, etc.

 

Given that, I would be glad to help you out.

[kristinac]

the column names are listed: FirstName, LastName, Year, and would be in row one

[jcbones]

Try this:

<?php

function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) {
    if (($handle = fopen($source_file, "r")) !== FALSE) {
        $columns = fgetcsv($handle, $max_line_length, ",");
        foreach ($columns as &$column) {
            $column = str_replace(".","",$column);
        }
        while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
            while(count($data) < count($columns)) {
			array_push($data, NULL);
		}
		$c = count($data);
		for($i = 0; $i < $c; $i++) {
			$data[$i] = "'{$data[$i]}'";
		}
		$sql[] = '(' . implode(',',$data) . ')';
        }
	fclose($handle);
	$query = "INSERT INTO $target_table (".implode(",",$columns).")VALUES " . implode(',',$sql) . "\n";
	mysql_query($query) or trigger_error(mysql_error());		
    }
} 

$file = 'test.csv'; //path name to your CSV file;
$table = 'test'; //name of your database table.  **NOTE *** column names must match EXACTLY.

csv_file_to_mysql_table($file,$table); //calling the function.

?>

 

If nothing shows up in your database table, let me know.  This should work if the file is less than ~1,000 rows.

 

[kristinac]

thanks. i've had to hard-code it for the time being to keep the project on time but i will try that out once we're live and i have a spare moment.