UPDATE in two PHP tables

Question

In my database I have a table called

register

• id_cadastro
• name_user
• email_user
• idade_user
• overuser
• fb_user
• tt_user
• in_user
• avatar_user

This is the information I use for logging in. However, after registering in this table the user is redirected to a page where he selects whether he is a physical or legal person, containing other information

personal

• id_fisica
• id_user
• cpf_user
• rg_user
• phone_user
• cellular_user
• cep_user
• city_user
• state_user
• address_user
• numero_user
• bairro_user

The personal table changes only the field for cnpj, state and municipal registration, besides is equal. But when the user will update the information he needs to update these two tables and I do not know how to do two UPDATES in a php file. 'Cause I’m just using a form

form

<form method="post" action="php/atualizarFisica.php" enctype="multipart/form-data">

   <div class="col-md-4 col-sm-12">
      <div class="form-group">
         <label>Nome Completo <span class="required">*</span></label>
         <input placeholder="" name="nome" class="form-control" type="text"  value="<?php echo $_SESSION['nome_user']; ?>" required>
      </div>
   </div>

   <div class="col-md-4 col-sm-12">
      <div class="form-group">
         <label>E-mail <span class="required">*</span></label>
         <input placeholder="" name="email" class="form-control" type="email" value="<?php echo $_SESSION['email_user']; ?>" required>
      </div>
   </div>

   <div class="col-md-4 col-sm-12">
      <div class="form-group">
         <label>Data Nascimento <span class="required">*</span></label>
            <input placeholder="" name="idade" class="form-control" type="text" value="<?php echo $_SESSION['idade_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Telefone <span class="required">*</span></label>
         <input placeholder="" name="telefone" class="form-control" type="text" value="<?php echo $row_fisica['telefone_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Celular <span class="required">*</span></label>
         <input placeholder="" name="cel" class="form-control" type="text" value="<?php echo $row_fisica['cel_user']; ?>">
      </div>
   </div>

   <div class="col-md-12 col-sm-12 mg-t">
      <div class="form-group">
         <label>Sobre você: </label>
         <textarea cols="6" name="sobre" rows="8" placeholder="" class="form-control"><?php echo $_SESSION['sobre_user']; ?></textarea>
      </div>
   </div>

   <div class="col-md-12 col-sm-12">
      <div class="heading-inner">
         <p class="title">Informações Pessoais</p>
      </div>
   </div>                 

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>CPF <span class="required">*</span></label>
         <input placeholder="" name="cpf" class="form-control" type="text" value="<?php echo $row_fisica['cpf_user']; ?>">
      </div>
   </div>

  <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>RG <span class="required">*</span></label>
         <input placeholder="" name="rg" class="form-control" type="text" value="<?php echo $row_fisica['rg_user']; ?>">
      </div>
   </div>

  <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Estado <span class="required">*</span></label>
         <input placeholder="" name="estado" class="form-control" type="text" value="<?php echo $row_fisica['estado_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Cidade <span class="required">*</span></label>
         <input placeholder="" name="cidade" class="form-control" type="text" value="<?php echo $row_fisica['cidade_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>CEP <span class="required">*</span></label>
         <input placeholder="" name="cep" class="form-control" type="text" value="<?php echo $row_fisica['cep_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Endereco <span class="required">*</span></label>
         <input placeholder="" name="endereco" class="form-control" type="text" value="<?php echo $row_fisica['endereco_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Numero <span class="required">*</span></label>
         <input placeholder="" name="numero" class="form-control" type="text" value="<?php echo $row_fisica['numero_user']; ?>">
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Bairro <span class="required">*</span></label>
         <input placeholder="" name="bairro" class="form-control" type="text" value="<?php echo $row_fisica['bairro_user']; ?>">
      </div>
   </div>                   

   <div class="col-md-12 col-sm-12">
      <div class="heading-inner">
         <p class="title">Senha</p>
      </div>
   </div>

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Nova Senha </label>
         <input placeholder="" name="senha" class="form-control" type="password">
      </div>
   </div> 

   <div class="col-md-6 col-sm-12">
      <div class="form-group">
         <label>Confirma Nova Senha </label>
         <input placeholder="" name="senha" class="form-control" type="password">
      </div>
   </div>

   <div class="col-md-12 col-sm-12">
      <div class="heading-inner">
         <p class="title">Mídias Sociais</p>
      </div>
   </div>

  <div class="col-md-4 col-sm-4 col-xs-12">
    <div class="input-group">
      <span class="input-group-addon" id="basic-addon1"><i class="fa fa-facebook" aria-hidden="true"></i></span>
      <input type="text" class="form-control" name="fb" placeholder="Facebook" aria-describedby="basic-addon1" value="<?php echo $_SESSION['fb_user']; ?>">
    </div>
  </div>

  <div class="col-md-4 col-sm-4 col-xs-12">
    <div class="input-group">
      <span class="input-group-addon" id="basic-addon1"><i class="fa fa-twitter" aria-hidden="true"></i></span>
      <input type="text" class="form-control border-field" name="tt" placeholder="Twitter" aria-describedby="basic-addon1" value="<?php echo $_SESSION['tt_user']; ?>">
    </div>
  </div>

  <div class="col-md-4 col-sm-4 col-xs-12">
    <div class="input-group">
      <span class="input-group-addon" id="basic-addon1"><i class="fa fa-instagram" aria-hidden="true"></i></span>
      <input type="text" value="<?php echo $_SESSION['in_user']; ?>" class="form-control border-field" name="in" placeholder="Instagram" aria-describedby="basic-addon1">
    </div>
  </div>

  <div class="col-md-2 col-sm-2 mg-t">
    <img src="images/users/<?php echo $_SESSION['avatar_user']; ?>" alt="" class="profile-img img-responsive" />
  </div>

  <div class="col-md-10 col-sm-10 mg-t">   
    <div class="form-group">
      <label for="exampleInputFile">Imagem Perfil</label>
      <input type="file" class="form-control-file" name="avatar[]" id="exampleInputFile" aria-describedby="fileHelp">
      <small id="fileHelp" class="form-text text-muted">Caso queira alterar sua imagem de perfil basta clicar no botão acima.</small>
    </div>
  </div>

   <div class="col-md-12 col-sm-12">
      <button name="submit" class="btn btn-default pull-right"><i class="fa fa-save"></i> Salvar </button>
   </div>

</form>

php update.

    <?php

    session_start();
    require_once('conexao/conexao.php');

    $id_user = $_SESSION['id_cadastro'];

    if(!empty($_POST['nome'])
    && !empty($_POST['email'])
    && !empty($_POST['telefone'])
    && !empty($_POST['cpf'])
    && !empty($_POST['rg'])){

        $nome       = $_POST['nome'];
        $email      = $_POST['email'];
        $idade      = implode("-",array_reverse(explode("/",$_POST['idade'])));
        $telefone   = $_POST['telefone'];
        $cel        = $_POST['cel'];
        $sobre      = $_POST['sobre'];
        $cpf        = $_POST['cpf'];
        $rg         = $_POST['rg'];
        $estado     = $_POST['estado'];
        $cidade     = $_POST['cidade'];
        $cep        = $_POST['cep'];
        $endereco   = $_POST['endereco'];
        $numero     = $_POST['numero'];
        $bairro     = $_POST['bairro'];
        $senha      = ($_POST['senha'])?md5($_POST['senha']):false;
        $fb         = $_POST['fb'];
        $tt         = $_POST['tt'];
        $in         = $_POST['in'];
        $avatar     = $_FILES['avatar'];

        /* Descobrir se o email já existe */
        $iguais = "SELECT * FROM cadastro 
                    WHERE email_user LIKE '%{$email}%' 
                    AND id_user != {$id_user}";
        $iguais_query = mysql_query($iguais, $con);
        $row_iguais = mysql_fetch_assoc($iguais_query);

        if (mysql_num_rows($iguais_query) > 0) {
            $_SESSION['aviso'] = "O Email $email ja esta sendo usado";
            header('location: ../user-edit-profile.php');
            exit;
        } else{

            $update = "UPDATE cadastro
                        SET 
                            A.nome_user     = '{$nome}',
                            A.email_user    = '{$email}',
                            A.idade_user    = '{$idade}',
                            A.sobre_user    = '{$sobre}',";
            if($senha){
                $update .= "A.senha_user    = '{$senha}',";
            }
                $update .= "A.fb_user       = '{$fb}',
                            A.tt_user       = '{$tt}',
                            A.in_user       = '{$in}',
                            B.cpf_user      = '{$cpf}',
                            B.rg_user       = '{$rg}',
                            B.estado_user   = '{$estado}',
                            B.cidade_user   = '{$cidade}',
                            B.cep_user      = '{$cep}',
                            B.endereco_user = '{$endereco}',
                            B.numero_user   = '{$numero}',
                            B.bairro_user   = '{$bairro}',
                            B.telefone_user = '{$telefone}',
                            B.celular_user  = '{$cel}'
                            FROM cadastro A
                            INNER JOIN pessoa_fisica B ON B.id_user = A.id_cadastro
                            WHERE A.id_cadastro = '{$id_user}'";

            $executaUp = mysql_query($update, $con);

            if(mysql_affected_rows($con) > 0){
                $_SESSION['certo'] = "Atualizado com sucesso";
                header('location: ../user-dashboard.php');
                exit;
            } else{
                echo "Ocorreu um erro na atualização das informações";
                print_r($update);
            }

        } //ELSE UPDATE SEM AVATAR


    } else{
        $_SESSION['aviso'] = "Preencha os campos necessários.";
        header('location: ../user-edit-profile.php');
        exit;
    }

?>

Here is only doing the UPDATE in the table register, I want to know how to do the UPDATE in the table personal if the user changes any field related to that table.

Answer 1

When you want to perform more than one query or several, and that this data is related or one operation depends on the other, sql transactions are recommended.

An example where I need to update my pf table and at the same time I want to update the pj table information with the pf data. The pf and pj tables are respectively to maintain data of individuals and legal entities:

• Table pf

• Table pj

Code of our example:

Connection to the Mysql database:

$host = 'localhost';
$porta = 3306;
$user = 'root';
$senha = '';
$database = 'test';

$conn = new mysqli($host,$user, $senha, $database, $porta);

if (!$conn){
    echo "Falha ao conectar banco de dados, verifique suas configurações.";
}

In the code below in php I add in variables (which in good practice would be sent by the view, this is a simple example), where you pass name, login and id of an individual in which you want to change the data:

$nome = "Luiz Fernando";
$login = "luiz_fernando";
$id = 1;

After we use the function mysqli_autocommit, it returns TRUE or FALSE value and has two required parameters:

1. connection variable (in our $Conn example)
2. boolean value for autocommit (in our case FALSE, to avoid autocommit, remember that if the transaction fails, we will have to undo the changes to avoid affecting the data)

We will still use a $error variable with initial value 0 to know if there was an error in the transaction.

mysqli_autocommit($conn, FALSE);
$erro = 0;

We will pass our querys and test if there is a failure in the execution of querys and if it occurs we will add our $error variable. If after testing the $error variable remains 0, no error occurred and we can run the mysqli_commit and actually apply in our database the changes ( and display a success message).

Otherwise, if error occurs we will execute the function mysqli_rollback to return the transaction and undo any changes. We will still print a message and how many errors occurred:

$query1 = "update tb_pf set nome = '$nome', login = '$login' where id = $id";
$query2 = "update tb_pj set responsavel = '$nome', login = '$login' where fk_pf = $id";

if (!mysqli_query($conn, $query1))
    $erro++;
if (!mysqli_query($conn, $query2))
    $erro++;

if ($erro == 0) {
    mysqli_commit($conn);
    echo "\n Transação realizado com sucesso, dados atualizados!";
} else {
    mysqli_rollback($conn);
    echo "\n Ocorrem $erro erro(s) na transação e não foi possível atualizar os dados.";
}

When we run this php script, we will have as a result:

Database:

Here the complete code:

<?php
// Dados para conexão ao banco de dados
$host = 'localhost';
$porta = 3306;
$user = 'root';
$senha = '';
$database = 'test';
// Variável de conexão ao banco de dados utilizando a função php mysqli
$conn = new mysqli($host, $user, $senha, $database, $porta);
// Teste de conexão
if (!$conn) {
    echo "Falha ao conectar banco de dados, verifique suas configurações.";
}

echo "MYSQLI COM PHP <br><br>";
// Variáveis com dados para alteração
$nome = "Luiz Fernando";
$login = "luiz_fernando";
$id = 1;
// Definição da função mysqli_autocommit
mysqli_autocommit($conn, FALSE);
$erro = 0;
// Querys
$query1 = "update tb_pf set nome = '$nome', login = '$login' where id = $id";
$query2 = "update tb_pj set responsavel = '$nome', login = '$login' where fk_pf = $id";
// Teste de execução das querys
if (!mysqli_query($conn, $query1))
    $erro++;
if (!mysqli_query($conn, $query2))
    $erro++;
// Se não ocorrer erros executamos o commit para o banco de dados caso contrários desfazemos qualquer alteração
if ($erro == 0) {
    mysqli_commit($conn);
    echo "Transação realizada com sucesso, dados atualizados!";
} else {
    mysqli_rollback($conn);
    echo "Ocorrem $erro erro(s) na transação e não foi possível atualizar os dados.";
}

Remembering that this was an example only to demonstrate how it can be done, the tables, information in the variables and its capture, passage of variables to query do not correspond a good code in practice (I only aim to demonstrate the function). There is also the function mysqli_begin_transaction with more options and better, to work with mysql and php database transactions.

Answer 2

You could do an INNER JOIN (join tables) effecting a relationship between the entities by the primary key of the registration table with the foreign key of the individual table and effecting the update at once.

Follow an example:

UPDATE TABELA1 
SET 
   A.Campo = 'Valor',
   B.Campo = 'Valor',
   C.Campo = 'Valor'
FROM TABELA1 A
INNER JOIN TABELA2 B on B.Campo = A.Campo
INNER JOIN TABELA3 C on C.Campo = B.Campo

I believe that in your script the primary key of the table registration is id_cadastre and should be repeated as foreign key in the table person as id_user so it would be for these fields that should generate your relationship.

PS: Note that when modeling tables (table = entity) where the primary key of a table relates to another entity as a foreign key it should have the same name in the field (attribute), therefore I advise against the practice of naming a field (attribute) how to id_register and repeat the same field(attribute) in the individual table as id_user this generates confusion. But it’s a practice we don’t pay much attention to at first.

I hope my answer will be useful to your understanding.

Updating

In order to solve the problem it is necessary to take some measures to isolate the cause. Do not program without err. That is why the messages of err and Warning

Note this code below:

A.user_name = '{$name}',

Instead just do it:

A.user_name = '". $name."',

Wrapped with single quotes, in case the variable you are inserting into the attribute (field) is a string of type (CHAR AND VARCHAR),already in the case of variables of the whole type (INT) no need for single quotes involving concatenation, only double quotes ending a string to concatenate a variable in the middle of it.

Example of use :

$update = "UPDATE cadastro SET  A.nome_user = '".$nome."' FROM CADASTRO 
A WHERE A.id_cadastro = ".$id_user.";

You defined that for the update to have been a success the number of lines affected after the execution of the querie has to be greater than zero. This is so right!

if(mysql_affected_rows($con) > 0){

However it did not post the connection code to the database and nor much less we know if your server’s settings have error display enabled (Error Reporting) generally to find out an error one must debug it so we asked in comment for that puts up before your if the command :

echo mysql_error();
if(mysql_affected_rows($con) > 0){

So that if there is any syntax error in your querie, defined in the variable $update he reported

Finally how do you debug a code? Instructions : **Exit , echo, print_r **

You report that when executing your code no errors are issued and therefore, if your settings are correct and php is set to issue warnings and errors and mysql_affected_rows is zero at the end of the run we deduce that the problem is about the querie that is set in the $update variable. In this case the correct way to find out is by printing the value of the concatenated variable $update and then stop executing the code with the Exit command. So it would be enough before this line :

 echo $update; exit;
 $executaUp = mysql_query($update, $con);

Note that since I used Exit before mysql_query this will not be executed only printing the querie that exists in the variable $update.By printing the querie you could pick up and run sql directly on your phpmyadmin and check which error is being reported and make the proper corrections. Otherwise you can go using echo , print_r (in cases of array or object) and Exit to stop code line by line to understand the problem.

This text is extremely important not to program without mistakes. If you do not understand how to clean your code in an organized way until you have all the understanding of the logic implemented PROGAM, copy and glue and that the market is full!

Hugs Solomon