CODEWITHSUNDEEP
phpsql
LJ27
LJ27

Reputation: 27

How to display limited data table in PHP?

I have a table called 'Goods' and there is a column called 'profit' in it. There have thousands of goods in the table but I want to make a table to show only the top 5 highest profit goods and top 5 lowest profit goods like this:

Goods profit
A 1000
B 900
C 800
D 700
E 600
Lowest Profit
V 50
W 40
X 30
Y 20
Z 10

Something I tried:
SQL SELECT:

$sql="SELECT * from goods order by profit ASC";
$query = $dbh -> prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount() > 0){
    foreach($results as $row){
        $goods[]=($row->goods);
        $profit[]=($row->profit);
    }
}

Display table:

<tr>
<?php
    $col=0;
    foreach($goods as $row1=>$value){
?>
    <tr>                                                
        <th scope="row"><?php echo $goods[$col];?></th>
        <td><?php  echo $profit[$col];?></td>  
        <?php $col=$col+1 && $col<5;}?> // $col<=5 is to make the data limited to 5 but it does work            
    </tr>        
    
    <th>Lowest Profit</th>
    <?php 
        $col=0;
        foreach($goods as $row1=>$value){
    ?>
        <tr>                                                
            <th scope="row"><?php echo $goods[$col];?></th>
                <td"><?php echo $profit[$col];?></td>  </td> 
                <?php $col=$col+1 && $col<5;}?>            
            </tr>        
        </tr>

Upvotes: 0

Views: 181

Answers (2)

Serg
Serg

Reputation: 22811

Row_number() twice

SELECT * 
FROM (
   SELECT *,
       row_number() over(order by profit) rn1,
       row_number() over(order by profit DESC) rn2,
   FROM goods) t
WHERE rn1 <= 5 OR rn2 <=5

Upvotes: 0

Koushik Roy
Koushik Roy

Reputation: 7387

You can do this easily using union all.

SELECT * from(SELECT * from goods order by profit ASC LIMIT 5) a 
union all
SELECT * from(SELECT * from goods order by profit desc  LIMIT 5) a

Pls refer to below sqlfiddle. I tried on mysql. Depending on DB, this can change. http://sqlfiddle.com/#!9/e00a4a/1

Upvotes: 1

Related Questions