UPDATE on two PHP tables

Question

UPDATE on two PHP tables

Navigation

#1 by (1 votes)

0

In my database I have a table called

register

id_cadastro
username_user
email_user
age_user
sobre_user
fb_user
tt_user
in_user
avatar_user

These are the information I use for login. But 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.

person_physical

Physical_id
id_user
cpf_user
rg_user
telefone_user
celular_user
cep_user
ciudad_user
estado_user
address_user
numero_user
bairro_user

The person_juridica table changes only the field to cnpj, state and municipal inscription, rest is the same. But when the user is going to update the information he needs to update these two tables and I do not know how to do two UPDATES in a php file. Because I'm only 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>

atualiza.php

    <?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;
    }

?>

php mysql sql-update

asked by anonymous 12.07.2017 / 03:59

1 answer

Doubt regarding dispose () in JFrame Using active menu as input "radio"

score 1 · Answer 1

You could do an INNER JOIN ( joining tables ) by doing a relationship between the entities by the primary key of the table register with the foreign key of the physical table and perform the update once.

Here's 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 register is id_cadastro and should be repeated as foreign key in the physical person table as id_user then it would be for those fields that it should generate your relationship.

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 (attribute) field, so I advise against naming a field (attribute) as id_cadastro and repeat the same field (attribute) in the person table as id_user this causes confusion. But it is a practice that at first we do not pay much attention.

I hope my answer will be useful to your understanding.

Update

In order for the problem to be resolved, it is necessary to take some steps to isolate the cause. Do not program without error . That is why err and warning messages are so fundamental

Repair this code below:

A.name_user = '{$ name}',

Instead do just this:

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

Involved with single quotes, in case the variable that is inserting in the attribute (field) is a string of type (CHAR AND VARCHAR) , already in case of variables of integer type (INT) There is no need for single quotes involving concatenation, only double quotation marks ending a string to concatenate a variable in the middle of it.

Example usage:

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

You have defined that for the update to have been a success the number of affected rows after executing the query must be greater than zero. This is very correct!

if (mysql_affected_rows ($ con) > 0) {

However, we did not post the connection code with the database and much less do we know if the settings of your server have the error display enabled (Error Reporting ) to usually find out an error one should debug it so I asked in comment for it to put itself before its if the command:

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

If there is any syntax error in your query, defined in the $ update variable, it would report

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

You report that when running your code no error is issued and therefore if your settings are correct and php is set to issue warnings and errors and mysql_affected_rows is equal to zero at the end of the run we deduce that the problem is over the query which is defined in the $ update variable. In this case the correct way to find out is to print the value of the $ update concatenated variable and then stop the code execution with the exit command. So it would be enough before this line to do:

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

Note that as I used exit before mysql_query this will not only print the query that exists in the $ update variable. By printing the query you could get and run the sql directly in your phpmyadmin and check which error is being reported and make the appropriate corrections. Otherwise you can go using echo, print_r ( in cases of array or object ) and exit to stop the code line by line to understand the problem.

This text is of the utmost importance not to be programmed without errors . If you do not understand how to debug your code in an organized way until you have all the understanding of the implemented logic, do not PROGAMA , copy and paste and that The market is full!

Solomon's Hugs