0

I have a table (users) that looks like this:

ID |    CODE   | Name
---+----------+---------
 1 |    00A    | Tom
 2 |    12E    | Steve
 3 |    A4B    | Maria

Now I have other table to insert data, and I need to insert user_id. The thing is I receive only the user code from a document. I have to search in the users table the id that corresponds to that code.

I imagine I have to do something like this:

$code = 123 

$query="select from users where id=$code"

$user_id = $query

$sql = "insert into table values ($user_id)"

I know the SQL/PHP code is not complete, I'm just looking for the correct LOGIC to this. Maybe there's a way to search and insert at the same query.

Ivar
  • 6,138
  • 12
  • 49
  • 61
emy
  • 7
  • 2
  • 2
    `insert into table values (select user_id from users where id=$code)` – bassxzero Nov 06 '17 at 16:11
  • 1
    Have you tried anything? – Wagner Danda da Silva Filho Nov 06 '17 at 16:11
  • 1
    @bassxzero that (and OP's post) would leave the code vulnerable to SQL injection. Isabella, I would look at a post like this (https://stackoverflow.com/questions/6379433/mysql-prepared-statements) to ensure you're not leaving your system vulnerable – kchason Nov 06 '17 at 16:13
  • @kchason ...or in this case (since it's just an id), make sure that `$code` is cast as an integer with either `$code = (int) $code;` or `$code = intval($code);`. – M. Eriksson Nov 06 '17 at 16:18
  • Agreed, but it's still better to encourage best practices except in cases where parameter binding isn't possible (column/table names etc). – kchason Nov 06 '17 at 16:22
  • The general format for an INSERT is `INSERT INTO `table` (`column1`) VALUES ($column1);` so you'd want to follow that – kchason Nov 06 '17 at 16:37

3 Answers3

0

You can do that with one query, But you have to use PDO or somethign similar to avoid SQL injection

Insert Into table
Select user_id from users
WHERE id = $code;

Check this out about How Can I Prevent Sql Injection In PHP

AnouarZ
  • 1,087
  • 8
  • 23
0

Using MySQLi, you would follow:

$statement = $connection->prepare("INSERT INTO `table` (`column_name`) VALUES (SELECT `user_id` FROM `users` WHERE `id`= ?)");
$statement->bind_param("i", $id);
$statement->execute();

PDO would be:

$statement = $connection->prepare("INSERT INTO `table` (`column_name`) VALUES (SELECT `user_id` FROM `users` WHERE `id`= :id)");
$statement->bindParam(':id', $id);
$statement->execute();

I included the column_name because I assume your table has multiple columns, and you want to specify into which column it should be inserted.

kchason
  • 2,836
  • 19
  • 25
0

I have to search in the users table the id that corresponds to that code from your question do this.

$code = 123 

$query="select * from users where code=$code"; //your variable code if confusing me, but it is fetching id based on id but different variable name do this.

$query="select * from users where id=$code";

$runquery = mysqli_query($con, $query); //$con is your database connection parameter 

$fetchuser = mysqli_fetch_array($runquery);

$idofuser = $fetchuser['id'];


$sql = "insert into table values ($idofuser)";
$coolrun = mysqli_query($con, $sql);

if ($coolrun){
 echo "The query ran.";
 }
Shasha
  • 439
  • 8
  • 26