I want to INSERT the user_Id from a table into the parent_id in another table however I have no common values between the two tables

Question

I currently have three tables

users table // Data is inserted at time of sign up

usersId //PRIMARY KEY
userfName
userlName
userUid
email
userPwd
dateTime

parent table

id //PRIMARY KEY
mFname
mLname
mEmail
mPhone
fFname
fLname
fEmail
fPhone
addressL1
addressL2
city
stateAbbr
zip
created_at
user_id //FOREIGN KEY

I used the email column from the users table and the mEmail OR fEmail to insert the usersId into the parents table

Children Table

child_id //PRIMARY KEY
child1Name
dobChild1
ageChild1
child2Name
dobChild2
ageChild2
child3Name
dobChild3
ageChild3
child4Name
dobChild4
ageChild4
child5Name
dobChild5
ageChild5
child6Name
dobChild6
ageChild6
child7Name
dobChild7
ageChild7
child8Name
dobChild8
ageChild8
child9Name
dobChild9
ageChild9
child10Name
dobChild10
ageChild10
parent_id //FOREIGN KEY

There are no common values between the childrens table and the other two. My hope was to validate between the users table and the parent tables then if the values match for the users.usersId and the parent.user_id are equal to each other then insert that value into the children.parent_id. Is this even possible? I was thinking of using a cross join but I am concerned about performance down the road as the number of entries in the Db grow.

SELECT usersId FROM users;
SELECT user_id FROM parent;
UPDATE children (parent_Id) INNER JOIN parent ON children.parent_id = users.usersId

UPDATE children 
SELECT usersId 
FROM users 
cross JOIN children.parent_id ON users.usersId = children.parent_id


UPDATE children
INNER JOIN users
    (INNER JOIN parent ON users.userId = parent.userId)
ON users.usersId = parent.user_id
SET children.parent_id = parent.user_Id;

I have tried each of these but none of them have worked. I have tried changing the order of the Joins as well to no avail. Any help would be greatly apprecaited.

<?PHP 

require_once 'dbh.inc.php';
include_once 'functions.inc.php';

session_start();

if (isset($_POST["submit"])) {
    //Child 1
    $child1Name = $_POST["child1Name"];
    $dobChild1 = date('Y-m-d', strtotime($_POST['dobChild1']));
    $ageChild1 = $_POST["ageChild1"];
    //Child 2
    $child2Name = $_POST["child2Name"];
    $dobChild2 = date('Y-m-d', strtotime($_POST['dobChild2']));
    $ageChild2 = $_POST["ageChild2"];
    //Child 3
    $child3Name = $_POST["child3Name"];
    $dobChild3 = date('Y-m-d', strtotime($_POST['dobChild3']));
    $ageChild3 = $_POST["ageChild3"];
    //Child 4
    $child4Name = $_POST["child4Name"];
    $dobChild4 = date('Y-m-d', strtotime($_POST['dobChild4']));
    $ageChild4 = $_POST["ageChild4"];
    //Child 5
    $child5Name = $_POST["child5Name"];
    $dobChild5 = date('Y-m-d', strtotime($_POST['dobChild5']));
    $ageChild5 = $_POST["ageChild5"];
    //Child 6
    $child6Name = $_POST["child6Name"];
    $dobChild6 = date('Y-m-d', strtotime($_POST['dobChild6']));
    $ageChild6 = $_POST["ageChild6"];
    //Child 7
    $child7Name = $_POST["child7Name"];
    $dobChild7 = date('Y-m-d', strtotime($_POST['dobChild7']));
    $ageChild7 = $_POST["ageChild7"];
    //Child 8
    $child8Name = $_POST["child8Name"];
    $dobChild8 = date('Y-m-d', strtotime($_POST['dobChild8']));
    $ageChild8 = $_POST["ageChild8"];
    //Child 9
    $child9Name = $_POST["child9Name"];
    $dobChild9 = date('Y-m-d', strtotime($_POST['dobChild9']));
    $ageChild9 = $_POST["ageChild9"];
    //Child 10
    $child10Name = $_POST["child10Name"];
    $dobChild10 = date('Y-m-d', strtotime($_POST['dobChild10']));
    $ageChild10 = $_POST["ageChild10"];

    $parent_id = $_POST['parent_id'];
    
$sql = "INSERT INTO children (
    child1Name, dobChild1, ageChild1,
    child2Name, dobChild2, ageChild2, 
    child3Name, dobChild3, ageChild3, 
    child4Name, dobChild4, ageChild4, 
    child5Name, dobChild5, ageChild5, 
    child6Name, dobChild6, ageChild6,
    child7Name, dobChild7, ageChild7, 
    child8Name, dobChild8, ageChild8, 
    child9Name, dobChild9, ageChild9, 
    child10Name, dobChild10, ageChild10, 
    parent_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
    echo "SQL ERROR";
    } else {
        mysqli_stmt_bind_param($stmt, "ssissississississississississii", 
        $child1Name, $dobChild1, $ageChild1,
        $child2Name, $dobChild2, $ageChild2,
        $child3Name, $dobChild3, $ageChild3,
        $child4Name, $dobChild4, $ageChild4, 
        $child5Name, $dobChild5, $ageChild5, 
        $child6Name, $dobChild6, $ageChild6,
        $child7Name, $dobChild7, $ageChild7, 
        $child8Name, $dobChild8, $ageChild8, 
        $child9Name, $dobChild9, $ageChild9, 
        $child10Name, $dobChild10, $ageChild10, 
        $parent_id);
        mysqli_stmt_execute($stmt);

    }


    $serverName = "localhost";
    $dBUsername = "root";
    $dBPassword = "";
    $dBName = "members"; 
    
    $conn = new mysqli($serverName, $dBUsername, $dBPassword, $dBName);
    
    $sql1 = "UPDATE Children c
    SET c.parent_id = (
      SELECT p.user_id
      FROM users u
      JOIN parent p ON u.email = p.mEmail OR u.email = p.fEmail
      WHERE c.parent_id IS NULL -- Only update rows with NULL parent_id to avoid overwriting existing values
      LIMIT 1 -- Assuming you want to update one parent_id per child record; you may modify this as needed
    );";
    
    if ($conn->query($sql1) === TRUE) {
        echo "New Record Created Successfully";
    } else {
        echo "Error: " .$sql1 . "<br>" . $conn->error;
    }
    $conn->close();



header("Location: ../mf3.php?childreninfo=success");

}

Your `Children` table is very poorly designed. You shouldn't have 10 sets of columns for different children. You should have a separate row for each child. — Barmar, Jul 29 '23 at 17:14
If there's no relationship between the tables, I don't see how you can automatically assign children. It has to be done by the person entering the children data, only they know who the child's parents are. — Barmar, Jul 29 '23 at 17:17

score -1 · Accepted Answer · answered Jul 29 '23 at 17:59

-1

Based on your description, it seems you want to update the parent_id column in the Children table based on matching values between the users and parent tables. You want to use the usersId from the users table and the user_id from the parent table to update the parent_id in the Children table.

You can achieve this using the following SQL query:

UPDATE Children c
SET c.parent_id = (
  SELECT p.user_id
  FROM users u
  JOIN parent p ON u.email = p.mEmail OR u.email = p.fEmail
  WHERE c.parent_id IS NULL -- Only update rows with NULL parent_id to avoid overwriting existing values
  LIMIT 1 -- Assuming you want to update one parent_id per child record; you may modify this as needed
);

Explanation:

The UPDATE statement updates the Children table, aliasing it as c.
We use a subquery to find the corresponding user_id from the parent table based on the email from the users table (matching either mEmail or fEmail).
The JOIN condition connects the users and parent tables based on the email match.
The WHERE clause ensures that we only update rows in the Children table where parent_id is currently NULL (if you have already inserted some parent IDs, this avoids overwriting them).
The LIMIT 1 ensures that we update one parent_id per child record; you can adjust this if you need to update multiple records for each child.

Please make sure to backup your database before running any update queries, just to be safe.

As for your concern about performance with growing database entries, this query should be efficient as long as you have appropriate indexes on the relevant columns, such as email in the users table and mEmail and fEmail in the parent table. Indexes will significantly speed up the lookup process, especially when dealing with large datasets.

answered Jul 29 '23 at 17:59

SunilDahal

10
3

My goodness you are a personal hero of mine now. I appreciate the explanation and the attention to detail. Thank you so much. – Joshua Lee Jul 29 '23 at 20:39
I will say I am only getting NULL in the parent_id for some reason now. Would you have any idea why it worked twice and once I cleared all records from the database it would only be showing NULL on the children table now? – Joshua Lee Jul 29 '23 at 20:51
It looks like you've provided the code for updating the user_id in the parent table, but you mentioned that you are getting NULL in the parent_id column of the Children table. Since the code you provided doesn't directly handle the Children table, it might not be the source of the issue. To troubleshoot the NULL values in the parent_id column of the Children table, let's focus on the part of your code that inserts data into the Children table. The problem might be related to how you are inserting the data or handling the foreign key relationship. – SunilDahal Jul 30 '23 at 11:12
Verify that the parent_id column in the Children table is set up as a foreign key that references the user_id column in the parent table. Make sure the data types and constraints are matching between the two tables. When inserting data into the Children table, ensure that you are providing a valid parent_id (which should be the user_id from the parent table). If you have a dropdown list or some other mechanism to select the parent during the child's registration, make sure the correct user_id is being assigned as the parent_id. – SunilDahal Jul 30 '23 at 11:13
Double-check your insert statement for the Children table to ensure you are providing the correct value for the parent_id column. You might want to add some debugging statements or print the SQL query to see what values are being used during the insert. – SunilDahal Jul 30 '23 at 11:13
Here's an example of how you might be inserting data into the Children table: // Assuming you have the parent_id available in a variable called $parent_id $sql_children = "INSERT INTO Children (childName, dob, age, parent_id) VALUES (?, ?, ?, ?)"; $stmt_children = mysqli_stmt_init($conn); if (!mysqli_stmt_prepare($stmt_children, $sql_children)) { echo "SQL error"; } else { mysqli_stmt_bind_param($stmt_children, "sssi", $childName, $dob, $age, $parent_id); mysqli_stmt_execute($stmt_children); } – SunilDahal Jul 30 '23 at 11:14
Make sure that $parent_id holds the correct value (i.e., the user_id from the parent table) before inserting data into the Children table. If the issue persists after reviewing the insert code for the Children table, please provide additional relevant code and database schema details, including how you are handling the relationship between parents and children, so I can better assist you in identifying the problem. – SunilDahal Jul 30 '23 at 11:15
I will update with my php code above. I think it may be an order of operations type problem. If I run the statement that you gave me it first will enter null in the parent_id column but if I go back to the page and immediately re-run the statement it will enter all of the children variables and the updated parent_id. The issue is that the statement will add the same parent_id each time. It doesn't however add the sequential number to the record that comes next. – Joshua Lee Jul 30 '23 at 16:35
For instance, if I add John Doe, 01-01-2011, 12, NULL : It enters the data from the form with the parent_id as NULL, then if I hit back on my browser and resubmit the form it will then update with the same (NAME)John Doe,(DATE) 01-01-2011,(AGE)12, (Parent_id) 81. this time however it will fill in the parent_id with the associated user_id from the parent table. However, if another user signs up I get (Name) Jane Doe, (DATE) 02-14-2011, (AGE) 12, (parent_id) 81, it inserts the same parent_id for two different records? – Joshua Lee Jul 30 '23 at 16:46
Try this : First, insert data into the parent table to create the parent record for the user. Retrieve the user_id of the newly created parent record. Use the obtained user_id as the parent_id when inserting data into the Children table. – SunilDahal Jul 30 '23 at 18:32
Forgive me I am really new to this. So, the parent information is on the previous forms page and has already inserted into the Db. I am unsure by what you mean retrieve the user_id of the parent record? How would I go about retrieving something from a previous submit into the database, storing it and then using the stored value in a new form? – Joshua Lee Jul 30 '23 at 19:09
SunilDahal I want to thank you very much for your patience and holding my hand through this. I am learning a great deal from you and know your time is valuable. Again many thanks to you for all you have taught me thus far. – Joshua Lee Jul 30 '23 at 19:26
// Step 1: Insert data into the parent table $sql_parent = "INSERT INTO parent (mFname, mLname, mEmail, mPhone, fFname, fLname, fEmail, fPhone, addressL1, addressL2, city, stateAbbr, zip) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"; $stmt_parent = mysqli_stmt_init($conn); parent); //Step 2: Retrieve the user_id of the newly created parent record $user_id = mysqli_insert_id($conn); // Step 3: Insert data into the Children table using the obtained user_id as parent_id $sql_children = "INSERT INTO Children (childName, dob, age, parent_id) VALUES (?, ?, ?, ?)"; – SunilDahal Jul 30 '23 at 19:40
By following this approach, you should be able to ensure that each child record is associated with the correct parent record using the parent_id as a foreign key – SunilDahal Jul 30 '23 at 19:42
Not sure if this is what is preventing me from accomplishing this or not but the Form is spread between 5 different pages to make the user experience a little less intimidating. The format currently is as follows Signup/Login page --> Parent Info --> Children Info --> Waiver Acceptance --> Payment page. How do I select the parent_id from the childrens php page and store that data for an insert into the childrens table? – Joshua Lee Jul 31 '23 at 13:21
I think, you have no idea about Session, and $_GET – SunilDahal Jul 31 '23 at 18:03
To achieve this, you need to pass the parent_id from the Parent Info page to the Children Info page so that it can be used when inserting data into the Children table. There are several ways to pass data between pages in PHP. // Assuming you already have $user_id as the parent's user_id header("Location: children_info.php?parent_id=" . $user_id); Children Info page: On the Children Info page, you can retrieve the parent_id using $_GET['parent_id'] and use it when inserting data into the Children table. – SunilDahal Jul 31 '23 at 18:05
if (isset($_GET['parent_id'])) { $parent_id = $_GET['parent_id']; // Use $parent_id when inserting data into the Children table } – SunilDahal Jul 31 '23 at 18:06
If you still need help, please share your codes for parents page and one of for child page, I’ll help you out to solve the issue you are facing. – SunilDahal Aug 03 '23 at 06:23
2

Hi, SunilDahal! All three of your answers, and most of your comments, appear likely to be entirely or partially written by AI (e.g., ChatGPT). Please be aware that [posting AI-generated content is not allowed here](//meta.stackoverflow.com/q/421831). If you used an AI tool to assist with any answer, I would encourage you to delete it. We do hope you'll stick around and continue to be a valuable part of our community by posting *your own* quality content. Thanks! – NotTheDr01ds Aug 05 '23 at 02:18
2

**Readers should review this answer carefully and critically, as AI-generated information often contains fundamental errors and misinformation.** If you observe quality issues and/or have reason to believe that this answer was generated by AI, please leave feedback accordingly. – NotTheDr01ds Aug 05 '23 at 02:18

I want to INSERT the user_Id from a table into the parent_id in another table however I have no common values between the two tables

1 Answers1