php - 将 PHP 变量分配给 SQL 结果

Question

我知道这是一个基本问题，但我似乎无法用我的代码来破解它。

我已将登录 session 用户名分配给 $username - 这可以完美插入，但是，我希望为 $id 分配位于“users”表中的用户 ID。

我目前有 2 张 table 、用户和行程。

Users 具有以下列:

id (PK,AI), 
username, 
email, 
password, 
trn_date

行程 具有以下列:

id (PK,AI), 
user (FK_users.ID), 
name, 
from, 
to, date, space, email, telephone, comments

这是我的代码:

<?php
session_start();
include("auth.php"); 
include("db.php"); 
?>              
<?php

// SERVER AND DATABASE DETAILS --- WORKING
$servername = "<redacted>";
$username1 = "<redacted>";
$password = "<redacted>";
$dbname = "<redacted>";



//sets session variable username --- WORKING
$username = $_SESSION['username'];

// sets variable to result of sql to determine user_id - NOT WORKING
$id = mysqli_query("SELECT id from `users` where username = '$username'");


// Create connection --- WORKING
$conn = new mysqli($servername, $username1, $password, $dbname);

// Check connection --- WORKING
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 // Insert script when adding trip --- WORKING
$sql = "INSERT INTO `trips` (`user`,`name`, `from`, `to`, 
`date`, `space`, `email`, `telephone`, `comments`) VALUES   ('$id','$username','".$_POST["from"]."','".$_POST["to"]."','".$_POST["datetime"]."','".$_POST["space"]."','".$_POST["email"]."','".$_POST["telephone"]."','".$_POST["comments"]."')";

// Alert pop-up confirming when insert is successful --- WORKING
if ($conn->query($sql) === TRUE) {
   echo '<script type="text/javascript">'; 
    echo 'alert("Your trip was successfully added!");'; 
    echo 'window.location.href = "/index.php";';
    echo '</script>';

    // Error when sql insert fails
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

$conn->close();
?>              

Answer 1

替换此行

$id = mysqli_query("SELECT id from `users` where username = '$username'");

有了这个

$conn = new mysqli($servername, $username1, $password, $dbname); //since I am not sure where did you initiate the mysqli connection
$res = mysqli_query($conn, "SELECT id from `users` where username = '$username'");
$id = mysqli_fetch_assoc ( $res ) ['id'] ;

我想这会解决你的问题。

mysqli_query 函数返回的不是您期望的值，而是 mysqli 结果 http://php.net/manual/en/mysqli.query.php

因此，您需要首先从该对象中获取结果，然后从结果中提取值。

在我的示例中，我使用 mysqli_fetch_assoc 将数据作为关联数组获取，然后使用 ['id'] 获取特定列值。