Problems with CRUD using Mysql

Question

Good evening folks, I come to ask for help regarding the insertion and query of Mysql using C. I am trying to create a simple CRUD, however I am having problems with the query method.

I wanted the method to display the following: Name, enrollment, course, telephone and email in the first row and just below the tuple information, one by one in an organized way.

However it displays totally disorganized information, a tuple is occupying more than one row, and the names of the table columns do not appear correctly:
Me using Codeblocks 17.12 with the GNU compiler

University database tables:

create table aluno(

nome varchar(100) not null,
matricula int,
curso varchar(50) not null,
telefone varchar(20) not null,
email varchar(75), 
primary key(matricula)

); 

create table professor(

nome varchar(100) not null,
matricula int,
disciplina varchar(50) not null,
telefone varchar(20) not null,
email varchar(75),
primary key(matricula)

);

Global variables, include and define:

#include <stdio.h>
#include <windows.h>
#include <winsock2.h>
#include <mysql.h>
#include <string.h>

#define NOME 100
#define CURSO 40
#define TEL 20
#define EMAIL 75

MYSQL conexao;
int statusConexao;
char query[100];

Methods (I put only those that involve the problem):

void cadastroAluno(void){

mysql_init(&conexao);
char nome[NOME];
int matricula;
char curso[CURSO];
char telefone[TEL];
char eMail[EMAIL];

system("cls");
printf("Cadastro de alunos\n\n");
setbuf(stdin,NULL);

printf("Digite o nome:");
fgets(nome,NOME,stdin);
nome[strcspn(nome,"\n")] = 0;

printf("Digite a matricula:");
scanf("%d",&matricula);

setbuf(stdin,NULL);

printf("Digite o curso:");
fgets(curso,CURSO,stdin);
curso[strcspn(curso,"\n")] = 0;

setbuf(stdin,NULL);

printf("Digite o telefone:");
fgets(telefone,TEL,stdin);
telefone[strcspn(curso,"\n")] = 0;

setbuf(stdin,NULL);

printf("Digite o e-mail:");
fgets(eMail,EMAIL,stdin);
eMail[strcspn(eMail,"\n")] = 0;

setbuf(stdin,NULL);


    if(mysql_real_connect(&conexao,"localhost", "root", "", "universidade", 3306, NULL, 0)){

        sprintf(query,"INSERT INTO aluno(nome,matricula,curso,telefone,email) values('%s','%d','%s','%s','%s');",nome,matricula,curso,telefone,eMail);

       statusConexao = mysql_query(&conexao,query);

       if(!statusConexao){
            printf("Cadastro concluído com sucesso\nLinhas afetadas:%d\n",mysql_affected_rows(&conexao));
       }else{
            printf("Falha no cadastro.Tente novamente\n");
       }
       system("PAUSE");
       mysql_close(&conexao);

    }else{

        printf("Falha na conexão ao banco de dados\n");
        printf("Erro %d: %s\n", mysql_errno(&conexao), mysql_error(&conexao));

    }

 }

---

void consultaAlunoSim(void){

system("cls");
mysql_init(&conexao);
MYSQL_RES * resp;
MYSQL_ROW linhas;
MYSQL_FIELD * campos;
int contador;
char querySimples[] = "SELECT * FROM aluno;";

if(mysql_real_connect(&conexao, "localhost", "root", "", "universidade", 3306, NULL, 0)){

    if(mysql_query(&conexao,querySimples)){
        printf("Erro: %s\n:",mysql_error(&conexao));
    }else{

        resp = mysql_store_result(&conexao);

        if(resp){

            campos = mysql_fetch_field(resp);

            for(contador = 0; contador < mysql_num_fields(resp); contador++){
                printf("%s",(campos[contador].name));

                if(mysql_num_fields(resp) > 1){
                    printf("\t");
                }

            }

            printf("\n");

            while((linhas = mysql_fetch_row(resp))!= NULL){

                for(contador = 0; contador < mysql_num_fields(resp); contador++){
                    printf("%s\t",linhas[contador]);
                }
                printf("\n");

            }
        }

        system("PAUSE");
        mysql_free_result(resp);
        mysql_close(&conexao);

    }

}else{
    printf("Falha na conexão ao banco de dados\n");
    printf("Erro %d : %s\n",mysql_errno(&conexao),mysql_error(&conexao));
  }

}

Answer 1

As for the problem you detected itself, it is only a matter of output formatting - the C compiler will not invent a way to abbreviate a name of about 40 characters "José dos Santos Pereira" in a column of just over 6 characters "name ". You have to make some sophisticated code to leave a space appropriate for the width of the columns, and possibly abbreviate the content of columns that are larger - That is, in possession of the query results, check the maximum size of the "name" and add an appropriate number of spaces after the word "name" at the top of the column. And also create a way to align the results in the other columns - if a name has 20 characters, and the second row has 30 characters, the second column (matricula), it can only have its values printed from column 31. You are using the tab character (\t) that does this magic - but only for fields that vary up to 7 characters in length. tab is at most 8 spaces wide.

In fact what happens is that to show well formatted data in a text terminal is far away of being one of the simplest tasks, and the use of the C language, which is a comparatively low-level language, to manipulate textual data does not help.

For comparison, the link below is to a code in Python that does this - displays tabular results in the terminal - it is the part of the application that does just this, the data itself is passed as parameters of other points of the same application - this is an application for "production"more sophisticated, that can draw frames around the columns, etc... and even it does not have a mechanism to abbreviate the size of the data in the columns. But it has all the part that would be needed in your code to calculate the width of each column before printing the table, etc...

https://github.com/turicas/rows/blob/develop/rows/plugins/txt.py

In short - it’s nice that you play with this code for learning - but if you’re really developing an application for use that will manipulate this data in the database the strong suggestion is:

1) Move to another programming language - (where for example, you don’t have to worry about the byte size of the query buffer for Mysql, as I mentioned in the question). I suggest studying a very high-level dynamic language like Python, Javascript or Ruby (PHP already has some idiosyncrasies that can introduce addictions to your learning stage - I wouldn’t recommend it - in particular you’ll be very tempted to mix code with presentation)

2) Even going to use such an application, the use of the terminal from a certain point will complicate the application for both you and developer (as you can see from the link above, using Python does not magically reduce the complexity of displaying a table in the terminal correctly to zero). So it’s nice to take a web framework and develop the web application - it’s much simpler to put the query output in a bnaco where each field is within a tag <td>...</td> than manually worrying about the table width (additionally, with the html output, you can separately, with CSS, limit the width of the columns and configure to truncate the data, or display scroll bars in your cells).

3) If you want to stay in C to learn anyway, I would still recommend changing the focus of your application to generate outputs in files of type ". csv", which you can open in a spreadsheet program - and you can focus on the control and operation of the database itself in your app. (Note that in the languages I mentioned above, interacting with the database is also proportionally easier).

(As for your current code I don’t even know how you got the partial result with the column names - your image shows 5 columns, but you only make a call to mysql_fetch_field which is what returns information, including the column name, and does not do this in any loop - since the function only brings the information from one column at a time: https://dev.mysql.com/doc/refman/5.7/en/mysql-fetch-field.html

That is, if this is the code that actually generates the result you show, the expression campos[contador].name is only working by chance because of an undocumented feature of the mysql function)