如何在一个查询中执行插入？

Question

Frame: MySQL server on localhost using XAMPP server.

I'm trying to insert all these fields in a particular table "tbl_labelled_images":

1. id_in_raw_image_table
2. image
3. label
4. gender

Out of them, image comes from another table named as "tbl_raw_image" and id_in_raw_image_table is the id of that image in table "tbl_raw_image".

Then label and gender are the inputs by the user taken on run time using radio buttons. But the problem is I cannot insert all these four attributes in a single query. Query just does not insert anything in the table if all of them are in the same query. I have tried following queries but none of them actually helped:

$label=mysqli_real_escape_string($conn, $_POST["radio"]);
$gender=mysqli_real_escape_string($conn,$_POST["radio-gender"]);
$query_insert="INSERT INTO labelled_images.tbl_labelled_image
               (`label`,`gender`,`image`, `id_in_raw_image_table`) '$label','$gender',
               SELECT  image, id FROM raw_images.tbl_raw_image
               WHERE raw_images.tbl_raw_image.id = $id_raw";
$insert_exec = mysqli_query($conn, $query_insert);

And

$sql = "SELECT * FROM tbl_raw_image WHERE id IN
    (SELECT id FROM (SELECT id FROM tbl_raw_image ORDER BY RAND() LIMIT 1) t)";
$sth = $conn1->query($sql);
$result=mysqli_fetch_array($sth);
$id=$result['id'];
$image=$result['image'];
$label=mysqli_real_escape_string($conn, $_POST["radio"]);
$gender=mysqli_real_escape_string($conn,$_POST["radio-gender"]);
$query2="INSERT INTO tbl_labelled_image(image, label, id_in_raw_image_table) VALUES('$image','$label','$id'); ";
$rs  = mysqli_query($conn2, $query2);

But none of them worked for me. As a work around I'm currently using a complex two step insertion as follows:

$sql = "SELECT * FROM tbl_raw_image WHERE id IN
    (SELECT id FROM (SELECT id FROM tbl_raw_image ORDER BY RAND() LIMIT 1) t)";
$sth = $conn1->query($sql);
$result=mysqli_fetch_array($sth);
$id=$result['id'];
$image=$result['image'];
$label=mysqli_real_escape_string($conn, $_POST["radio"]);
$gender=mysqli_real_escape_string($conn,$_POST["radio-gender"]);
$query_insert="INSERT INTO labelled_images.tbl_labelled_image
               (`image`, `id_in_raw_image_table`)
               SELECT  image, id FROM raw_images.tbl_raw_image
               WHERE raw_images.tbl_raw_image.id = $id_raw;";
$insert_exec = mysqli_query($conn, $query_insert);
$labelled_id=mysqli_insert_id($conn);
$query_label = "UPDATE labelled_images.tbl_labelled_image SET
               `label` = '$label', `gender` = '$gender' WHERE `id`=$labelled_id";
$label_insert_exec = mysqli_query($conn, $query_label);
$query_update_Is_labelled="UPDATE raw_images.tbl_raw_image SET `Is_labelled`= 1 WHERE id= $id_raw; ";
$update=mysqli_query($conn,$query_update_Is_labelled);

It works but it is far from ideal. So my question is that is there any way to perform this insert in one step? Or more generally what should be done when different fields of record to be inserted are from different sources?

Answer 1

You can try to use a static value in your MySQL select query SELECT '$label' as label,'$gender' as gender

complete query:

$query_insert="INSERT INTO labelled_images.tbl_labelled_image
    (`label`,`gender`,`image`, `id_in_raw_image_table`)
        SELECT '$label' as label,'$gender' as gender, image, id FROM raw_images.tbl_raw_image
        WHERE raw_images.tbl_raw_image.id = $id_raw
    ";

Edited: remove the VALUES() before the SELECT