Question 218705 in PhpMyAdmin Question posted in
The official documentation can be found here.

Phpmyadmin – I want to automatically place the ID from the Parent table into a child table

I have two separate tables each will input the form data into the phpmyadmin Db respectively.

Users and Parents respectively

I am using procedural PHP and a prepared statement for form data. I have had success with each form inserting data into the Db just fine, however when it comes to the usersId from the users table being inserted into the Parents table user_id (fk) I only receive NULL in the database. I then can manually update that columnn with the appropriate data from a drop down list in PHPmyadmin.

The fact that I can get the information into the tables is great but I want the usersID automatically inserted into the parents table in the user_Id column.

Here is the prepared statement that I am using.

<?php

if (isset($_POST["submit"])) {
$mFname = $_POST["mFname"];
$mLname = $_POST["mLname"];
$mEmail = $_POST["mEmail"];
$mPhone = $_POST["mPhone"];

$fFname = $_POST["fFname"];
$fLname = $_POST["fLname"];
$fEmail = $_POST["fEmail"];
$fPhone = $_POST["fPhone"];

$addressL1 = $_POST["addressL1"];
$addressL2 = $_POST["addressL2"];
$city = $_POST["city"];
$stateAbbr = $_POST["stateAbbr"];
$zip = $_POST["zip"];
$user_Id = $_POST['user_Id'];


include_once 'dbh.inc.php';
include_once 'functions.inc.php';
 
    $sql1 = "UPDATE parent INNER JOIN users ON users.usersId = parent.user_id SET parent.user_id = users.usersId";

    if (mysqli_query($conn, $sql1)) {
    echo "Record updated successfully";
    } else {
    echo "Error updating record: " . mysqli_error($conn);
    }


    $sql = "INSERT INTO parent (mFname, mLname, mEmail, mPhone, fFname, fLname, fEmail, fPhone, addressL1, addressL2, city, stateAbbr, zip, user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error";
    } else {
        mysqli_stmt_bind_param($stmt, "ssssssssssssis", $mFname, $mLname, $mEmail, $mPhone, $fFname, $fLname, $fEmail, $fPhone, $addressL1, $addressL2, $city, $stateAbbr, $zip, $user_id);
        mysqli_stmt_execute($stmt);
 
    }
    header("Location: ../mf2.php?parentinfo=success");
}

Picture of the dropdown I can manually change in the DB to include usersId

The image above shows that the data makes it into the table however as the image shows the user_Id column will display NULL. I need the information from the users table, specifically the usersID column to show in the parents table under the user_Id column automatically on submit.

Please help and talk to me as though I am just learning this because I am. The more detailed you are the better. Thank you all in advance for your help with this.

I tried to use an inner join and found that it says that updating the record was successful at one point but when I checked the Db I found that wasn’t the case.

I have tried using a the usersId session variable in the prepared statement that was unsuccessful.

I am not sure what I am doing wrong here but any help would be greatly appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    First, being new to PHP and dB's I wrongly assumed that creating a foreign key constraint would automatically push the data from one table to another.This is not so.

    The connection between the two tables is there for data integrity and not as I thought to move data. The Primary key - Foreign key constraint links the tables and builds a relationship that ensures that you aren't able to damage the link between tables but doesn't actually move or insert anything from one column to another.

    That being said, I ran my initial sql prepared statement as you see in the code above, then immediately ran the following code which used an INNER JOIN from the two separate tables and then set the value of parent.user_id to the same value as users.usersId.

    I am sure that there is a better way to do this but given that i am new to all this I wanted to share the solution that seems to be working form me right now.

        $serverName = "localhost";
    $dBUsername = "root";
    $dBPassword = "";
    $dBName = "members"; 

    $conn = new mysqli($serverName, $dBUsername, $dBPassword, $dBName);

    $sql1 = "UPDATE parent INNER JOIN users ON parent.mEmail OR parent.fEmail = email SET parent.user_id = users.usersId;";
   
    if ($conn->query($sql1) === TRUE) {
        echo "New Record Created Successfully";
    } else {
        echo "Error: " .$sql1 . "<br>" . $conn->error;
    }
    $conn->close();

  2. 0

    Based on your description and code, it seems that you want to insert data into the parent table while also updating the user_id column in the same table with the corresponding usersId from the users table. The issue seems to be that the user_id column in the parent table remains NULL after the update.

    To achieve the desired behavior, you should first insert the data into the parent table and then update the user_id column with the corresponding usersId. Additionally, you need to make sure that you have the correct variable name in the mysqli_stmt_bind_param function.

    Here’s how you can modify your PHP code to achieve this:

    <?php
if (isset($_POST["submit"])) {
    // ... (your existing code for getting form data)

    include_once 'dbh.inc.php'; // Assuming this file contains your database connection
    include_once 'functions.inc.php'; // Assuming this file contains your other functions

    // Step 1: Insert data into the parent table
    $sql = "INSERT INTO parent (mFname, mLname, mEmail, mPhone, fFname, fLname, fEmail, fPhone, addressL1, addressL2, city, stateAbbr, zip, user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        echo "SQL error";
    } else {
        mysqli_stmt_bind_param($stmt, "ssssssssssssis", $mFname, $mLname, $mEmail, $mPhone, $fFname, $fLname, $fEmail, $fPhone, $addressL1, $addressL2, $city, $stateAbbr, $zip, $user_Id);
        mysqli_stmt_execute($stmt);
    }

    // Step 2: Update the user_id column in the parent table with the corresponding usersId
    $lastInsertedId = mysqli_insert_id($conn); // Get the auto-generated ID from the last insert
    $userId = $_POST['user_Id']; // Assuming this is the usersId from the users table

    $updateSql = "UPDATE parent SET user_id = ? WHERE id = ?;";
    $updateStmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($updateStmt, $updateSql)) {
        echo "SQL error";
    } else {
        mysqli_stmt_bind_param($updateStmt, "ii", $userId, $lastInsertedId);
        mysqli_stmt_execute($updateStmt);
    }

    header("Location: ../mf2.php?parentinfo=success");
}
?>

    Explanation:

    1. In the first step, you insert the data into the parent table using a prepared statement as you already did in your code.

    2. After the insert, you get the auto-generated ID of the last inserted row in the parent table using mysqli_insert_id($conn).

    3. You then use the retrieved lastInsertedId and the user_Id (assuming
      this is the usersId from the users table) to update the user_id
      column in the parent table using another prepared statement.

    By following these steps, you should be able to insert data into the parent table and have the correct usersId from the users table automatically added to the user_id column in the parent table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search