php 从表单提交更新两个表

nhjlsmyf 于 2023-10-15 发布在 PHP

关注(0)|答案(1)|浏览(113)

我有一个预订表格，在提交时将预订详细信息输入到“预订”表中
但我也想更新和事件表在同一时间与调整后的数字出售门票。

<?php
/* Duplicate information to DB */
function queryMysqli($mysql_table, $bevent_id,$bevent_price,$att1title,$att1firstname,$att1surname,$att1email, $att1phone,$people,$payment,$dietry,$message) {

    $total="60";
    $grandtotal=$total+$people;

    /* Variables */
    $error_exists = false;
    $error_mysql = "";

    /* Connection to DB */
    /* Constants, that defined in action.php, are used */
    $link = mysqli_connect(SERVER, USER, PASSWORD, DATABASE);
    if (mysqli_connect_error()) {
        $error_mysql = ("Error connecting to database (" . mysqli_connect_errno() . ") ". mysqli_connect_error());
        return $error_mysql;
    }
    mysqli_set_charset($link, 'utf8');

    /* Query to DB */
    /* Add data to DB */
    $result = mysqli_query($link, "INSERT INTO ".$mysql_table."(`id`,
                                                                `bevent_id`,
                                                                `bevent_price`,
                                                                `att1title`,
                                                                `att1firstname`,
                                                                `att1surname`,
                                                                `att1email`,
                                                                `att1phone`,
                                                                `people`,
                                                                `payment`,
                                                                `dietry`,
                                                                `editor`,
                                                                `message`)
                                                    VALUES (NULL,   '".mysqli_real_escape_string($link, $bevent_id)."',
                                                                    '".mysqli_real_escape_string($link, $bevent_price)."',
                                                                    '".mysqli_real_escape_string($link, $att1title)."',
                                                                    '".mysqli_real_escape_string($link, $att1firstname)."',
                                                                    '".mysqli_real_escape_string($link, $att1surname)."',
                                                                    '".mysqli_real_escape_string($link, $att1email)."',
                                                                    '".mysqli_real_escape_string($link, $att1phone)."',
                                                                    '".mysqli_real_escape_string($link, $people)."',
                                                                    '".mysqli_real_escape_string($link, $payment)."',
                                                                    '".mysqli_real_escape_string($link, $dietry)."',
                                                                    '".mysqli_real_escape_string($link, $message)."')");

    /* Get a last row ID to send in message */
    $row_id = mysqli_insert_id($link);

$query="UPDATE `bookable_events` SET `sold`='$grandtotal' WHERE `bevent_id`='$bevent_id'";

    /* If error occurs */
    if (!$result){
        $error_exists = true;
        $error_mysql = "Error database query: ".mysqli_error($link);
    }

    /* Return result */
    mysqli_close($link);
    return $error_exists ? $error_mysql : $row_id;
}
?>

预订表正在更新，但活动表没有，有人能帮忙吗？

php

来源：https://stackoverflow.com/questions/77246196/updating-two-tables-from-form-submission

1条答案

按热度按时间

enyaitl31#

在单个事务中更新多个表是一个常见的需求。看起来您正在尝试使用PHP函数来实现这一点，但是为了获得更好的可靠性、安全性和可读性，还有几个方面可以改进。
下面是使用预处理语句和事务查询的函数的修订版本：

function queryMysqli($mysql_table, $bevent_id, $bevent_price, $att1title, $att1firstname, $att1surname, $att1email, $att1phone, $people, $payment, $dietry, $message) {
    $total = 60;
    $grandtotal = $total + $people;

    $link = new mysqli(SERVER, USER, PASSWORD, DATABASE);

    if ($link->connect_error) {
        return "Error connecting to database: " . $link->connect_error;
    }

    $link->set_charset('utf8');

    $link->begin_transaction();

    try {
        $stmt = $link->prepare("INSERT INTO {$mysql_table} (bevent_id, bevent_price, att1title, att1firstname, att1surname, att1email, att1phone, people, payment, dietry, message) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("sssssssssss", $bevent_id, $bevent_price, $att1title, $att1firstname, $att1surname, $att1email, $att1phone, $people, $payment, $dietry, $message);
        $stmt->execute();
        $stmt->close();

        $stmt2 = $link->prepare("UPDATE bookable_events SET sold = ? WHERE bevent_id = ?");
        $stmt2->bind_param("ss", $grandtotal, $bevent_id);
        $stmt2->execute();
        $stmt2->close();

        $link->commit();
        $last_inserted_id = $link->insert_id;

    } catch (Exception $e) {
        $link->rollback();
        return "Error: " . $e->getMessage();
    }

    $link->close();

    return $last_inserted_id;
}

另一种方式，我建议更清洁

function createDbConnection() {
    $link = new mysqli(SERVER, USER, PASSWORD, DATABASE);
    if ($link->connect_error) {
        throw new Exception("Error connecting to database: " . $link->connect_error);
    }
    $link->set_charset('utf8');
    return $link;
}

function executePreparedStatement($link, $query, $types, ...$params) {
    $stmt = $link->prepare($query);
    if (!$stmt) {
        throw new Exception("Failed to prepare statement: " . $link->error);
    }
    $stmt->bind_param($types, ...$params);
    if (!$stmt->execute()) {
        throw new Exception("Failed to execute statement: " . $stmt->error);
    }
    $stmt->close();
}

function queryMysqli($mysql_table, $bevent_id, $bevent_price, $att1title, $att1firstname, $att1surname, $att1email, $att1phone, $people, $payment, $dietry, $message) {
    $total = 60;
    $grandtotal = $total + $people;

    try {
        $link = createDbConnection();
        $link->begin_transaction();

        $insertQuery = "INSERT INTO {$mysql_table} (bevent_id, bevent_price, att1title, att1firstname, att1surname, att1email, att1phone, people, payment, dietry, message) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        executePreparedStatement($link, $insertQuery, "sssssssssss", $bevent_id, $bevent_price, $att1title, $att1firstname, $att1surname, $att1email, $att1phone, $people, $payment, $dietry, $message);

        $updateQuery = "UPDATE bookable_events SET sold = ? WHERE bevent_id = ?";
        executePreparedStatement($link, $updateQuery, "ss", $grandtotal, $bevent_id);

        $link->commit();
        $last_inserted_id = $link->insert_id;

    } catch (Exception $e) {
        if (isset($link)) {
            $link->rollback();
        }
        return "Error: " . $e->getMessage();
    } finally {
        if (isset($link)) {
            $link->close();
        }
    }

    return $last_inserted_id;
}

希望能帮到你

赞(0）回复(0）举报 2023-10-15

我来回答

php 从表单提交更新两个表

1条答案

相关问题

热门标签

最新问答