How to display the result of an Inner Join for the user?

Question

I have three tables and want to join them and display them to the user, how can I do this using the functions of Wordpress?

INSERTS THE DATA INTO THE DATABASE:

function cadastra_experiencia($nome, $email, $experiencia){          //INSERE OS DADOS NO BANCO
     global $wpdb;

    $table = 'experiencia';

    $data = array(
      'nome' => $nome,
      'email' => $email,
      'exp' => $experiencia,
    );

    $updated = $wpdb->insert( $table, $data );

    if ( ! $updated ) {
      $wpbb->print_error();
    }
}

function cadastra_alturapesotipo($altura, $peso, $estilo){
//INSERE OS DADOS NO BANCO
     global $wpdb;

    $table = 'alturapesoestilo';

    $data = array(
      'altura' => $altura,
      'peso' => $peso,
      'estilo' => $estilo,
    );

    $updated = $wpdb->insert( $table, $data );

    if ( ! $updated ) {
      $wpbb->print_error();
    }
}

BENCH:

CREATE TABLE EXPERIENCIA(
     exp_pri INT NOT NULL AUTO_INCREMENT,
     nome VARCHAR(150),
     email VARCHAR(50),
     exp VARCHAR(100),
     PRIMARY KEY(exp_pri)
);

CREATE TABLE PRANCHA(
    prancha_pri INT NOT NULL AUTO_INCREMENT,
    tamanho_prancha VARCHAR(8),
    meio_prancha VARCHAR(2),
    litragem_prancha VARCHAR(3),
    PRIMARY KEY (prancha_pri)
);

CREATE TABLE ALTURAPESOESTILO(
    idAltPes INT NOT NULL AUTO_INCREMENT,
    idExp INT,
    idPrancha INT,
    altura VARCHAR(4),
    peso VARCHAR(3),
    estilo VARCHAR(15),
    primary key (idAltPes),
    constraint fk_idExp foreign key (idExp) references EXPERIENCIA (exp_pri),
    constraint fk_idPrancha foreign key (idPrancha) references PRANCHA (prancha_pri)
 );

INNER JOIN:

SELECT EXP.exp,
       AEP.altura,
       AEP.peso,
       AEP.estilo,
       PRAN.tamanho_prancha,
       PRAN.meio_prancha,
       PRAN.litragem_prancha
FROM EXPERIENCIA AS EXP
    INNER JOIN ALTURAPESOESTILO AS AEP ON
    (EXP.exp_pri = AEP.idAltPes)
    INNER JOIN PRANCHA AS PRAN ON
    (PRAN.prancha_pri = AEP.idAltPes)

I put on Sqlfiddle for a better view: http://sqlfiddle.com/#! 9/5eb8a/3

I want to display this inner join for the user (text, Alert, modal), any example/path of how to do this is welcome.

I took a look at the Wordpress Codex, but without much success.

Answer 1

You are using $wpdb->insert,I don’t know if you know but the class wpdb it is easy to inject an attack to SQL, as it interprets some functions of its own class as input,and may end up damaging its database.

For better understanding, read the Wordpress documentation on the subject :

https://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks

Now talking about your problem, fetchAll()? It returns an array containing all rows of the result set and for Inner Join this works perfectly.

There are two functions related to search result lines. The fetchAll() gets all lines at once. And fetch() gets line for line at each call.

But I believe you will use the function fetchAll() to get all the results at once, as it is more convenient when working with inner joins.

Take a look at his documentation.With this command you can easily execute and show the result to the user.

Documentation of the fetchAll(): http://php.net/manual/en/pdostatement.fetchall.php.

Documentation of fetch() : http://php.net/manual/en/pdostatement.fetch.php