PDO插入3个表格值到4个表格

Question

I just switched to PDO and I'm still having problems with the PHP form. The form is made for teams registration by country user, each country can enter minimum 2 and max 3 players. I created a form that is for 3 players by default the 3rd form is optional in case they needed to add a player or not.

However, I'm new to PDO and I will show you the whole script so you can help me in troubleshooting the script.

The script and form are located in the same file, I run a test and the page turns to white with no Errors.

Now what I'm trying to do is insert at least 2 / a Max of 3 team players data to MySQL.

Here's my PHP code:

<?php 
try 
{
require_once 'connection_pdo.php';

    $championships = 'SELECT * FROM champs 
                      WHERE champ_status = 1 
                      ORDER BY champ_name ASC';
    $championships_results = $db->query($championships);

    $users = 'SELECT userid, username FROM users';
    $users_results = $db->query($users);

    $colname_numRows = "-1";
    if (isset($_SESSION['MM_UserGroup'])) {
      $colname_numRows = $_SESSION['MM_UserGroup']; 
    }

    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

    $stmt = $db->prepare("INSERT INTO players (playerid, FirstName, LastName, Dob, NationalRanking, PhoneNumber, EmailAddress, CountryID)
    VALUES (:playerid, :FirstName, :LastName, :Dob, :NationalRanking, :PhoneNumber, :EmailAddress, :CountryID)");
    //player 1
    $stmt->bindParam(':FirstName', $_POST["playerFirstName1"]);
    $stmt->bindParam(':LastName', $_POST["playerLastName1"]);
    $stmt->bindParam(':Dob', $_POST['year1']."-".$_POST['month1']."-".$_POST['day1']);
    $stmt->bindParam(':Ranking', $_POST["playerNationalRanking1"]);
    $stmt->bindParam(':playerPhoneNumber', $_POST["playerPhoneNumber1"]);
    $stmt->bindParam(':playerEmailAddress', $_POST["playerEmailAddress1"]);
    $stmt->bindParam(':playerCountryID', $_POST["playerCountryID1"]);
    $stmt->execute();
    $last_ID_of_P1 = $db->lastInsertId();
    //player 2
    $stmt->bindParam(':FirstName', $_POST["playerFirstName2"]);
    $stmt->bindParam(':LastName', $_POST["playerLastName2"]);
    $stmt->bindParam(':Dob', $_POST['year2']."-".$_POST['month2']."-".$_POST['day2']);
    $stmt->bindParam(':Ranking', $_POST["playerNationalRanking2"]);
    $stmt->bindParam(':playerPhoneNumber', $_POST["playerPhoneNumber2"]);
    $stmt->bindParam(':playerEmailAddress', $_POST["playerEmailAddress2"]);
    $stmt->bindParam(':playerCountryID', $_POST["playerCountryID2"]);
    $stmt->execute();
    $last_ID_of_P2 = $db->lastInsertId();
    //player 3
    $stmt->bindParam(':FirstName', $_POST["playerFirstName3"]);
    $stmt->bindParam(':LastName', $_POST["playerLastName3"]);
    $stmt->bindParam(':Dob', $_POST['year3']."-".$_POST['month3']."-".$_POST['day3']);
    $stmt->bindParam(':Ranking', $_POST["playerNationalRanking3"]);
    $stmt->bindParam(':playerPhoneNumber', $_POST["playerPhoneNumber3"]);
    $stmt->bindParam(':playerEmailAddress', $_POST["playerEmailAddress3"]);
    $stmt->bindParam(':playerCountryID', $_POST["playerCountryID3"]);
    $stmt->execute();
    $last_ID_of_P3 = $db->lastInsertId();

    $stmt2 = $db->prepare("INSERT INTO participants (playerID, idparticipants, idChampionships, idUsers, created_at, status, booking_status) VALUES($last_ID_of_P1, :idparticipants, :idChampionships, :idUsers, :created_at, 1, 0)");

    //participant player 1
    $stmt2->bindParam(':idparticipants', $_POST["idparticipants1"]);
    $stmt2->bindParam(':idChampionships', $_POST["idChampionships1"]);
    $stmt2->bindParam(':idUsers', $_POST['idUsers1']);
    $stmt2->bindParam(':created_at', $_POST["created_at1"]);
    $stmt2->execute();
    $last_ID_of_participant_1 = $db->lastInsertId();

    //participant player 2
    $stmt2->bindParam(':idparticipants', $_POST["idparticipants2"]);
    $stmt2->bindParam(':idChampionships', $_POST["idChampionships2"]);
    $stmt2->bindParam(':idUsers', $_POST['idUsers2']);
    $stmt2->bindParam(':created_at', $_POST["created_at2"]);
    $stmt2->execute();
    $last_ID_of_participant_2 = $db->lastInsertId();

    //participant player 3
    $stmt2->bindParam(':idparticipants', $_POST["idparticipants3"]);
    $stmt2->bindParam(':idChampionships', $_POST["idChampionships3"]);
    $stmt2->bindParam(':idUsers', $_POST['idUsers3']);
    $stmt2->bindParam(':created_at', $_POST["created_at3"]);
    $stmt2->execute();
    $last_ID_of_participant_3 = $db->lastInsertId();

    $stmtTeam = $db->prepare("INSERT INTO teamPlayers (TeamID, idparticipant, idUsers, Idplayer1, Idplayer2, Idplayer3) VALUES(TeamID, 111021, $last_ID_of_P1, $last_ID_of_P2, $last_ID_of_P3)");
    $stmtTeam->execute();

    //upload script 1
    if(!is_dir("upload/".$_SESSION['MM_Username'])) {
    //do we need to make the uploads directory for the files?
    mkdir("upload/".$_SESSION['MM_Username']);
    //make the rest of the script safe, though this will only be done once
    if(is_uploaded_file($_FILES['uploadedfile']['tmp_name'])){ 
    $folder = "upload/".$_SESSION['MM_Username']; 
    $file = "/" .sha1(rand())."-" .basename($_FILES['uploadedfile']['name']);
    //basename( $_FILES['uploadedfile']['name']); 
    $full_path = $folder.$file; 

        $idPlayer       =   $last_ID_of_P1;
        $IP             =   $_SERVER['REMOTE_ADDR'];
        $FileLocation   =   $full_path;
        $idUsers        =   $_SESSION['MM_UserGroup'];

        if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $full_path)){

            $sql_image ="INSERT INTO files(Time, FileLocation, IP, idUsers, idparticipant)values(NOW(),:FileLocation,:IP,:idUsers,:idparticipant)";
            $qry=$db->prepare($sql_image);
            $success = $qry->execute(array(':FileLocation'=>$full_path,':IP'=>$IP,':idUsers'=>$idUsers,':idparticipant'=>$idPlayer));
        }else{
            $sql_image ="UPDATE files SET Time=?, FileLocation=?, IP=?, idUsers=?, idparticipant=? where idUsers=?";
            $qry=$db->prepare($sql_image);
            $success = $qry->execute(array(NOW(), $full_path, $IP, $idUsers, $idPlayer));   
        }

        if($success){
            echo "<script language='javascript' type='text/javascript'>alert('Successfully Saved!')</script>";
            echo "<script language='javascript' type='text/javascript'>window.open('listfiles.php','_self')</script>";
        }else{
            echo 'db transaction failed';
        }
    } else { 
       echo "upload received! but process failed";
            } 
    }
    else{ 
    echo "upload failure ! Nothing was uploaded";
        } 
    }
}//upload script2 

catch (PDOException $e) 
{
    //$error = $e->getMessage();    
    echo "Error: " . $e->getMessage();
}
?>

As you saw, I'm repeating 3 inserts to get last inserted id on each table for referencing in other tables.

HTML Forms are located in my previous question about the same issues but with mysql_* functions.

Thanks in advance.

PHP MySQL inserting 3 different form values to 4 different tables

Answer 1

you have to read PDO Example:

Complete guide and error debugging

Check your debugging mode is on

error_reporting(E_ALL);

$stmt = $db->prepare("INSERT INTO table(field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $stmt->rowCount();

At a time only one Execute Statement for one Prepare Statement can possible