1

Situation: user is logged in and wants to save their favorite color through html form.

Page where logged in user is after logging in: welcome.php.

welcome.php starts with:

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

The html form in welcome.php:

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

The php code below the form:

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

There are two tables in phpMyAdmin:

users

colors

FYI, I have created a FOREIGN KEY constraint on the "user_id" column in table colors.

When user saves their color, they will see the following error:

ERROR: Could not execute query: INSERT INTO colors (favorite_color) VALUES (?).

What am I doing wrong? Thanks a bunch for any suggestion!

Rone
  • 35
  • 8
  • Your INSERT query would need the `user_id` also (`INSERT INTO colors (user_id, favorite_color) VALUES (?,?))`). Also, `"sss"`, you're binding three parameters here but only have _one_ placeholder "?" in your query – brombeer May 02 '22 at 10:20
  • @brombeer It works! Thanks! I did receive the error that the column user_id cannot be null. So I changed it to null and then it worked finally :) But the only problem is is that the user_id in table colors isn't showing the user_id value from table users, which is 2 (2 belongs to the user I'm logged in with). In the meantime I did try a couple of things. But so far this is what I have: CREATE TABLE colors ( favorite_color int NOT NULL, user_id int, FOREIGN KEY (user_id) REFERENCES users(user_id) ); Or shall I ask this in a separate question? Let me know. Thanks! – Rone May 02 '22 at 12:39
  • @brombeer Oh wait, the column favorite_color in table colors is type text, not int. – Rone May 02 '22 at 12:43
  • I wouldn't change the `user_id` column to be nullable, after all it's a user's favourite colour you want to save, so `user_id` should be required. iirc you store the logged in user's id in `$_SESSION['id']`, use this as value for the `user_id` column in your query – brombeer May 02 '22 at 16:59
  • @brombeer Thanks for your reply. You mean like this `$sql = "INSERT INTO colors (favorite_color, $_SESSION['user_id']) VALUES (?, $_SESSION['user_id'])";` ? – Rone May 02 '22 at 18:03
  • Hi did you resolve this? currently working on the same problem – mikkelven May 15 '22 at 19:39
  • @mikkelven Check out my other post: https://stackoverflow.com/q/72112985/4546157 – Rone May 16 '22 at 13:28

0 Answers0