PDO транзакции через функцию

Meylis Day

Первоначально стояла задача сделать множественный запрос на добавление данных в три разные таблицы связанные по одному id. Решил делать в три разных запроса через транзакции. Функции по отдельности работают, а через транзакцию данные не добавляются, прилагаю код:

public static function createTransaction($dbh){
try {
    $dbh->beginTransaction();
    $get_id=func::getSessionId($dbh);

    $first_name = htmlspecialchars($_POST['first_name']);
    $surname = htmlspecialchars($_POST['surname']);
    $patronymic = NULL;
    $birthday =  htmlspecialchars($_POST['birthday']);
    $scope_of_work = htmlspecialchars($_POST['scope_of_work']);
    $programming_languages = htmlspecialchars($_POST['programming_languages']);
    $experience = htmlspecialchars($_POST['experience']);
    $last_place_of_work = NULL;
    $personal_info = NULL;
    $link_of_github = NULL;
    $points = 10;
    $city = NULL;
    $dev_status = 0;

    $get_id_users=func::checkDeveloper($dbh);

    if (!$get_id_users){
    $stmt = $dbh->prepare("INSERT INTO devpro.developers (surname, first_name, patronymic, birthday, scope_of_work, programming_languages, experience, last_place_of_work, personal_info, link_of_github, user_id, points, city, dev_status) VALUES (:surname, :first_name, :patronymic, :birthday, :scope_of_work, :programming_languages,:experience, :last_place_of_work, :personal_info, :link_of_github, :user_id, :points, :city,:dev_status)");

    $stmt->bindParam(':surname', $surname);
    $stmt->bindParam(':first_name', $first_name);
    $stmt->bindParam(':patronymic', $patronymic, PDO::PARAM_INT);
    $stmt->bindParam(':birthday', $birthday);
    $stmt->bindParam(':scope_of_work', $scope_of_work);
    $stmt->bindParam(':programming_languages', $programming_languages);
    $stmt->bindParam(':experience',$experience);
    $stmt->bindParam(':last_place_of_work', $last_place_of_work, PDO::PARAM_INT);
    $stmt->bindParam(':personal_info', $personal_info, PDO::PARAM_INT);
    $stmt->bindParam(':link_of_github', $link_of_github,PDO::PARAM_INT);
    $stmt->bindParam(':user_id', $get_id);
    $stmt->bindParam(':points', $points);
    $stmt->bindParam(':city', $city, PDO::PARAM_INT);
    $stmt->bindParam(':dev_status', $dev_status);
    $stmt->execute();
    }
    $last_id = $dbh->lastInsertId();


    $name_place_education = NULL;
    $specialty = NULL;
    $year_start = NULL;
    $year_finish = NULL;

    $stmt = $dbh->prepare("INSERT INTO devpro.education (name_place_education, specialty, year_start, year_finish, developer_id) VALUES (:name_place_education, :specialty, :year_start, :year_finish, :developer_id)");

    $stmt->bindParam(':name_place_education',$name_place_education);
    $stmt->bindParam(':specialty',$specialty);
    $stmt->bindParam(':year_start',$year_start);
    $stmt->bindParam(':year_finish',$year_finish);
    $stmt->bindParam(':developer_id',$last_id );
    $stmt->execute();

    $vk_link = NULL;
    $facebook_link = NULL;
    $tel = NULL;


    $stmt = $dbh->prepare("INSERT INTO devpro.contacts (vk_link, facebook_link, tel, developer_id) VALUES (:vk_link, :facebook_link, :tel, :developer_id)");

    $stmt->bindParam(':vk_link', $vk_link);
    $stmt->bindParam(':facebook_link', $facebook_link);
    $stmt->bindParam(':tel', $tel);
    $stmt->bindParam(':developer_id', $last_id );
    $stmt->execute();

    $dbh->commit();
    } catch (PDOException $e) {
        $dbh->rollBack();
    }
}

Функция находится в отдельном файле, при вызове ее func::createTransaction($dbh); вообще ничего не происходит, не совсем понимаю, что я делаю не так. Заранее, спасибо.

1 ответ

Meylis Day

Решил проблему. Сделал функцию не статичной, соответственно и вызов ее поменялся, а также перенес определение переменных перед $dbh->beginTransaction();

Получилось вот так

public function createTransaction($dbh)
    {
    try {
    $get_id=func::getSessionId($dbh);
    $get_id_users=func::checkDeveloper($dbh);
    $first_name = htmlspecialchars($_POST['first_name']);
    $surname = htmlspecialchars($_POST['surname']);
    $patronymic = NULL;
    $birthday =  htmlspecialchars($_POST['birthday']);
    $scope_of_work = htmlspecialchars($_POST['scope_of_work']);
    $programming_languages = htmlspecialchars($_POST['programming_languages']);
    $experience = htmlspecialchars($_POST['experience']);
    $last_place_of_work = NULL;
    $personal_info = NULL;
    $link_of_github = NULL;
    $points = 10;
    $city = NULL;
    $dev_status = 0;

    $name_place_education = NULL;
    $specialty = NULL;
    $year_start = NULL;
    $year_finish = NULL;

    $vk_link = NULL;
    $facebook_link = NULL;
    $tel = NULL;

    $dbh->beginTransaction();

    if (!$get_id_users){
    $stmt = $dbh->prepare("INSERT INTO devpro.developers (surname, first_name, patronymic, birthday, scope_of_work, programming_languages, experience, last_place_of_work, personal_info, link_of_github, user_id, points, city, dev_status) VALUES (:surname, :first_name, :patronymic, :birthday, :scope_of_work, :programming_languages,:experience, :last_place_of_work, :personal_info, :link_of_github, :user_id, :points, :city,:dev_status)");
    $stmt->bindParam(':surname', $surname);
    $stmt->bindParam(':first_name', $first_name);
    $stmt->bindParam(':patronymic', $patronymic, PDO::PARAM_INT);
    $stmt->bindParam(':birthday', $birthday);
    $stmt->bindParam(':scope_of_work', $scope_of_work);
    $stmt->bindParam(':programming_languages', $programming_languages);
    $stmt->bindParam(':experience',$experience);
    $stmt->bindParam(':last_place_of_work', $last_place_of_work, PDO::PARAM_INT);
    $stmt->bindParam(':personal_info', $personal_info, PDO::PARAM_INT);
    $stmt->bindParam(':link_of_github', $link_of_github,PDO::PARAM_INT);
    $stmt->bindParam(':user_id', $get_id);
    $stmt->bindParam(':points', $points);
    $stmt->bindParam(':city', $city, PDO::PARAM_INT);
    $stmt->bindParam(':dev_status', $dev_status);
    $stmt->execute();
    }
    $last_id = $dbh->lastInsertId();

    $stmt = $dbh->prepare("INSERT INTO devpro.education (name_place_education, specialty, year_start, year_finish, developer_id) VALUES (:name_place_education, :specialty, :year_start, :year_finish, :developer_id)");

    $stmt->bindParam(':name_place_education',$name_place_education);
    $stmt->bindParam(':specialty',$specialty);
    $stmt->bindParam(':year_start',$year_start);
    $stmt->bindParam(':year_finish',$year_finish);
    $stmt->bindParam(':developer_id',$last_id );
    $stmt->execute();


    $stmt = $dbh->prepare("INSERT INTO devpro.contacts (vk_link, facebook_link, tel, developer_id) VALUES (:vk_link, :facebook_link, :tel, :developer_id)");

    $stmt->bindParam(':vk_link', $vk_link);
    $stmt->bindParam(':facebook_link', $facebook_link);
    $stmt->bindParam(':tel', $tel);
    $stmt->bindParam(':developer_id', $last_id );
    $stmt->execute();

    $dbh->commit();
    } catch (PDOException $e) {
        $dbh->rollBack();
    }
}

Ну и вызов самой функции соответственно:

$func = new func;
$func->createTransaction($dbh);

Может кому-то будет полезно.

licensed under cc by-sa 3.0 with attribution.