获取一个自动递增id，并在具有相同提交的不同查询中使用该id - Getting an auto increment id and using that in a different query with the same submit

Getting an auto increment id and using that in a different query with the same submit

本文关键字：id 提交查询一个获取 | 更新日期: 2023-09-27

我有一个注册页面，最初只有一个查询指向一个名为"users"的数据库表。在"users"表中，我有一个名为"id"的列，它是主要的自动递增字段。

然后，我在原来的查询下创建了一个新的查询，该查询将发送到一个名为"付款状态"的数据库表。除了我尝试使用"users"表创建的"id"并将其放入列"user_id"的"payment_status"数据库表之外，此查询中的所有内容都在工作。它总是在其中输入一个0值。

if($validation->passed()) {
            $user = new User();
            $salt = Hash::salt(32);
            try {
                $user->create(array(
                    'firstname' => Input::get('firstname'),
                    'lastname' => Input::get('lastname'),
                    'email' => Input::get('email'),
                    'phone_number' => Input::get('phone_number'),
                    'username' => Input::get('username'),
                    'password' => Hash::make(Input::get('password'), $salt),
                    'salt' => $salt,
                    'joined' => date('Y-m-d H:i:s'),
                    'group' => 1
                ));
                $success = "You have successfully created an account. We will notify you once the account has been approved. Then you will be able to login.";
                echo $success;
            } catch(Exception $e) {
                die($e->getMessage());
            }
        } else {
            foreach($validation->errors() as $error) {
                $error;
            }
        }

我的第二个查询

if(isset($_POST['submit'])){
$id = ( isset( $_SESSION['id'] ) ? $_SESSION['id'] : "" );
$user_id = ( isset( $_SESSION['id'] ) ? $_SESSION['id'] : "" );
$firstname = Input::get('firstname');
$payment_name = "Owes";
$payment_id = 1;
$payment_amount = 0;
//Query to add user's name to owes db table
$con = mysqli_connect("localhost","root","","db");
/* check connection */
   if (mysqli_connect_errno()) {
    printf("Connect failed: %s'n", mysqli_connect_error());
        exit();
    }
$stmt = $con->prepare("INSERT INTO payment_status (id, user_id, firstname, payment_name, payment_id, payment_amount) VALUES (?, ?, ?, ?, ?, ?)");
    if ( false===$stmt ) {
  // Check Errors for prepare
  die(' Owes DB prepare() failed: ' . htmlspecialchars($con->error));
}
$stmt->bind_param('iissii', $id, $user_id, $firstname, $payment_name, $payment_id, $payment_amount);
    if ( false===$stmt ) {
      // Check errors for binding parameters
      die('Owes DB bind_param() failed: ' . htmlspecialchars($stmt->error));
    }
$stmt->execute();
    if ( false===$stmt ) {
      die('execute() failed: ' . htmlspecialchars($stmt->error));
    }   
}

我正试图在我准备的第二个语句中使用这个变量。。

$user_id = ( isset( $_SESSION['id'] ) ? $_SESSION['id'] : "" );

因此，我试图从"users"表中获取"id"，并将其用于"payment_status"表的"user_id"。

有人看到我做错了什么吗？

您使用的是MySQLi，因此可以使用mysqli::$insert_id获取插入数据库的最后一个ID。

<?php
    // Connect to MySQL database
    $con = mysqli_connect("localhost", "root", "", "db");
    // Insert into database
    $stmt = $con->prepare("INSERT INTO payment_status (id, user_id, firstname, payment_name, payment_id, payment_amount) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->bind_param('iissii', $id, $user_id, $firstname, $payment_name, $payment_id, $payment_amount);
    $stmt->execute();

    // Debug
    var_dump($con->insert_id);
    // Output:
    // int(1)