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
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 tblItems VALUES (NULL, '".$checkbox[$i]."', (SELECT id FROM tblUser))"; 
        $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 result in the database in the next insert when user selected 3 items for example would be:

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

Any help would be appreciated.
Thanks in advance.

Recommended Answers

What about trying your query like this (I have removed the NULL values as they are not required for your id column)

if($_POST["Submit"]=="Submit"){ 
        mysql_query("INSERT INTO tblUser VALUES ('$fname', '$lname')"); 
        $new_id = mysql_insert_id();
        for ($i=0; $i<sizeof($checkbox);$i++){ 
        $sql2=mysql_query("INSERT INTO tblItems VALUES ('".$checkbox[$i]."', '$new_id')"); 
        } 
    }
Jump to Post

All 3 Replies

What about trying your query like this (I have removed the NULL values as they are not required for your id column)

if($_POST["Submit"]=="Submit"){ 
        mysql_query("INSERT INTO tblUser VALUES ('$fname', '$lname')"); 
        $new_id = mysql_insert_id();
        for ($i=0; $i<sizeof($checkbox);$i++){ 
        $sql2=mysql_query("INSERT INTO tblItems VALUES ('".$checkbox[$i]."', '$new_id')"); 
        } 
    }

Thank you so much! works just fine.
Keep it up.

No problem, glad I could help. Please mark as solved :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.