PHP MySQL - 用户输入名称，对应的ID被插入

Question

PHP MySQL - 用户输入名称，对应的ID被插入

3

我有两个表，一个是members，另一个是games。在members表中存储着member_id、first_name、last_name等数据。

我正在尝试创建一个games表格的表单，用户可以输入参与人员的名字和姓氏（不分开），然后一些PHP代码会查询这个名字，找到相应的ID并将其存储起来。当然，member_id是games表格的外键，但用户不会知道成员的ID，他们只知道他们的姓名。

如果有人能解释一下我该如何做，我将非常感激。

表格：

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Form</title>
</head>
<body>
<form action="action.php" method="post">
    <p>
        <label for="date">Date:</label>
        <input type="date" name="date" id="date">
    </p>
    <p>
        <label for="duration">Duration:</label>
        <input type="time" name="duration" id="duration">
    </p>
        <p>
        <label for="member_id">Member Name:</label>
        <input type="text" name="member_id" id="member_id">
    </p>
    <input type="submit" value="Submit">
</form>
</body>
</html>

操作：

<?php

// database connection
include 'pdo_config.php';

try {
// new pdo connection
$conn = new PDO($dsn, $user, $pass, $opt);

// prepare statement and bind parameters
$stmt = $conn->prepare("INSERT INTO games (date, duration, member_id)
  VALUES (:date, :duration, :member_id)");

$stmt->bindParam(':date', $date);
$stmt->bindParam(':duration', $duration);
$stmt->bindParam(':member_id', $member_id);

// post data
$date = $_POST['date'];
$duration = $_POST['duration'];
$member_id = $_POST['member_id'];

// execute statement
$stmt->execute();

// success or error message
echo "New record created successfully";
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}

$conn = null;

?>

- sinesine

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nanocv · Accepted Answer

这应该可以工作。

要求用户在表单中输入成员名称而不是成员ID。然后首先查询数据库以获取成员名称对应的成员ID。

请注意，从名称中搜索成员ID并不是一个好主意，因为可能会有多个具有相同名称的成员。

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Form</title>
</head>
<body>
<form action="action.php" method="post">
    <p>
        <label for="date">Date:</label>
        <input type="date" name="date" id="date">
    </p>
    <p>
        <label for="duration">Duration:</label>
        <input type="time" name="duration" id="duration">
    </p>
    <p>
        <label for="member_name">Member Name:</label>
        <input type="text" name="member_name" id="member_name">
    </p>
    <input type="submit" value="Submit">
</form>
</body>
</html>

<?php

// database connection
include 'pdo_config.php';

try {
// new pdo connection
$conn = new PDO($dsn, $user, $pass, $opt);

// post data
$date = $_POST['date'];
$duration = $_POST['duration'];

// Note that the explode only works well if user inputs one blank space to separate the name
// You can try to improve the separation method or better use two different inputs in the form

$nameArray = explode(" ", $_POST['member_name']);
$first_name = $nameArray[0];
$last_name = $nameArray[1];

$statement = $conn->prepare("SELECT member_id FROM members WHERE first_name = :first_name AND last_name = :last_name");
$statement->execute(array(':fisrt_name' => $first_name, ':last_name' => $last_name));
$row = $statement->fetch();

$member_id = $row['member_id'];


// prepare statement and bind parameters
$stmt = $conn->prepare("INSERT INTO games (date, duration, member_id)
  VALUES (:date, :duration, :member_id)");

$stmt->bindParam(':date', $date);
$stmt->bindParam(':duration', $duration);
$stmt->bindParam(':member_id', $member_id);

// execute statement
$stmt->execute();

// success or error message
echo "New record created successfully";
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}

$conn = null;

?>