How to Create a Wappler Server Action to Save Multiple Days of Data to MySQL

[Partikel]

Hi everyone,

I'm currently working on a project in Wappler where I need to save multiple days' worth of time tracking data to a MySQL database. The data is collected from a form that includes fields for each day of the month, and I need to store this data in a MySQL table.

Here's a brief overview of what I'm trying to achieve:

1. Form Structure: I have an HTML form that collects time tracking data for each day of the month. This includes fields like date, client, job description, start time, end time, breaks, and special conditions for each day.
2. MySQL Table: The data needs to be saved into a MySQL table named time_tracking with columns for each of the form fields.
3. Wappler Server Action: I need to create a Wappler Server Action that processes this form data and saves it to the MySQL table. This action should loop through each day of the month and insert the corresponding data into the database.

Steps to Achieve This:

1. Database Schema:

Here's the structure of my MySQL table:

CREATE TABLE time_tracking (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  date DATE,
  client VARCHAR(255),
  job_description VARCHAR(255),
  shift_number VARCHAR(255),
  state VARCHAR(255),
  location VARCHAR(255),
  start_time TIME,
  end_time TIME,
  break1_start TIME,
  break1_end TIME,
  break1_paid BOOLEAN,
  break2_start TIME,
  break2_end TIME,
  break2_paid BOOLEAN,
  vma BOOLEAN,
  special_conditions VARCHAR(255),
  working_time TIME,
  additional_time TIME,
  night_shift TIME,
  sunday_shift TIME,
  holiday_shift TIME,
  total_working_time TIME,
  total_gross DECIMAL(10,2)
);

2. Form Submission:

The form submits data for each day, and I need to ensure that the server action can handle and save all this data correctly.

Here's a snippet of the form HTML:

const daysInMonth = new Date(year, getMonthIndex(month) + 1, 0).getDate();
// Number of days in the month
const tableBody = document.getElementById('tableBody');

for (let day = 1; day <= daysInMonth; day++) {
  const date = `${year}-${String(getMonthIndex(month) + 1).padStart(2, '0')}-${String(day).padStart(2, '0')}`;
  // Formats the date
  const weekday = new Date(date).toLocaleDateString('en-EN', { weekday: 'short' });
  // Gets the weekday

  tableBody.innerHTML += `
    <tr>
      <td>${day}.${month.slice(0, 3)}.${year}<br>- ${weekday}</td>
      <td><input type="text" id="client_${day}" name="client_${day}" class="form-control" value="ERIXX SH"></td>
      <td>
        <select id="jobDescription_${day}" name="jobDescription_${day}" class="form-control" onchange="updateJobDescription(${day})" onchange="calculateTime(${day}, '${date}')" required>
          <option value="">None</option>
          <option value="Shift">Shift</option>
          <option value="U">Vacation</option>
          <option value="K">Sick</option>
          <option value="Rest">Rest</option>
        </select>
        <input type="text" id="shiftNumber_${day}" name="shiftNumber_${day}" class="form-control d-none" placeholder="Shift No." onchange="calculateTime(${day}, '${date}')">
      </td>
      <td><input type="text" id="state_${day}" name="state_${day}" class="form-control" value="SH"></td>
      <td><input type="text" id="location_${day}" name="location_${day}" class="form-control"></td>
      <td><input type="time" id="start_${day}" name="start_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="time" id="end_${day}" name="end_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="time" id="break1From_${day}" name="break1From_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="time" id="break1To_${day}" name="break1To_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="checkbox" id="break1Paid_${day}" name="break1Paid_${day}" class="form-check-input" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="time" id="break2From_${day}" name="break2From_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="time" id="break2To_${day}" name="break2To_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="checkbox" id="break2Paid_${day}" name="break2Paid_${day}" class="form-check-input" onchange="calculateTime(${day}, '${date}')"></td>
      <td><input type="checkbox" id="vma_${day}" name="vma_${day}" class="form-check-input" disabled></td>
      <td>
        <select name="specialConditions_${day}" id="specialConditions_${day}" class="form-control" onchange="calculateTime(${day}, '${date}')">
          <option value="">None</option>
          <option value="driverTraining">Driver Training</option>
        </select>
      </td>
      <td><input type="text" id="workingTime_${day}" name="workingTime_${day}" class="form-control" readonly></td>
      <td><input type="text" id="overtime_${day}" name="overtime_${day}" class="form-control" readonly></td>
      <td><input type="text" id="night_${day}" name="night_${day}" class="form-control" readonly></td>
      <td><input type="text" id="sunday_${day}" name="sunday_${day}" class="form-control" readonly></td>
      <td><input type="text" id="holiday_${day}" name="holiday_${day}" class="form-control" readonly></td>
    </tr>
  `;
  // Dynamically adds rows for each day of the month
}

Request for Help:

I would greatly appreciate any guidance or examples on how to create the Wappler Server Action to loop through each day's form data and insert it into the MySQL table. Specifically, I need help with:

• Setting up the server action to process multiple records.
• Handling potential errors during the data insertion.
• Ensuring data integrity and avoiding duplicate entries.

Thank you in advance for your assistance!

[Hyperbytes]

I think you need to look at form repeats to make that front end more compatible with Wappler SAs

[Partikel]

Hello,
do you have any ideas on how to do this? It's my first time actively working with Wappler.

Thanks!

[famousmag]

Have a look of that sample here:

Can we Repeat Table Rows Only while using Form Repeat

You just need to move the form repeater code on the tbody: <div class="container-fluid mt-2"> <div class="row"> <div class="col"> <form id="frm_addExp" is="dmx-serverconnect-form" method="post" action="/api/instProfile/staffProfile/teaching/addStaffModal/upd_staffExp" dmx-on:success="notifies1.success('Experience Info Saved')"> <div class="row"> <div class="col"> <div class="form-group mb-3"> …

If you have difficulties on accomplishing it, ask here details on what exactly you don't understand😉

[Partikel]

I am looking for someone who can create a table in Wappler that automatically generates based on the month specified in the domain query. For example, if the domain is test.com/timesheet?month=May2024, the table should dynamically determine how many days are in the month and create a table accordingly.

I only require the logic for creating the table to be implemented. If you can successfully create an error-free and queryable table in this format, I will offer you $100 as a reward.

Please reach out if you have the skills to complete this task!

The Image was Auto Translated

[Partikel]

If someone has anymore questions feel free to ask.

[Cheese]

@Partikel

month=May2024

ChatGPT offers the following solution...

NodeJS:

Here’s how you can create a MySQL table in Node.js based on a URL parameter for the month and year (e.g., month=May2024).

Step 1: Set Up Your Project

First, ensure you have the required packages installed. You'll need express for handling HTTP requests and mysql for connecting to the MySQL database. You can install them using npm:

npm install express mysql

Step 2: Create the Server

Here’s a complete example of a simple Node.js server that handles the URL parameter and creates a MySQL table accordingly:

const express = require('express');
const mysql = require('mysql');
const app = express();
const port = 3000;

// MySQL connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'your_database',
});

// Connect to MySQL
connection.connect((err) => {
    if (err) throw err;
    console.log('Connected to MySQL');
});

// Helper function to convert month name to number
const getMonthNumber = (monthName) => {
    return new Date(Date.parse(monthName + " 1, 2021")).getMonth() + 1;
};

// Endpoint to handle the request
app.get('/create-table', (req, res) => {
    const monthYear = req.query.month; // Get the parameter from the URL

    // Validate the input
    const regex = /([A-Za-z]+)(\d{4})/;
    const matches = monthYear.match(regex);
    
    if (!matches) {
        return res.status(400).send('Invalid month and year format. Use: month=May2024');
    }

    const month = matches[1];
    const year = matches[2];
    const monthNumber = getMonthNumber(month);
    const tableName = `data_${year}_${monthNumber}`;

    // Create table query
    const sql = `
        CREATE TABLE IF NOT EXISTS \`${tableName}\` (
            id INT AUTO_INCREMENT PRIMARY KEY,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            data VARCHAR(255) NOT NULL
        )`;

    // Execute the query
    connection.query(sql, (err, results) => {
        if (err) {
            return res.status(500).send('Error creating table: ' + err.message);
        }
        res.send(`Table ${tableName} created successfully.`);
    });
});

// Start the server
app.listen(port, () => {
    console.log(`Server is running on http://localhost:${port}`);
});

Explanation:

1. Express Setup: The code sets up an Express server that listens for GET requests at the /create-table endpoint.
2. MySQL Connection: It establishes a connection to the MySQL database using the provided credentials.
3. Month Conversion: A helper function getMonthNumber converts the month name to its corresponding number.
4. Request Handling: When a request is made to /create-table, the server extracts the month query parameter, validates it, and constructs the table name.
5. Table Creation: A SQL query is executed to create the table if it doesn't already exist, with an id column and a data column.
6. Response: The server sends a success message if the table is created successfully or an error message if something goes wrong.

Run the Server

To run the server, save the code to a file (e.g., server.js) and run:

node server.js

Then you can test it by navigating to:

http://localhost:3000/create-table?month=May2024

Make sure to adjust the database connection details to match your setup!

[Teodor]

@Partikel

For example, if the domain is test.com/timesheet?month=May2024, the table should dynamically determine how many days are in the month and create a table accordingly.

You can easily determine the number of days in a month by creating a custom formatter.

Make sure the moment.js framework is added on the page:

Then add this code in the head tags, just before the closing </head> tag:

<script>
    dmx.Formatter('string', 'monthDays', function(val, format) {
        const date = moment(val, 'MMMMYYYY');
        return date.daysInMonth();
    });
</script>

On the page you can then use:

{{query.month.monthDays()}}

to get the number of days in a month, so you can use this number to create your repeat region for the table.

test.com/timesheet?month=May2024
Outputs 31

test.com/timesheet?month=November2024
Outputs 30

test.com/timesheet?month=February2025
Outputs 28

[Partikel]

Hey first of all thanks for the Help!

This is my test code can't it work like this?:

<!-- Wappler include head-page="layouts/admin" fontawesome_5="cdn" bootstrap5="local" is="dmx-app" id="abrechnung" appConnect="local" components="{dmxBootstrap5Modal:{}}" -->
<meta name="ac:route" content="/activesession/abrechnung">

<!-- Main Container -->
<div class="container-fluid">

    <div class="row">
        <!-- Erste Zeile -->
        <div class="col mt-5">
            <h2 id="pageTitle">Timesheet for Month: </h2>
            <!-- Titel der Seite mit dynamischem Monat und Jahr -->
        </div>
        <div>
            Days in the Month: {{query.month.monthDays()}}
        </div>
    </div>


</div>

<script>
    dmx.Formatter('string', 'monthDays', function(val, format) {
        const date = moment(val, 'MMMMYYYY');
        return date.daysInMonth();
    });
  
</script>

[Teodor]

You need to include the moment js framework on your main page, as shown on the screenshot above.