Is there a way to select a row of a table with a specific column value that also displays its row number from the in original table in phpmyadmin?
I am trying to make a full-stack CRUD web app using mysql through phpmyadmin, which will be sent to an express front end (this is my first time ever working with SQL and PHP so pardon my mistakes or my lack of knowledge) For example, my table in PHP:
ID Name Time Date
1 COR 01:40:37 01/24/24
2 BING 01:39:38 01/23/24
whose values have been inserted with user input. Is the following possible?
First by getting the row number and sorting by 'time':
ID Name Time Date Rn
2 BING 01:39:38 01/23/24 1
1 COR 01:40:37 01/24/24 2
However I only want the following output when 'BING' is searched:
ID Name Time Date Rn
2 BING 01:39:38 01/23/24 1
My search function is:
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 = ?;";
// try separating the two queries
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);
}
}
This is called by:
app.get('/search/:name', (request, response) => {
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 activated through the front end with an HTML input element:
const searchBtn = document.querySelector('#search-btn');
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']));
}
My loadHTMLTable function is:
function loadHTMLTable(data) {
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;
}
If I only use the query: const query = "SELECT * FROM medley_relay WHERE name = ?;"; and name is passed through, I get an undefined value for rn because it hasn't been defined through the ROW_NUMBER function. However when I do add the ROW_NUMBER FUNCTION 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
}
]
]
with undefined values across my entire table. Is there a way to do this without getting a nested array and only the array at index [0][1]? Right now my query to load the data from the array without restricting it to any name looks like:
app.get('/getAll', (request, response) => {
const db = dbService.getDbServiceInstance();
const result = db.getAllData();
result
.then(data => response.json({data : data}))
.catch(err => console.log(err));
})
and
async getAllData() {
try{
const response = await new Promise((resolve,reject) => {
const query = "SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn FROM medley_relay ORDER BY rn;"
"SELECT * FROM medley_relay;";
connection.query(query, (err,results) => {
if (err) reject(new Error(err.message));
resolve(results);
})
});
return response;
} catch (error) {
console.log(error);
}
}
In this case it does work to have both of these queries like this and my table functions correctly. Why is it that when I change "SELECT * FROM medley_relay;" to "SELECT * FROM medley_relay WHERE name = ?;" it stops working? I've tried to use a dedicated column to contain the value of row numbers that would constantly refresh to match updates and deletions, but it doesn't seem like that is possible.
Answers
It seems like you want to achieve two things:
- Displaying row numbers (
rn) for each row in your result set, which are ordered by thetimecolumn. - When searching for a specific name, you want to return the rows that match the name along with their row numbers (
rn).
You're on the right track with using the ROW_NUMBER() function to generate row numbers. However, you need to modify your SQL query to include a filter for the name when searching. Here's how you can do it:
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);
}
}
This query will return rows from the medley_relay table where the name column matches the input name, and each row will have its corresponding row number (rn) based on the time column order.
Now, let's address why you're getting a nested array in your response. The reason you're getting a nested array is because your SQL query returns two result sets: the first result set contains rows with row numbers (rn), and the second result set contains rows filtered by name. To fix this, you need to only return a single result set. Here's how you can modify your searchByName function to return only one result set:
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);
}
}
Now, your searchByName function should return only one result set with the rows filtered by name and their corresponding row numbers (rn). This should resolve the issue of getting a nested array.
