Subquery returns more than 1 row on 2nd insert

[foulplay21]

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`:

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:

    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.

[r937]

the user table should have a UNIQUE index, presumably you want users to have unique names, so it should look something like this --

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 --

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

[foulplay21]

may I know what would be the value of $fname and $lname??
like:

$fname=?
$lname=?

[r937]

may I know what would be the value of $fname and $lname??

the exact same values that you used to insert the user

[r937]

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

[foulplay21]

By the way, you mentioned mysql_inser_id(), I already tried it and this is the code I did:

$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.