Here is the code for reference in case anyone needs bits of it:
class dataUpload
{
protected function parseDataType($strUnparsed)
{
$mysqlDataTypes=array ("CHAR","VARCHAR","TINYTEXT","TEXT","BLOB","MEDIUMTEXT","MEDIUMBLOB","LONGTEXT","LONGBLOB", "TINYINT","SMALLINT","MEDIUMINT","INT","BIGINT","FLOAT","DOUBLE","DECIMAL","DATE","DATETIME","TIMESTAMP","TIME","ENUM","SET");
Foreach ($mysqlDataTypes as $aMysqlDataType)
{
if (preg_match("/" . $aMysqlDataType ."/i", $strUnparsed))
{$inputDataType=$aMysqlDataType;}
}
switch ($inputDataType)
{
case "TINYINT":
case "SMALLINT":
case "MEDIUMINT":
case "INT":
case "BIGINT":
$outputDataType="i";
break;
case "FLOAT":
case "DOUBLE":
$outputDataType="d";
break;
case "BLOB":
case "MEDIUMBLOB":
case "LONGBLOB":
$outputDataType="b";
break;
default:
$outputDataType="s";
break;
}
return $outputDataType;
}
protected function getFieldMetaData($whatMetaData,$dbName,$table)
{
#This function can be used to retrieve any meta attribute of a table field
$dbManual = new mysqli('localhost', 'user', 'pass', $dbName);
$sql="SHOW FIELDS FROM $table";
$result=$dbManual->query($sql);
$strFieldMetaData="";
$counter=0;
switch ($whatMetaData)
{
case "Type":
while ($row = mysqli_fetch_array($result))
{
if ($counter==0)
{$strFieldMetaData=$this->parseDataType($row[$whatMetaData]);}
else
{$strFieldMetaData=$strFieldMetaData . $this->parseDataType($row[$whatMetaData]);}
$counter++;
$StrOfQuestionMarks="";
}
$arrFieldMetaData=array ($strFieldMetaData,$counter,$StrOfQuestionMarks);
break;
case "Field":
$StrOfQuestionMarks="";
while ($row = mysqli_fetch_array($result))
{
if ($counter==0)
{$strFieldMetaData="". $row[$whatMetaData];
$StrOfQuestionMarks="?";}
else
{$strFieldMetaData=$strFieldMetaData .",". $row[$whatMetaData];
$StrOfQuestionMarks=$StrOfQuestionMarks . "," . "?";
}
$counter++;
}
$arrFieldMetaData=array ($strFieldMetaData,$counter,$StrOfQuestionMarks);
break;
default:
echo "No action has been programmed to process this attribute of the table!";
break;
}
$result->close();
mysqli_close($dbManual);
Return $arrFieldMetaData;
}
function doUploadUsingInserts($csvfile,$uploadDir,$table,$uniqueId,$dbName)
{
#Working from the perspective of the mysql database
# Create the string of data types for the prepared insert statement
$arrFieldMetaTypesDb=$this->getFieldMetaData('Type',$dbName,$table);
$strOfDataTypesDb= $arrFieldMetaTypesDb [0];
# Create the list of field names for the prepared insert statement
$arrFieldMetaNamesDb=$this->getFieldMetaData('Field',$dbName,$table);
$strOfFieldNamesDb=$arrFieldMetaNamesDb [0];
$strOfQuestionMarksDb=$arrFieldMetaNamesDb [2];
$noOfFieldsInTableDb=$arrFieldMetaNamesDb [1];
echo "$strOfDataTypesDb</br>";
echo "$strOfFieldNamesDb</br>";
echo "$strOfQuestionMarksDb</br>";
echo "$noOfFieldsInTableDb</br></br>";
#Working form the perspective of the csv file
$csv = new File_CSV_DataSource;
$csv->load($uploadDir . $csvfile);
#Create a comma delimited string of field names from the csv file
$arrFieldNamesCsv=($csv->getHeaders());
Foreach ($arrFieldNamesCsv as $key=>$value)
{
if ($key==0)
{$strFieldNamesCsv=$value;}
else
{$strFieldNamesCsv=$strFieldNamesCsv . "," . $value;}
}
#Upload Validation Check #1 :Count the number of fields returned from db and compare with those in csv file
#No fields in csv file
$NoOfColumnsCsv=($csv->countHeaders());
#Now we get the data from the csv file
#Create a comma delimited string of values for each row
$arrOfValues=array ();
$arrAllData=$csv->getRawArray();
$NoOfRows=count($arrAllData);
$counter=0;
While ($counter < $NoOfRows)
{
$subArray=$csv->getRow($counter);
$strValuesToBeInserted="";
Foreach ($subArray as $FieldKey=>$FieldValue)
{
if ($FieldKey==0)
{$strValuesToBeInserted=$FieldValue;}
else
{$strValuesToBeInserted=$strValuesToBeInserted . "," . $FieldValue;}
}
$arrOfValues[$counter]=$strValuesToBeInserted;
$counter++;
}
echo "There are " . $NoOfRows . " data records in the csv file that are going to be uploaeded to the table " . $table . " in the " . $dbName . " database...</br></br>";
#Create a mysqli prepared query wrapped in a transaction to insert these values into the database table
$dbManual = new mysqli('localhost', 'user', 'pass', $dbName);
if (mysqli_connect_errno())
{
printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
exit;
}
// turn off auto-commit
mysqli_autocommit($dbManual, FALSE);
#Create a mysqli prepared query wrapped in a transaction to insert these values into the database table
// Bind parameters (an integer and a string). 'is' tells MySQL you're passing an integer(i) and a string(s)
#Get the csv file as an associative array with keys and values where the keys are the field names
#and we are trying to convert these to variables to bind to the Mysqli prepared statement
$params=$csv->getRow(0);
$sql="INSERT INTO $table VALUES ($strOfQuestionMarksDb)";
echo $sql;
$stmt = $dbManual->prepare($sql);
/* bind parameters for markers */
# We are trying to achieve: $stmt->bind_param("sssssssssiiiiiiiiii", $city,$fdf,$dfdf);
array_unshift($params, $strOfDataTypesDb);
print_r ($params);
$res=call_user_func_array(array(&$stmt, "bind_param"), $params);
echo "Did it bind the parameters " . $res;
$stmt->execute();
$params=$csv->getRow(2);
$stmt->execute();
$params=$csv->getRow(3);
$stmt->execute();
mysqli_commit($dbManual);
#$result = $dbManual->query($sql);
#if ($result !== TRUE)
# {
# echo ("There was an error in the upload, it is being rolled back!");
# mysqli_rollback($dbManual); // if error, roll back transaction
# }
#else
# {
# echo ("The upload was successful, it has been committed!");
# // assuming no errors, commit transaction
# }
$dbManual->close();
}
