Table row counter help

[PRodgers4284]

I am working on code that counts the number of records in a table and display page numbers depending on the number of records in the table, i have my code set to display 5 records per page but i cant same to get it to work properly, can anyone help:

 

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="forum_question"; // Table name


// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");



// get value of id that sent from address bar
$id= $_GET['id'];

$sql="SELECT * FROM $tbl_name WHERE id='$id'";

$result=mysql_query($sql) or die(mysql_error());

$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong><?php echo $rows['topic']; ?></strong></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><?php echo $rows['detail']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>By :</strong> <?php echo $rows['name']; ?> <strong>Email : </strong><?php echo $rows['email'];?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>Date/time : </strong><?php echo $rows['datetime']; ?></td>
</tr>
</table></td>
</tr>
</table>
<BR>


<?php

// how many rows to show per page 
$rowsPerPage = 1; 

// by default we show first page 
$pageNum = 1; 

// if $_GET['page'] defined, use it as page number 
if(isset($_GET['page'])) 
{ 
    $pageNum = $_GET['page']; 
} 

// counting the offset 
$offset = ($pageNum - 1) * $rowsPerPage; 


$tbl_name2="forum_answer"; // Switch to table "forum_answer"

$sql2="SELECT * FROM $tbl_name2 WHERE question_id='$id' LIMIT $offset, $rowsPerPage";
$result2=mysql_query($sql2);

while($rows=mysql_fetch_array($result2)){
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong>ID</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>Name</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><?php echo $rows['a_name']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Email</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_email']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Answer</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_answer']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_datetime']; ?></td>
</tr>
</table></td>
</tr>
</table><br>


<?php 
} 

// how many rows we have in database 
$query   = "SELECT COUNT(question_id) AS numrows FROM $tbl_name2"; 
$result  = mysql_query($query) or die('Error, query failed'); 
$row     = mysql_fetch_array($result, MYSQL_ASSOC); 
$numrows = $row['numrows']; 

// how many pages we have when using paging? 
$maxPage = ceil($numrows/$rowsPerPage); 

// print the link to access each page 
$self = $_SERVER['PHP_SELF']; 
$nav = ''; 
for($page = 1; $page <= $maxPage; $page++) 
{ 
    if ($page == $pageNum) 
    { 
        $nav .= " $page ";   // no need to create a link to current page 
    } 
    else 
    { 
        $nav .= " <a href=\"$self?page=$page\">$page</a> "; 
    }         
} 

// creating previous and next link 
// plus the link to go straight to 
// the first and last page 

if ($pageNum > 1) 
{ 
    $page = $pageNum - 1; 
    $prev = " <a href=\"$self?page=$page\">[Prev]</a> "; 
     
    $first = " <a href=\"$self?page=1\">[First Page]</a> "; 
}  
else 
{ 
    $prev  = ' '; // we're on page one, don't print previous link 
    $first = ' '; // nor the first page link 
} 

if ($pageNum < $maxPage) 
{ 
    $page = $pageNum + 1; 
    $next = " <a href=\"$self?page=$page\">[Next]</a> "; 
     
    $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> "; 
}  
else 
{ 
    $next = ' '; // we're on the last page, don't print next link 
    $last = ' '; // nor the last page link 
} 

// print the navigation link 
echo $first . $prev . $nav . $next . $last; 

?> 



<?php

$sql3="SELECT * FROM $tbl_name WHERE id='$id'";

$result3=mysql_query($sql3) or die(mysql_error());

$rows=mysql_fetch_array($result3);
$view=$rows['view'];

// if have no counter value set counter = 1
if(empty($view)){
$view=1;
$sql4="INSERT INTO $tbl_name(view) VALUES('$view') WHERE id='$id'";
$result4=mysql_query($sql4);
}

// count more value
$addview=$view+1;
$sql5="update $tbl_name set view='$addview' WHERE id='$id'";
$result5=mysql_query($sql5);

mysql_close();
?>
<BR>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="add_answer.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td width="18%"><strong>Name</strong></td>
<td width="3%">:</td>
<td width="79%"><input name="a_name" type="text" id="a_name" size="45"></td>
</tr>
<tr>
<td><strong>Email</strong></td>
<td>:</td>
<td><input name="a_email" type="text" id="a_email" size="45"></td>
</tr>
<tr>
<td valign="top"><strong>Answer</strong></td>
<td valign="top">:</td>
<td><textarea name="a_answer" cols="45" rows="3" id="a_answer"></textarea></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" value="<?php echo $id; ?>"></td>
<td><input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

[PRodgers4284]

The code displays the page numbers but when i click the link for each page the records dont appear in the table. Below is what appears when i run the code

 

 

And then when i click the page links i cant see the records:

 

 

 

 

 

 

 

     

 

[Maq]

You should echo out $sql2 to make sure the statement is correct and you're passing the correct values (if any).

 

There's a great tutorial, if you haven't already seen it, on pagination here at phpfreaks.  It may help you...

[PRodgers4284]

Hey thanks for the reply, i echoed sql2 and it output the following:

 

SELECT * FROM forum_answer WHERE question_id='22' LIMIT 0, 1

 

I also changed my query that counts the rows in the table to:

 

// how many rows we have in database 

$query   = "SELECT COUNT(question_id) AS numrows FROM $tbl_name2 WHERE question_id='" .$id. "'"; 

 

Im kinda stuck now, i cant same to get the question id for the page link

[MatthewJ]

if question_id is an integer value (it looks like it probably is) then it should not be quoted in the query

 

SELECT * FROM forum_answer WHERE question_id='22' LIMIT 0, 1

 

should be

 

SELECT * FROM forum_answer WHERE question_id= 22 LIMIT 0, 1