php - 从单一表单将数据插入到多个 SQL 表中

Question

<小时/>

嘿..我对将数据插入多个 SQL 表的 PHP 代码有点困惑。我无法在单个操作中将数据放入两个表中。这是一个注册页面，用于创建用户登录名和公司简介的开始。任何建议将不胜感激。谢谢

  <?php
session_start();
if(isset($_SESSION['user'])!="")
{
    header("Location: home.php");
}
include_once 'resources/php/dbconnect.php';




if(isset($_POST['btn-signup']))
{
    $uname = mysql_real_escape_string($_POST['uname']);
    $email = mysql_real_escape_string($_POST['email']);
    $upass = md5(mysql_real_escape_string($_POST['pass']));
    $orgname = mysql_real_escape_string($_POST['orgname']);

    $uname = trim($uname);
    $email = trim($email);
    $upass = trim($upass);
    $orgname = trim($orgname);

    // email exist or not
    $query = "SELECT user_email FROM users WHERE user_email='$email'";
    $result = mysql_query($query);

    $count = mysql_num_rows($result); // if email not found then register

    if($count == 0){

        if(mysql_query("START TRANSACTION; 
        INSERT INTO users('user_name','user_email','user_pass') 
            VALUES('$uname','$email','$upass');
        INSERT INTO companies('name','owner_id') 
            VALUES('$orgname','$email');
        END;"))
        {
            ?>
            <script>alert('Registration Successful');</script>
            <?php
        }
        else
        {
            ?>
            <script>alert('error while registering you...');</script>
            <?php
        }       
    }
    else{
            ?>
            <script>alert('Sorry Email ID already taken ...');</script>
            <?php
    }

}

?>

Answer 1

如您所见here mysql 扩展已被弃用，您必须停止使用它。很好的替代品是 PDO 和 mysqli

您还应该使用Prepared Statements保护您的代码免受 SQL 注入(inject)攻击。

要使用 PDO 完成交易，您可以执行以下操作:

$conn->query("START TRANSACTION");
$stmt = $conn->prepare("INSERT INTO users(user_name,user_email,user_pass) VALUES(?,?,?)");
try
{
    $stmt->bindValue(1,$user_name);
    $stmt->bindValue(1, $user_email);
    $stmt->bindValue(3, $user_pass);
    $stmt->execute();
    $secondStmt = $conn->prepare("INSERT INTO companies(name,owner_id) VALUES(?,?)");
    $secondStmt->bindValue(1, $name);
    $secondStmt->bindValue(2, $owner_id);
    $secondStmt->execute();
    $conn->query("COMMIT");   //This commits all changes if no error occured
}
catch(Exception $e)
{
    $conn->query("ROLLBACK");  //This reverts any changes made in case of an error
}