sinesine sinesine - 20 days ago 4
MySQL Question

PHP MySQL - User inputs name and corresponding id is inserted

I have two tables,

members
and
games
. In
members
is data such as
member_id
,
first_name
,
last_name
, etc.

What I'm trying to do is create a form for
games
, where the user can input the first and last names of the member who participated (in one string, not separately) and some PHP code queries this name, finds the corresponding id and stores this instead. Of course,
member_id
is a foreign key in
games
, but the users aren't going to know the member's id, they will only know their name.

If anyone could explain how I might go about doing this I would greatly appreciate it.

Form:

<!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>


Action:

<?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;

?>

Answer

This should work.

Ask the user to input the member name in the form instead of the member id. Then make a first query to the database to get the member id from the member name.

Have in mind that it's not a good idea to search the member id from its name, because you could have more than one member whit the same name.

<!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;

?>