How to return values from two distinct tables php

Navigation
2

I am a beginner and wanted to create a function that would return me from the name of the cpf, user and password of the person in the system. Where the name and cpf fields belong to the users table and the user and password fields belong to users.

Way I tried and did not succeed 

          <script type='text/javascript'>
                    $(document).ready(function(){
                        $("input[name='nome']").blur(function(){
                                var $cpf = $("input[name='cpf']");
                                var $usuario = $("input[name='usuario']");
                                var $senha = $("input[name='senha']");

                                $.getJSON('function.php',{ 
                                        nome: $( this ).val() 
                                },function( json ){
                                        $cpf.val( json.cpf );
                                        $usuario.val( json.usuario );
                                        $senha.val( json.senha );

                                });
                        });
                });
        </script>

My function.php 

     function retorna($nome, $conn){
            $result = "SELECT CPF, USUARIO, SENHA FROM USUARIOS A INNER JOIN USUAR B ON B.NOME = A.NOME WHERE nome = '$nome' LIMIT 1";
            $resultado = mysqli_query($conn, $result);
            if($resultado->num_rows){
                    $row = mysqli_fetch_assoc($resultado);
                    $valores['cpf'] = $row['cpf'];
                    $valores['usuario'] = $row['usuario'];
                    $valores['senha'] = $row['senha'];

            }else{
                    $valores['cpf'] = '';
                    $valores['usuario'] = '';
                    $valores['senha'] = '';

            }

            return json_encode($valores);

    }

    if(isset($_GET['nome'])){
            echo retorna($_GET['nome'], $conn);
    }
    
asked by anonymous 22.09.2017 / 21:18

1 answer

1

Are the two tables related? If yes, try to search by number, such as cod_usuario . I believe it is not possible to do select with inner setting 2 different types of values for the same field. In this case I would recommend that you create a view in the bank and give it a select by it. Example: 

create view vw_user as
  select a.cpf, b.usuario, b.senha  
  from usuario a, user b 
  where a.cod_usuario = b.cod_usuario
;

Where inside the code in php you put the query 

select * from vw_user where nome = $nome

Of course, claiming the user name of the person table is the same as the name of the users table . If not, I suggest that this login be done by the cpf number, or that it uses another key to find the user.

EDIT 1: I created a dummy bank to illustrate my thinking (without adding the view, to make it simpler) 

create database logincliente;
use logincliente;

create table pessoa(
cod_pessoa int not null auto_increment primary key,
nome_pessoa varchar(50)
);

create table login(
cod_login int not null auto_increment primary key,
cod_pessoa int not null,
login varchar(50),
senha varchar(50)
);

alter table login add constraint fk_loginPessoa foreign key (cod_pessoa) references pessoa (cod_pessoa);


insert into pessoa(nome_pessoa) values 
('Flávio'),
('Castro'),
('Reginaldo'),
('Péricles');

insert into login (cod_pessoa,login,senha) values 
(1,'flaviko','12345'),
(2,'kastr','stqvb'),
(3,'reginaldo','reginaldo'),
(4,'pokkter','033line');



select p.nome_pessoa, l.login, l.senha from pessoa p, login l where nome_pessoa = 'péricles' and p.cod_pessoa = l.cod_pessoa;

Try to test this last select, and see if this is the result you want:

    
22.09.2017 / 21:44
How to fetch data from one table using reference from others? ComboBox Content Matching