Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unhappy Unanswered: cannot update child row error

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Quote Originally Posted by it-iss.com View Post
    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.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •