How to store the data in a variable and then use a query?

Question

I have a combobox filled with data from the database (doctor’s name), where I choose a name, but I wanted to record in another table of the database the id_medico and not the name.

I thought I’d use a SELECT to get the id, store that information in a variable and then use that variable in INSERT, but I don’t know how to fit that into my code. You can help me?

The combobox is filled with this code:

....

$dbconn = mysqli_connect($servername, $username, $password, $dbname)or die("Failed to connect to database:" . mysqli_error($dbconn));
$query = "SELECT nome FROM Medicos";
$data = mysqli_query($dbconn, $query);
$result = mysqli_num_rows($data);       

?>
&nbsp &nbsp<label for="cbMedicos">Selecione um M&eacute;dico</label>
<select id="cbMedicos" name="cbMedicos">
<option>Selecione...</option>

 <?php while($prod = $data->fetch_assoc()) { 
    echo '<option value="'.$prod['nome'].'">'.$prod['nome'].'</option>';
 }
 ?>    
 </select>

My code for now is:

$dbconn = mysql_connect($servername, $username, $password, $dbname);
mysql_select_db($dbname);


if (isset($_POST['botao_marcar_consulta']))
{

    $hora_inicio= trim($_POST['txthora_inicio']); 
    $hora_fim = trim($_POST['txthora_fim']);
    $medicos = trim($_POST['cbMedicos']);

     $sql = "INSERT INTO Consulta_marcada (hora_inicio, hora_fim ) VALUES ('".$hora_inicio."', '".$hora_fim."')";


  if(mysql_query($sql))
 {
 echo "<script>alert('Dados inseridos com sucesso');</script>";
 }
 else
{
echo "<script>alert('FAILED TO INSERT ".mysql_error()."');</script>";
}
}

Answer 1

In your HTML, change the value of the attribute value to the id:

<label for="cbMedicos">Selecione um Médico</label>
<select id="cbMedicos" name="cbMedicos">
    <option>Selecione...</option>
    <?php while($prod = $data->fetch_assoc()) { 
        echo '<option value="'.$prod['id'].'">'.$prod['nome'].'</option>';
    }
    ?>    
</select>

This requires adding the column id in consultation: SELECT id, nome FROM Medicos.

The value that will be displayed to the user will continue to be $prod['name'], but the value that will be passed to PHP in $_POST['cbMedicos'] will be the value of id of the selected doctor. This way, instead of you need to search the database to find the id, just insert it directly. Something like:

$hora_inicio= trim($_POST['txthora_inicio']); 
$hora_fim = trim($_POST['txthora_fim']);
$medicos = intval($_POST['cbMedicos']);

$sql = "INSERT INTO Consulta_marcada (hora_inicio, hora_fim, id_medico) VALUES ('".$hora_inicio."', '".$hora_fim."', ".$medicos.")";

Answer 2

Make the following changes to your code:

1. To get the doctor

   $query = "SELECT id, nome FROM Medicos";
   

2. In the display of the data in the doctor in the combobox

   <?php 
   while($prod = $data->fetch_assoc()) { 
       echo '<option value="'.$prod['id'].'">'.$prod['nome'].'</option>';
   }
   ?>
   

3. And when it’s time to record the data

   $hora_inicio= trim($_POST['txthora_inicio']); 
   $hora_fim = trim($_POST['txthora_fim']);
   $id_medico = trim($_POST['cbMedicos']);
   
   $sql = "INSERT INTO Consulta_marcada (hora_inicio, hora_fim, medico_id) VALUES ('".$hora_inicio."', '".$hora_fim."','".$id_medico."')";
   

If your table in the bank consulta_marcada have not Foreign key id_medico, you will have to add.

In the future you will need to display which appointment each doctor contains. You can do so:

SELECT 
me.nome, cm.hora_inicio, cm.hora_fim
FROM
    consulta_marcada AS cm
        INNER JOIN
    medico AS me ON cm.id_medico = me.id
WHERE id_medico = $id['id_medico'];

Comment if you have problems.

Answer 3

You can use the fetch_assoc() function of mysqli to fetch database data:

$sql = "SELECT * FROM <nome_da_tabela> WHERE tabela_nome_do_medico = <nome_do_medico>";

$res = $con->query($sql);

if ($res) {
   while ($linha = $res->fetch_assoc()) {
      $id_medico = $linha['tabela_id_do_medica'];
   }
}