cannot update child row error

Robert Freeman · #1 (**permalink**) 04-14-11, 12:18

I don't know why this isn't working.

MYSQL TABLES:

Code:

CREATE TABLE users( 
user_id	BIGINT(10) NOT NULL AUTO_INCREMENT,	
password VARCHAR(20) NOT NULL,	
date date NOT NULL,	
email VARCHAR(80) NOT NULL, 
PRIMARY KEY(user_id) 
); 

CREATE TABLE advert( 
user_id BIGINT(10) NOT NULL, 
ad_id BIGINT(10) NOT NULL AUTO_INCREMENT, 
email VARCHAR(80) NOT NULL, 
expiry_date DATE NOT NULL, 
item_desc VARCHAR(200) NOT NULL, 
PRIMARY KEY(ad_id), 
FOREIGN KEY(user_id) references users(user_id) NOT NULL ON UPDATE CASCADE ON DELETE CASCADE 
);

PHP CODE:

Code:

$lol="SELECT users.user_id 
FROM users 
INNER JOIN advert 
ON users.user_id = advert.user_id"; 

$sql="INSERT INTO advert (user_id, email,expiry_date, item_desc) VALUES('$lol','$_POST[email]','$_POST[expiry]','$_POST[itemdesc]')";

The only way I've got this to function without it saying 'cannot update/add child row'is if i manually enter the user_id in the values, but obviously that's useless. The tables seem to be linked okay because if i try entering a user_id into the adverts table that doesn't correspond to a user_id in the users table it throws the same 'cannot update child row' error.

Could anybody tell me where I'm going wrong?

Thanks in advance. Rob.

it-iss.com · #2 (**permalink**) 04-14-11, 13:20

Hi Rob,

If you want to insert with contents from a SELECT statement then use the following syntax:

Code:

INSERT INTO advert (user_id, email,expiry_date, item_desc)
SELECT users.user_id, '$_POST[email]','$_POST[expiry]','$_POST[itemdesc]'
FROM users 
INNER JOIN advert 
ON users.user_id = advert.user_id;

But are you sure about the entry/entries from the users. It looks like it will return quite a number of entries. Is this what you want?

Robert Freeman · #3 (**permalink**) 04-14-11, 13:40

Quote:

Originally Posted by it-iss.com

Hi Rob,

If you want to insert with contents from a SELECT statement then use the following syntax:

Code:

INSERT INTO advert (user_id, email,expiry_date, item_desc)
SELECT users.user_id, '$_POST[email]','$_POST[expiry]','$_POST[itemdesc]'
FROM users 
INNER JOIN advert 
ON users.user_id = advert.user_id;

But are you sure about the entry/entries from the users. It looks like it will return quite a number of entries. Is this what you want?

Hi,
Thanks for the quick reply.

And No I only wanted it to return one user_id for one ad_id (unless ofcourse someone has posted multiple adverts).

What would be the best way to do this?

Thanks again,
Rob.

it-iss.com · #4 (**permalink**) 04-15-11, 02:46

Hi Rob,

do you know the user_id of the person who is posting? With this you can limit the number of returned rows. In your original post you want to do the following:

$lol="SELECT users.user_id
FROM users
INNER JOIN advert
ON users.user_id = advert.user_id";

This will return all user_id's that have placed an advert. So you need to find a way to constrain your search for fewer rows. For instance, how do you know the current user who is posting an advert? Internally within your application they should have gone through either a registration process or a login process and you should be keeping the user_id somewhere. If this is the case then you do not need an INSERT with SELECT as you should already know the user_id.

You have to understand that we know very little about your project other than what you have posted. So I am guessing as to what you need.

Internet Services

Web Development

Digital Marketing

Consumer Tech