Insert multiple Insert statements into one table row

Question

I think it should be possible, but i dont have a clue how to write it.

1st statement: Select multiple data from table1 and copy them to table2.
2nd statement: Save a variable to table2.

both statements should be saved in the same table row.

here is my code:

foreach($_POST["checkbox"] as $id){

            $query ="INSERT INTO table2 (name, number, price, quantity, sumPrice, supplier, customer)
                    SELECT name, number, price, quantity, sumPrice, supplier, customer
                    FROM table1
                    WHERE table1_id = '".$id."'";

                    "INSERT INTO table2(adoptedBy)
                    VALUES ('$name')";

            $result = mysqli_query($db_link, $query);

what did i wrong?

`INSERT INTO table2 SELECT name, number, price, quantity, sumPrice, supplier, customer FROM table1 WHERE table1_id=` — Masivuye Cokile, Apr 12 '17 at 09:01
@Hienz if that `$name` is null and the db is set not to be null for adobtedby will cause problems — Masivuye Cokile, Apr 12 '17 at 09:30
@Yosshi you have answers below please examine them and comment on them — Masivuye Cokile, Apr 12 '17 at 09:30
@Masivuye Cokile $name ist the username from the actual session. it shouldnt be null — Yosshi, Apr 12 '17 at 09:33

Hienz · Answer 1 · 2017-04-12T09:05:57.187

0

Meh.. If you need explanation, there you go. You mentioned, that you want to insert a single row and then update it. Since the INSERT statement is inserting a new row to the table (self-explained..), you need to use an UPDATE statement, which updates a specific row with new values.

So you'll first insert a new row with INSERT and then update the 'adoptedBy' field with your new value.

Example:

"UPDATE table2 SET adoptedBy='$name' WHERE --Here you refer to the row's primary id";

After the WHERE clause you just identify somehow your row (e.g. primary ID, which is not shown in the OP)

edited Apr 12 '17 at 09:05

answered Apr 12 '17 at 08:53

Hienz

688
7
21

2

An example would be better – Carl Binalla Apr 12 '17 at 08:53
1

or a comment if u don't have an example – Masivuye Cokile Apr 12 '17 at 08:57
you can not do that using single query – Shafiqul Islam Apr 12 '17 at 08:58
shouldn't this be a comment? – Rotimi Apr 12 '17 at 08:59
Give him a chance to represent him @Hienz your answer should be descriptive with proper explanations and example. – Shashank Shah Apr 12 '17 at 09:01
The OP used SQL properly, he just probably didn't think of the UPDATE statement, that's why I didn't explain it that much. But as u wish guys, there you go, hope it's fine. – Hienz Apr 12 '17 at 09:10
ok, thanks a lot first, yeah an update makes sense. but how do i identify the row? the table id is named "id" with auto increment. – Yosshi Apr 12 '17 at 09:15

score 0 · Accepted Answer · edited May 23 '17 at 11:46

0

1st statement: Select multiple data from table1 and copy them to table2.

You will need to use INSERT INTO SELECT which you did well.

$query ="INSERT INTO table2 (name, `number`, price, quantity, sumPrice, supplier, customer)
                    SELECT name, `number`, price, quantity, sumPrice, supplier, customer
                    FROM table1
                    WHERE table1_id = '".$id."'";

2nd statement: Save a variable to table2.

One thing you did not notice is that after WHERE table1_id = '".$id."'"; you terminated $query therefore this never runs `

"INSERT INTO table2(adoptedBy)
                    VALUES ('$name')";

what you can do is to use mysqli_multi_query();

Then you query will be something like :

 <?php

    $query = "INSERT INTO table2 (name, `number`, price, quantity, sumPrice, supplier, customer)
                    SELECT name, `number`, price, quantity, sumPrice, supplier, customer
                    FROM table1
                    WHERE table1_id = '" . $id . "';";

    $query .= "UPDATE table2 SET adoptedBy =".$name."  where id =  LAST_INSERT_ID()";

    $result = mysqli_multi_query($db_link, $query);


    if ($result) {

        echo "success";
    } else {

        echo "Error : " . mysqli_error($db_link);
    }

?>

Hope this helps.

NB: I advice that you start learning doing sql queries using prepared statements whether you are using mysqli prepared or pdo prepared statements

Edit :

Alternative when the above suggestion does not work,

You may update the LAST_INSERT_ID() when the first query run successfully get that last inserted id then do an update on that id

<?php


    $query = "INSERT INTO table2 (name, `number`, price, quantity, sumPrice, supplier, customer)
                    SELECT name, `number`, price, quantity, sumPrice, supplier, customer
                    FROM table1
                    WHERE table1_id = '" . $id . "';";
    $result = mysqli_query($db_link, $query);


    if ($result) {

        $query = "UPDATE table2 SET adoptedBy ='".$name."' WHERE id = LAST_INSERT_ID();";
        if(mysqli_query($db_link,$query)){

            echo "success";
        }else{

            echo "update failed".mysqli_error($db_link);
        }
    } else {

        echo "Error : " . mysqli_error($db_link);
    }

?>

edited May 23 '17 at 11:46

Community

1
1

answered Apr 12 '17 at 09:21

Masivuye Cokile

4,754
3
19
34

doesnt this just overwrite the first query? – Yosshi Apr 12 '17 at 09:31
No there's a concatenation sign for `$query` in your case the second statement never execute this solves that – Masivuye Cokile Apr 12 '17 at 09:33
yea i get an error, but a blank one without any info – Yosshi Apr 12 '17 at 09:59
what is the error that you are getting? That is impossible how can an error be blank, check server log and enable error reporting – Masivuye Cokile Apr 12 '17 at 09:59
the error from your if statement is blank. Errorr reporting is on E_ALL and the last entry from the xampp error log is from 09:14 am. – Yosshi Apr 12 '17 at 10:08
add this `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` at the top before connection.. then tell m what u get – Masivuye Cokile Apr 12 '17 at 10:13
edit: ok maybe i have some trouble with a second query. got an sql syntax error after commenting the 2nd one!. $query2 ="DELETE FROM table1 WHERE id = '".$id."'"; $result2 = mysqli_query($db_link, $query2); – Yosshi Apr 12 '17 at 10:15
You did not have any delete and `$query2` on your OP – Masivuye Cokile Apr 12 '17 at 10:17
Heres the error: Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE table2 SET adoptedBy =testwhere id = LAST_INSER' at line 4 – Yosshi Apr 12 '17 at 10:19
And yea, didnt thought that this would be neccessary – Yosshi Apr 12 '17 at 10:20
`adoptedBy =testwhere id` there must be a space between where an test – Masivuye Cokile Apr 12 '17 at 10:21
Edited my answer check and run again – Masivuye Cokile Apr 12 '17 at 10:22
@Yosshi please see the revised edit tested and works like a charm – Masivuye Cokile Apr 12 '17 at 10:58

Insert multiple Insert statements into one table row

2 Answers2