CODEWITHSUNDEEP
phpmysqlsql
Venk
Venk

Reputation: 230

How can I echo the position number of SQL row in PHP?

Before you misread the title... I'm not asking how to echo the number of rows, I'm simply looking to find the position that the row is in.

Let's say we have five total rows:

+---------------------+
| Post ID | Author |
+---------------------+
|    463 | Me     |
|    477 | Me     |
|    718 | Me     |
|    883 | Me     |
|    276 | Me     |
+---------------------+

Now I'd like to get the position of the row that contains the post ID of 718. From looking at the data, we can visually see that it is the third row in the data. How can I echo this in PHP?

Upvotes: 0

Views: 392

Answers (2)

Flash Ang
Flash Ang

Reputation: 192

when you fetch records, you may use a variable as position number.

#DB is a class connect to mysql database.
DB::init();

$sql = "SELECT * FROM RowNo ";
$stmt = DB::query( $sql );

$i = 1;
while( $rec1 = $stmt->fetch() ) {
    echo "row $i : ";
    print_r( $rec1 );
    echo '<BR>'.PHP_EOL;
    $i++;
};

result :

row 1 : Array ( [Post ID] => 8788277463 [Author] => Me )
row 2 : Array ( [Post ID] => 2894728477 [Author] => Me )
row 3 : Array ( [Post ID] => 3898994718 [Author] => Me )
row 4 : Array ( [Post ID] => 4891784883 [Author] => Me )
row 5 : Array ( [Post ID] => 1185819276 [Author] => Me )

Upvotes: 1

Flash Ang
Flash Ang

Reputation: 192

You may use row_number :

#1. ORDER BY Post ID

SELECT * , row_number() over ( order by Post ID ) FROM RowNo order by Post ID asc

Post ID Author  row_number() over ( order by `Post ID` )    
1185819276  Me  1
2894728477  Me  2
3898994718  Me  3
4891784883  Me  4
8788277463  Me  5

#2. ORDER BY Post ID DESC

SELECT * , row_number() over ( order by Post ID desc ) FROM RowNo order by Post ID desc

Post ID Ascending 1 Author  row_number() over ( order by `Post ID` desc )   
8788277463  Me  1
4891784883  Me  2
3898994718  Me  3
2894728477  Me  4
1185819276  Me  5

#3. ORDER BY part of Post ID

SELECT * , substr(Post ID, 4, 2), row_number() over ( order by substr(Post ID, 4, 2) ) FROM RowNo order by substr(Post ID, 4, 2) asc

Post ID Author  substr(`Post ID`, 4, 2) Ascending 1 row_number() over ( order by substr(`Post ID`, 4, 2) )  
4891784883  Me  17  1
2894728477  Me  47  2
1185819276  Me  58  3
8788277463  Me  82  4
3898994718  Me  89  5

Upvotes: 0

Related Questions