Omar Alhadidy Omar Alhadidy - 3 months ago 12
SQL Question

use insert and select in the same query

$new_id = mysqli_insert_id($dbc)
foreach ($_POST['d_specialty'] as $key => $value) {
$q = "INSERT INTO d_specialty (d_id, s_id) VALUES ($new_id, (SELECT specialty.id FROM specialty WHERE specialty.name = $value))";
$r = mysqli_query($dbc,$q);


i want to insert two values, the first one is a variable and the second is a select statement but the code above does not work...

Answer

First, use insert . . . select:

INSERT INTO d_specialty (d_id, s_id)
    SELECT $new_id, specialty.id
    FROM specialty
    WHERE specialty.name = $value;

Second, parameterize the query so $new_id and $value are passed in as parameters rather than put directly into the query string. One reason is to prevent SQL injection attacks. Another very important reason is to guard against potential syntax errors.