What steps can I take to ensure my dashboard table is dynamic, updates in real-time, and automatically reflects changes made in my MySQL database

FrontEnd Code

import React, { Component, useState, useEffect } from "react";
import Navbar from "../Navbar/Navbar.js";
import BarChart from "../BarChart/BarChart";
import {
  Chart,
  Tooltip,
  CategoryScale,
  LinearScale,
  Title,
  LineController,
  LineElement,
  PointElement,
} from "chart.js";
import Card from "../Card/Card.js";
import CardHeader from "../Card/CardHeader.js";
import CardIcon from "../Card/CardIcon.js";
import CardBody from "../Card/CardBody";
import CardFooter from "../Card/CardFooter";
import GridItem from "../Grid/GridItem.js";
import GridContainer from "../Grid/GridContainer.js";
import { makeStyles } from "@material-ui/core/styles";
import Table from "../Table/Table";
import Icon from "@material-ui/core/Icon";
import Danger from "../Typography/Danger.js";
import Warning from "@material-ui/icons/Warning";
import DateRange from "@material-ui/icons/DateRange";
import Update from "@material-ui/icons/Update";
import Store from "@material-ui/icons/Store";
import LocalOffer from "@material-ui/icons/LocalOffer";
import Accessibility from "@material-ui/icons/Accessibility";
// import { Icon, InlineIcon } from '@iconify/react';
import contentCopy from "@iconify-icons/mdi/content-copy";
import styles from "../../assets/jss/material-dashboard/views/dashboardStyle.js";

import Map from "../Map/map";

import Axios from "axios"; //axios library to make requests to api
import "./Home.css";
import ReactPaginate from "react-paginate";
import ReactExport from "react-data-export";

const ExcelFile = ReactExport.ExcelFile;
const ExcelSheet = ReactExport.ExcelFile.ExcelSheet;

Chart.register(
  Tooltip,
  CategoryScale,
  LinearScale,
  Title,
  LineController,
  LineElement,
  PointElement
);

var i = 0;

function Home(props) {
  //make an axios request to get intents data from database
  const [intentsList, setintentsList] = useState([]);
  useEffect(() => {
    Axios.get("http://localhost:3001/intents").then((response) => {
      setintentsList(response.data);
    });
  }, []);

  const [customerList, setCustomerList] = useState([]); //store all that information of the database in a list
  //make an axios request to get information from database
  useEffect(() => {
    Axios.get("http://localhost:3001/customers").then((response) => {
      setCustomerList(response.data);
    });
  }, []);

  const updateCustomerContacted = (ID) => {
    Axios.put("http://localhost:3001/update", {
      contacted: newContacted,
      ID: ID,
    }).then((response) => {
      setCustomerList(
        customerList.map((val) => {
          const dateStr = new Date(val.latest_time_of_visit).toLocaleDateString(
            "en-CA"
          );
          const timeStr = new Date(
            val.latest_time_of_visit
          ).toLocaleTimeString();
          const dateTime = `${dateStr} ${timeStr}`;

          return val.ID == ID
            ? {
                ID: val.ID,
                name: val.name,
                email: val.email,
                counts_of_visit: val.counts_of_visit,
                latest_time_of_visit: dateTime,
                contacted: newContacted,
              }
            : val;
        })
      );
    });
  };
  //function to format the datetime to correct format
  const formatDatetime = (datetime) => {
    const dateStr = new Date(datetime).toLocaleDateString("en-CA");
    const timeStr = new Date(datetime).toLocaleTimeString();
    return `${dateStr} ${timeStr}`;
  };

  //function to format serial number manually

  //delete function
  const deleteCustomer = (ID) => {
    Axios.delete(`http://localhost:3001/stats/delete/${ID}`).then(
      (response) => {
        setCustomerList(
          customerList.filter((val) => {
            return val.ID != ID;
          })
        );
      }
    );
  };

  //pagination
  const [pageNumber, setPageNumber] = useState(0);
  const customersPerPage = 5; //change this number according to desired number of rows in a page
  const pagesVisited = pageNumber * customersPerPage;
  const displayCustomers = customerList
    .slice(pagesVisited, pagesVisited + customersPerPage)
    .map((val, key) => {
      const dateStr = new Date(val.latest_time_of_visit).toLocaleDateString(
        "en-CA"
      );
      const timeStr = new Date(val.latest_time_of_visit).toLocaleTimeString();
      const dateTime = `${dateStr} ${timeStr}`;
      const my_serial = key + pageNumber * customersPerPage;
      return (
        <tr>
          
          <td>{my_serial + 1}</td>
          <td>{val.name}</td>
          <td>{val.email}</td>
          <td>{val.counts_of_visit}</td>
          <td>{dateTime}</td>
          <td>{val.contacted}</td>
          <td>
            <select
              onChange={(event) => {
                setNewContacted(event.target.value);
              }}
            >
              <option value="" selected disabled hidden>
                Select Yes/No
              </option>
              <option value="Yes">Yes</option>
              <option value="No">No</option>
            </select>
            <button
              className="btn btn-primary"
              onClick={() => {
                updateCustomerContacted(val.ID);
              }}
            >
              Update
            </button>
          </td>
          <td>
            <button
              className="btn btn-danger"
              onClick={() => {
                deleteCustomer(val.ID);
              }}
            >
              Delete
            </button>
          </td>
        </tr>
      );
    });
  //to account for the fact that total number of customers cannot be divided equally among the pages
  const pageCount = Math.ceil(customerList.length / customersPerPage);
  //page change
  const changePage = ({ selected }) => {
    setPageNumber(selected);
  };

  //update contacted column
  const [newContacted, setNewContacted] = useState(0);

  

  //export to csv function

  const DataSet = [
    {
      columns: [
        {
          title: "S/N",
          style: { font: { sz: "18", bold: true } },
          width: { wpx: 125 },
        }, // width in pixels
        {
          title: "Customer Information",
          style: { font: { sz: "18", bold: true } },
          width: { wpx: 250 },
        }, // width in pixels
        {
          title: "Customer Email",
          style: { font: { sz: "18", bold: true } },
          width: { wpx: 250 },
        }, // width in pixels
        {
          title: "Counts of Visit",
          style: { font: { sz: "18", bold: true } },
          width: { wpx: 175 },
        }, // width in pixels
        {
          title: "Latest Time of Visit",
          style: { font: { sz: "18", bold: true } },
          width: { wpx: 250 },
        }, // width in pixels
        {
          title: "Contacted?",
          style: { font: { sz: "18", bold: true } },
          width: { wpx: 250 },
        }, // width in pixels
      ],
      data: customerList.map((val, key) => [
        { value: key + 1, style: { font: { sz: "14" } } },
        { value: val.name, style: { font: { sz: "14" } } },
        { value: val.email, style: { font: { sz: "14" } } },
        { value: val.counts_of_visit, style: { font: { sz: "14" } } },
        {
          value: formatDatetime(val.latest_time_of_visit),
          style: { font: { sz: "14" } },
        },
        { value: val.contacted, style: { font: { sz: "14" } } },
      ]),
    },
  ];

  const useStyles = makeStyles(styles);
  const classes = useStyles;

  return (
    <div>
      <Navbar />
      <GridContainer>
        <GridItem xs={12} sm={6} md={6}>
        
     
        
    

           
       
        <GridItem xs={12} sm={12} md={12}>
         
            
            
          
        
           
            
              
                      <th>S/N</th>
                      <th>S/N</th>
                       
                       
        
        
                 
                  
                  
                    <table className="customertable">
                   
                  <tbody>{displayCustomers}</tbody>
                </table>
                
                
            
             
             
            <ButtonGroup aria-label="outlined primary button group"><button variant="contained" color="primary" size="small" href=".csv">Download CSV</button ></ButtonGroup ><br />
         
            


             
   
                    
       </CardContent>
             
    
                        
                            
                      
                          
               

    
            
        </GridItem>
      </GridContainer>
    </div>
  );
}

export default Home;

BACKEND CODE BACKEND CODE BACKEND CODE BACKEND CODE

//set up express server
const express = require("express");
const app = express();
//set up sql server
const mysql = require("mysql");
const cors = require("cors");

app.use(cors());
app.use(express.json());
//create a variable called db to make your SQL Statements
const db = mysql.createConnection({
  user: "",
  host: "",
  password: "",
  database: "",
});

//GET REQUEST to database to retrieve customers information from database
app.get("/customers", (req, res) => {
  db.query("SELECT * FROM customer_info", (err, result) => {
    if (err) {
      console.log(err);
    } else {
      res.send(result);
    }
  });
});

//Update customers
app.put("/update", (req, res) => {
  const ID = req.body.ID;
  const contacted = req.body.contacted;

  db.query(
    "UPDATE customer_info SET contacted = ? WHERE ID = ?",
    [contacted, ID],
    (err, result) => {
      if (err) {
        console.log(err);
      } else {
        res.send(result);
      }
    }
  );
});

//Delete customers
app.delete("/stats/delete/:ID", (req, res) => {
  const ID = req.params.ID;

  db.query("DELETE FROM customer_info WHERE ID = ?", ID, (err, result) => {
    if (err) {
      console.log(err);
    } else {
      res.send(result);
    }
  });
});

//api call to pull intents data
app.get("/intents", (req, res) => {
  db.query("SELECT * FROM data_analytics", (err, result) => {
    if (err) {
      console.log(err);
    } else {
      res.send(result);
    }
  });
});

//check if backend server is running
app.listen(3001, () => {
  console.log("Your server is running on port 3001");
});

HELLO SO HOW CAN I make my dashboard table generated be real-time --> meaning to say every time there is a change to mySQL database be it CRUD operations, read, create, update or delete, the table will automatically refresh it self so multiple users can use it at the same time and see the changes in real time

javascriptcssreactjsfrontendbackend

Answer №1

Have you considered utilizing the built-in index feature within your map function?

It seems like you already have the necessary variables for current page and items per page based on your code.

For example:

myArray.map((val, idx) => {
    const myIndex = idx + (pageNumber * itemsPerPage)
  })

EDIT: Here are more details added:

const [displayItems, setDisplayItems] = useState([]);
useEffect(() => {
    const displayData = itemList
      .slice(pagesVisited, pagesVisited + itemsPerPage)
      .map((val, key) => {
        const dateStr = new Date(val.latest_time_of_visit).toLocaleDateString(
          'en-CA'
        );
        const timeStr = new Date(val.latest_time_of_visit).toLocaleTimeString();
        const dateTime = `${dateStr} ${timeStr}`;
        return (
          <tr>
            {/*}
          <td>{val.ID}</td>
      */}
            <td>{i++}</td>
            <td>{val.name}</td>
            <td>{val.email}</td>
            <td>{val.counts_of_visit}</td>
            <td>{dateTime}</td>
            <td>{val.contacted}</td>
            <td>
              <select
                onChange={(event) => {
                  setNewContacted(event.target.value);
                }}
              >
                <option value="" selected disabled hidden>
                  Select Yes/No
                </option>
                <option value="Yes">Yes</option>
                <option value="No">No</option>
              </select>
              <button
                className="btn btn-primary"
                onClick={() => {
                  updateItemContacted(val.ID);
                }}
              >
                Update
              </button>
            </td>
            <td>
              <button
                className="btn btn-danger"
                onClick={() => {
                  deleteItem(val.ID);
                }}
              >
                Delete
              </button>
            </td>
          </tr>
        );
      });
    setDisplayItems(displayData);
  }, [itemList]);

You can choose to iterate over 

<tbody>{displayItems}</tbody>
directly by mapping on displayItems and returning the values, or incorporate this logic directly into your HTML output to save on creating an array of components.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Styles are absent from the NextJS 13.4 App Router Middleware Page Redirect feature

Currently, I have implemented middleware that redirects users if they are not on the sign-in screen. This is only a test scenario, and in the future, it will check for a valid authentication session before redirecting. However, after the redirect, the pag ...

reactjshttp-redirectnext.jsmiddleware

Placing markers on a straight path

Struggling to create a CSS component where the first and last points don't align properly with the ends of the line. This component should be able to handle any number of points (between 1 and 4) without relying on flexbox. I have a React component ...

htmlcss

IE9 causing trouble with CSS vertical alignment

I'm trying to center a button with text using Cuffon for the font, but I can't get it to align vertically and horizontally. The text-align property is working fine, but vertical align isn't. Here's the code for my div block: btndownlo ...

htmlcss

Guide on showcasing an array of objects in HTML with the help of the template element

My goal was to populate the HTML page with an array of objects using the template element. However, I made a mistake and only the last object in the array was displayed correctly on the page. Can anyone help me identify my error and suggest a correction? I ...

javascripthtml

Unlocking $refs with the Composition API in Vue3 - A step-by-step guide

I am currently exploring how to access $refs in Vue 3 using the Composition API. In my template, I have two child components and I specifically need to obtain a reference to one of them: <template> <comp-foo /> <comp-bar ref="ta ...

javascripttypescriptvue.jsvuejs3

Getting the URL path within getStaticPaths in Next.js

Is there a way to retrieve the last number from the current URL pathnames in getStaticPaths? http://localhost:3000/category/food/2 -> 2, http://localhost:3000/category/food/3 -> 3, ... I have attempted: export const getStaticPaths: GetStaticPaths = ...

javascriptnext.js

Update the function to be contained in a distinct JavaScript file - incorporating AJAX, HTML, and MySQL

Currently, I am working on a project and need to showcase a table from MySQL on an HTML page. The JavaScript function in my code is responsible for this task, but due to the Framework 7 requirement, I must separate the function into a different .js file ra ...

javascripthtmlmysqlajax

Determine changes in data retrieved from a JSON file using React

I've been working on a cryptocurrency app using React and a JSON API to fetch the latest data. My approach involves using fetch to load the JSON API and setInterval to refresh the app every 10 seconds. Now, I'm wondering if there's a way to ...

javascriptjsonreactjsfetch

Is it possible to include numbers and commas in JQuery Validation?

I have implemented a jQuery validation plugin to validate the fields of a form. One specific requirement is to validate a field to only allow commas and numbers. Below is the HTML code snippet: <input type="text" placeholder="Number of Employees" requ ...

javascriptjqueryhtmlvalidation

What is the process of transforming a PSD file into a responsive HTML file?

I have a PSD file with multiple images that I need to display on my responsive website. The challenge is that when the images are displayed inside the webpage, their position needs to be set to absolute in order to maintain layout integrity. However, when ...

csshtml

Dependency on the selection of items in the Bootstrap dropdown menu

I am currently struggling with a few tasks regarding Bootstrap Dropdown Selection and despite looking for information, I couldn't find anything helpful. Check out my dropdown menu Here are the functions I would like to implement: 1) I want the subm ...

javascriptjqueryhtmlcsstwitter-bootstrap

Limiting page entry with passport.js and express middleware

My server is set up to authenticate user login. I have successfully redirected users to the success page after authentication (and back to the login page if it fails). However, I am facing an issue with using my own express middleware to restrict access fo ...

javascriptnode.jsexpressauthenticationpassport.js

Tips for modifying the background color of an individual page in Ionic 3 and above

https://i.stack.imgur.com/t2mDw.pngI am just starting with Ionic and I'm attempting to modify the CSS of a single page by changing the background color to something different, like green, for example. I know that I can make global changes, but in this ...

javascriptangularionic-framework

React.js Material-UI project experiencing technical difficulties on Codepen, functioning perfectly on local environment

After building a project locally using create-react-app, I encountered an issue when transferring it to Codepen. The console is throwing a vague error: Uncaught ReferenceError: require is not defined. Any assistance would be greatly appreciated. Here&apos ...

reactjsmaterial-uicodepen

Error: The request could not be completed as the server responded with a 404 status code. The error code is ERR_BAD_REQUEST. Please check the request configuration and try again

Seeking to retrieve React Data from an API for my website, I utilized Axios to make a get request in React (home.jsx) to connect with my Express server file (database.js), which fetches information from my Mongo database. However, upon submitting input on ...

reactjsmongodbexpressmern

I'm having an issue where whenever I click on a different page, I keep getting redirected back to the first page

Upon conducting research, I discovered that by implementing the refined code below, I was able to resolve my issue (my other html was also corrected using this solution) setTimeout(function() { datatable_FTP.ajax.reload(null, false); }, 30000); Although I ...

javascriptjqueryajaxdatatabledatatables

Create a row in React JS that includes both a selection option and a button without using any CSS

My dilemma involves a basic form consisting of a select element and a button. What I want to accomplish is shifting the position of the form to the right directly after the select element Below is the code snippet that I have: return ( <> <div ...

javascriptcssreactjsfrontend

Issue with displaying the second dropdown based on the selection made in the previous dropdown

I wanted to address a recurring issue that has been discussed in various posts, such as this one on Stack Overflow: Show a second dropdown based on previous dropdown selection Despite attempting numerous solutions suggested in those posts, I have not been ...

javascriptjqueryhtml

Using Express.js to Serve Static Content on a Dynamic Route

I am working with the app.js code below; app.use(express.static(__dirname + "/public")); app.use("/example", express.static(__dirname + "/public")); app.engine("html", require("ejs").renderFile); app.get("/", (req, res) => res.render("index.ejs")); a ...

javascriptnode.jsexpressdynamicroutes

Attempted to set up VITE for React development, but encountered an npm ERR during installation

I'm encountering an NPM error in my terminal, here is the error message: PS C:\Users\hp pavilion\Desktop\bank_modern_app> npm create vite@latest npm ERR! code ENOENT npm ERR! syscall getaddrinfo npm ERR! errno ENOENT npm ERR! en ...

reactjsnpmvite