csv files

[Hank]

I need to write a script to do the following.

1. read data from csv files in a bunch of directories (the list of directories will grow in time)

2. the idea is that these csv files will be updated by car dealers and then uploaded to their respective directories thru ftp (I'm good with this bit as I will set the dir's and access)

3. problem comes in that at the dealer side the content of the files will keep changing. (some car info will remain / some will be deleted / and new ones will be added), this goes for all the dealers.

4. With every csv file they will upload images relating to the data in the csv files, so I will need to link this as well somehow.

5. the idea is to run this file with cron.php at set intervals to update the database (will ask how to do this when I get there)

 

What I've managed so far is to loop through the lot with the glob function, and adding the files, but my problem is updating the database. (cars removed from the csv files must not be deleted just set to sold, so we can keep that data for statistics)

 

Hope someone can point me in the right direction.

 

This is the code i've written so far: (Also added the temp database and some csv files)

PS: there are no headings in the csv files.

 

<?php

$username ="root";

$password = "myPasword";

$host = "localhost";

$table = "csv_table";

$conn = new mysqli("$host", "$username", "$password");

 

// echo "Connected to localhost" . "<br />";

 

mysql_select_db("csvdb") or die(mysql_error());

// echo "Connected to Database";

 

?>

 

 

<?php

 

// Set variable for csv file path

$dir = "dealer_upload/*/*.csv";

 

// Open a known directory, and proceed to read its contents

foreach(glob($dir) as $file)

{

//echo "PATH AND FILENAME: " . $file . "<br /><br />";

 

// Create the array

 

$fileTemp = $file;

$fp = fopen($fileTemp,'r');

$datas = array();

while (($data = fgetcsv($fp)) !== FALSE)

{

$stockNumber = trim($data[0]);

$make = trim($data[1]);

$model = trim($data[2]);

$derivative = trim($data[3]);

$series = trim($data[4]);

$reg = trim($data[5]);

$vin = trim($data[6]);

$driveAwayPrice = trim($data[7]);

$priceExcluding = trim($data[8]);

$specialPrice = trim($data[9]);

$year = trim($data[10]);

$kilometres = trim($data[11]);

$body = trim($data[12]);

$colour = trim($data[13]);

$engine = trim($data[14]);

$transmission = trim($data[15]);

$fuel = trim($data[16]);

$options = trim($data[17]);

$sellingPoints = trim($data[18]);

$nvic = trim($data[19]);

$redBook = trim($data[20]);

 

// Insert Data

mysql_query ("INSERT INTO $table (id_dealer, stockNumber, make, model, derivative, series, reg, vin, driveAwayPrice, priceExcluding, specialPrice, year, kilometres, body, colour, engine, transmission, fuel, options, sellingPoints, nvic, redBook)

VALUES ('$file', '$stockNumber', '$make', '$model', '$derivative', '$series', '$reg', '$vin', '$driveAwayPrice', '$priceExcluding' ,'$specialPrice' , '$year' , '$kilometres' , '$body', '$colour', '$engine', '$transmission', '$fuel', '$options', '$sellingPoints', '$nvic', '$redBook')

")

or die (mysql_error());

 

 

}

 

}

 

?>

csv.zip