Is there a way to set a column in mysql that will automatically

ghz 8months ago ⋅ 107 views

Is there a way to set a column in mysql that will automatically be the value of the row's row number?

I'm very new to creating my own projects and SQL in general and I'm having issues with having a "rank" column that will generate the ranking of a specific row of data based on the "time" column. I'm making a full-stack CRUD database which pulls from a mysql database to an express front end, and so far I can't seem to figure out how to do a "search" function that returns the row where name = ? from user input while using the ROW_NUMBER function. I still think that having an entirely separate column would be ideal compared to running the ROW_NUMBER query every time I want to find the search result, but I have no clue how I would do that.

So far my search function is as follows:

async searchByName(name) {
        try{
            const response = await new Promise((resolve, reject) => {
                const query = "SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn FROM medley_relay; SELECT * FROM medley_relay WHERE name = ?;";

                connection.query(query, [name], (err, results) => {
                    if (err) reject(new Error(err.message));
                    resolve(results);
                })
            });
            console.log(response);
            return response;
        } catch (error) {
            console.log(error);
        }
    }

It is called like so:

    const {name} = request.params;
    const db = dbService.getDbServiceInstance();

    const result = db.searchByName(name);

    

    result
    .then(data => response.json({data : data}))
    .catch(err => console.log(err)); 
    console.log(result);
})

and sent to the front end with this:

searchBtn.onclick = function() {
    const searchValue = document.querySelector('#search-input').value;

    fetch('http://localhost:5000/search/' + searchValue)
    .then(response => response.json())
    .then(data => loadHTMLTable(data['data']));
}

loadHTMLTable is

    const table = document.querySelector('table tbody');

    console.log(data);

    if (data.length === 0) {
        table.innerHTML = "<tr><td class='no-data'>No Data</td><td class = 'no-data'colspan='5'>No Data</td></tr>";
        return;
    }

    let tableHtml = "";

    data.forEach(function ({id, name, time, date, rn}) {
    
        tableHtml += "<tr>";
        tableHtml += `<td>${rn}</td>`;
        tableHtml += `<td>${name}</td>`;
        tableHtml += `<td>${time}</td>`;
        tableHtml += `<td>${new Date(date).toLocaleDateString()}</td>`;
        tableHtml += `<td><button class="delete-row-btn" data-id=${id}>Delete</td>`;
        tableHtml += `<td><button class="edit-row-btn" data-id=${id}>Edit</td>`;
        tableHtml += "</tr>";
        
    });

    table.innerHTML = tableHtml;

}

where I have a user input for the searched name as an html element on my web app.

What I get in return is simply an undefined table for all elements, and when I try to console.log the response I get:

  [
    RowDataPacket {
      id: 15,
      name: 'Corning',
      time: '01:40:15',
      date: 2024-04-10T04:00:00.000Z,
      rn: 1
    },
    RowDataPacket {
      id: 16,
      name: 'Binghamton',
      time: '01:52:42',
      date: 2024-04-02T04:00:00.000Z,
      rn: 2
    }
  ],
  [
    RowDataPacket {
      id: 16,
      name: 'Binghamton',
      time: '01:52:42',
      date: 2024-04-02T04:00:00.000Z
    }
  ]
]

Again I apologize if this is something that is really easy as its my first time using SQL or PHPmyadmin, but being able to have a dedicated column to being the row's number that will automatically refresh when entries are added or deleted would be great. So far all of the other things I've found rely on ROW_NUMBER which doesn't set up a permanent column, at least not for me, which wouldn't work as my "rank" ends up being undefined for my table.

Answers

It seems like you're trying to perform a search in your database table based on the name and retrieve the row number (rn) along with other columns. There are a few issues in your code:

  1. Your SQL query contains two statements separated by a semicolon (;). This will result in two separate result sets. Instead, you should combine both queries into one.

  2. You're using ROW_NUMBER() OVER (ORDER BY time) rn to assign row numbers to your result set. However, you're not specifying any condition to filter rows based on the name.

  3. Your searchByName function doesn't return a promise, which means result.then(...) won't work as expected.

To address these issues, you can modify your SQL query to include a filter condition based on the name and use a single query to achieve the desired result. Here's how you can modify your searchByName function:

async searchByName(name) {
    try {
        const response = await new Promise((resolve, reject) => {
            const query = `
                SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn 
                FROM medley_relay 
                WHERE name = ?
            `;

            connection.query(query, [name], (err, results) => {
                if (err) reject(new Error(err.message));
                resolve(results);
            });
        });

        return response;
    } catch (error) {
        throw new Error(error);
    }
}

And modify your route handler like so:

app.get('/search/:name', async (request, response) => {
    const { name } = request.params;
    const db = dbService.getDbServiceInstance();

    try {
        const result = await db.searchByName(name);
        console.log(result);
        response.json({ data: result });
    } catch (error) {
        console.log(error);
        response.status(500).json({ error: 'Internal server error' });
    }
});

With these changes, your search function should work as expected, fetching rows based on the provided name and including the row number (rn) in the result set.

#mysql