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.
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.";
}
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
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.