9 Replies - 292 Views - Last Post: 18 October 2017 - 07:05 AM Rate Topic: -----

#1 hackеr  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 31
  • Joined: 18-February 16

PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 12:47 PM

Error:
Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`phpcms`.`comments`, CONSTRAINT `Foreign Key to admin_panel table` FOREIGN KEY (`admin_panel_id`) REFERENCES `admin_panel` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) in ...\CMS\fullPost.php:34 Stack trace: #0 ...\CMS\fullPost.php(34): PDOStatement->execute(Array) #1 {main} thrown in ...\CMS\fullPost.php on line 34

<?php require_once("include/db.php"); ?>
<?php require_once("include/sessions.php"); ?>
<?php require_once("include/functions.php"); ?>
<?php  

	if(isset($_POST['Submit'])){
		
		$name 			=	$_POST['Name'];
		$email 			=	$_POST['Email'];
		$comment 		=	$_POST['Comment'];
		//Set timezone and current time in format
		date_default_timezone_set("America/Denver");
		$currentTime 	=	time();
		$dateTime 		=	strftime("%b-%d-%Y %H:%M:%p", $currentTime);
		//name,email,comment varifications
		if(empty($name) || empty($email) || empty($comment))
		{
			$_SESSION['ErrorMessage']="All fields are required!";	
		} 
		else if(strlen($email) < 5)
		{
			$_SESSION['ErrorMessage']="Please enter a valid email!";
		} 
		else if(strlen($comment) > 1000)
		{
			$_SESSION['ErrorMessage']="Comment should not be more than 1000 characters!";
		} 
		else 
		{
			$postID 	= 	$_GET['id'];

			$Query		= 	$pdo->prepare('INSERT INTO comments (datetime, name, email, comment, status, admin_panel_id) VALUES (?,?,?,?,?,?)');

			if($Query->execute([$dateTime, $name, $email, $comment, "OFF", $postID]))
			{
				$_SESSION['SuccessMessage']="Comment Submitted Successfully!";
			} 
			else 
			{
				$_SESSION['ErrorMessage']="Comment Failed to Submit!";
			}
		}
	}

?>


Rest of the code:
<!DOCTYPE html>
<html>
<head>

	<title>Full Blog Page</title>
	<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">
	<script src="js/jquery-3.2.1.min.js"></script>
	<script src="js/popper.js"></script>
	<script src="js/bootstrap.min.js"></script>
	<link rel="stylesheet" href="css/publicstyle.css">

</head>
<body>
	<?php include 'head.php';?>
	
	<div class="container">
		<div class="blog-header">
			<h1>CMS Blog</h1>
			<p class="lead">Complete Blog</p>
		</div>
		<div class="row">
			<div class="col-sm-8"><!-- Main Blog -->
				<div><?php echo Message(); echo successMessage(); ?></div>
				<?php
					
					if(isset($_GET['SearchButton']))
					{
						$Search=$_GET['Search'];
						$ViewQuery = $pdo->prepare('SELECT * FROM admin_panel
													WHERE datetime LIKE "%:search%"
														OR title LIKE "%:search%"
														OR category LIKE "%:search%"
														OR author LIKE "%:search%"
														OR post LIKE "%:search%"
													ORDER BY datetime desc');
						$ViewQuery->execute(['search' => $Search]);

					//	$ViewQuery="SELECT * FROM admin_panel 
					//		WHERE datetime LIKE '%$Search%' 
					//			OR title LIKE '%$Search%' 
					//			OR category LIKE '%$Search%'
					//			OR author LIKE '%$Search%'
					//			OR post LIKE '%$Search%'
					//		ORDER BY datetime desc;";
					} 
					else 
					{
						$PostID=$_GET['id'];
						$ViewQuery = $pdo->prepare('SELECT * FROM admin_panel WHERE id=? ORDER BY datetime desc');
						$ViewQuery->execute([$PostID]);
					//	$ViewQuery = "SELECT * FROM admin_panel WHERE id='$PostIDFromURL' ORDER BY datetime desc;";
					}

					while ($post = $ViewQuery->fetch())
					{
						$Id 		=	$post["id"];
						$DateTime 	=	$post["datetime"];
						$Title 		=	$post["title"];
						$Category 	=	$post["category"];
						$Admin 		=	$post["author"];
						$Image 		=	$post["image"];
						$Post 		=	$post["post"];
				?>
				<div class="img-thumbnail blog-post">
					<div class="caption">
						<h1 id="heading"><?php echo htmlentities($Title); ?></h1>
					</div>
					<img class="img-fluid img-thumbnail mx-auto d-block" src="upload/<?php echo $Image; ?>" >
					<div class="caption">
						<p class="description">&emsp;Category: <?php echo htmlentities($Category); ?>&emsp;
							Published on <?php echo htmlentities($DateTime); ?>
						</p>
						<p class="post"><?php 
							
							echo $Post; ?></p>
					</div>
					
				</div>
				<?php } ?>
				<br>
				<span class="fieldInfo">Comments</span><br><br>
					
				<?php  
					$PostID=$_GET['id'];

					$ExtractQuery = $pdo->prepare('SELECT * FROM comments 
									WHERE admin_panel_id=? AND status="ON"');
					$ExtractQuery->execute([$PostID]);
					//$ExtractQuery="SELECT * FROM comments 
					//				WHERE admin_panel_id=$PostId AND status='ON';";
					//$Execute=mysqli_query($Connection, $ExtractQuery);

					while ($comment = $ExtractQuery->fetch())
					{
						$CommentDate=$comment["datetime"];
						$CommentName=$comment["name"];
						$Comment=$comment["comment"];
				?>
				
				<div class="comments">
					<img class="pull-left" src="assets/user.png" width="80px" height="80px">
					<p><b><?php echo $CommentName; ?></b> <br> <span class="commentDate"><?php echo $CommentDate; ?></span></p>
					<p><?php echo $Comment; ?></p>
				</div>
				<hr>
				<?php } ?>


				<div>
					<form action="fullPost.php?id=<?php echo $PostIDFromURL; ?>" method="POST">
						<fieldset>
							<div class="form-group">
								<label for="name"><span class="field">Name:</span></label>
								<input class="form-control" type="text" name="Name" id="name" placeholder="Name">
							</div>
							<div class="form-group">
								<label for="email"><span class="field">Email:</span></label>
								<input class="form-control" type="text" name="Email" id="email" placeholder="Email">
							</div>
							<div class="form-group">
								<label for="commentarea"><span class="field">Comment:</span></label>
								<textarea class="form-control" name="Comment" id="commentarea" placeholder="Please enter your comment here.."></textarea>
							</div>
							<br>
							<input class="btn btn-success" type="Submit" name="Submit" value="Add New Comment">
						</fieldset>
					</form>
				</div><br>
			</div><!-- end of Main Blog -->
			<div class="ml-md-auto col-sm-3"><!-- Side Area -->
				<h2>Test</h2>
				<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit. Dolore velit, ipsum aperiam dicta doloremque. Fuga saepe fugiat eos facilis, illum ratione similique tempore, voluptatum, neque vel blanditiis, ipsam labore aspernatur! Laborum a excepturi, debitis accusantium quasi harum tenetur maxime, enim officiis odio officia labore quo dolor veniam sint alias similique.</p>
			</div> <!-- end of Side Area -->
		</div>
	</div>

	<?php include 'footer.php';?>
</body>
</html>


I get that the error is because of a foreign key, but I don't get why I receive the error.
Each column data in the INSERT statement matches the data type in the table so that's not the problem.

Do I have to add more to the $postID?

Is This A Good Question/Topic? 0
  • +

Replies To: PHP/MySQL Integrity constraint violation

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13495
  • View blog
  • Posts: 53,911
  • Joined: 12-June 08

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 12:51 PM

Is the key combo not unique?
Was This Post Helpful? 0
  • +
  • -

#3 hackеr  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 31
  • Joined: 18-February 16

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 12:54 PM

I'm not sure what you mean?

The 'id' in the 'admin_panel' table wasn't unique but I just changed it and the error still occurs.
The 'admin_panel_id' in the 'comment' table, I don't think should be unique because there can be multiple comments on one post.

This post has been edited by hackеr: 17 October 2017 - 12:55 PM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13495
  • View blog
  • Posts: 53,911
  • Joined: 12-June 08

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 12:58 PM

Ok.. take a step back. If you have a foreign key to another table.. does that row exist BEFORE you are doing this insert?
Was This Post Helpful? 0
  • +
  • -

#5 hackеr  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 31
  • Joined: 18-February 16

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 01:02 PM

Well, in order to submit a comment to a particular Post ('x' id), there must be a post with the 'x' id. Does that make sense? Is that what you were asking?

Actually, do I even need the foreign key relationship between the two tables? It makes sense to me to have the foreign key but when I think about it I don't really need it because as I said, if I am submitting a comment it won't be associated with any other post's id but the one that the comment was submitted on.
Was This Post Helpful? 0
  • +
  • -

#6 hackеr  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 31
  • Joined: 18-February 16

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 01:19 PM

I tested it without the foreign key relationship and the comment does get inserted but the admin_panel_id is always 0. I suspect that the $postID = $_GET['id']; is null but when I get the id later on in the code, that return the right id.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6384
  • View blog
  • Posts: 25,781
  • Joined: 12-December 12

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 01:25 PM

Where are you assigning a value to $PostIDFromURL?
Was This Post Helpful? 1
  • +
  • -

#8 hackеr  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 31
  • Joined: 18-February 16

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 01:28 PM

THANK YOU!!!

I was converting outdated php to pdo and commented out some things to test and then I changed the name of that variable. Now I fixed it and it works.
Was This Post Helpful? 1
  • +
  • -

#9 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6384
  • View blog
  • Posts: 25,781
  • Joined: 12-December 12

Re: PHP/MySQL Integrity constraint violation

Posted 17 October 2017 - 01:33 PM

Glad I could help.
Was This Post Helpful? 0
  • +
  • -

#10 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3716
  • View blog
  • Posts: 13,476
  • Joined: 08-August 08

Re: PHP/MySQL Integrity constraint violation

Posted 18 October 2017 - 07:05 AM

View Posthackеr, on 17 October 2017 - 03:28 PM, said:

I was converting outdated php to pdo...

+1 for that!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1