Simple query of Mysql

Asked

Viewed 498 times

-4

I know very little about SQL and I’m thinking about a very simple system - but I don’t know how to set it up and it’s for a public school that doesn’t have a programmer to hire at the moment.

I have an SQL table with some student registration data and each one has an ID. I wanted to build a system with PHP with only one field in which I would type this ID and with a Enter it would display all the data to confirm.

I don’t know if I used all the correct code and called it correctly in PHP. I did some research and found an AJAX code for this, but I also don’t know if it’s all right... Follow what I have so far:

index.js file

//Classe para criar e configurar requisição ajax
var Ajax = function() {
    'use strict';

    var request;

    if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari
        request = new XMLHttpRequest();
    } else { // code for IE6, IE5
        request = new ActiveXObject("Microsoft.XMLHTTP");
    }

    var url;
    var data;
    var method;

    var getUrl = function() {
        return url;
    }
    var setUrl = function(v) {
        url = v;
    }

    var getData = function() {
        return data;
    }
    var setData = function(v) {
        data = v;
    }

    var getMethod = function() {
        return method;
    }
    var setMethod = function(v) {
        method = v;
    }

    var send = function(loading, done) {
        if (!url) throw new Error('Url esperada.');
        if (!method) {
            console.warn('Metodo não especificado. Presumido POST.');
            method = 'POST';
        }

        request.onprogress = function(event) {
            if (event.lengthComputable && loading) {
                var percentComplete = event.loaded / event.total * 100;
                loading(percentComplete);
            }
        };

        request.onreadystatechange = function() {
            if (request.readyState == 4 && request.status == 200 && request.responseText && done) {
                done(request.responseText.toString().replace('while(1);', ''));
            }
        };

        request.open(method, url, true);
        request.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded; charset=utf8');
        request.send(data);
    }

    //Métodos ou propriedades públicas da classe
    return {
        //Significa que quando instanciar a classe,
        //O objeto terá o método 'getUrl', por exemplo.
        //Quando o mesmo for executado: instancia.getUrl
        //Será executada a função 'getUrl', definida acima 'var getUrl = function..'
        getUrl: getUrl,
        setUrl: setUrl,
        getData: getData,
        setData: setData,
        getMethod: getMethod,
        setMethod: setMethod,
        send: send
    }
}

var campoid = document.getElementById('campoid');

campoid.onblur = function() {
    //Instancia a classe Ajax
    var requestid = new Ajax();

    //Configura a requisição
    requestid.setUrl('/conecta.php');
    requestid.setData('ID = ' + this.value);
    requestid.setMethod('POST');
    //Envia a requisição
    requestid.send(null,
    function(resposta) {
        //falha na busca, id ausente
        if (!resposta) {
            alert('ID não encontrado');
            campoid.focus();
            campoid.clear();
            return false;
        }
        //não precisa de else, o return false acima termina a execução da função

        //Transforma a string que o PHP criou em um objeto (JSON)
        var dados = JSON.parse(resposta);

        document.getElementById('outrocampodoform').value = dados.nome;
    });
}
</script>

index.html file

<!DOCTYPE html>
<html lang="pt-BR">
  <head>
    <meta charset="utf-8">
    <title>Central</title>
    <link href='https://fonts.googleapis.com/css?family=Lato:400,700,300|Open+Sans:400,600,700,300' rel='stylesheet' type='text/css'>
    <link href="https://fonts.googleapis.com/css?family=Cinzel" rel="stylesheet" type='text/css'>
    <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">
    <link rel="stylesheet" type="text/css" href="css/font-awesome.min.css">
    <link rel="stylesheet" type="text/css" href="css/animate.css">
    <link rel="stylesheet" type="text/css" href="css/style1.css">
  </head>
  <body>
      <php include 'conecta.php' ?></php>
      <div class="field" align="center">
          <label>ID: </label>
          <input name="id" id="id" placeholder="ID" tabindex="3" required maxlength="10" onblur="ajax.js" autofocus>
      </div><br>
      <div class="field" align="center">
            <label>Nome: </label> <input class="input-tam-1" name="nome" id="nome" type="text" disabled>
      </div><br>    
      <div class="field" align="center">
            <label>Status: </label> <input class="input-tam-1" name="status" id="status" type="text" disabled> 
      </div><br>     
      <div class="field" align="center">
            <label>Documento: </label> <input class="input-tam-1" name="documento" id="documento" type="text" disabled>
      </div><br>
          <div class="field" align="center">
            <label>Nascimento: </label> <input class="input-tam-1" name="nascimento" id="nascimento" type="text" disabled>
      </div><br>
      <div class="field" align="center">
            <label>Instituição: </label> <input class="input-tam-1" name="instituicao" id="escola" type="text" disabled>
      </div><br>
      <div class="field" align="center">
            <label>Validade: </label> <input class="input-tam-1" name="validade" id="validade" type="text" disabled>
      </div><br> 
  </body>
</html>

File connects.php

<?php
error_reporting (E_ALL & ~ E_NOTICE & ~ E_DEPRECATED);
// definições de host, database, usuário e senha
$host = "localhost";
$db   = "wjr_estudante";
$user = "wjr_estudante";
$pass = "xdr56tfc";
// conecta ao banco de dados
$con = mysql_pconnect($host, $user, $pass) or trigger_error(mysql_error(),E_USER_ERROR); 
// seleciona a base de dados em que vamos trabalhar
mysql_select_db($db, $con);
// cria a instrução SQL que vai selecionar os dados
$query = sprintf("SELECT ID, NOME, STATUS, DOCUMENTO, NASCIMENTO, INSTITUICAO, VALIDADE FROM estudantes WHERE NOME = ". $nome");
// executa a query
$dados = mysql_query($query, $con) or die(mysql_error());
// transforma os dados em um array
$linha = mysql_fetch_assoc($dados);
// calcula quantos dados retornaram
$total = mysql_num_rows($dados);

 //Jogar dentro dessa $results os resultados da query


    if (mysqli_num_rows($results) != 0)
    {
        $i = 0;

        //Pega os resultados e transforma em um array
        while ($result = mysqli_fetch_assoc($results))
        {
            $campos = array_keys($result);
            foreach($campos as $campo)
            {
                $allData[$i][$campo] = $result[$campo];
            }
            $i++;
        }
        echo "while(1);" . json_encode($allData);
    }
?>
  • You can do this with ajax and an sql query by or a field called matricula. You need to give more details about your problem and which 'foot' it is

  • This jquery was the code embed from here that added... I will not use, no

  • The server runs PHP 5.6, but I can change up to 7.1. The table has columns of ID, name, status, date etc. The same that are in the html form and I want to return from the ID query (unique for each registration).

  • No, just query... The data goes to db by another system. I just want to enter the ID so that it looks at the other data in sql and display in the form.

1 answer

0


I’ll be taking over the following table:

CREATE TABLE `aluno` (
  `id_aluno` int(11) NOT NULL,
  `nome` varchar(250) DEFAULT NULL,
  `status` int(2) DEFAULT NULL,
  `documento` varchar(250) DEFAULT NULL,
  `nascimento` date DEFAULT NULL,
  `instituicao` varchar(250) DEFAULT NULL,
  `validade` date DEFAULT NULL,
  `criado` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `alterado` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `aluno`
  ADD PRIMARY KEY (`id_aluno`);

ALTER TABLE `aluno`
  MODIFY `id_aluno` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

I will take into account that already developed a way to register these data in the system, if it is necessary to register, inform in the comments.

The first thing to do is to create a database connection, see that in your example you cause some confusion with the mysql and mysqli extensions, the first one was discontinued and you can no longer use it if you upgrade php to version 7, then it is highly recommendable you go to use mysqli or Pdo, I will make an example using Pdo.

File connected.php

This file will be the same for both mysqli and Pdo extensions, the ideal is to create a separate file, so it is easy to change if there is more than one environment or future system migrations.

<?php
    // Host
    define("host","localhost");
    // Esquema
    define("schema","teste");
    // Usuário
    define("user","root");
    // senha
    define("password","");
?>

The function define() creates a constant that can be used anywhere in the system and that cannot be changed in future codes.

File functions.php

In this file you can concentrate auxiliary functions that will be useful in more than one script, such as a function that logs script or registration errors to the database, for example

<?php
    function create_log( $filename, $string ) {
        date_default_timezone_set( 'America/Sao_Paulo' );
        file_put_contents( $filename.".log", date( 'r' )." ".$string.PHP_EOL.json_encode($_REQUEST).PHP_EOL, FILE_APPEND );
    }

date_default_timezone_set -> Configures the default time zone used by all date and time functions in a script

file_put_contents -> Writes a string to a file

date -> Format local date and time, in case r in Thu format, 21 Dec 2000 16:01:07 +0200

json_encode -> Returns the JSON representation of a value


File initializes-sessao.php

To make the system more secure, and block external access, I would create a token in the session that could be generated as follows:

<?php
    // Inicializa a sessão, caso ainda não tenha sido inicializada
    session_start(); 
    // Seto que o retorno será como json
    header('Content-Type: application/json; charset=utf-8');
    // A função crypto "criptografa" alguma string passada como parâmetro, e a função time retorna o timestamp atual do servidor, salvo a concatenação dentro de uma váriavel token que seto na sessão
    $_SESSION['token'] = crypt( "o4sddfgsa5dv4A2vDAaXta7aasdff87" . time(), "" );
    // Retorna um json com o token para ser usado futuramente
    die('{"token": "'.$_SESSION['token'].'"}');
?>

Remembering that this script should not be used to generate password encryption, it will only generate a momentary access key to the api

I would make some modifications to your index.php file, first convert to . html, when the server detects that the requested file has the extension. php, it obligatorily calls the php interpreter, in your case on the page itself there is not much need to consume server resources at this time, so I would convert to .html. Another point are libraries, an interesting tip is whenever possible, for static contents, like external libraries, use Cdn, of course it is more optional than anything, but it is at your discretion, I leave a contents on Cdn if you want to iterate more on the subject.

Other caveats, in your index.php file you make two syntax errors, the first: <php include 'conecta.php' ?></php>, php is not an html tag that should be used as other type <table>, <img>, etc. When the php interpreter is called on the server it fetches all the tag openings <?php and interprets all that is contained until the closing of this tag, which is ?>, on that line also lacks a ;, she should be: <?php include 'conecta.php'; ?>.

The second error is in the event call onblur of input id, onblur="ajax.js" ... html tags event must call javascript functions, or perform javascript functions, and not load javascript files, the only tag that can load an external javascript file is the tag <script type="text/javascript" src="path/do/seu/js.js"></script>, or if you want to include the script on the page itself just do as follows:

<script type="text/javascript">
    /* Seu código aqui!
     .
     .
     .
    */
</script>

index.html file

With the changes and reservations, I would create a file like this:

<html lang="pt-BR">
<head>
  <meta charset="utf-8">
  <title>Central</title>
  <link href='https://fonts.googleapis.com/css?family=Lato:400,700,300|Open+Sans:400,600,700,300' rel='stylesheet' type='text/css'>
  <link href="https://fonts.googleapis.com/css?family=Cinzel" rel="stylesheet" type='text/css'>
  <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
  <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.5.2/animate.css">
  <link rel="stylesheet" type="text/css" href="css/style1.css">
  <script type="text/javascript" src="index.js"></script>
</head>
<!-- O evento onload chama a função iniciarSessao() apos toda a página html ser carregada -->

<body onload="iniciarSessao()">
  <!-- Input token tipo hidden -->
  <input id="token" name="token" type="hidden">
  <!-- Div mostra mensagem de retorno caso haja erro -->
  <div id="retorno" name="retorno" align="center"></div>
  <!-- Form apenas para ficar mais conveniente na hora de limpar os campos -->
  <div class="field" align="center">
    <label>ID: </label>
    <!-- O evento onblur chama a função pesquisarAluno() com o valor digitado apos cada alteração no valor do input -->
    <input id="id" name="id" placeholder="ID" tabindex="3" required maxlength="10" onblur="pesquisarAluno(this.value)">
  </div>
  <br/>
  <form id="form">
    <div class="field" align="center">
      <label>Nome: </label>
      <input id="nome" name="nome" type="text" class="input-tam-1" disabled>
    </div>
    <br/>
    <div class="field" align="center">
      <label>Status: </label>
      <input id="status" name="status" type="text" class="input-tam-1" disabled>
    </div>
    <br/>
    <div class="field" align="center">
      <label>Documento: </label>
      <input id="documento" name="documento" type="text" class="input-tam-1" disabled>
    </div>
    <br/>
    <div class="field" align="center">
      <label>Nascimento: </label>
      <input id="nascimento" name="nascimento" type="text" class="input-tam-1" disabled>
    </div>
    <br/>
    <div class="field" align="center">
      <label>Instituição: </label>
      <input id="instituicao" name="escola" type="text" class="input-tam-1" disabled>
    </div>
    <br/>
    <div class="field" align="center">
      <label>Validade: </label>
      <input id="validade" name="validade" type="text" class="input-tam-1" disabled>
    </div>
    <br/>
  </form>
</body>
</html>

index.js file

Your intention is to use pure ajax, without using jquery, I would mount this way:

// Função cria um token e seta o valor no input com id token
var iniciarSessao = function(){
    var request = new XMLHttpRequest();
    // chamo o script php inicializa-sessao.php criado anteriormente
    request.open('GET', 'inicializa-sessao.php', true);
    request.onload = function() {
        var data = JSON.parse(request.responseText);
        if (request.status == 200) {
            document.getElementById("token").value = data.token;
        }else{
            alert(data.msg);
        }
    };
    request.send();
}

// Função pesquisa o aluno e seta os valores de retorno em seus respectivos campos 
var pesquisarAluno = function(id){
    var token = document.getElementById("token").value;
    var request = new XMLHttpRequest();
    // chamo o script php consulta.php setando como query string token e id_aluno
    request.open('GET', 'consulta.php?token='+token+'&id_aluno='+id, true);
    request.onload = function() {
        var data = JSON.parse(request.responseText);
        if (request.status == 200) {
            document.getElementById("retorno").innerHTML = "";
            document.getElementById("nome").value = data.nome;
            document.getElementById("status").value = data.status;
            document.getElementById("documento").value = data.documento;
            document.getElementById("nascimento").value = data.nascimento;
            document.getElementById("instituicao").value = data.instituicao;
            document.getElementById("validade").value = data.validade;
        }else{
            form.reset();
            document.getElementById("retorno").innerHTML = "<b>"+data.msg+"</b>";
        }
    };
    request.send();
}

Query file.php

Last but not least the file that will make the query on the server

<?php
    // Inicializo a sessão que já foi criada anteriormente
    session_start();
    $token = ( empty( $_GET['token'] ) ? "" : $_GET['token'] );
    // Seto o cabeçalho html para aceitar requisições de qualquer lugar, utilizando o método GET, e que responda no formato json
    header('Access-Control-Allow-Origin: *');
    header('Access-Control-Allow-Methods: GET');
    header('Content-Type: application/json; charset=utf-8');
    /*
    As funções abaixo habilitam um modo de depuração mais adequado pra um ambiente de homologação ou de testes, não recomendo usar em produção por que possibilita expor a sua infraestrutura ou suas funções para quem acessar o script de fora
    error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE | E_STRICT);
    ini_set('display_errors', 'On');
    */
    // Se o token não estiver mais setado, significa que a conexão expirou, então retorno um http status 401 Unauthorized (Não autorizado), ou seja a pessoa que tentou acessar não está mais autorizada a utilizar o mesmo token
    if( ! isset( $_SESSION['token'] ) ){
        http_response_code(401);
        die('{"msg": "Conexão perdida."}');
    }
    // Se o token estiver vazio, ou for diferente do token de sessão, provavelmente houve alguma tentativa de burlar o sistema, retorno o mesmo http status 401 Unauthorized (Não autorizado)
    if( empty( $_SESSION['token'] ) || $_SESSION['token'] != $token ) {
        http_response_code(401);
        die('{"msg": "Acesso Negado."}');
    }
    // Se não houver a váriavel id_aluno ou ela vier vazia, retorno o http status 404 Not Found (Não Encontrado)
    if( empty($_GET['id_aluno']) ) {
        http_response_code(404);
        die('{"msg": "Aluno não encontrado."}');
    }

    // Se não houver nenhum erro até aqui, posso começar o sistema sem mais preocupações, insiro as constantes da conexão com o banco de dados e as funções auxiliares que poderão ser utilizadas
    require_once "conexao.php";
    require_once "funcoes.php";

    try {
        // Crio a variável de conexão dentro de um bloco try...catch
        $db = new PDO("mysql:host=".host.";dbname=".schema.";charset=utf8;", user, password);
        /*
        Só deve ser usado se uma transação do tipo insert, alter ou delete for necessária
        $db->beginTransaction();
        */
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        http_response_code(500);
        create_log("logs/db_errors","Conexão".PHP_EOL."Exceção: ".$e);
        die('{"msg": "Erro interno do servidor."}');
    }

    $retorno = procurar_usuario($db, "id", "*");
    if(empty($retorno)){
        http_response_code(404);
        die('{"msg": "Aluno não encontrado."}');
    }else{
        die(json_encode($retorno));
    }

    function procurar_usuario($db, $parameter, $arguments) {
        switch ($parameter) {
            case 'id':
                $sql = "SELECT ".$arguments." FROM aluno WHERE id_aluno = ?";
                try {
                    $stmt = $db->prepare($sql);
                    $stmt->execute(array( 
                        $_GET['id_aluno']
                    ));
                    $db_data = $stmt->fetch(PDO::FETCH_OBJ);
                } catch (PDOException $e) {
                    http_response_code(500);
                    create_log( "logs/db_errors", "Search customer".PHP_EOL.$parameter.PHP_EOL.$arguments.PHP_EOL."Exception: ".$e );
                    die('{"msg": "Erro interno do servidor."}');
                }
                break;
            default:
                http_response_code(404);
                die('{"msg": "Método não encontrado."}');
        }
        return $db_data;
    }
?>

To understand the functioning of the block Try...catch

In the end, the face of your system should be more or less that:

inserir a descrição da imagem aqui

Developing response.

  • I created the table you assumed, even tested it and ok... But what do I put in the file initializes.php? Pq calls it in js and inserts the token in the request with the studentid_, as I do to generate the token?

  • I’m running on Chrome but did not return with the results, in the Console is giving Uncaught Referenceerror: startSessao is not defined at onload ((index):15)... I don’t understand, the js is called in the head

  • I did it! I ended up adding a character unintentionally in index.js He consulted the right bank, no problem! How do I insert a button that after the query is in focus to refresh the page and clear the form? That is... The person enters the code and with a Enter they can clear to consult the next

  • @Williamgerrit understand that the question itself should have already been closed for the reason of not having a defined scope and for being very broad, see that the answer was immense, outside the community standards.... I suggest you read Manual on how NOT to ask questions and mainly Thinking we’ll do all your work for free.

  • I suggest first of all that if there are any more questions, ask another question, and set a scope with a [mcve] and still do the [tour] to understand how the Sopt community works

Browser other questions tagged

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