SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subquery returns more than 1 row on 2nd insert

    I have 2 tables:

    tblUser:


    +------+-----------+
    | id | Name |
    +------+-----------+

    tblItems(this table accepts multiple checkbox value depending on how many user selected):

    +------+-----------+---------------+
    | id | items | name_id |
    +------+-----------+---------------+

    `name_id` will get the value of id in `tblUser`.<br />
    I use this code to get the value of id of `tblUser` to `name_id`:

    PHP Code:
    for ($i=0$i<sizeof($checkbox);$i++){
            
    $sql2="INSERT INTO tbl_trainings VALUES (NULL, '".$checkbox[$i]."', (SELECT id FROM tbl_info))";
            
    $result2=mysql_query($sql2);
         } 

    It works fine on the first `INSERT` of data that will look like this in database:

    +------+-----------+---------------+
    | id | items | name_id |
    +------+-----------+---------------+
    | 1 | Bucket | 1 |
    +------+-----------+---------------+
    | 2 | Tree | 1 |
    +------+-----------+---------------+
    | 3 | House | 1 |
    +------+-----------+---------------+

    But in the next or second `INSERT` of data will be an error. The error is

    **Subquery returns more than 1 row** from the `mysql_error();`

    By the way, this is the full codes:

    PHP Code:
        if($_POST["Submit"]=="Submit"){
            
    $sql1="INSERT INTO tblUser VALUES (NULL, '$fname', '$lname')";
            
    $result1=mysql_query($sql1);
            for (
    $i=0$i<sizeof($checkbox);$i++){
            
    $sql2="INSERT INTO tblItems VALUES (NULL, '".$checkbox[$i]."', (SELECT id FROM tblUser))";
                
    $result2=mysql_query($sql2);
            }
        }

        if(
    $result2 && result1){
            echo
    "<center>";
            echo
    "<h1>";
            echo 
    "SUCCESSFUL!";
            echo
    "</h1>";
            echo
    "</center>";
        }
        else {
            echo 
    "ERROR"mysql_error();
        } 
    And the desired output in the database would be:

    +------+-----------+---------------+
    | id | items | name_id |
    +------+-----------+---------------+
    | 1 | Bucket | 1 |
    +------+-----------+---------------+
    | 2 | Tree | 1 |
    +------+-----------+---------------+
    | 3 | House | 1 |
    +------+-----------+---------------+
    | 4 | Tree | 2 |
    +------+-----------+---------------+
    | 5 | Air plane | 2 |
    +------+-----------+---------------+
    | 6 | Bucket | 3 |
    +------+-----------+---------------+


    Any help would be appreciated.
    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the user table should have a UNIQUE index, presumably you want users to have unique names, so it should look something like this --
    Code:
    CREATE TABLE users
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , fname VARCHAR(37) NOT NULL
    , lname VARCHAR(37) NOT NULL
    , UNIQUE username ( fname , lname )
    );
    (notice i called the table "users" -- i abhor putting "tbl" into the table name)

    so then when you do your insert into the items table, you can retrieve the user's id by name --
    Code:
    INSERT 
      INTO items 
    SELECT NULL
         , '".$checkbox[$i]."'
         , id 
      FROM users
     WHERE fname = '".$fname."'
       AND lname = '".$lname."';"
    notice it's INSERT SELECT, not INSERT VALUES with a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    may I know what would be the value of $fname and $lname??
    like:

    $fname=?
    $lname=?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by foulplay21 View Post
    may I know what would be the value of $fname and $lname??
    the exact same values that you used to insert the user
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    alternatively, since you are going to insert multiple items per user, rather than retriev the user's id each time, you might instead use the mysql_insert_id function right after inserting the user, to obtain and save the user's id, in which case you could then use INSERT with VALUES (and no subselect) repetitively
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way, you mentioned mysql_inser_id(), I already tried it and this is the code I did:

    PHP Code:
    $user_id mysql_insert_id();
    for (
    $i=0$i<sizeof($checkbox);$i++){
        
    $sql2="INSERT INTO tblItems VALUES (NULL, '".$checkbox[$i]."', $user_id)";
        
    $result2=mysql_query($sql2);

    But for example, the user choose 5 checkbox values, the result in database in name_id will be: 1 1 2 3 4 instead of 1 1 1 1 1.
    On the second insert for example the user choose 3 checkbox again, the result will be : 1 1 2 2 3 4 5 6 7.


Tags for this Thread

Bookmarks

Posting Permissions

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