当前位置: 动力学知识库 > 问答 > 编程问答 >

php - numbering individual rows in a league table

问题描述:

I have in a mysql database the current, upto date premier league table. I have pulled them from the database using a foreach loop to display them exactly how I want them, what I can't work out is how to have the position of the team displayed. So the team at the top of the league, I want to display number 1 in the table row, for the team in 10th I want to be able to display 10 and team in 19th , 19 ... I just can't seem to work out how to do so. I thought it had something to do with the COUNT function but I can't seem find anything that I understand properly!

Here is the code on my test page

<?php

$prem = prem_table();

?>

<table>

<th>POS</th>

<th>TEAM</th>

<th>PLD</th>

<th>GD</th>

<th>PTS</th>

<?php

foreach ($prem as $table) {

$team = $table['team'];

$pld = $table['pld'];

$win = $table['win'];

$draw = $table['draw'];

$loss = $table['loss'];

$gd = $table['gd'];

$pts = $table['points'];

?>

<tr>

<td>X</td><!-- This number needs to change to equal the position the team is in -->

<td><?php echo $team ;?></td>

<td><?php echo $pld ;?></td>

<td><?php echo $gd ;?></td>

<td><?php echo $pts ;?></td>

</tr>

<?php

}

?>

</table>

Here is the function I used to get the table from the database

function prem_table() {

$sql = "SELECT *

FROM `premier_league`

ORDER BY `points` DESC , `goal_difference` DESC , `for`";

$result = mysql_query($sql) or die(mysql_error());

$prem = array();

while (($row = mysql_fetch_array($result)) !== false) {

$prem[] = array(

'id' => $row['id'],

'team' => $row['team'],

'pld' => $row['pld'],

'win' => $row['win'],

'draw' => $row['draw'],

'loss' => $row['loss'],

'for' => $row['for'],

'against' => $row['against'],

'gd' => $row['goal_difference'],

'points' => $row['points'],

);

}

return $prem;

}

网友答案:

I wouldn't number the rows in MySQL since MySQL stores the data and you need the numbering just for presentation. You can number the rows with something like

$rows = retrieveData(); // somehow retrieve data from storage
$rowNumber = 0;

foreach ($rows as $currentRow) {
    $rowNumber++;
    // output your data here. Current row number is in $rowNumber.
}
网友答案:

Microsoft SQL server has the ROW_NUMBER() function which will number rows, unfortunately MySQL doesn't have such a feature.

However, you can do this by using LIMIT x,y

For example to find the 1st team:

SELECT xxx FROM table
LIMIT 1,1

and the 19th team:

SELECT xxx FROM table
LIMIT 19,1

In order to make sure you always get the correct position you will need to also use ORDER BY to make sure your rows are ordered correctly in the server.

It is also worth knowing that MySQL will still select all the data but will only return what you have specified via LIMIT, this won't be a problem with a premiership table but with a very large table could cause a performance hit.

Edit:

As pointed out by the comment below, the OP has been updated to include more information. At the time of answering the question it wasn't clear they already had all the data and just required a simple incrementing value within a loop or wanted to query the database for a specific potion. My answer clearly answers the latter which isn't in fact what the OP wanted. I'll leave this answer here as it may help someone else in the future.

分享给朋友:
您可能感兴趣的文章:
随机阅读: