How to compare database data and display to user

Question

I’m developing a surf site. In it, there’s a section called My Ideal Surfboard.

Where the user will enter their data (Nome, email, peso, altura and experiencia(beginner, intermediate or advanced)).

I will save this data in the bank and based on user characteristics (weight, height and experience) I will say the ideal board type.

The section is already done and is also already recording the user data in the database. I received a table/reference list with all the board measurements according to the height and weight of the user.

To return the ideal board, I need to take user data and compare with this table.

I thought I’d do it this way,

I divided into three tables/sections: Experiencia, Peso e Altura and Modelo de prancha.

Each table/section I will assign an ID to:

User experience -> ID a,b,c
Weight and Height -> ID a,b,c,d....
Board Model -> ID a,b,c,d....

Table with user experience, one ID for each.

Table with the Weight and Height ratio of the user, an ID for each.

Final table with the board models an ID for each.

To get the result I will somehow compare the table Experiencia with the table Peso+Altura, generating an ID or X number that will be compared with the ID of the third table (with the Board models) and thus obtaining the result. I do not know if it was very clear the explanation, anything can ask me.

My question is: Is that my logic? Is this the best way to do it? In code, how can I do that?

Any kind of help is welcome!

So far I only have the code that registers the data in the database:
--I don’t think the form’s HTML is very useful. If requested, I post it here or in Pastebin.

function cadastrar($nome,$email,$estilo,$experiencia,$altura,$peso){          //INSERE OS DADOS NO BANCO
     global $wpdb;
     // Minha tabela
    $table = 'aa_minhaprancha';

    // Inserindo os dados no array "data", responsável pelos dados a serem gravados no banco
    $data = array(
      'nome' => $nome,
      'email' => $email,
      'estilo' => $estilo,
      'experiencia' => $experiencia,
      'altura' => $altura,
      'peso' => $peso,
    );

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

    // Se não ocorrer o update, retorna o erro
    if ( ! $updated ) {
      $wpbb->print_error();
    }

}

The bank is in Wordpress. However, there is no problem if there is some help in Mysql. I convert.

Recapping:

What I want to do? - Display the ideal board model according to the user’s profile.

As I want/am doing? - Taking user data (Level of experience, Height, Weight and Type of board), comparing them with mine lista de referência and displaying to the user.

How can I do that?

Answer 1

From what you presented, I believe you are looking for an answer that simplifies your process.

So I’d do it this way:

1. Create a single table with all possibilities but by height and weight range (I explain below).

2. Create a unique ID that matches the user’s characteristics, as follows:

   NAAAPPPTTTT

Where:

N = Level of experience (may be more than one digit if applicable), for example N equal to 3 (Advanced)

AAA = Digits of Height (without the score, for example. 1.82 height would equal 182. I suggest you use numbers that indicate height ranges, for example, 180 would meet heights of 1.80 to 1.8499... and 185 of 1.85 to 1.8999... Thus reduces the combinations (better if they are wider ranges, as ten by ten for example)

PPP = Digits of the Heaviness, for example, 96 would be equal to 096 (same height, I suggest to do by weight range, in this example, 96 would be 095, as it corresponds to the range of 95 to 99.999... weightless)

TTTT = Type board (in this case each board shall have a unique code of how many digits need, I did with four digits), for example, board 0324

Thus, for this user the ideal board would have the following ID (with intervals of 5 by 5 for height and weight):

31800950324

If there is a single table with these Ids indexed properly, all that remains is to search.

I hope I’ve helped.

Answer 2

Come on. What you basically want to do is model the database according to surf table data, and divide it so that they have relationships with each other.

For example: You have a table called customer:

CREATE TABLE CLIENTES(
     idCliente INT AUTO_INCREMENT,
     Nome VARCHAR(100),
     Sexo CHAR(1),
     PRIMARY KEY(idCliente)
);

And another table of documents:

CREATE TABLE DOCUMENTOS(
    idDocumento INT AUTO_INCREMENT,
    idCliente INT,
    NOME_DOCUMENTO VARCHAR(100),
    VALOR_DOCUMENTO VARCHAR(200),
    idTipoDocumento INT,
    PRIMARY KEY(idDocumento)
);

And finally the Document Type table:

CREATE TABLE TIPO_DOCUMENTO(
    idTipoDocumento INT AUTO_INCREMENT,
    NOME_DOCUMENTO VARCHAR(100),
    PRIMARY KEY (idTipoDocumento)
);

We will now make some Inserts :

INSERT INTO CLIENTES VALUES (NULL, 'AMERICO SOUZA', 'M');
INSERT INTO CLIENTES VALUES (NULL, 'IGOR SOUZA', 'M');
INSERT INTO CLIENTES VALUES (NULL, 'MARIA QUITANDA', 'F');
INSERT INTO TIPO_DOCUMENTO VALUES (NULL,'RG');
INSERT INTO TIPO_DOCUMENTO VALUES (NULL,'CPF');
INSERT INTO TIPO_DOCUMENTO VALUES (NULL, 'TELEFONE');
INSERT INTO DOCUMENTOS VALUES (NULL, 1, '123456789-67', 1);
INSERT INTO DOCUMENTOS VALUES (NULL, 2, '093.356.879-90', 2);
INSERT INTO DOCUMENTOS VALUES (NULL, 3, '[phone]', 3);

The bank was modeled after the 1:1 relationship, that is, an entity can only relate to just one other entity. Then we can make use of JOIN (Merge) to join the data, since we are making use of relationship.

SELECT CLI.NOME,
   DOC.VALOR_DOCUMENTO,
   TIPO.NOME_DOCUMENTO
FROM CLIENTES AS CLI
   INNER JOIN DOCUMENTOS AS DOC ON 
   (CLI.idCliente = DOC.idCliente)
   INNER JOIN TIPO_DOCUMENTO AS TIPO ON 
   (TIPO.idTipoDocumento = Doc.idTipoDocumento)

And we have the following result:

If you want to filter more results, you can add the WHERE clause in which you will achieve beyond the junction tuples, you can determine for which conditions you want the result.

I believe that with this example you can move forward. For more questions I suggest reading about SQL, JOINS, WHERE and Relationships.

http://sqlfiddle.com/#! 9/13527/1