View Single Post
  #6 (permalink)  
Old November 3rd, 2016, 12:40 PM
klakier klakier is offline
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Probably I'm having the same issue, but unfortunately the author didn't explain exactly how he fixed it.

From the:
PHP Code:
    echo mysqli_error($link); 
after adding a post I'm getting this:
Code:
Cannot add or update a child row: a foreign key constraint fails (`boronczyk_2`.`FORUM_MESSAGE`, CONSTRAINT `FORUM_MESSAGE` FOREIGN KEY (`PARENT_MESSAGE_ID`) REFERENCES `FORUM_MESSAGE` (`MESSAGE_ID`))
and the post is not added to the DB.

That's the code with which I'm creating FORUM_MESSAGE table:

Code:
CREATE TABLE `FORUM_MESSAGE` (
  `MESSAGE_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `PARENT_MESSAGE_ID` bigint(20) unsigned NOT NULL DEFAULT 0,
  `FORUM_ID` int(11) unsigned NOT NULL,
  `USER_ID` int(11) unsigned NOT NULL,
  `SUBJECT` varchar(100) NOT NULL,
  `MESSAGE_TEXT` text NOT NULL,
  `MESSAGE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`MESSAGE_ID`),

  FOREIGN KEY (`USER_ID`) REFERENCES `USER`(`USER_ID`),

  FOREIGN KEY (`PARENT_MESSAGE_ID`) REFERENCES `FORUM_MESSAGE`(`MESSAGE_ID`));
I'm using phpMyAdmin. When exporting SQL I have this:

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

CREATE TABLE `FORUM_MESSAGE` (
  `MESSAGE_ID` bigint(20) UNSIGNED NOT NULL,
  `PARENT_MESSAGE_ID` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `FORUM_ID` int(11) UNSIGNED NOT NULL,
  `USER_ID` int(11) UNSIGNED NOT NULL,
  `SUBJECT` varchar(100) NOT NULL,
  `MESSAGE_TEXT` text NOT NULL,
  `MESSAGE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `FORUM_MESSAGE`
  ADD PRIMARY KEY (`MESSAGE_ID`),  ADD KEY `USER_ID` (`USER_ID`),
  ADD KEY `PARENT_MESSAGE_ID` (`PARENT_MESSAGE_ID`);

ALTER TABLE `FORUM_MESSAGE`
  MODIFY `MESSAGE_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `FORUM_MESSAGE`
  ADD CONSTRAINT `FORUM_MESSAGE_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `USER` (`USER_ID`),
  ADD CONSTRAINT `FORUM_MESSAGE_ibfk_2` FOREIGN KEY (`PARENT_MESSAGE_ID`) REFERENCES `FORUM_MESSAGE` (`MESSAGE_ID`);
There is ADD KEY `PARENT_MESSAGE_ID` (`PARENT_MESSAGE_ID`); (as MasterYoda mentioned as the issue), but afterwards there is also ADD CONSTRAINT `FORUM_MESSAGE_ibfk_2` FOREIGN KEY (`PARENT_MESSAGE_ID`) REFERENCES `FORUM_MESSAGE` (`MESSAGE_ID`); so I am not exactly sure if that's the problem.

Anyway I tried to add it like this
Code:
ALTER TABLE `FORUM_MESSAGE`
  ADD KEY `PARENT_MESSAGE_ID` (`MESSAGE_ID`);

ALTER TABLE `FORUM_MESSAGE`
  ADD CONSTRAINT `FORUM_MESSAGE_ibfk_2` FOREIGN KEY (`PARENT_MESSAGE_ID`) REFERENCES `FORUM_MESSAGE` (`MESSAGE_ID`);
But that didn't work.

That's my add_post.php If you would need it:
PHP Code:
<?php

require_once '../lib/DBconn.php';
require_once 
'../lib/functions.php';
require_once 
'../lib/User.php';
require_once 
'401.php';

$user User::getById(($_SESSION['userId']));

$forum_id = (isset($_GET['fid'])) ? intval($_GET['fid']) : 0;

$query "SELECT FORUM_ID FROM FORUM WHERE FORUM_ID = '$forum_id'";

$conn DB::connect();

$result $conn->query($query);

if (!
mysqli_num_rows($result)) {
    
    
mysqli_free_result($result);
    die(
"<p>Incorrect forum ID.</p>");
}

mysqli_free_result($result);

$msg_id = (isset($_GET['mid'])) ? intval($_GET['mid']) : 0;

$query "SELECT MESSAGE_ID FROM FORUM_MESSAGE WHERE MESSAGE_ID = '$msg_id'";

$conn DB::connect();

$result $conn->query($query);

if (
$msg_id && !mysqli_num_rows($result)) {
    
    
mysqli_free_result($result);
    die(
"<p>Incorrect message ID.</p>");
}

mysqli_free_result($result);

$msg_subject = (isset($_POST['msg_subject'])) ? trim($_POST['msg_subject']) : '';
$msg_text = (isset($_POST['msg_text'])) ? trim($_POST['msg_text']) : '';

if (isset(
$_POST['submitted']) && $msg_subject && $msg_text) {
    
    
$conn DB::connect();
    
$msub mysqli_real_escape_string($conn$msg_subject);
    
$mtxt mysqli_real_escape_string($conn$msg_text);
    
$uid $user->getUid();
    
    
$query "INSERT INTO FORUM_MESSAGE (SUBJECT, MESSAGE_TEXT, PARENT_MESSAGE_ID, FORUM_ID, USER_ID) VALUES ('$msub', '$mtxt', '$msg_id', '$forum_id', '$uid')";
    
    
    
$conn->query($query);
    echo 
mysqli_error($conn);
    
    
// redirection
    
    //header('Location: view.php?fid=' . $forum_id . (($msg_id) ? '&mid=' . $msg_id : ''));
    
// form sent but incorret data was given
} elseif (isset($_POST['submitted'])) {
    
    
$message "<p>Not all necessary dava was given. Please fill the form and send it again</p>";
}

ob_start();

if (isset(
$message)) echo $message;
?>
<form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']) . '?fid=' $forum_id '&mid=' $msg_id?>" method="POST">
    <div>
        <label>Subject</label><br>
        <input type="input" name="msg_subject" id="msg_subject" value="<?php echo htmlspecialchars($msg_subject); ?>"><br>
        <label>Message</label><br>
        <input type="input" name="msg_text" id="msg_text" value="<?php echo htmlspecialchars($msg_text); ?>"><br><br>
        <input type="submit" value="Create"><br><br>
        <input type="hidden" name="submitted" value="1">
    </div>
</form>

<?php

$GLOBALS
['TEMPLATE']['content'] = ob_get_clean();

include 
'../templates/template.php';
I would really appreciate any help :D
Reply With Quote