Creating Queries | Example

Example Method

// Function to fetch the total touchdowns for a specific player
export function getPlayerTouchdowns(playerName) {
    return new Promise((resolve, reject) => {
        // Define the SQL query
        const query = 'SELECT Player, SUM(Touchdowns) as TotalTouchdowns FROM NFL_Player_Stats_1922 WHERE Player = ?';

        // Define the parameters for the query
        const params = [playerName];

        // Execute the query
        db.get(query, params, (err, row) => {
            if (err) {
                // If an error occurs, reject the promise
                reject(err);
            } else {
                // Resolve the promise with the row result
                resolve(row);
            }
        });
    });
}

---

Step-by-Step Explanation

Function Declaration:

export function getPlayerTouchdowns(playerName) {

• Declares a named function getPlayerTouchdowns that accepts a parameter playerName.
• The export keyword allows this function to be imported in other files.
• The purpose of this function is to retrieve the total touchdowns for a given player.

---

Returning a Promise:

return new Promise((resolve, reject) => {

• Since SQLite operates asynchronously, this function returns a Promise to handle the operation.
• The resolve function will return the query result if it succeeds, while reject will handle any errors.

---

SQL Query Definition:

const query = 'SELECT Player, SUM(Touchdowns) as TotalTouchdowns FROM NFL_Player_Stats_1922 WHERE Player = ?';

• Defines an SQL query string to fetch the player's name and calculate the sum of their touchdowns (SUM(Touchdowns)).
• The ? is a placeholder for parameters, which prevents SQL injection attacks.

---

Parameters for Query:

const params = [playerName];

• An array containing playerName, which will replace the ? in the query. This ensures safe parameter binding.

---

Executing the Query:

db.get(query, params, (err, row) => {

• The db.get method executes the query and fetches a single row of results.
• It takes the query string, parameters, and a callback function that processes the result.

---

Error Handling:

if (err) {
    reject(err);
}

• If an error occurs during query execution, the reject function is called with the error object.

---

Processing Results:

resolve(row);

• If the query is successful, the resolve function is called with the result (row), which contains the player's name and total touchdowns.

---

Example Frontend Usage:

async function fetchPlayerTouchdowns(playerName) {
    try {
        const result = await window.api.getPlayerTouchdowns(playerName);
        console.log(`${result.Player} has a total of ${result.TotalTouchdowns} touchdowns.`);
    } catch (error) {
        console.error('Error fetching player touchdowns:', error);
    }
}

fetchPlayerTouchdowns('John Doe');