CODEWITHSUNDEEP
phpjquerymysql
JUST Chillax
JUST Chillax

Reputation: 45

How to Show row number for every row fetched from mysql

I want to show row number for every row fetched from database. can someone help me to do this.

My Code to fetch rows is:

$query="
select id
     , title
     , description
     , url,votes 
  from posts 
 order 
    by votes desc
";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);


$arr = array();
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

    $arr[] = $row;  

}


}

# JSON-encode the response
$json_response = json_encode($arr);
// # Return the response
echo $json_response;

My code displays post, title, post votes and post controls (Vote up or Down). I want to show row number for every row fetched. My Code to Display posts is:

<ul class="thumbnails" ng-controller="votingCtrl">
    <li ng-repeat="post in posts" class="clearfix">
        <div class="col-md-1 voting well">
            <div class="votingButton" ng-click="upVote(post);">
                <i class="glyphicon glyphicon-chevron-up"></i>
            </div>
            <div class="badge badge-inverse">
                <div>{{post.votes}}</div>
            </div>
            <div class="votingButton" ng-click="downVote(post);">
                <i class="glyphicon glyphicon-chevron-down"></i>
            </div>
        </div>
        <div class="well col-md-11">
            <h4><a href="{{post.url}}">{{post.title}}</a></h4>
            <p>{{post.description}}</p>
        </div>

    </li>
</ul>

Thanks

Upvotes: 0

Views: 826

Answers (3)

Nilay Tiwari
Nilay Tiwari

Reputation: 554

SELECT 
    (@row_number:=@row_number + 1) AS num, 
    id, 
    title
FROM
    Test,
    (SELECT @row_number:=0) AS t
LIMIT 5;

Please try above its tested it will work, only need to replace your query with that.

Upvotes: 0

Spy.Murad
Spy.Murad

Reputation: 51

try to use ROW_NUMBER() mysql function 

select id,title,description,url,votes, ROW_NUMBER() OVER (ORDER BY votes DESC) as row_num from posts order by votes desc

Upvotes: 1

Pupil
Pupil

Reputation: 23958

With the current logic, row number can be displayed using {{post.id}} as the SELECT query fetches id along with votes as fields.

In the loop itself, we can display a custom counter like:

$arr = array();
$cntr = 0;
if ($result->num_rows > 0) {
 while($row = $result->fetch_assoc()) {
  ++$cntr;
  $row['cntr'] = $cntr;
  $arr[] = $row;
 }
}

And in view template, you can use {{post.cntr}}.

Upvotes: 0

Related Questions