INSERT ... SELECT(insert many rows)

Question

i want to insert the scholar's id to the tblinbox. Here is my query:

    $sql = "INSERT INTO tblinbox VALUES ('','$sender','$type','$subject','$LRN','$content','$date', '$newyearLevel','','$userType','THIS_IS_FOR_THE_ID_OF_THE_SCHOLAR')
        SELECT id FROM tblscholar WHERE schoYear = '$newyearLevel'";

my problem is,it is not inserting. what will i change in my query?

Mike Brant · Accepted Answer · 2014-05-08T17:20:10.477

INSERT ... SELECT syntax does not allow for VALUES declaration. The values ARE the results returned from the SELECT.

See the documentation here: http://dev.mysql.com/doc/refman/5.6/en/insert-select.html

I honestly am not fully sure what you are trying to do with your insert. If you are trying to insert the same values held in your variables for each id value from the tblscholar table then perhaps you need to do something like this:

INSERT INTO tblinbox
/* 
   maybe add column definitions here to make it clearer
   column definitions could look like this:
    (
    someField,
    type,
    subject,
    LRN,
    content,
    `date`,
    newyearLevel,
    someOtherField,
    userType,
    id
    )
*/
SELECT
    '',
    '$sender',
    '$type',
    '$subject',
    '$LRN',
    '$content',
    '$date',
    '$newyearLevel',
    '',
    '$userType',
    id
FROM tblscholar
WHERE schoYear = '$newyearLevel'

@LeRandomGirl OK. I edited my answer to move id value to last column to reflect your most recent edit. I would still suggest using column name definitions in your query unless you are 100% sure the columns in the `tblinbox` table will ALWAYS match the columns created in the `SELECT` statement in order. — Mike Brant, May 08 '14 at 17:19

Thiago França · Answer 2 · 2014-05-08T17:20:26.693

0

considering id is the first column in your insert statement, try this

$sql = "INSERT INTO tblinbox VALUES ((SELECT id FROM tblscholar WHERE schoYear = '$newyearLevel'),'$sender','$type','$subject','$LRN','$content','$date', '$newyearLevel','','$userType')";

edited May 08 '14 at 17:20

answered May 08 '14 at 17:13

Thiago França

1,817
1
15
20

Parse error: syntax error, unexpected ')' in C:\xampp\htdocs\SS\msg_postCompose.php on line 29 – LeRandomGirl May 08 '14 at 17:19
@LeRandomGirl sorry for the syntax error... try again with corrected answer. – Thiago França May 08 '14 at 17:21
@LeRandomGirl what you mean it's not inputting? – Thiago França May 08 '14 at 17:29

score 0 · Answer 3 · answered May 08 '14 at 17:13

An INSERT statement supports either a VALUES clause followed by a row of values, or else a SELECT query with columns to match the columns of the table you want to insert into.

But not both!

But you can add constant values into your SELECT query:

$sql = "INSERT INTO tblinbox 
    SELECT '','$sender','$type','$subject','$LRN','$content','$date',
        '$newyearLevel','','$userType', id 
    FROM tblscholar WHERE schoYear = '$newyearLevel'";

score 0 · Answer 4 · answered Jun 24 '14 at 13:19

You can insert values either fetching values form another table or providing values as follows:

Way 1: INSERT INTO tblinbox(coloumn_name1,coloumn_name2) VALUES (value1,value2);

Way 2: INSERT INTO tblinbox(coloumn_name1,coloumn_name2) SELECT value1,value2 from tblscholer where schoYear= '$newyearLevel';

INSERT ... SELECT(insert many rows)

4 Answers4

Linked

Related