use insert and select in the same query

Question

$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...

The reason might be that your subquery returns more than 1 row. — Paul Spiegel, Aug 13 '16 at 20:07
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Aug 13 '16 at 22:58

score 3 · Accepted Answer · answered Aug 13 '16 at 19:55

3

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.

answered Aug 13 '16 at 19:55

Gordon Linoff

1,242,037
58
646
786

you mean create a function of the query and call it with the variables as a parameters? – Omar Alhadidy Aug 13 '16 at 20:05
Aren't those variables already `parameterized`? – sgeddes Aug 13 '16 at 20:05
they are not @sgeddes – Omar Alhadidy Aug 13 '16 at 20:12
@OmarAlhadidy -- parameterized queries don't mean using a `function` and passing them... With that said, I don't use `php` very often so I wasn't sure. This looks right using `?` instead: http://stackoverflow.com/questions/728229/parameters-in-mysqli – sgeddes Aug 13 '16 at 20:16
thank you i will search a little bit more and correct them after that @sgeddes – Omar Alhadidy Aug 13 '16 at 20:23

score 1 · Answer 2 · answered Aug 13 '16 at 19:54

1

When using insert into select you don't need to use values. Remove that and then move the $new_id variable into the select statement:

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

answered Aug 13 '16 at 19:54

sgeddes

62,311
6
61
83

use insert and select in the same query

2 Answers2