Kind assistance with inserting into multiple tables

Question

I am building a study planner.

I have designed a draft interface and the database and I am currently trying to insert test registration details into the database but I have been on this for three days now with no success. I will highly appreciate some kind help with pointing out what I have been doing wrong, please.

The message/error I am getting is as thus:

Connected successfully

Error details for Result 2: Cannot add or update a child row: a foreign key constraint fails (`p00702`.`universityreferences`, CONSTRAINT `universityreferences_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE).

Error details for Result 3: Cannot add or update a child row: a foreign key constraint fails (`p00702`.`mobiles`, CONSTRAINT `mobiles_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE).

Error details for Result 4: Cannot add or update a child row: a foreign key constraint fails (`p00702`.`logins`, CONSTRAINT `logins_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE).

All queries were rolled back

I also noticed that $id = $mysqli->insert_id; right after the first query is also not working and I don’t know why but I suspect it may be the root cause.

My PHP code is:

<?php
if (isset($_POST['submitted'])) {

    include 'serverSide/connectToServer.php';

    $firstName = $_POST['firstName'];
    $lastName = $_POST['lastName'];
    $studentId = $_POST['studentId'];
    $mobile = $_POST['mobile'];
    $username = $_POST['username'];
    $password = $_POST['password'];

    //turn off autocommit
    $mysqli->autocommit(false);

    $flag = true;

    // attempt insert query executions
    $query1 = "INSERT INTO users (firstname, lastname) VALUES ('$firstName', '$lastName');";
    $id = $mysqli->insert_id;

    $query2 = "INSERT INTO universityreferences (userid, universityreference) VALUES ('$id', '$studentId');";
    $id2 = $mysqli->insert_id;

    $query3 = "INSERT INTO mobiles (userid, mobile) VALUES ('$id2', '$mobile');";
    $id3 = $mysqli->insert_id;

    $query4 = "INSERT INTO logins (userid, username, password) VALUES ('$id3', '$username', '$password');";

    $result1 = mysqli_query($mysqli, $query1);
    if (!$result1) {
        $flag = false;
        echo "Error details for Result 1: " . mysqli_error($mysqli) . ".";
    }

    $result2 = mysqli_query($mysqli, $query2);
    if (!$result2) {
        $flag = false;
        echo "Error details for Result 2: " . mysqli_error($mysqli) . ".";
    }

    $result3 = mysqli_query($mysqli, $query3);
    if (!$result3) {
        $flag = false;
        echo "Error details for Result 3: " . mysqli_error($mysqli) . ".";
    }

    $result4 = mysqli_query($mysqli, $query4);
    if (!$result4) {
        $flag = false;
        echo "Error details for Result 4: " . mysqli_error($mysqli) . ".";
    }

    if ($flag) {
        mysqli_commit($mysqli);
        echo "All queries were executed successfully";
    } else {
        mysqli_rollback($mysqli);
        echo "All queries were rolled back";
    }

    mysqli_close($mysqli);
}
?>

<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->

<html>
    <head>
        <meta charset="UTF-8">
        <link rel="shortcut icon" type="image/png" href="/images/favicon.png"/>
        <title>Just-Read</title>
        <link rel="stylesheet" type="text/css" href="css/styles.css">
    </head>
    <body>
        <!-- Link to external JavaScript file -->
        <script src="javascript/validator.js"></script>
        <div id="container">
            <div id="header">
                <!-- Web site Logo -->
                <div class="logo">
                    <img src="images/logo.png" width="128" height="93.5" alt="Logo"/><br><br>
                </div>
                <div id="logoText">
                    <h1>Just Read</h1>
                </div>
            </div>
            <div id="leftColumn">
                <h4>The ultimate study planner</h4>
            </div>
            <div id="rightColumn">
                <!-- Registration Form -->
                <h3>Please fill out the form below</h3>
                <form name="Register" action="registration.php" onsubmit="return registrationValidator()" autocomplete="on" method="POST">
                    <!--According to YouTuber Ralph Philips, this makes sure a blank form cannot be submitted to the database-->
                    <input type="hidden" name="submitted" value="true"/>
                    <div class="register">
                        <label><b>First Name*</b></label>
                        <input type="text" id="firstName" name="firstName" placeholder="Enter your first name" autofocus/>
                        <label><b>Last Name*</b></label>
                        <input type="text" id="lastName" name="lastName" placeholder="Enter your last name" />
                        <label><b>Student ID*</b></label>
                        <input type="text" id="studentId" name="studentId" placeholder="Enter your university ID" />
                        <label><b>Mobile</b></label>
                        <input type="text" id="mobile" name="mobile" placeholder="Enter your phone number" />
                        <label><b>Email Address (Username)*</b></label>
                        <input type="email" id="username" name="username" placeholder="Enter your email address" />
                        <label><b>Password*</b></label>
                        <input type="password" id="password" name="password" placeholder="Enter your password" />

                        <button type="submit">Register</button>
                    </div>
                    <div id="back">
                        <a href="index.php">Back</a>
                    </div>
                    <div id="mandatoryFields">
                        <h4>* Mandatory Fields</h4>
                    </div>
                </form>
            </div>
            <div id="footer">
                Copyright &copy; 2017, Chizzy Meka.
            </div>
        </div>
    </body>
</html>

My full MySQL code is:

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 08, 2017 at 06:49 PM
-- Server version: 5.7.14
-- PHP Version: 5.6.25

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `p00702`
--

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `commentid` int(10) NOT NULL,
  `modulecodeid` int(10) NOT NULL,
  `comment` text,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `logins`
--

CREATE TABLE `logins` (
  `loginid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `mobiles`
--

CREATE TABLE `mobiles` (
  `mobileid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `mobile` varchar(10) DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `modulecodes`
--

CREATE TABLE `modulecodes` (
  `modulecodeid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `modulecode` varchar(10) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `moduletitles`
--

CREATE TABLE `moduletitles` (
  `moduletitleid` int(10) NOT NULL,
  `modulecodeid` int(10) NOT NULL,
  `moduletitle` varchar(100) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `studyplans`
--

CREATE TABLE `studyplans` (
  `studyplan` int(10) NOT NULL,
  `modulecodeid` int(10) NOT NULL,
  `studydate` date NOT NULL,
  `numberofstudyhours` int(10) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `universityreferences`
--

CREATE TABLE `universityreferences` (
  `universityreferenceid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `universityreference` varchar(100) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userid` int(10) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `comments`
--
ALTER TABLE `comments`
  ADD PRIMARY KEY (`commentid`),
  ADD KEY `modulecodeid` (`modulecodeid`);

--
-- Indexes for table `logins`
--
ALTER TABLE `logins`
  ADD PRIMARY KEY (`loginid`),
  ADD KEY `userid` (`userid`);

--
-- Indexes for table `mobiles`
--
ALTER TABLE `mobiles`
  ADD PRIMARY KEY (`mobileid`),
  ADD KEY `userid` (`userid`);

--
-- Indexes for table `modulecodes`
--
ALTER TABLE `modulecodes`
  ADD PRIMARY KEY (`modulecodeid`),
  ADD KEY `userid` (`userid`);

--
-- Indexes for table `moduletitles`
--
ALTER TABLE `moduletitles`
  ADD PRIMARY KEY (`moduletitleid`),
  ADD KEY `modulecodeid` (`modulecodeid`);

--
-- Indexes for table `studyplans`
--
ALTER TABLE `studyplans`
  ADD PRIMARY KEY (`studyplan`),
  ADD KEY `modulecode` (`modulecodeid`);

--
-- Indexes for table `universityreferences`
--
ALTER TABLE `universityreferences`
  ADD PRIMARY KEY (`universityreferenceid`),
  ADD KEY `userid` (`userid`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `comments`
--
ALTER TABLE `comments`
  MODIFY `commentid` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `logins`
--
ALTER TABLE `logins`
  MODIFY `loginid` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `mobiles`
--
ALTER TABLE `mobiles`
  MODIFY `mobileid` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `modulecodes`
--
ALTER TABLE `modulecodes`
  MODIFY `modulecodeid` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `moduletitles`
--
ALTER TABLE `moduletitles`
  MODIFY `moduletitleid` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `studyplans`
--
ALTER TABLE `studyplans`
  MODIFY `studyplan` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `universityreferences`
--
ALTER TABLE `universityreferences`
  MODIFY `universityreferenceid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `userid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `comments`
--
ALTER TABLE `comments`
  ADD CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`modulecodeid`) REFERENCES `modulecodes` (`modulecodeid`) ON UPDATE CASCADE;

--
-- Constraints for table `logins`
--
ALTER TABLE `logins`
  ADD CONSTRAINT `logins_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE;

--
-- Constraints for table `mobiles`
--
ALTER TABLE `mobiles`
  ADD CONSTRAINT `mobiles_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE;

--
-- Constraints for table `modulecodes`
--
ALTER TABLE `modulecodes`
  ADD CONSTRAINT `modulecodes_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE;

--
-- Constraints for table `moduletitles`
--
ALTER TABLE `moduletitles`
  ADD CONSTRAINT `moduletitles_ibfk_1` FOREIGN KEY (`modulecodeid`) REFERENCES `modulecodes` (`modulecodeid`) ON UPDATE CASCADE;

--
-- Constraints for table `studyplans`
--
ALTER TABLE `studyplans`
  ADD CONSTRAINT `studyplans_ibfk_1` FOREIGN KEY (`modulecodeid`) REFERENCES `modulecodes` (`modulecodeid`) ON UPDATE CASCADE;

--
-- Constraints for table `universityreferences`
--
ALTER TABLE `universityreferences`
  ADD CONSTRAINT `universityreferences_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Thanking you in advance for your assistance.

Possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) — asteriskTheServer, Apr 08 '17 at 19:09
Hi, @asteriskTheServer, Thank you for the suggested similar question, however, I had already seen that post and it didn't inform me enough before I decided to put up my own post. While I agree that there is a similarity, I believe my PHP is wrong and the suggested answer cannot help me with that. Besides, I am dealing with more tables, which makes things trickier, so please, I will appreciate if anyone can look into my own case and offer some advice. — Chizzy Meka, Apr 08 '17 at 19:22

score 1 · Answer 1 · answered Apr 08 '17 at 19:19

The problem is that you are using mysqli->insert_id before the query has been executed. When you define the first query, the database hasn't generated the id yet, because the insert hasn't been performed.

The correct way of doing that is

$query1 = "INSERT INTO users (firstname, lastname) VALUES ('$firstName', '$lastName');";

$result1 = mysqli_query($mysqli, $query1);
if (!$result1) {
    $flag = false;
    echo "Error details for Result 1: " . mysqli_error($mysqli) . ".";
}

$id = $mysqli->insert_id;

//go on with the next insert
//defining the query, executing it and then using the generated id

All those error message are caused by the fact that when you run query2, you're violating the foreign key you have defined on user_id, because you're inserting a row with an empty value.

Thank you very much, @Stefano Zanini, I will let you know how it works out. — Chizzy Meka, Apr 08 '17 at 19:52
It is now working. Thank you so much and may you also find help when you need it! — Chizzy Meka, Apr 08 '17 at 20:14

score 1 · Accepted Answer · answered Apr 08 '17 at 19:20

1

The problem is that you try to get the last inserted id before executing any query. For example:

$query1 = "INSERT INTO users (firstname, lastname) VALUES ('$firstName', '$lastName');";
$id = $mysqli->insert_id;

In the above code you just define the sql statement, but you do not execute it. Therefore last insert id will not return anything.

Execute the insert_id() method only after you execute the query:

$result1 = mysqli_query($mysqli, $query1);

answered Apr 08 '17 at 19:20

Shadow

33,525
10
51
64

Thank you very much, @Shadow, I will let you know how it works out. – Chizzy Meka Apr 08 '17 at 19:53
It is now working. Thank you so much and may you also find help when you need it! – Chizzy Meka Apr 08 '17 at 20:14

Kind assistance with inserting into multiple tables

2 Answers2