PDO中的beginTransaction

Question

I've recently started learning about PDO. My question is how can i execute more than 1 prepared statement. In my example i'm trying to add a new student to the database. The first part of the code i'm adding the student into the 'students' table. The second part of the code i'm trying to add all of his classes (from array e.g an array(PHP,JAVA,ANGULAR)) into student_class table (which contain 2 columns - student_id and class_id). Here's a snippet of what i've tried:

function addStudent($name, $phone, $email, $classes){
        global $conn;
        //first part
        $stat = $conn->prepare("INSERT INTO students (sName, phone, email) VALUES(:name, :phone, :email)");
        $stat->bindValue("name",$name,PDO::PARAM_STR);
        $stat->bindValue("phone",$phone,PDO::PARAM_STR);
        $stat->bindValue("email",$email,PDO::PARAM_STR);
        $stat->execute();
        //second part
        //insert classes into student_class
        $lastId = $conn->lastInsertId();
        $conn->beginTransaction();
        $len = count($classes);
        for ($i=0; $i < $len; $i++) {
            $cid = getClassByName($classes[$i]);//returns the class id
            $cl = $conn->prepare("INSERT INTO student_class (student_id,class_id) VALUES(:sid, :cid)");
            $cl->bindValue("sid",$lastId,PDO::PARAM_INT);
            $cl->bindValue("cid",$cid,PDO::PARAM_INT);
            $cl->execute();
        }
        $conn->commit();
    }

try{
     addStudent($params['name'], $params['phone'], $params['email'], $params['classes']);
   }
catch(PDOException $e){
       echo $e->getMessage();
       $conn->rollback();
   }

The result of this is: the user gets added to the 'students' table but the classes remain untouched (i'm getting no error), so i guess i'm doing something wrong with the second part. I hope you can shed some light on this matter.

Answer 1

If these are prepared statements then you only "create" them once, and can execute them multiple times. Also edited your code to print error information, use it to debug.

function addStudent($name, $phone, $email, $classes){
        global $conn;
        //first part
        $stat = $conn->prepare("INSERT INTO students (sName, phone, email) VALUES(:name, :phone, :email)");
        $stat->bindValue("name",$name,PDO::PARAM_STR);
        $stat->bindValue("phone",$phone,PDO::PARAM_STR);
        $stat->bindValue("email",$email,PDO::PARAM_STR);
        $stat->execute();

        //second part
        //insert classes into student_class
        $lastId = $conn->lastInsertId();
        $conn->beginTransaction();
        $len = count($classes);

        $cl = $conn->prepare("INSERT INTO student_class (student_id,class_id) VALUES(:sid, :cid)");
        if (!$cl) {
            echo "
PDO::errorInfo():
";
            print_r($conn->errorInfo());
        }
        for ($i=0; $i < $len; $i++) {
            $cid = getClassByName($classes[$i]);//returns the class id
            $cl->bindValue("sid",$lastId,PDO::PARAM_INT);
            $cl->bindValue("cid",$cid,PDO::PARAM_INT);
            $cl->execute();

            echo "
PDOStatement::errorInfo():
";
            $arr = $cl->errorInfo();
            print_r($arr);
        }
        $conn->commit();
    }

try{
     addStudent($params['name'], $params['phone'], $params['email'], $params['classes']);
   }
catch(PDOException $e){
       echo $e->getMessage();
       $conn->rollback();
   }