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

2
Contributors
3
Replies
4
Views
4 Years
Discussion Span
Last Post by simplypixie
0

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')"); 
        } 
    }
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.