[SOLVED] Cannot add or update a child row: a foriegn key constraint fails

cbear2021 · April 2, 2009

Hi There,

I'm having an issue doing an insert into a table - How my website works is standard user registration and login scripts, and then the user has the ability to upload a pitch for a film into a webform, and select multiple genre's and resources using checkboxes which are passed through an array. I've set all my tables up using the InnoDB format so that I can use foriegn keys. The way I've been coding this is through using various textbook examples, and tutorials from the net. I've tried doing a search for my subject phrase here on phpfreaks, but none of the posts seem (unless i'm being totally stupid) to answer my question. The problem is occuring when trying to do an insert into the pitch table (i'll post my sql code below).

I've set my tables up so I have a users table, a pitch table, a genre and resource table, and two look up tables for the genre and resource linking to the pitch table. When I run the insert query, What i'm trying to do is use the current user ID obtained from the users table via sessions, and insert that into the userid column in the pitch table, which references the userid in the user table, but it doesn't seem to like it. The users id is obtained through the mysql_fetch_array function, and then assigning the id field to the session id. So i'm wondering if perhaps I've set my foriegn key constraints up wrong, or if it's an error in my coding. I'm a bit of a newbie to mysql, I've looked around on google/here/database textbooks, but not 100% sure where i'm going wrong, so any help would be much appreciated.

The sql code is generated from phpmyadmin.

Users table:

CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `password` varchar(32) NOT NULL,
  `location` varchar(50) NOT NULL,
  `age` mediumint(2) NOT NULL,
  `gender` varchar(12) NOT NULL,
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `email` (`email`),
  KEY `lastname` (`lastname`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='table of users' AUTO_INCREMENT=3 ;

Pitch Table: userid refs user.userid

CREATE TABLE IF NOT EXISTS `pitch` (
  `pitchid` int(11) NOT NULL auto_increment,
  `pitchname` varchar(255) NOT NULL,
  `pitch` longtext NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY  (`pitchid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='table for pitches' AUTO_INCREMENT=1 ;

Genre table:

CREATE TABLE IF NOT EXISTS `genre` (
  `genreid` int(11) NOT NULL auto_increment,
  `genrename` varchar(30) NOT NULL,
  PRIMARY KEY  (`genreid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='genre table' AUTO_INCREMENT=13 ;

Resource Table:

CREATE TABLE IF NOT EXISTS `resources` (
  `resourceid` int(11) NOT NULL auto_increment,
  `resourcename` varchar(30) NOT NULL,
  PRIMARY KEY  (`resourceid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='resource table' AUTO_INCREMENT=8 ;

genre lookup table: pitchid refs pitch.pitchid and genreid refs genre.genreid

CREATE TABLE IF NOT EXISTS `genrecategory` (
  `pitchid` int(11) NOT NULL,
  `genreid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pitchid`,`genreid`),
  KEY `genreid` (`genreid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='genre lookup table';

resource lookup table: pitch refs pitch.pitchid and resourceid refs resource.resourceid

CREATE TABLE IF NOT EXISTS `resourcecategory` (
  `pitchid` int(11) NOT NULL,
  `resourceid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pitchid`,`resourceid`),
  KEY `resourceid` (`resourceid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='resource lookup table';

And here is my php code for the form upload:

<?php 
session_start();

if(!isset($_SESSION['user'])) {

echo "You must login to see this page.  If you don't have an account, please sign up first ";
exit();

}


include 'dbconnect.php';?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Upload New Project</title>

		<link rel="stylesheet" type="text/css" href="main.css" /> 
</head>

<body>







<div id="wrapper">

<!-- Main div wrapper : used to control the layout of the entire page - i.e fixed width -->







<div id="header">
<!-- Text below here is the header for the webpage.  e.g - the banner etc. -->
<h1> website </h1>
    
    
    

            
</div>  <!-- end DIV for the header -->




<!-- Navigation section below here -->


<div id="navigation">

    <?php include("modules/navi.php"); ?>
</div>






<div id="content">


<div id="uploadproject">  <!-- DIV container to upload new project information -->  

<h2>Project Details</h2>



<form action="regproj.php" method="post">


  	<label for="projname">Project Name: </label>	<input type="text" id="projname" name="projectname" value="Last Tango on Mars" />
        

           <p>                   
                         
       		
      <label for="pitch"> Enter Pitch Here: </label>  <textarea name="pitch" id="pitch" cols="50" rows="8"</textarea> 
       

        
      </p> 		

<!-- php to grab the list of genre's and place them into the form as an array -->


<?php


// While the var gcats is being assigned the array details from the genre table
while($gcats = mysql_fetch_array($gcat)) {

//assign the var gc_id the array values of genre_id 
$gcid = $gcats['genreid'];
$gcname = htmlspecialchars($gcats['genrename']);

echo "<label for='genre'>  <input type='checkbox' id='genre' name='gcat[]' value='$gcid' /> $gcname </label>";

}




// While the var gcats is being assigned the array details from the genre table
while($rcats = mysql_fetch_array($rcat)) {

//assign the var gc_id the array values of genre_id 
$rcid = $rcats['resourceid'];
$rcname = htmlspecialchars($rcats['resourcename']);

echo "<label for='resource'> <input type='checkbox' id='resource' name='rcat[]' value='$rcid' /> $rcname </label>";

}


?>	


      
         
        

  
      
  <p><input type="submit" name="upldproj" value="upload!" /></p>

	</form>        
      




</div> <!-- closing DIV for upload projects container -->
        	



<!-- Final DIV for closing the content division. -->
</div>



<div id="clearfooter">



</div>



<div id="footer">

<!-- footer info and code goes here -->
	<ul>


<li><a href="about.php">About</a></li>			
<li><a href="contact.php">Contact</a></li>			
<li><a href="toc.php">TOC</a></li>			
<li><a href="code.php">Code</a></li>			
<li><a href="links.php">Links/references</a></li>		

    	
        </ul>



</div>



</div> <!-- Closing DIV for the wrapper -->


</body>
</html>

and finally, the php code for processing the form information :

<?php
session_start();

include 'dbconnect.php';


// if there is no projectname entered			
if(empty($_POST['projectname'])) {

		echo "Please enter a project name";
		exit();

}

// if no pitch information has been entered....
if(empty($_POST['pitch']))

{

		echo "Please enter a pitch";
		exit();

}



//if all has been entered, check to see if an existing pitchname exists	
 $pitchcheck = ("SELECT * FROM pitch WHERE pitchname='$_POST[pitch]'");

$result = mysql_query($pitchcheck, $dbconnect);
//if there is, tell them they cant pick that name
if(mysql_num_rows($result) == 1) {

echo "<p>Sorry, That pitch name is already taken! Please go back and choose another</p>";
exit();


}


//if not, then assign the post variables into normal vars
else {

$projectname = mysql_real_escape_string($_POST['projectname']);
$pitch		 = mysql_real_escape_string($_POST['pitch']);
$uid  = $_SESSION['id'];



}



//insert the pitch details

$registerproject = "INSERT INTO pitch SET

pitchname='$projectname',
pitch='$pitch',	
userid='$uid'";

if(@mysql_query($registerproject)) {

echo"<p>Pitch successfully added!</p>";

}

else {

echo "error inserting pitch" . mysql_error();

//echo "<p> sorry, your pitch could not be added at this time.  Please try again.</p>";
exit();

}

$pitchid = mysql_insert_id();

// if any of the genre's are selected then assign the value to the var
if(isset($_POST['gcat'])) {

$genrecats = $_POST['gcat'];

}
// otherwise, make the var an empty array
else {

$genrecats = array();

}


if(isset($_POST['rcat'])) {

$resourcecats = $_POST['rcat'];

}

else {

$resourcecats = array();

}



//insertion foreach loop

$numGenreCats = 0;

foreach($genrecats as $genrecatsID) {

$sql = "INSERT IGNORE INTO genrecategory SET

	pitchid = $pitchid, genreid = $genrecatsID";

$ok = @mysql_query($sql);
if ($ok) {

$numGenreCats = $numGenreCats + 1;

}

else {

echo " Error inserting genre into category $genrecatsID: . mysql_error()";
	exit();

}

}





$numResourceCats = 0;

foreach($resourcecats as $resourcecatsID) {

$sql = "INSERT IGNORE INTO resourcecategory SET

	pitchid = $pitchid, resourceid = $resourcecatsID";

$ok = @mysql_query($sql);
if ($ok) {

$numResourceCats = $numResourceCats + 1;

}

else {

echo " Error inserting genre into category $resourcecatsID: . mysql_error()";

}

}


?>

This is where it's failing and generating the error

//insert the pitch details

$registerproject = "INSERT INTO pitch SET

pitchname='$projectname',

pitch='$pitch',

userid='$uid'";

if(@mysql_query($registerproject)) {

echo"<p>Pitch successfully added!</p>";

}

else {

echo "error inserting pitch" . mysql_error();

//echo "<p> sorry, your pitch could not be added at this time. Please try again.</p>";

exit();

}

Sorry about the insanely long post, but after reading a lot of the other posts where people have had this error, i've noticed people don't usually post their code when asking, so I thought i'd just put everything in so that you wouldn't have to waste your time by asking me for the code. If anyone could shed some light on why it's not working, I'd really appreciate it, as I'd like to understand where I'm going wrong.

Thanks

cbear2021 · April 2, 2009

Also one thing I forgot - the casade options are set as on delete no action.

fenway · April 2, 2009

What errors do you get from mysql? I don't see any FKs in the table defs.. and you're supression errors.

cbear2021 · April 2, 2009

I removed the suppression of the errors - I don't know why phpmyadmin didn't exportthe foriegn key info into the mysql - sorry I didn't realise at the time, it was about 6am. This is the error I'm getting

error inserting pitchCannot add or update a child row: a foreign key constraint fails (`collab/pitch`, CONSTRAINT `pitch_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE)

I understand that this means there isn't a corresponding value in the parent table, but the thing is there is - I'm trying to insert the ID obtained through the use of mysql_fetch_array which is then assigned to the $_SESSION['id'] variable when the user successfully logs in into pitch.userid.

Here are the foriegn key details from mysql

Pitch table:

ADD CONSTRAINT `pitch_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE;

genrecategory (lookup table)

ADD CONSTRAINT `genrecategory_ibfk_1` FOREIGN KEY (`pitchid`) REFERENCES `pitch` (`pitchid`) ON DELETE NO ACTION,
  ADD CONSTRAINT `genrecategory_ibfk_2` FOREIGN KEY (`genreid`) REFERENCES `genre` (`genreid`) ON DELETE NO ACTION;

resourcecategory (lookup table)

ADD CONSTRAINT `resourcecategory_ibfk_1` FOREIGN KEY (`pitchid`) REFERENCES `pitch` (`pitchid`) ON DELETE NO ACTION,
  ADD CONSTRAINT `resourcecategory_ibfk_2` FOREIGN KEY (`resourceid`) REFERENCES `resources` (`resourceid`) ON DELETE NO ACTION;

Sorry they're not in with the code - I didn't notice phpmyadmin put these at the very bottom of the export page.

cbear2021 · April 2, 2009

okay - I've figured out whats wrong. I removed the foriegn key from the pitch table - and all the insertions work fine, the genre's etc are all inserted with no problems and put into the database. But why it's failing on the foriegn key constraint between the pitch table and the user table is the session variable which holds the user id which is set when they login isn't being passed on for some reason, so I cant get the data out of it to insert into the pitch table, and thats why it's complaining because it's blank and it doesn't match the record in the user table. I tried doing an echo "$uid" after the variable assignment and then an exit(); so it wouldn't run any further, and it didn't print anything to the screen, which means the variable is empty. Any suggestions?

cbear2021 · April 2, 2009

Update. The issue is fixed. Turns out - I'd made some typos in the login script where the session variables were being assigned. Because I'd renamed my tables because of using special char's, I'd forgotton I'd also renamed some of the columnames, mainly the userid table, which was still specified as user_id. It's all sorted now, and it works and does everything I wanted to. I'd still like to say thanks - because if it wasn't for going through all your tutorials and the other solved issues, I wouldn't have even thought to look at that. Thanks though! (and you have no idea how happy I am.) ^__________^

Sign In

[SOLVED] Cannot add or update a child row: a foriegn key constraint fails

Recommended Posts

cbear2021

Link to comment

Share on other sites

cbear2021

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

cbear2021

Link to comment

Share on other sites

cbear2021

Link to comment

Share on other sites

cbear2021

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information