0

My situation:

I have to do an e-learning system, where the lecturers(user) will upload an assignment. User will enter assignment details (name, description, etc) and also upload file(s). I have tried to do multiple file upload with transaction to insert the data into two tables, one containing the detail of the assignment and the other is for the file(s) uploaded in one assignment.

I have 2 related tables:

1) assignment - where all the assignment details will be stored

2) assignmentfile - where all the filepath will be stored

I have referred to several posts like this:

Inserting multiple tables with transaction in mysql

PHP & mySQL: Simple code to implement Transaction - Commit & Rollback

I tried to code like that, but somehow my data could not be inserted into the database.

Here is my connection code:

$host = "localhost";
$dbusername = "root";
$dbpassword = "";
$dbname = "test";

// Create connection
$con = new mysqli($host, $dbusername, $dbpassword, $dbname);
// Check connection
if (mysqli_connect_error()){
  die('Connect Error ('. mysqli_connect_errno() .') '
    . mysqli_connect_error());
}

and here is my transaction code:

for ($i = 0; $i < count($_FILES['filename']['name']); $i++) {

        $filename = $_FILES['filename']['name'][$i];
        $target = "upload/";   
        $filetarget = $target.$filename;  
        $tempfilename = $_FILES["filename"]["tmp_name"][$i];
        $result = move_uploaded_file($tempfilename,$filetarget);

        if($result) { 

            // disable autocommit

            mysqli_autocommit($con, FALSE);

            $con->begin_transaction();

            $con-> query("INSERT INTO assignment (assignid, topicid,classid,a_name,description,startdate,duedate,cutoffdate)VALUES ((SELECT topicid FROM topic WHERE topicid = '$topicid'),(SELECT classid FROM class WHERE classid = '$classid'),'$a_name','$description','$startdate','$duedate','$cutoffdate')");

            // last inserted id for assignid
            $assignid = mysqli_insert_id($con);

            $con->query("INSERT INTO assignmentfile (fileid, assignid, filename, filetarget) VALUES ((SELECT assignid FROM assignment where assignid = '$assignid'), '$filename','$filetarget')");

            $con->commit();


      } // end of result

      else {
        $con->rollback();
      }

    } // end of for loop 
}

What did I do wrong? There is no error displayed but the data is not inserted into the database. Is there a better way to do this?

Nazmin Nasya
  • 1
  • 2
  • Use error reporting. Why are you selecting the values you already know? If `assignid = '$assignid'` then `assignid` is `$assignid` so just use `$assignid`... BUT you really should parameterize this. – user3783243 Sep 21 '18 at 03:47
  • what i want my code to do is to insert into the assignment table, then take the last inserted id (which is assignid) and then insert it to the assignmentfile table. assignid is the foreign key in the assignmentfile table. – Nazmin Nasya Sep 21 '18 at 03:57
  • You do the same thing for `classid` and `topicid`. – user3783243 Sep 21 '18 at 04:03
  • Assuming you've got assignid as an AUTO_INCREMENT column in the assignment table. [INSERT INTO ... SELECT](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html) [LAST_INSERT_ID()](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id)`, :filename, :filetarget` is a bit more straight forwards that `INSERT.. VALUES`. Check the return value of each `$con->query`. Do you want each loop iteration to be a transaction or all of them? – danblack Sep 21 '18 at 04:32
  • i want each loop iteration to be a transaction. how would you suggest me to change my code? i'm new to php and i've never done any transaction before... – Nazmin Nasya Sep 22 '18 at 10:50

0 Answers0