How to insert my primary key under my signup table into my feedback table as a foreign key

Question

How do I identify which users did the feedback as I have two different tables. I want to insert my signup id as a foreign key under my feedback table.

My two tables

1.signup

2.feedback

signup

signup_id as a primary key

Username

Password

feedback

feedback_id as a primary key

signup_id as a foreign key

category

feedback text

rating

My Signup Code

<?php
include 'config.php';
if (isset($_POST['signup'])) {
    $email = $_POST['email'];
    $username = $_POST['username'];
    $password = md5($_POST['password']);
    $cpassword = md5($_POST['confirmpassword']);

    if ($password == $cpassword) {
        $sql = "INSERT INTO signup(email,username,password)VALUES('$email','$username','$password')";
        $result = mysqli_query($conn, $sql);
        if ($result) {
            header("location: feedback.php");
        }
    }
}

My Feedback Code

<?php
include "config.php";
if(isset($_POST['submit'])){
    $category = $_POST['category'];
    $rating = $_POST['rating'];
    $feedbacktext = $_POST['feedback'];

    $sql = "INSERT INTO feedback(category,rating,feedbacktext)VALUES('$category','$rating','$feedbacktext')";
    $result = mysqli_query($conn,$sql);
}
?>

Your code also has bad security and reliability problems - I could break it with a single, simple apostrophe, or run illegitimate queries, or decode your passwords. Urgently read https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement and also https://stackoverflow.com/questions/30496061/why-not-use-md5-for-password-hashing (and then https://stackoverflow.com/questions/30279321/how-to-use-phps-password-hash-to-hash-and-verify-passwords to fix password issue) — ADyson, Aug 14 '22 at 05:42
Anyway, get the database to tell you the last inserted ID, then you can re-use it. See [mysqli last insert id](https://stackoverflow.com/questions/19738283/mysqli-last-insert-id). That's if feedback happens immediately after sign up. If not, the username ought to be unique, so use that to get the user ID you need. Either have the user submit it — ADyson, Aug 14 '22 at 05:45
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Aug 14 '22 at 11:03
**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, Aug 14 '22 at 11:03

Professor Abronsius · Accepted Answer · 2022-08-14T12:01:22.813

There are some issues with your code that you should address, namely SQL injection and incorrect password hashing using MD5

One method of using the last insert id from the signup routine would be to assign it to a session variable. As long as any pages visited after the signup also maintain that session the ID will be available in that session variable. You could pass it in a querystring but that is ropey.

I tried to address the issues mentioned with the following - Prepared statements replace the vulnerable sql commands and the password is hashed using password_hash

signup

<?php
    # start a session to save the user id
    session_start();

    # we are only interested in proceeding if ALL these POST variables are present!
    if( isset(
        $_POST['email'],
        $_POST['username'],
        $_POST['password'],
        $_POST['confirmpassword']
    )) {

        include 'config.php';
        
        $email = $_POST['email'];
        $username = $_POST['username'];
        
        # never use MD5 for password hashing. It is broken and not secure/reliable.
        # Use password_hash & password_verify!
        $hash = password_hash( $_POST['password'], PASSWORD_DEFAULT );


        if( $_POST['password'] == $_POST['confirmpassword'] ) {
            
            # mitigate SQL injection by using a prepared statement
            $sql = "INSERT INTO `signup`( `email`, `username`, `password`) VALUES ( ?, ?, ? )";
            
            $stmt = $conn->prepare( $sql );
            $stmt->bind_param('sss', $email, $username, $hash );
            $stmt->execute();
            
            # save the insert id as a session variable to use after redirect.
            # this ensures the id is available when the next insert statement occurs
            # so long as the session is maintained on all pages following this.
            $_SESSION['uid']=$stmt->insert_id;
            $stmt->close();
            
            
            exit( header("Location: feedback.php") );
        }
    }
?>

Feedback

<?php

    session_start();
    
    #again, only proceed if ALL required POST vars are present, not just the submit button!
    if( isset(
        $_POST['category'],
        $_POST['rating'],
        $_POST['feedback'],
        $_SESSION['uid']
    )){
        include "config.php";
        
        # create the basic sql with placeholders for the prepared statement bound parameters.
        $sql = "INSERT INTO `feedback` ( `signup_id`, `category`, `rating`, `feedbacktext` ) VALUES ( ?, ?, ?, ? )";
        # create the statement, bind the vars and execute...
        $stmt = $conn->prepare( $sql );
        $stmt->bind_param('ssss', $_SESSION['uid'], $_POST['category'], $_POST['rating'], $_POST['feedback'] );
        $stmt->execute();
        $stmt->close();
        
        # now what?....
    }
?>

Am I supposed to write more code under the comment of #now what. I am quite new to SQL. Any help is appreciated! — RhinoCoder, Aug 14 '22 at 09:36
it was not clear what was to happen next at that stage - quite commonly it would be a `header( 'Location: page.ext')` type thing but only you know what is supposed to happen after the successful posting of feedback — Professor Abronsius, Aug 14 '22 at 10:36
When I went back to a account that I sign up before and submitted the feedback. It will still take the last insert id instead of the signup id account that is created before. How do I go about this? — RhinoCoder, Aug 14 '22 at 14:12
In the feedback page, once the insert has been done erase that session variable, ie: `unset( $_SESSION['uid'] );` — Professor Abronsius, Aug 14 '22 at 14:26

How to insert my primary key under my signup table into my feedback table as a foreign key

1 Answers1

signup

Feedback