Combining two queries into a single query

Question

I have two queries and I want to combine them into one so that it only returns one row in my database.

I have tried UNION but I keep getting an error. Can anyone please advise me on the code for it?

Below are my queries:

if(isset($_POST["response"]))
{
    $query = "INSERT INTO response(response) VALUES (:response)";
    $statement = $conn->prepare($query);
    $statement->execute(
    array(
    ':response' => $_POST["response"]
    )
        );
    $query = " INSERT INTO response (student_id)
SELECT studentid
FROM student
WHERE studentid = '".$_SESSION['studentid']."'";
 $statement = $conn->prepare($query);
    $statement->execute(

        );

I see two inserts into two different tables, why would you want to combine those queries? What is the point? If I read the manual it says: "`INSERT` inserts new rows into an existing table.", one table, see: https://dev.mysql.com/doc/refman/5.7/en/insert.html See also this answer: https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — KIKO Software, Feb 08 '18 at 10:18
Possible duplicate of [MySQL Insert into multiple tables? (Database normalization?)](https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) — KIKO Software, Feb 08 '18 at 10:20
the studentid is a foreign key in the response table.... I want to do an insert into the response table! — nad, Feb 08 '18 at 10:21
@KIKOSoftware both the inserts appear to be into the same "response" table? — ADyson, Feb 08 '18 at 14:03
You have to insert the whole row at once, if you do two inserts with partial data you get two possibly malformed rows. There should be one insert statement executed if you want one row. — James, Feb 08 '18 at 14:38

ADyson · Accepted Answer · 2018-02-08T14:12:07.123

1

UNION is used for combining multiple SELECT queries into a single result set. Check the mySQL (or any generic ANSI SQL) documentation.

Anyway, for no apparent reason you are making two INSERT queries when it looks like you're inserting into the same table and presumably want to insert everything into the same row in the same table. Right now you will make 2 rows instead of 1. You can insert more than one field as part of a single query.

I'm thinking:

if(isset($_POST["response"]))
{
    $query = "INSERT INTO response (student_id, response) SELECT studentid, :response FROM student WHERE studentid = :studentID";
    $statement = $conn->prepare($query);
    $statement->execute(
      array(
        ':response' => $_POST["response"],
        ':studentID' => $_SESSION['studentid']
      )
   );
}

However, since you only require the studentID in the table, and you already have the studentID from the session, it seems pointless to select from the students table at all. The only exception might be if you need to verify that the value in the session is correct - but surely you have already verified it before you added it to the session? If you haven't, you certainly should.

So in fact simply

if(isset($_POST["response"]))
{
    $query = "INSERT INTO response (student_id, response) VALUES (:studentID, :response)";
    $statement = $conn->prepare($query);
    $statement->execute(
      array(
        ':response' => $_POST["response"],
        ':studentID' => $_SESSION['studentid']
      )
   );
}

should be sufficient.

edited Feb 08 '18 at 14:12

answered Feb 08 '18 at 14:06

ADyson

57,178
14
51
63

1

I chose the bottom answer! Thanks! Much appreciated! – nad Feb 08 '18 at 15:21
Another question, I also need to add the disciplineid to the table at the same time.... I have written this: $query = "INSERT INTO response (student_id, discipline_id, response) VALUES (:studentID, :disciplineID, :response)"; But i am getting an error saying undefined index: disciplineid. – nad Feb 08 '18 at 15:21
well did you add it to the parameter array? Have you got such a value available via a variable in your PHP? Just changing the SQL string on its own isn't the full story. I would have thought the pattern of how you need to define the parameters is fairly clear from the code examples above? – ADyson Feb 08 '18 at 15:31
Yes i have added it to the parameter array too and i have a variable aswell – nad Feb 08 '18 at 15:35
See https://stackoverflow.com/questions/4261133/php-notice-undefined-variable-notice-undefined-index-and-notice-undef . Probably the variable is not set when you try to access it. – ADyson Feb 08 '18 at 15:36

Combining two queries into a single query

1 Answers1