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

Question

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

javascript css reactjs frontend backend

Answer 1

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.

Answer 2

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.

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

Answer №1

Similar questions

The React component I was working with mysteriously vanished from my application

Modifying static content within jQuery tabs

Everlasting Dropdown in AngularJS Always Open Mode

Postponing the findings of a database for a quarter of an hour

Conditions are in an angular type provider with AOT

checkbox-activated navigation bar

Transferring identification data between views within an application (AngularJS, NodeJs, HTML)

Issue with unit testing a ViewportRuler in Angular 2 Material Library

What is the proper method for utilizing assignments instead of simply assigning values directly?

Can Selenium be used to retrieve a list of pinned IDs from a website?

Develop expandable objects containing a set size of items using HTML and CSS

What is the method to verify if a variable in ES6 is constant?

The integration of a side panel with a chrome extension is experiencing issues

Error encountered with React Hooks - TypeError

Dealing with errors in Express.js within the service or controller layers

Incorporating a static image file into a Material UI cardMedia component within a Next.js project

The combination of TypeScript 2.6 and material-ui 1.0.0-beta.24's withStyles with react-router withRouter is resulting in the error message: "Property 'classes' is missing in type."

Autocomplete feature in MUI allows filtering to begin after typing at least 3 characters

Is it possible to utilize both setReduxObject and selectReduxObject within the same .jsx file?

Tips for storing an array consisting of 4 elements (objects) in local storage using React