Hi Halojoy:🙂
Thanks for the detailed response. Essentially I realized the same thing. Transactions dont work with ISAM tables. But they work with InnoDb tables.
I was simply able to convert my table to INNODb using an ALTER statement in PHPMYADMIN and my lo and behold the transactional features of Mysqli started working.
But then I ran into the next issue in trying to import my data from a CSV file into my database using LOAD DATA INFILE. It works, for anyone in the future who wants to use it here is the code:
function doUploadLoadInFile($csvfile,$uploadDir,$table,$uniqueId,$dbName)
{
/* Connect to a MySQL server */
$dbManual = new mysqli('localhost', 'xxxx', 'xxxx', $dbName);
$sql = "LOAD DATA LOCAL INFILE '" . $uploadDir . $csvfile . "' INTO TABLE " . $table ." FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r'";
if (mysqli_connect_errno())
{
printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
exit;
}
#Count rows in table before upload
$sqlCount1="SELECT " . $uniqueId . " FROM " . $table;
$result1 = $dbManual->query($sqlCount1);
$resultCount1=mysqli_num_rows($result1);
// turn off auto-commit
mysqli_autocommit($dbManual, FALSE);
$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
mysqli_commit($dbManual);
}
#Count rows in table after upload and calculate the no of rows uploaded.
$sqlCount2="SELECT " . $uniqueId . " FROM " . $table;
$result2 = $dbManual->query($sqlCount2);
$resultCount2=mysqli_num_rows($result2);
$NoAffectedRows=$resultCount2-$resultCount1;
printf("Affected rows (Upload): %d\n", $NoAffectedRows);
// close connection
mysqli_close($dbManual);
}
However i realized that LOAD DATA INFILE is not the ideal solution as in case there is an error in uploading a record it will skip it. Therefore i am now redoing this by using individual insert statements wrapped in a mysqli transaction as described in the above code.
However issues just keep coming up. The next issue is one of Mysqli prepared statements which i will post in a separate thread. These haves several issues in that in that each of the parameters have to be specified to be bound, moreover their data type has to be listed as:
/* Create the prepared statement */
if ($stmt = $mysqli->prepare("INSERT INTO CodeCall (FirstName, LastName) values (?, ?)")) {
/* Bind our params */
$stmt->bind_param('ss', $firstName, $lastName);
/* Set our params */
$firstName = "Jordan";
$lastName = "DeLozier";
/* Execute the prepared Statement */
$stmt->execute();
However I have to run this code dynamically on different tables, with different parameters, each imported from a csv file, and each field has a different data type which i cannot gather from looking at the csv file (as some values are null)
Therefore mysqli prepared statements seem really restrictive and bothersome and I am thinking of just going with old mysql stlyle inserts where i can simply put in a string a fields and corresponding values and wrap in a loop and in my transaction and it will work like magic.
However this will be much slower than load in file, but much more reliable. It will be slower than mysqli queries as each query will have to be executed separately unlike in a prepared statement. And i will have to figure out how to guard against mysql injection attacks .
But it will work. Mysqli prepared statements dont seem suited at all to dyamic parameters with unknown data types.
Any suggestions?
Should i be starting a separate thread for this?😕
