SQL Split Results

[eRott]

Hi,

 

I was just wondering. Lets say I have for example, 50 links in a database. How would I make it so it lists all the links in the database but only 10 links per page?

[effigy]

See this tutorial.

[eRott]

Ok, I have looked at that tutorial, edited the code etc, and I've even seen other tutorials, but it never works. What I am looking to do, is display a list of links (just an example), 10 per page, that have to do with lets say, games. This is the code I currently use to display all the 'links' (i actually display games) and it works fine. Could anyone please explain what the problem is?

 

<?php
//connect to and select db
$dbhost = 'localhost';
$dbuser = 'aaa';
$dbpass = 'aaa';
$dbname = 'aaa';
$tbl_name = 'games';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
$db = mysql_select_db($dbname, $conn) or die(mysql_error());
   
   //get a list of the info from the table to display the games
   $sql = "SELECT * from $tbl_name WHERE type='Action'";
   $result = mysql_query($sql, $conn) or die(mysql_error());

       echo "<table width='100%' border='0' cellspacing='0' cellpadding='5' align='left'>";

   // for each row fetched from the results...   
   while ($list = mysql_fetch_array($result)) {
       //result
       echo "<tr>";
       echo "<td>";
       echo "<a href='games/{$list['srcname']}/{$list['src']}'><img src='games/thumbs/{$list['thumb']}' border='0' height='60' width='70'></a>";
       echo "</td>";
       echo "<td>";
       echo "<b><font color='#000'>{$list['name']}</font></b><br>{$list['description']}";
       echo "</td>";
       echo "</tr>";
   } // end while

       echo "</table>";

mysql_close($conn);
?>

[eRott]

Ok, well, I got that code to work after all (from that tutorial you provided the link for above). However, there is a slight problem. I set it to display 10 results per page, but after 10, it justs stop. It doesnt list any more pages or any more results. Here is the code: (any idea whats wrong?)

 

<?php
$dbhost = 'localhost';
$dbuser = 'aaa';
$dbpass = 'aaa';
$dbname = 'aaa';
$tbl_name = 'games';

@mysql_connect($dbhost, $dbuser, $dbpass) or die("Error: Can't connect to server");
@mysql_select_db($dbname) or die("Error: Can't connect to databse");

    $limit = 10;
    $query_count = "SELECT count(*) FROM $tbl_name WHERE type='Action'";
    $result_count = mysql_query($query_count);
    $totalrows = mysql_num_rows($result_count);

    if(empty($page)){
        $page = 1;
    }        

    $limitvalue = $page * $limit - ($limit); 
    $query  = "SELECT * FROM $tbl_name LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error());

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }

    $bgcolor = "#E2E2E2"; // light gray

    echo "<table width='100%' border='0' cellspacing='0' cellpadding='5' align='left'>";
    
    while($row = mysql_fetch_array($result)){
        if ($bgcolor == "#E2E2E2"){
            $bgcolor = "#FFFFFF";
        }else{
            $bgcolor = "#E2E2E2";
        }

       echo "<tr bgcolor='.$bgcolor.'>";
       echo "<td>";
       echo "<a href='games/{$row['srcname']}/{$row['src']}'><img src='games/thumbs/{$row['thumb']}' border='0' height='60' width='70'></a>";
       echo "</td>";
       echo "<td>";
       echo "<b><font color='#000'>{$row['name']}</font></b><br>{$row['description']}";
       echo "</td>";
       echo "</tr>";
    }

    echo "</table>";

    if($page != 1){
        $pageprev = $page--;
        echo "<a href='$PHP_SELF&page=$pageprev'>PREV</a> ";
    }else{
        echo "PREV ";
    }

    $numofpages = $totalrows / $limit;
    
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo($i." ");
        }else{
            echo "<a href='$PHP_SELF?page=$i'>$i</a> ";
        }
    }


    if(($totalrows % $limit) != 0){
        if($i == $page){
            echo($i." ");
        }else{
            echo"<a href='$PHP_SELF?page=$i'>$i</a> ";
        }
    }

    if(($totalrows - ($limit * $page)) > 0){
        $pagenext = $page++;
         
        echo"<a href='$PHP_SELF?page=$pagenext'>NEXT</a>"; 
    }else{
        echo "NEXT"; 
    }
    
    mysql_free_result($result);

?>

 

Thank you,

Regards,

eRott