Subquery returns more than 1 row on 2nd insert

PHP
1

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.

2

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

3

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

$fname=?
$lname=?

4

the exact same values that you used to insert the user

5

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

6

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.

7