Query fails when i try to insert data.

[Rizzuti]

Hello! I’ve got three tables that I want to link together. When I try to upload images and text throught a form, the web browser show this error message:

Error, query failed 1452-Cannot add or update a child row: a foreign key constraint fails (`mn000532_almacen`.`images`, CONSTRAINT `images_ibfk_1` FOREIGN KEY (`itemID`) REFERENCES `items` (`itemID`) ON DELETE CASCADE)

The itemID field is used in my php script($_GET["itemID"]) to retrieve the all the info about a certain item therefore it needs to be the same in all tables.

CREATE TABLE categories (
categoryID INT(5) NOT NULL,
cat_Description VARCHAR(50),
PRIMARY KEY (itemID)
)TYPE = INNODB;

CREATE TABLE items (
itemID INT(5) NOT NULL AUTO_INCREMENT,
categoryID INT(5) NOT NULL,
itemName CHAR(25) NOT NULL,
item_Description VARCHAR(255),
price CHAR(10),
contactName VARCHAR(50),
phone CHAR(15),
email VARCHAR(50),
website CHAR (25),
submitDate DATE NOT NULL,
expireDate DATE NOT NULL,
PRIMARY KEY(itemID,categoryID),
INDEX (submitDate),
FOREIGN KEY (categoryID) REFERENCES categories (categoryID)
ON DELETE CASCADE
)TYPE = INNODB;

CREATE TABLE images (
imagesID INT (5) NOT NULL AUTO_INCREMENT,
itemID INT (5) NOT NULL,
name VARCHAR (30) NOT NULL,
size INT (11) NOT NULL,
type VARCHAR (30) NOT NULL,
pix MEDIUMBLOB NOT NULL,
PRIMARY KEY(imagesID),
FOREIGN KEY (itemID) REFERENCES items (itemID)
ON DELETE CASCADE
)TYPE = INNODB;

Thank you

Hernan

[smantscheff]

Please show us your script to determine what is going wrong.
And your first "CREATE TABLE categories" statement cannot be correct. It references an index field (itemID) which is not in the table. Please submit the table structure as shown by "SHOW CREATE TABLE categories" and not your edited version.

[Rizzuti]

apologises! let start again,
Here is the error:

Error, query failed 1452-Cannot add or update a child row: a foreign key constraint fails (`mn000532_almacen`.`images`, CONSTRAINT `images_ibfk_1` FOREIGN KEY (`itemID`) REFERENCES `items` (`itemID`) ON DELETE CASCADE)

Here are the table:

CREATE TABLE `categories` (
 `CategoryID` int(5) NOT NULL DEFAULT '0',
 `cat_Description` varchar(50) NOT NULL,
 PRIMARY KEY (`CategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `items` (
 `itemID` int(5) NOT NULL AUTO_INCREMENT,
 `categoryID` int(5) NOT NULL,
 `itemName` char(25) NOT NULL,
 `item_Description` varchar(255) DEFAULT NULL,
 `price` char(10) DEFAULT NULL,
 `contactName` varchar(50) DEFAULT NULL,
 `phone` char(15) DEFAULT NULL,
 `email` varchar(50) DEFAULT NULL,
 `website` char(25) NOT NULL,
 `submitDate` date NOT NULL,
 `expireDate` date NOT NULL,
 PRIMARY KEY (`itemID`,`categoryID`),
 KEY `submitDate` (`submitDate`),
 KEY `categoryID` (`categoryID`),
 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`categoryID`) REFERENCES `categories` (`CategoryID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `images` (
 `imageID` int(5) NOT NULL AUTO_INCREMENT,
 `itemID` int(5) NOT NULL,
 `name` varchar(30) NOT NULL,
 `size` int(11) NOT NULL,
 `type` varchar(30) NOT NULL,
 `pix` mediumblob NOT NULL,
 PRIMARY KEY (`imageID`),
 KEY `itemID` (`itemID`),
 CONSTRAINT `images_ibfk_1` FOREIGN KEY (`itemID`) REFERENCES `items` (`itemID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here are the script i use to upload the images and text
form: postad.php

<form action = "upload.php" method = "POST" enctype="multipart/form-data">
<select name="CID" size="1">
<option selected value="">Select Category</option>
<?php
while ($cat = mysql_fetch_array($cats)) {
$CID = $cat['categoryID'];
$catDescription = htmlspecialchars($cat['cat_Description']);
echo("<option value='$CID'>$catDescription</option>\n");
}
?>
</select><br /> <br />
<p>
<b>Title of ad: </b><input type="text" name="itemName" id="itemName"/><br /><br />
<b>Description: </b><br />
<textarea name="item_Description" id="item_Description" rows="10" cols="40" wrap>
</textarea><br/><br/>
<b>Price: </b><input type="text" name="price" id="price"/><br />
<input type="hidden" name="itemID" /><br /><br />
<b>Contact Name: </b><input type="text" name="contactName" id="contactName"/><br /><br />
<b>Phone Number: </b><input type="text" name="phone" id="phone"/><br /><br />
<b>Email Address: </b><input type="text" name="email" id="email"/><br /><br />
<b>Website: </b><input type="text" name="website" id="website"/><br /><br />
<b>Upload image: </b>
<input type="hidden" name="MAX_FILE_SIZE" value="2000000"><br />
<input type="hidden" name="imageID" /><br /><br />
<input type="file" name="userfile" id="userfile" /><br /><br />
<input type="submit" name="submit" id="submit" class="box" value="SUBMIT AD">
<input type="submit" name="goBack" value="Cancel">
</form>

this is php and sql script to upload the images and text: upload.php

<?php
include("misc.inc");

$db = 0;
$db = mysql_connect($db_host . ":" . $db_port, $db_user, $db_pwd);
if ($db == 0) {die("Cannot connect, Error <b>" . mysql_errno() . "</b>: " . mysql_error());};

mysql_query("USE " . $db_db);
if (mysql_errno() != 0) {die("Cannot USE database, Error <b>" . mysql_errno() . "</b>: " . mysql_error());};

if (@$_POST['goBack'] == "Cancel")
{
	header("Location: index.php");
}	


	if ($_POST['submit']) {
$itemID = $_POST ['itemID'];	
$itemName = $_POST ['itemName'];
$CID = $_POST ['CID'];
$item_Description = $_POST ['item_Description'];
$price = $_POST ['price'];
$contactName = $_POST ['contactName'];
$phone = $_POST ['phone'];
$email = $_POST ['email'];
$pix = $_POST ['pix'];
$website =$_POST ['website'];

$sql = "INSERT INTO items SET
		itemID = '$itemID',
		itemName = '$itemName',
		categoryID = '$CID',
		item_Description = '$item_Description',
		price = '$price',
		contactName = '$contactName',
		phone = '$phone',
		email = '$email',
		website ='$website',
		submitDate = CURDATE()";

if (@mysql_query ($sql)) {
	  echo('<p>Thank you for your posting. If you wish your item or other items go to Classifieds</p>');
	  } 
	else {
	  echo('<p>Error adding submitted ad: ' .mysql_errno().'-'.
            mysql_error() . '</p>');
	  }
	}
	  echo "Title Of Ad: " .$itemName. "<br />\n";
	  echo "Description: " .$item_Description. "<br />\n";
      echo "Price: " .$price. "<br />\n";
      echo "Contact Name: " .$contactName. "<br />\n";
      echo "Email: ".$email. "<br />\n";
      echo "picture: ".$pix. "<br />\n";
    
	  echo('<p><a href="postad.php"> Post another ad!</a></p>');
	  echo ('<p><a href="Classifieds.php"> Classifieds</a></p>');
	  echo ('<p><a href="home.php"> Home</a></p>'
		);
$sql1 = "INSERT INTO images SET
		 
		 itemID = '$itemID',
		 categoryID = '$CID',
		 pix = '$pix'";

	if (@mysql_query ($sql1)) {
	  echo('');
	  } 
	else {
	  echo('<p>Error adding submitted ad: ' .mysql_errno().'-'.
            mysql_error() . '</p>');
	  }	
?>

Thanks a lot
Hernan

[smantscheff]

This cannot be the whole story. The error message starts with "Error, query failed" which is not a mysql error message and which I do not find in the code. Seems that you have edited the script again before posting.
The error is that the hidden input field named itemID does not have a value. You might have seen it if you had a debug output of the insert query which you are trying to submit.

[Rizzuti]

This is the upload.php, which I editted last...I'm really sorry i dont want to waste your time, but i've got some many scripts that sometime i get confused... i'm new in php and therefore i've been editing them for a long time due to so many errors!!

I really appreciate your help smantscheff, I'll make sure the script i post are the right ones!!!

upload.php

<?php
 
include("misc.inc");

$db = 0;
$db = mysql_connect($db_host . ":" . $db_port, $db_user, $db_pwd);
if ($db == 0) {die("Cannot connect, Error <b>" . mysql_errno() . "</b>: " . mysql_error());};

mysql_query("USE " . $db_db);
if (mysql_errno() != 0) {die("Cannot USE database, Error <b>" . mysql_errno() . "</b>: " . mysql_error());};

if (@$_POST['goBack'] == "Cancel")
{
	header("Location: index.php");
}

if(isset($_POST['submit']) && $_FILES['userfile']['size'] > 0)
{
$itemID = $_POST ['itemID'];
$imageID =$_POST ['imageID'];	
$itemName = $_POST ['itemName'];
$CID = $_POST ['CID'];
$item_Description = $_POST ['item_Description'];
$price = $_POST ['price'];
$contactName = $_POST ['contactName'];
$phone = $_POST ['phone'];
$email = $_POST ['email'];
$website =$_POST ['website'];

$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp      = fopen($tmpName, 'r');
$pix = fread($fp, filesize($tmpName));
$pix = addslashes($pix);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}

$sql = "INSERT INTO items SET
		itemID = '$itemID',
		itemName = '$itemName',
		categoryID = '$CID',
		item_Description = '$item_Description',
		price = '$price',
		contactName = '$contactName',
		phone = '$phone',
		email = '$email',
		website ='$website',
		submitDate = CURDATE()";

if (@mysql_query ($sql)) {
	  echo('<p>Thank you for your posting. If you wish your item or other items go to Classifieds</p>');
	  } 
	else {
	  echo('<p>Error adding submitted ad: ' .mysql_errno().'-'.
            mysql_error() . '</p>');
	  }
	}
    
	  echo('<p><a href="postad.php"> Post another ad!</a></p>');
	  echo ('<p><a href="Classifieds.php"> Classifieds</a></p>');
	  echo ('<p><a href="home.php"> Home</a></p>'
		);

$sql1 = "INSERT INTO images (itemID, name, size, type, pix) ".
"VALUES ('$itemID', '$fileName', '$fileSize', '$fileType', '$pix')";

mysql_query($sql1) or die('<p>Error, query failed ' .mysql_errno().'-'.mysql_error() .'</p>');

echo "<br>File $fileName uploaded<br>";
?>

Thanks a lot
Hernan

[smantscheff]

As I said before, the problem is a null value for itemID in postad.php: <input type="hidden" name="itemID" /> The value="..." attribute is missing in the input tag.

[Rizzuti]

I added the value $itemID but it's still giving me the same error. So I think there is something wrong with the relationship between the tables!!!

Anyway, I'll carry on looking. thanks for your help

[smantscheff]

Let's see how you did it.

[Rizzuti]

<input type="hidden" name="itemID" value="<?=$itemID?>"/><br /><br />

I removed the foreign key from the image table (`images_ibfk_1`) and it is not giving me this error anymore. However, the itemID in the items table and the itemID in images table doesn't much. I wonder if there is a way of moving the itemID from items to images without linking these two tables.

[smantscheff]

I never saw this php syntax before. Maybe you try value= "<?php echo $itemID; ?>" instead.
And if removing the foreign key constraint removes your problem, than you have a problem, because the foreign key constraint makes a lot of sense. You would not have any orphan records, would you?
And, as I said before, debug the content of your $sql variable before you submit it to mysql. I'm pretty sure there is no value for itemID in it.

[Rizzuti]

Sorry i haven't get back any earlier, it working now!!!

<?php
 
include("misc.inc");

$db = 0;
$db = mysql_connect($db_host . ":" . $db_port, $db_user, $db_pwd);
if ($db == 0) {die("Cannot connect, Error <b>" . mysql_errno() . "</b>: " . mysql_error());};

mysql_query("USE " . $db_db);
if (mysql_errno() != 0) {die("Cannot USE database, Error <b>" . mysql_errno() . "</b>: " . mysql_error());};

if (@$_POST['goBack'] == "Cancel")
{
	header("Location: index.php");
}

if(isset($_POST['submit']) && $_FILES['userfile']['size'] > 0)
{
$itemName = $_POST ['itemName'];
$CID = $_POST ['CID'];
$item_Description = $_POST ['item_Description'];
$price = $_POST ['price'];
$contactName = $_POST ['contactName'];
$phone = $_POST ['phone'];
$email = $_POST ['email'];
$website =$_POST ['website'];

$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp      = fopen($tmpName, 'r');
$pix = fread($fp, filesize($tmpName));
$pix = addslashes($pix);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}
$sql = mysql_query("INSERT INTO items (itemName, categoryID, item_Description, price, contactName, phone, email, website, submitDate)".
		"VALUES ('$itemName', '$CID', '$item_Description', '$price', '$contactName', '$phone', '$email', '$website', CURDATE())");


if (@mysql_query ($sql)) {
	  echo('<p>Thank you for your posting. If you wish your item or other items go to Classifieds</p>');
	  } 
	else {
	  echo('<p>Error adding submitted ad: ' .mysql_errno().'-'.
            mysql_error() . '</p>');
	  }
	}
    
	  echo('<p><a href="postad.php"> Post another ad!</a></p>');
	  echo ('<p><a href="Classifieds.php"> Classifieds</a></p>');
	  echo ('<p><a href="index.php"> Home</a></p>'
		);

$lastID = mysql_insert_id();

$sql1 = "INSERT INTO images (itemID, categoryID, name, size, type, pix) 
"." VALUES ('$lastID', '$CID', '$fileName', '$fileSize', '$fileType', '$pix')";

mysql_query($sql1) or die('<p>Error, query failed ' .mysql_errno().'-'.mysql_error() .'</p>');

echo "<br>File $fileName uploaded<br>";
?>

I removed itemID and imageID from the queries as the were auto increment and also added a function called "mysql_insert_id", which retrieves the itemID from items and insert it into images as the itemID=$lastID.

Thanks
Hernan