Wednesday, January 6, 2010

EASY csv to MySQL in PHP5

This is a class that turns a csv file to a mysql query. It assumes the first line contains the row names. Your database table must contain the same field names (don't need to be in the same order) as the csv file you are importing. The class is free for anyone to use.

The url property is the path to the csv file.
The tableName property is the name of the table you want to insert the csv to.

Sample Usage:


$csv = new csv2mysql();
$csv->tableName = "your mysql table name";
$csv->url = "the path to your csv file";
$csv->putInDb();

Here it is:


class csv2mysql
{
public $url;
public $tableName;

function putInDb()
{
$fh = fopen($this->url, "r");
$i = 0;
while (($data=fgetcsv($fh,1000,","))!==FALSE)
{
$rowValue = "";

foreach($data as $key=>$value)
{
if($i == 0)
{
$row1Value = $row1Value . $value . ",";
//if rows contain any illegal mysql characters now is the time to str_replace them.
}
else
{
$value = htmlspecialchars($value, ENT_QUOTES); //take out any quotes that might mess up the query
$rowValue = $rowValue . "'" . $value . "',"; //wrap row with single quotes for mysql.

}
}
$rowValues = substr($rowValue, 0, -1); //take out extra comma at the end.

$query = "INSERT INTO " . $this->tableName . " (" . substr($row1Value, 0, -1) . ") VALUES (" . $rowValues . ")";
if($i > 0)
{
//echo $query; //for debugging.
mysql_query($query) or die(mysql_error());
}
$i = $i + 1;

}

}
}


If you find errors please let me know in the comments.