Put specific values into specific table cells from the database. (Razor - html)
<table class="prTable">
<tr>
<th>Exercise</th>
<th>1</th>
<th>2</th>
<th>3</th>
<th>4</th>
<th>5</th>
<th>6</th>
<th>7</th>
<th>8</th>
<th>9</th>
<th>10</th>
<th>11</th>
<th>12</th>
</tr>
@for (var i = 1; i <= 3; i++)
{
<tr class="prTableRow">
@if (i == 1)
{
<td class="prExerVariNameTD">Squat</td>
}
else if (i == 2)
{
<td class="prExerVariNameTD">Benchpress</td>
}
else
{
<td class="prExerVariNameTD">Deadlift</td>
}
@for (var ii = 1; ii <= 12; ii++)
{
var getPR = "SELECT top 1 kg, rep, date FROM Test WHERE exerVariName LIKE '%Comp%' AND exercise = @0 AND rep = @1 order by kg desc";
db.Execute(getPR, i, ii);
foreach (var get in db.Query(getPR, i, ii))
{
DateTime Date = get.Date;
var finalDate = Date.ToString("MMM d, yyyy");
var weight = get.kg + "kg";
var reps = "x " + get.rep;
<td class="prTableCell" title="@finalDate">@weight @reps</td>
}
}
</tr>
}
</table>
So in my database I have a bunch of training based data and each row has the name exerise, several may have the same, all rows also have weights and repetitions done on it. This means that it checks each exercise name and selects the highest weight to be performed on that exercise name for 1 repetition, then 2, and so on up to 12. So it will show me the best result I did for each rep for each name exercises in the table as in the picture below
however, not always the data for each rep for all exercises is a database, so some numbers do not show, as you can see in the picture, the squat line goes 1,2,3,5,6,10,12, so some of they are missing and I want every number to be under every corresponding number that is in the table header, so 5 will be under 5 and so on, since I would like to solve this, one needs to put 0 kg in cells that do not have data, so for example there is no data in the squat row for the number 4, I would like to put 0kg in there, which will push 5 one cell forward and it will end up at number 5, and therefore for everyone so that everyone is in the right place.
So, for all repetition numbers that are not in the database, I would like to put 0. Does this even make sense?
EDIT: Database Image
source to share
var getPR = "SELECT kg, rep, date FROM Test WHERE exerVariName LIKE '%Comp%' AND exercise = @0 order by kg desc";
db.Execute(getPR, i);
var data = db.Query(getPR, i)
@for (var ii = 1; ii <= 12; ii++)
{
var matched = data.SingleOrDefault(x => x.rep == ii);
if (matched != null)
{
DateTime Date = get.Date;
var finalDate = Date.ToString("MMM d, yyyy");
var weight = matched.kg + "kg";
var reps = "x " + matched.rep;
<td class="prTableCell" title="@finalDate">@weight @reps</td>
}
else
{
<td class="prTableCell">0kg</td>
}
}
You can only execute sql query once and get all records based on exercise type. You don't need to ask for repetition every time, it will be more efficient.
When in a loop use Linq
to find a record that has 1 rep. If there is no such entry, il will return null
and you will know that you need an empty one td
. If a record is found, use its data to fill it in td
.
Edit: even more, you can just query the database in just one go, like this
var getPR = "SELECT kg, rep, date, exercise FROM Test WHERE exerVariName LIKE '%Comp%' order by kg desc";
and change Linq
to this
var matched = data.SingleOrDefault(x => x.exercise == i && x.rep == ii);
source to share