使用 PHP 链接 SQL 表

我想将我的两个表链接在一起，但是，我不知道该怎么做。我有两个表，系统和预订，这是两个表的sql：

CREATE TABLE `system` (
  `id` int(11) NOT NULL,
  `date` date NOT NULL,
  `time` varchar(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id)
)
CREATE TABLE `booking` (
  `ID` int(11) NOT NULL,
  `table_layout` varchar(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  'systemid' int(11) NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (user_id),
  FOREIGN KEY (systemid)
)

如您所见，两个表共享一个字段"systemid"，这将用于将两者链接在一起。我想让"系统"中的"ID"号码输入预订表"systemid"。我不知道该怎么做，我用这段代码来检索系统'ID'。

按钮。公司。.PHP：

<?php
session_start();
?>
<?php
if (isset($_POST['submit'])) {
    include_once 'calendardatabase.inc.php';
    $date = mysqli_real_escape_string($conn, $_POST['datepicker']);
    $time = mysqli_real_escape_string($conn, $_POST['time']);
    $u_id = mysqli_real_escape_string($conn, $_POST['u_id']);
        if (empty($time) || empty($date)) {
header("Location: ../loginsystem/time=empty.html");
    exit();
    } else {
            $sql = "INSERT INTO system (date, time, user_id) VALUES ('$date', '$time', '$u_id');";
     $result = mysqli_query($conn, $sql);
     if ($conn->query($sql) === TRUE) {
    $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
    }
}
?>
<!doctype html>
<html lang="en">
<head>
    <br></br>
<link rel="stylesheet" href="style.css">
<center><h4>Please Select A Table: </h4></center>
    <center>
<section class="main-container">
<div class="main-wrapper"> 
<form class="calendar-form" action="booking.inc.php" method="POST">

      <center><h4>The Restaurants Layout: </h4></center>
      <br></br>
<img class= "img-tablelayout" src="tables.png" alt="">
    <br></br>
  <div class="main-wrapper"> 
  <br></br>
  <?php
echo 'For the selected time: '; echo $time;
echo ' & the selected date: '; echo $date;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
?>
   <p>Please select an available table: <select name="table_layout">
    <option value=""></option>
    <option value="one"> <?php
    $sql = "SELECT system.date, system.time, booking.table_layout FROM system JOIN booking ON system.id= booking.id WHERE system.time = '$time'  AND system.date='$date' AND booking.table_layout = 'one'";
    $result = mysqli_query($conn, $sql);
    $resultCheck = mysqli_num_rows($result);
    if ($resultCheck > 0) {
echo '';
    }
    if ($resultCheck == 0) {
echo 'Booth One: available';
    }   

?>
    </option>
    <option value="two"> <?php
    $sql = "SELECT system.date, system.time, booking.table_layout FROM system JOIN booking ON system.id= booking.id WHERE system.time = '$time'  AND system.date='$date' AND booking.table_layout = 'two'";
    $result = mysqli_query($conn, $sql);
    $resultCheck = mysqli_num_rows($result);
    if ($resultCheck > 0) {
echo '';
    }
    if ($resultCheck == 0) {
echo 'Booth Two: available';
    }   

?> </option>
   </p> </select>
        <input type="hidden" name="u_id" value="<?php echo $_SESSION['u_id']; ?>">
    <input type="hidden" name="last_id" value="<?php ['last_id']; ?>">

    <button type="send" name="send">Next</button>   

</form>

预订。公司。.PHP：

<?php
session_start();
 ?>
    <?php
if (isset($_POST['send'])) {
    include_once 'calendardatabase.inc.php';
    $table_layout = mysqli_real_escape_string($conn, $_POST['table_layout']);
    $u_id = mysqli_real_escape_string($conn, $_POST['u_id']);
    $last_id = mysqli_real_escape_string($conn, $_POST['last_id']);

$sql = "INSERT INTO booking (table_layout, user_id, systemid) VALUES ('$table_layout','$u_id', '$last_id');";
     $result = mysqli_query($conn, $sql);
} var_dump($_POST) 
?>

在第二个脚本中交换了列名和变量名。它应该是：

$sql = "INSERT INTO booking (table_layout, user_id, systemid) VALUES ('$table_layout','$u_id', '$last_id');";

您还应该学习将准备好的查询与 mysqli_stmt_bind_param() 一起使用，以防止 SQL 注入。

嗨，

你写的这段代码，但这不正确

$sql = "INSERT INTO booking (table_layout, user_id, last_id) VALUES ('$table_layout','$u_id', '$systemid');";

您应该替换

$sql = "INSERT INTO booking (table_layout, user_id, systemid) VALUES ('$table_layout','$u_id', '$last_id');";

相关内容

最新更新

热门标签：