HTML Table from SQL Data

[azamkandy]

Hi,

I've created a sql data base and a web page to view the database evrything works fine but just wondering how to show only last 10 rows of the table in the webpage ??
The code I use now:

<?
$username='abc';
$password='abc'';
$database='kand_dedicates';

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM dedications";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

?>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"name");
$f2=mysql_result($result,$i,"song");
$f3=mysql_result($result,$i,"ded_for");

?>
<table width="596" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="14" height="60">&nbsp;</td>
<td width="20" bgcolor="#FFFFFF" class="style30" style="BORDER-bottom: #333333 1px solid">&nbsp;</td>
<td width="146" bgcolor="#FFFFFF" class="style30" style="BORDER-bottom: #333333 1px solid"><span class="style30" style="BORDER-bottom: #333333 1px solid"><?php echo $f1; ?></span></td>
<td width="147" bgcolor="#FFFFFF" class="style30" style="BORDER-bottom: #333333 1px solid"><span class="style30" ><?php echo $f2; ?></span></td>
<td width="269" bgcolor="#FFFFFF" class="style30" style="BORDER-bottom: #333333 1px solid"><span class="style30" ><?php echo $f3; ?></span></td>


<?php
$i++;
}
?>

[descalzo]

how to show only last 10 rows of the table in the webpage ??

You want to start ten rows from the end, so you have to adjust the starting value for $i.
You have to take into account that you might have less than 10 results...

Method that minimally changes your code:

 
 
if( $num < 10 ){  # in case you have fewer than 10 results, start at 0
 
    $i=0;
 
} else {  # start ten from the end,  note that when $num == 10, you start at 0
 
    $i = $num - 10 ;
 
}
while ($i < $num) {
 
.... 

[azamkandy]

You want to start ten rows from the end, so you have to adjust the starting value for $i.
You have to take into account that you might have less than 10 results...

Method that minimally changes your code:

 
 
if( $num < 10 ){  # in case you have fewer than 10 results, start at 0
 
    $i=0;
 
} else {  # start ten from the end,  note that when $num == 10, you start at 0
 
    $i = $num - 10 ;
 
}
while ($i < $num) {
 
.... 

Beatiful descalzo!!!

Much appreciated!!!
Edit:
hi and also I want to sort results by descending order for ex 5,4,3,2,1 now its coming like 12345 ....

[marshian]

$query="SELECT * FROM dedications";

You can do a lot with sql queries. Instead of altering your php you could change that!

Just keep showing every entry but edit the query. For example try this:

SELECT * FROM dedications LIMIT 10
or
SELECT * FROM dedications ORDER BY `name` ASC LIMIT 10
(this will get the first 10 rows in the database)
I think you could use something like this to get the last 10 rows ordered alpabetically:
(SELECT * FROM dedications ORDER BY `name` DESC LIMIT 10) ORDER BY `name` ASC

[descalzo]

hi and also I want to sort results by descending order for ex 5,4,3,2,1 now its coming like 12345 ....

If you want the result to count in a different direction, you have to make it count in a different direction.

If there are 43 results, you are currently counting from 33 to 42 ( since computers start at 0, the 43rd result is numbered 42).

To reverse it, you have to start at 42 ( $num - 1 in your script ) and count down and end at 33 ( $i in your script).

 
 
if( $num < 10 ){  
 
    $bottom=0;
 
} else {  
 
    $bottom = $num - 10 ;
 
}
 
$i = $num - 1 ;  # this is the last item returned...
 
while ($i >= $bottom ) {
 
 
....
 
 
$i-- ;  # INSTEAD OF $i++ 

As marshian pointed out, you can do this with the SQL query, if you have a field to order the results on.

Generally you should have a field named id in your table that is an autoincremented integer. Then it is easy to get the last X entries.