How to compare in update

Asked

Viewed 61 times

0

The main code is this:

$dbconn = mysqli_connect($servername, $username, $password, $dbname)or die("Failed to connect to database:" . mysqli_error($dbconn));

$query = "SELECT id_medico as txtid, nome as txtNome, email as txtEmail, telemovel as txtTelemovel, morada as txtMorada, especialidade as txtEspecialidade, NIF as txtNif, Localidade as txtLocalidade, codigo_postal as txtCodigo, data_nascimento as txtnascimento, observacoes as txtObservacao  FROM Medicos";
$data = mysqli_query($dbconn, $query);
$result = mysqli_num_rows($data);       
$meujson = array();   


?>
<div id=Corpo>
<br>
    <br>
<form name="medicos" method="post" action="">
<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['txtid'].'">'.$prod['txtNome'].'</option>';
    $meujson[$prod['txtid']] = $prod; // coleciona as linhas no $meujson
     $_SESSION[$prod['txtid']] = $prod['txtid'];

   }
   ?>
</select>

<br> <br> <br>

&nbsp &nbsp <label for="txtid">Nome:</label>
<input type="text" id="txtNome" name="txtNome" value="" size=90 /> <br> <br>
&nbsp &nbsp<label for="txtid">NIF:</label> &nbsp &nbsp
<input type="number" id="txtNif" name="txtNif" value="" /> &nbsp &nbsp &nbsp 
&nbsp
&nbsp &nbsp<label for="txtid">Data de Nascimento:</label> &nbsp
<input type="date" id="txtdata" name="txtdata" value="" /> <br> <br>
&nbsp &nbsp<label for="txtid">Email:</label> 
<input type="text" id="txtEmail" name="txtEmail"value="" size=30 /> &nbsp 
&nbsp
&nbsp &nbsp<label for="txtid">Telem&oacute;vel:</label>
<input type="number" id="txtTelemovel" name="txtTelemovel" value="" /> <br> 
<br>
&nbsp &nbsp<label for="txtid">Morada:</label>
<input type="text" id="txtMorada" name="txtMorada" value="" size=90 /> <br> 
<br>
&nbsp &nbsp<label for="txtid">C&oacute;digo Postal:</label>
<input type="number" id="txtCodigo" name="txtCodigo" value="" /> 
&nbsp &nbsp<label for="txtid">Localidade:</label>
<input type="text" id="txtLocalidade" name="txtLocalidade"value="" /> <br> 
<br>
 &nbsp &nbsp<label for="txtid">Especialidade:</label>
 <input type="text" id="txtEspecialidade" name="txtEspecialidade" value="" /><br> <br>
 &nbsp &nbsp<label for="txtid">Observa&ccedil;&otilde;es:</label>
 <input type="text" id="txtObservacao" name="txtObservacao" value="" size=90/><br> <br>


<script type="text/javascript">

var dados = <?php echo json_encode($meujson); ?>;
document.getElementById("cbMedicos").addEventListener("change", function () 
{
var linha = dados[this.value];
for (var ch in linha) {
    (document.getElementById(ch) || {}).value = linha[ch];
}
});
</script>

<script type="text/javascript">
document.getElementById("cbMedicos").addEventListener("change", function () 
{
    document.getElementById("txtid").value = this.value;
});
</script>

        <br>
    <br>
    <br> 


        &nbsp &nbsp <input type="submit" value="Novo" class="botaoNovo" name="botao_novo"> 
        &nbsp &nbsp &nbsp <input type="reset" value="Apagar" class="botaoApagar" name="botao_apagar"> 
        &nbsp &nbsp &nbsp <input type="submit" value="Editar" class="botaoEditar" name="botao_editar"> 
        </div>
        </form>

And then the code below is called to the front page. And I want the query update to compare the name of the database with the name chosen from the combobox, I had put so:

....
$cbnome= trim($_POST['cbMedicos']);

$query = "UPDATE Medicos SET nome= '".$Nome."', NIF='".$nif."', data_nascimento='".$bday."', email='".$email."', telemovel='".$telemovel."', morada='".$morada."', codigo_postal= '".$codigo."', Localidade= '".$localidade."', especialidade= '".$especialidade."', observacoes= '".$observacao."' WHERE nome = '$cbnome'";  
$data = mysqli_query($dbconn, $query);
$result = mysqli_num_rows($data);

if ($result == 0) 
{
    $query = "INSERT INTO Medicos (nome, NIF, telemovel, data_nascimento, email, morada , observacoes, codigo_postal,   especialidade, Localidade )  VALUES ('".$Nome."', '".$nif."', '".$telemovel."', '".$bday."', '".$email."', '".  $morada."', '".$observacao."', '".$codigo."', '".$especialidade."', '".$localidade."')";

    $data = mysqli_query($dbconn, $query);

    echo  "<br>Dados do pacientes gravados com sucesso <br>";
}

else
{
   echo "<br> O novo paciente foi inserido com sucesso <br>";
}


?>
<?php
    }
    mysqli_close($dbconn);
    ?>

but it’s not working, someone knows how to do?

php sql-update

  • Your query seems to be right, try to write it directly on SQL and see if any errors return, or anything out of the ordinary.

  • @Francisco already did this, but no sql can not put with the names of Abels and I think the problem will be in WHERE

  • Give an Insert just for testing and run the query simulating some values.

  • @Francisco edited the question and put all the code... He always does the Insert, never does the update.

  • $Row['txtid_medico']; Wouldn’t it be $Prod['txtid_medico'];? Something else who is txtid_medico?

  • I had never used this line of code, they gave me this line to save a variable and to be called in another PHP file, I adapted to the combobox, but the id_medico is wrong is txtid... I already edit the question, but in this case this line is not called to this code...

  • continues wrong is not $Row['txtid'] and yes $Prod['txtid']

  • $dbconn != $dbcon You are using mysqli_query($dbconn and when you close the connection you are mysqli_close($dbcon

  • @Leocaracciolo already edited the code but it’s still the same...

  • I’m not saying that these errors would fix the update, they’re just details. I’m creating an environment on my server/bank to test your script

  • I could post HTML as well

  • @Leocaracciolo already edited my code with what I have

Show 7 more comments

2 answers

1

First on update line WHERE nome = '$cbnome' has a mistake.

This variable $cbnome does not refer to column nome and yes the column id_medico

So the right thing is WHERE id_medico = '$cbnome'";

this variable $cbnome was misnamed and caused some confusion.

Has more mistakes

this code snippet 

$data = mysqli_query($dbconn, $query);
$result = mysqli_num_rows($data);

if ($result == 0) 
{

does not work properly, it does update and Insert regardless of the button clicked Novo or Editar

The form below will work correctly:

if ($_POST['botao_novo'] == "Novo") 
{
    $query = "INSERT INTO Medicos (nome, NIF, telemovel, data_nascimento, email, morada , observacoes, codigo_postal,   especialidade, Localidade )  VALUES ('".$Nome."', '".$nif."', '".$telemovel."', '".$bday."', '".$email."', '".  $morada."', '".$observacao."', '".$codigo."', '".$especialidade."', '".$localidade."')";

    $data = mysqli_query($dbconn, $query);

    echo  "<br> O novo paciente foi inserido com sucesso<br>";
}

if ($_POST['botao_editar'] == "Editar") 
{
   $query = "UPDATE Medicos SET nome= '".$Nome."', NIF='".$nif."', data_nascimento='".$bday."', email='".$email."', telemovel='".$telemovel."', morada='".$morada."', codigo_postal= '".$codigo."', Localidade= '".$localidade."', especialidade= '".$especialidade."', observacoes= '".$observacao."' WHERE id_medico = '$cbnome'"; 

   $data = mysqli_query($dbconn, $query);

   echo "<br> Dados do paciente atualizados com sucesso <br>";
}

If it is not possible to insert new ones with some pre-established criterion, for example, by email, a check of the existence of this before the Insert.

Example:

$query = "SELECT email FROM Medicos Where email= '$email'";
$data_mail = mysqli_query($dbconn, $query);
$result_mail = mysqli_num_rows($data_mail); 


    if ($result_mail == 0) 
    {
        $query = "INSERT INTO Medicos (nome, NIF, telemovel, data_nascimento, email, morada , observacoes, codigo_postal,   especialidade, Localidade )  VALUES ('".$Nome."', '".$nif."', '".$telemovel."', '".$bday."', '".$email."', '".  $morada."', '".$observacao."', '".$codigo."', '".$especialidade."', '".$localidade."')";

        $data = mysqli_query($dbconn, $query);

        echo  "<br> O novo paciente foi inserido com sucesso<br>";
    }
    else 
    {

       $query = "UPDATE Medicos SET nome= '".$Nome."', NIF='".$nif."', data_nascimento='".$bday."', email='".$email."', telemovel='".$telemovel."', morada='".$morada."', codigo_postal= '".$codigo."', Localidade= '".$localidade."', especialidade= '".$especialidade."', observacoes= '".$observacao."' WHERE id_medico = '$cbnome'"; 
       $data = mysqli_query($dbconn, $query);

       echo "<br> Dados do paciente atualizados com sucesso <br>";
    }

In this case, one Ubmit button is enough, and the Insert will only occur if there is a change of email in the email field

  • if you take out the new button, only a single button that does one of the two things, as if that name already exists or not, is possible?

  • There may be in this world two doctors with the same name!!! can be done for example by mail, this is unique

  • It’s a good option, but how did I get the code? That’s what I was trying to do with ifs... make one or make another as if it already existed or not.

  • @Isabelsousa, yes it is possible. Just do a SELECT.... WHERE email = '$email' and then check with mysqli_num_rows and do if ($result_mail == 0) update Else Insert

  • I’ll put this other option in the answer, only it will enter more processing

0

The error was in this line of code: instead of "'. $Prod['txtid']. '" gets:

echo '<option value="'.$prod['txtNome'].'">'.$prod['txtNome'].'</option>';

Since the query passes to: 

UPDATE Medicos SET nome= '".$Nome."', NIF='".$nif."', data_nascimento='".$bday."', email='".$email."', telemovel='".$telemovel."', morada='".$morada."', codigo_postal= '".$codigo."', Localidade= '".$localidade."', especialidade= '".$especialidade."', observacoes= '".$observacao."' WHERE nome = '$cbnome'";

  • And if two people have the same name?

  • @Everson, ai Laulau Lau :)

Browser other questions tagged php sql-update

You are not signed in. Login or sign up in order to post.