/////////////////////////////  js file /////////////////////


const express = require('express');
const mysql = require('mysql');

const app = express();
const pkPort = 3009;

app.use(express.static('public'));
app.set('view engine', 'ejs');

const pkConnection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root@123',
  database: 'abc'
});

pkConnection.connect(error => {
  if (error) {
    console.error('Error connecting to MySQL:', error);
    return;
  }
  console.log('Connected to MySQL');
});

app.get('/', (req, res) => {
  const pkQuery = 'SELECT * FROM photo';
  pkConnection.query(pkQuery, (queryError, results) => {
    if (queryError) {
      console.error('Error executing MySQL query:', queryError);
      res.status(500).send('Error retrieving product data');
      return;
    }
    results.forEach(product => {
      product.image = product.image.toString('base64');
    });
    const pkProducts = results;
    res.render('index', { products: pkProducts });
  });
});

app.listen(pkPort, () => {
  console.log(`Server listening on port ${pkPort}`);
});



///////////////////////////////////////   ejs ////////////////////////


<!DOCTYPE html>
<html>
<head>
   <title>Product List</title>
   <link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
   <h1>Product List</h1>
   <ul>
       <% products.forEach(pkProduct => { %>
           <li>
               <img src="data:image/png;base64,<%= pkProduct.image %>" alt="Product Image" width="200px" height="200px">
               <h3>
                   <%= pkProduct.name %>
               </h3>
               <p>Price: <%= pkProduct.id %></p>
           </li>
           <% }) %>
   </ul>
   <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
   <script>
       $(document).ready(function () {
           // Load product data via AJAX and update the HTML
           $.ajax({
               url: '/',
               method: 'GET',
               dataType: 'html',
               success: function (data) {
                   $('ul').html(data);
               },
               error: function () {
                   console.log('Error retrieving product data via AJAX');
               }
           });
       });
   </script>
</body>
</html>