php mysql rollback the transaction when user abort the connection

[madura31]

Hi,

 

In my case i want upload csv data in mysql database. any errors come in this transaction. so i want to rollback mysql data. i have no idea. what i do. help me.

ex: user abort connection, empty columns have in csv file, etc..

 

Thanks,

[QuickOldCar]

InnoDB supports rollback but I think you should do all the checks prior and have the file before you do any insertions.

[madura31]

InnoDB supports rollback but I think you should do all the checks prior and have the file before you do any insertions.

 

this is my code.... InnoDB already okay.

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

$db_host = 'localhost';
$db_user = 'root';
$db_pwd = '';

$database = 'csv_import';
$table = 'USER';

$con = mysqli_connect($db_host, $db_user, $db_pwd);
$mysqli = new mysqli($db_host, $db_user, $db_pwd, $database);

if (!$con)
    die("Can't connect db");

if (!mysqli_select_db($con, $database))
    die("can't select db");

if (isset($_POST['submit'])) {
    $fname = $_FILES['sel_file']['name'];
    echo 'upload file name : ' . $fname . ' <br />';
    $chk_ext = explode(".", $fname);

    if (strtolower(end($chk_ext)) == "csv") {
        $filename = $_FILES['sel_file']['tmp_name'];
        $handle = fopen($filename, "r");

        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

            $sql = "INSERT into USER(name,email,phone) values('$data[0]','$data[1]','$data[2]')";
            //mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
            $mysqli->autocommit(FALSE);
            $mysqli->query($sql);
            $mysqli->rollback();
            $mysqli->commit();

//            try {
//                $sql = "INSERT into USER(name,email,phone) values('$data[0]','$data[1]','$data[2]')";
//                //mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
//                $mysqli->autocommit(FALSE);
//                $mysqli->query($sql);               
//                $mysqli->rollback(); 
//            } catch (Exception $e) {                
//                echo $exc->getTraceAsString();
//            }
//            $mysqli->commit();
            //mysqli_query($con, $sql) or ( mysql_query("ROLLBACK") and die(mysqli_error($con) . " - $sql"));
        }


        echo "<br /><br />Success";
        fclose($handle);
    } else {
        echo 'invalid file';
    }
}
?>

<form action="<?php $_SERVER["PHP_SELF"]; ?>" method="post" enctype="multipart/form-data">
    File - <input type="file" name="sel_file" size="20">
    <input type="submit" name="submit" value="submit">
</form>

Edited October 9, 2015 by madura31

[benanamen]

As @QuickOldCar said, you need to validate/check your data first.