Search code examples
node.jsangularexpressangular-formsangular-formbuilder

how to save value of formarray in mysql with nodejs

I have a problem and I want someone to help me.
My English is not that good, I'm sorry about that I'll try my best to explain the problem to you hopefully u can help me and thank you.
I'm working on an activity management platform where an employee login to his account and chose a project, then a table contains the days of a certain month under each day there is an input where he enters 1 or 0 if he worked that day or not this is how the UI looks:
this is how the UI look like

When he clicks the button VALIDER (Validate in French) the data entered should be saved in mysql database.
to collect the data I used FormBuilder in angling, I defined as a form group that contains a form control that should get the name of the project, a form control that gets the month, and one for the year, and a form array that should get the values of the 3 inputs, when I console.log the value of the form I get this:

console.log(this.form.value)
when I try to save the data in my database, I get the message successful, but when I look at my database nothing gets stored, my database contain a table with : projectName: varchar(45), month: number, year: number, days: JSON

I think that the problem is that days are an array and not of type Jason because I tried saving an array, but I did like this: insert into project (projectName, days) values ('nomProjet', '['0', '0', '0']') and it gets saved but my days Array doesn't. my node js code for the backend :

app.post('/cra/add', function (req, res) {
    let nomProjet = req.body.projet;
    let year = req.body.year;
    let month = req.body.month;
    let days = req.body.days;
    if (nomProjet && year && month && days) {
        connection.query('INSERT INTO projetcra2 ( nomProjet, month, year, days ) SET ( ? , ? , ? , ?) ',
            [nomProjet, month, year, days],
            function (error, results, fields) {
                res.send({ status: 'success' , days});
                res.end();
            });
    } else {
        res.send({ status: 'failed', message: 'some data are required', loggedin: false });
        res.end();
    }
});

my formbuilder :
formBuilder


my save function to save to the database :

addDaysWorked() {
    this.api.cra(this.form.value).subscribe(
      (data: any) => {
        console.log(data);
      }, (error: HttpErrorResponse) => {
        console.log(error);
      }
    )
 }

when i test with postman :
Postman
my database :

database


I hope my problem is explained, if u think I can help with anything else let me know and thank you.
Solution

  • I'm not an expert at backend stuff by any means. However, I believe storing items as an array is inside of a single column in a database is not ideal. You should consider creating a new, separate table, for just days worked.

    In the table, you could have a column that specified the date, whether or not he/she worked, and link obviously link this using a foreign key (like userId) to the users table or the main table in this case.

    This would allow you to more easily insert the data. Since each day would just be a simple row, querying would also be simpler as you would just query data given a Timeframe (example: from beginning of August - End of August), a user (unique user ID).

    Here are a couple other generic stack questions that might clarify as well.

    Hope this helps!

    Check out this resource as well