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

Question

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

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

0

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

I thought about using SELECT to fetch id , save this information in a variable and then use that variable in INSERT , but I do not know how to fit it into my code. Can you help me?

The combobox is populated 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>";
}
}

php

asked by anonymous 30.06.2017 / 19:42

3 answers

1

Make the following changes to your code:

To seek the doctor

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

Displaying the doctor data in the combobox

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

And at the time of writing 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 consulta_marcada database does not have foreign key id_medico , you will have to add it.

In the future you will need to show which appointment each doctor has. You can do this:

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.

30.06.2017 / 20:08

0

You can use mysqli's fetch_assoc () function 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'];
   }
}

30.06.2017 / 19:57

What's happening here in this C program? Apply focus to component

score 3 · Accepted Answer

In your HTML, change the value of the attribute value to 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>

To do this, you need to add the id column in the query: SELECT id, nome FROM Medicos .

The value that will be displayed to the user will remain $prod['name'] , but the value that will be passed to PHP in $_POST['cbMedicos'] will be the id value of the selected doctor. This way, instead of having to search the database to find id , just enter 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.")";