Working SQL and JavaScript for selected title

SQL for start time, title, completed date, total days worked and total duration

SELECT 
    painting_title,
    MIN(timestamp_for_DB) AS Start_Time,
    MAX(timestamp_for_DB) AS End_Time,
    TIMESTAMPDIFF(SECOND, MIN(timestamp_for_DB), MAX(timestamp_for_DB)) AS Duration_Seconds,
    FLOOR(TIMESTAMPDIFF(SECOND, MIN(timestamp_for_DB), MAX(timestamp_for_DB)) / 86400) AS Total_Days,
    CONCAT(
        LPAD(FLOOR(TIMESTAMPDIFF(SECOND, MIN(timestamp_for_DB), MAX(timestamp_for_DB)) / 3600), 2, '0'), ':',
        LPAD(FLOOR((TIMESTAMPDIFF(SECOND, MIN(timestamp_for_DB), MAX(timestamp_for_DB)) % 3600) / 60), 2, '0'), ':',
        LPAD(TIMESTAMPDIFF(SECOND, MIN(timestamp_for_DB), MAX(timestamp_for_DB)) % 60, 2, '0')
    ) AS Duration_HH_MM_SS
FROM 
    progress
WHERE 
    painting_title = 'Final Title Two'  -- Placeholder for the painting title selected from the drop-down
GROUP BY 
    painting_title;

When executing this query in your application’s backend code, you’ll need to replace the ‘?’ with the actual title selected from the dropdown. The exact method for doing this depends on the programming language and database library you’re using. For example, in a Node.js application using the ‘mysql’ library, it might look something like this:

const selectedTitle = 'The selected painting title from dropdown'; // This should be set to the actual user selection

connection.query('SELECT p.final_title AS Painting_Title, MIN(td.timestamp_for_DB) AS Start_Time, MAX(td.timestamp_for_DB) AS Completed_Date, DATEDIFF(MAX(td.timestamp_for_DB), MIN(td.timestamp_for_DB)) AS Total_Days_Worked, SEC_TO_TIME(SUM(TIME_TO_SEC(td.duration))) AS Total_Duration FROM progress p INNER JOIN time_data td ON p.uuid = td.uuid WHERE p.final_title = ? AND p.painting_status IN ('start', 'completed') GROUP BY p.final_title', [selectedTitle], function (error, results, fields) {
  if (error) throw error;
  // Process the results here
});

Populating Dropdown

To dynamically populate the dropdown with titles of completed paintings, you can use a query like:

SQL

SELECT DISTINCT painting_title FROM progress WHERE painting_status = 'completed' ORDER BY painting_title;

Use the results of this query to fill in the

Frontend Update

Use JavaScript to update the caption with the fetched data. For example:

document.getElementById('paintingInfo').innerHTML = `
    ${Painting_Title}<br>
    Start date: ${Start_Time} &nbsp;&nbsp; Completed: ${Completed_Date}<br>
    Total days worked: ${Total_Days_Worked} &nbsp;&nbsp; Total duration: ${Total_Duration}
`;

Ensure you have an element with id=“paintingInfo” in your HTML to display this information.