How to insert data into table with foreign key using html form and php?

Question

Situation: user is logged in and wants to save their favorite color through html form. But in phpMyAdmin I can see that the foreign key and the primary key (which are columns 'user_id' in two separate tables) do not match. The foreign key shows NULL in the rows with data, while the primary key shows numbers (e.g. 3) in the rows with data.

As mentioned, there are 2 tables: (users & colors)

The following sql is used to create table colors:

CREATE TABLE colors ( 
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
favorite_color TEXT NOT NULL, 
user_id INT, 
FOREIGN KEY (user_id) REFERENCES users(user_id) 
);

The following sql is used to create table users:

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

From the page where users insert data is welcome.php and it contains the following code:

<?php
session_start();
    
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login");
    exit;
}
?>

The html form:

<form action="welcome.php" method="post"> 
<label>My favorite color:
    <input type="text" name="favorite_color">
</label>
<input type="submit" value="Save">
</form>

And the php code to insert data:

<?php
$link = mysqli_connect("localhost", "root", "", "my_db");
 
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
$sql = "INSERT INTO colors (id, favorite_color, user_id) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    mysqli_stmt_bind_param($stmt, "sss", $id, $favorite_color, $user_id);
    
    $id = $_REQUEST['id'];
    $favorite_color = $_REQUEST['favorite_color'];
    $user_id = $_REQUEST['user_id'];

    if(mysqli_stmt_execute($stmt)){
        echo "Records inserted successfully.";
    } else{
        echo "ERROR: Could not execute query: $sql. " . mysqli_error($link);
    }
} else{
    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
 
mysqli_stmt_close($stmt);
 
mysqli_close($link);
?>

What am I doing wrong? Any suggestion is welcome. Thanks.

It looks like it should work. What happens when you try, and what error are you getting?. — aynber, May 04 '22 at 12:29
1) Why are you inserting into the `id` field when that's an auto_increment field? The database will generate the ID for you - that's the whole point of that sort of field. So just remove that from your INSERT query entirely. 2) `$user_id = $_REQUEST['user_id'];`...you seem to be trying to get the user ID from the form submission, but the HTML form doesn't contain a "user_id" field. And anyway I'd expect, for security reasons, that you'd get the ID of the logged-in user from the Session, rather than allowing it to be sent in a form (and therefore potentially manipulated by a bad user). — ADyson, May 04 '22 at 12:29
P.S. As an aside, there is a way to avoid cluttering up your code with endless, repetitive boilerplate error handling code for your mysqli operations. Instead, just enable mysqli's automatic, global error reporting. See [mysqli or die, does it have to die?](https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die) for details. — ADyson, May 04 '22 at 12:35
@aynber There's no error as it can insert data without problems. Or at least it cannot detect there's a problem. — Rone, May 04 '22 at 12:43
@Rone Kendle means that, as per the [ask] guide, anything which is text should be posted as text. You've already provided a CREATE TABLE statment for the second picture, so the same for the first would have been more appropriate. Then you don't need either image. See also [Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) — ADyson, May 04 '22 at 12:44
Okay, so it's inserting a null `user_id`, which you allow. You then need to figure out why `$_REQUEST['user_id']` is null. You don't have it on your form, so where do you expect to get it? — aynber, May 04 '22 at 12:44
@ADyson I've learned you have to include all colums into that sql query, even if you're not actually inserting any data into them. I remember I've tried to use only the necessary columns, but it didn't work. However, I'm not sure. I can try that again. About REQUEST. Thanks. I will delete that. So do you mean I have to write `$user_id = $_SESSION['user_id'];` instead of `$user_id = $_REQUEST['user_id'];`? — Rone, May 04 '22 at 12:48
@ADyson Oh I see. Thanks. Okay, I can edit it in my question. — Rone, May 04 '22 at 12:49
`I've learned you have to include all colums into that sql query, even if you're not actually inserting any data into them`...no, that's not true. Especially not for an auto_increment field. And also any other column which isn't specified as NOT NULL you can omit as well. — ADyson, May 04 '22 at 12:56
`do you mean I have to write $user_id = $_SESSION['user_id']; instead of $user_id = $_REQUEST['user_id'];`...yes, if that's how your logged-in user ID is stored. — ADyson, May 04 '22 at 12:58
(1) In the insert query --- instead of "sss", you should use "si" for $favorite_color, $user_id respectively (remove the auto_increment field `id` please) (2) make sure $_REQUEST['user_id'] contains the user's id value which is integer — Ken Lee, May 04 '22 at 13:04
@ADyson You're right, it does work. Good to know! Unfortunately however, so far, replacing REQUEST with SESSION doesn't work. I've tried user_id, username, loggedin, but they all don't work. But I also do not receive any errors. — Rone, May 04 '22 at 13:09
In your HTML form ,if you do not have , the target php will not have any value in $_REQUEST["user_id"]. On the other hand, as suggested by ADyson, use $_SESSION["user_id"] is a nice way, but make sure this session variable is assigned (when the user logs in) and `session_start();` is at the top of all the PHP scripts using session variables — Ken Lee, May 04 '22 at 13:17
`all don't work`...means what, precisely? What debugging did you do? Did you check if the Session value actually exists, for example? I don't know what your Session item is actually called...but you should be able to work it out by seeing what your application sets when a user logs in. — ADyson, May 04 '22 at 13:22
@ADyson On the page where users login, this is the data stored in session variables: `$_SESSION["loggedin"] = true; $_SESSION["user_id"] = $user_id; $_SESSION["username"] = $username;` — Rone, May 04 '22 at 13:24
@KenLee Thanks. Changed it to "si", removed 'id' and added 'user_id' again. Changed REQUEST to SESSION. And the page does start with `session_start();` — Rone, May 04 '22 at 13:29
one small reminder: you have a html form where you can input the favorite_color. Please make sure it is NOT html but is PHP and contains `` at the top as well. Good luck — Ken Lee, May 04 '22 at 13:31
Guys it now works! Let me answer this question to show you what I did. — Rone, May 04 '22 at 13:37

score 2 · Accepted Answer · edited May 05 '22 at 19:26

I have been able to solve the problem.

Here's what I did:

I changed:

$sql = "INSERT INTO colors (id, favorite_color, user_id) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    mysqli_stmt_bind_param($stmt, "sss", $id, $favorite_color, $user_id);

into:

$sql = "INSERT INTO colors (favorite_color, user_id) VALUES (?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    mysqli_stmt_bind_param($stmt, "si", $favorite_color, $user_id);

As you can see I removed 'id' and changed "sss" into "si".

And I changed:

$id = $_REQUEST['id'];
$favorite_color = $_REQUEST['favorite_color'];
$user_id = $_REQUEST['user_id'];

into:

$favorite_color = $_REQUEST['favorite_color'];
$user_id = $_SESSION['user_id'];

I removed 'id' entirely and I replaced REQUEST with SESSION for the column 'user_id'.

It is now showing matching numbers under 'user_id' in table colors.

How to insert data into table with foreign key using html form and php?

1 Answers1

Linked