display query results with next button

[eagled2]

I'm creating a site with several articles. On the main page of this site I am creating section that shows you a preview of the articles and allows you go through them using a next and previous button. I found some example code designed for use with search results and am trying to adapt it my use. Instead of the prev and next links going to another page of results I want it to show the details of one row in the table and the links go to the prev and next row. I tested my query but when i put it into my php code it shows one page only and does not show any of my field data. Also the prev button is showing up even though it only detects one page. Here is the code I have so far. Any help you can provide is appreciated.

<?php

include 'config.php';
include 'opendb.php';

$limit=12; // rows to return
$numresults=mysql_query("select * from htarticles where archived=0 order by date desc");
$numrows=mysql_num_rows($numresults);

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
    $offset=1;
}

// get results
$result=mysql_query("select id,title,date,picname,summary,archived ".
    "from htarticles where archived=0 ".
    "order by date desc limit $offset,$limit");

// now you can display the results returned
while ($data = mysql_fetch_array($result)) {
    // code to display results
echo '<center><table><tr><td>'.$result['title'].'</td></tr>
<tr><td>'.$result['date'].'</td></tr>
<tr><td>'.$result['pic'].'</td></tr>
<tr><td>'.$result['summary'].'</td></tr>
</table></center>';
}

// next we need to do the links to other results

if ($offset==1) { // bypass PREV link if offset is 0
    $prevoffset=$offset-20;
    print "<a href=\"$PHP_SELF?offset=$prevoffset\">PREV</a> &nbsp; \n";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
    // has remainder so add one page
    $pages++;
}

for ($i=1;$i<=$pages;$i++) { // loop thru
    $newoffset=$limit*($i-1);
    print "<a href=\"$PHP_SELF?offset=$newoffset\">$i</a> &nbsp; \n";
}

// check to see if last page
if (!(($offset/$limit)==$pages) && $pages!=1) {
    // not last page so give NEXT link
    $newoffset=$offset+$limit;
    print "<a href=\"$PHP_SELF?offset=$newoffset\">NEXT</a><p>\n";
}

?>

[eagled2]

After thinking about it some more I realized the limit is how many results to display per page so I changed that to 1 and now I have only one result showing up per page giving me one number for each article. My search result will never have more than 12 or 18 articles total at the very most so this should not create an excessive number of pages.

I also found that I was using the wrong variable name in my display results section so i changed the $result to $data and it shows the contents of the first article. Clicking the numbers or the next and previous buttons does not change which article is displayed. Also the next button changes the offset to 2 which is the value which clicking on page 3. And prev changes it to -19. Here is the current code I am using. Also if i can I would like to replace the numbers with a drop down menu of the posting months as there is only one article per month.

<?php

include 'config.php';
include 'opendb.php';

$limit=1; // rows to return
$numresults=mysql_query("select * from htarticles where archived=0 order by date desc");
$numrows=mysql_num_rows($numresults);

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
    $offset=1;
}

// get results
$result=mysql_query("select id,title,date,picname,summary,archived ".
    "from htarticles where archived=0 ".
    "order by date desc limit $offset,$limit");

// now you can display the results returned
while ($data = mysql_fetch_array($result)) {
    // code to display results
echo '<center><table><tr><td>'.$data['title'].'</td></tr>
<tr><td>'.$data['date'].'</td></tr>
<tr><td>'.$data['pic'].'</td></tr>
<tr><td>'.$data['summary'].'</td></tr>
</table></center>';
}

// next we need to do the links to other results

if ($offset==1) { // bypass PREV link if offset is 0
    $prevoffset=$offset-20;
    print "<a href=\"$PHP_SELF?offset=$prevoffset\">PREV</a> &nbsp; \n";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
    // has remainder so add one page
    $pages++;
}

for ($i=1;$i<=$pages;$i++) { // loop thru
    $newoffset=$limit*($i-1);
    print "<a href=\"$PHP_SELF?offset=$newoffset\">$i</a> &nbsp; \n";
}

// check to see if last page
if (!(($offset/$limit)==$pages) && $pages!=1) {
    // not last page so give NEXT link
    $newoffset=$offset+$limit;
    print "<a href=\"$PHP_SELF?offset=$newoffset\">NEXT</a><p>\n";
}

?>

I assume you have the following:

<PREV> [1] [2] [3] [4] 5 [6] [7] <NEXT>

And you want something like:

<select id="get_art" name="get_art" onchange="getArticle();">
  <option value="22" selected="selected">October 1st, 2009</option>
  <option value="23">November 1st, 2009</option>
  <option value="24">December 1st, 2009</option>
...
</select>

The above is generated by php and MySQL - get the titles or dates of the articles as well as the Primary Keys and echo them in a loop to produce the select form field.
This shows the first article (earliest non-archived) selected by default.
You can use a js function to redirect:

function getArticle(){
  var id = document.getElementById('get_art').value;
  var url = "http://www.example.com?article=" + id;
  window.location = url;
}

You can then pick up the 'id' via $_GET. This value then serves as the basis of your SQL query. Remember that it still needs checking and sanitizing.

A minor-ish point - automatic redirects/refreshes on dropdowns are considered evil by some. They may have a point as accessibility may be compromised. If you're worried about this - give the select field a friend - a submit button. This submit button then sends the data via POST - no need for any javascript. You pick up the $_POST variable, if set, as if it was $_GET.

[eagled2]

Thanks i'll try this in the next couple days. I'm also having a problem with the prev and next buttons not working. Any ideas why?

[eagled2]

I've been working on getting the prev and next links to work correctly before I tried changing the numbers to a drop down menu as I figured it would need to be working to implement that change into my page properly. I searched online some more and found some working code online I was able to modify to suit my needs. It still has the numbers but I will try your example for a drop down menu next time I get a chance. Here is the working code I have so far. I'll let you know if I need more help once I try modifying it.

<?

require "config.php";
include 'opendb.php';

$offset=$_GET['offset'];
if(strlen($offset) > 0 and !is_numeric($offset)){
echo "Data Error";
exit;
}


$pages = ($offset - 0); 
$limit = 1;              // The layout of the results requires this to be one to look correct.
$this1 = $pages + $limit; 
$back = $pages - $limit; 
$next = $pages + $limit; 


// Find the number of records in table to determine total number of pages needed.
$query2=" SELECT * FROM htarticles where archived=0 order by date desc ";
$result2=mysql_query($query2);
echo mysql_error();
$numrec=mysql_num_rows($result2);

$query=" SELECT * FROM htarticles where archived=0 order by date desc limit $pages, $limit ";
$result=mysql_query($query);
echo mysql_error();

//Display info from database
while($data = mysql_fetch_array($result))
{
echo '<center><table>
<tr><td>'.$data[title].'</td></tr>
<tr><td>'.$data[date].'</td></tr>
<tr><td align="center"><img src="'.$data[picname].'.jpg" alt="'.$data[picname].'" title="'.$data[picname].'"></td></tr>
<tr><tr>Summary:<br>'.$data[summary].'</td></tr>';
}
echo "</table></center>";

if($numrec > $limit ){ // Display links to switch between database entries


echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";

if($back >=0) { 
print "<a href='$self?offset=$back'>PREV</a>"; 
} 

echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $numrec;$i=$i+$limit){
if($i <> $pages){
echo " <a href='$self?offset=$i'>$l</a> ";
}
else { echo "<font face='Verdana' size='4' color=red>$l</font>";}  // Current page is not a link and is red
$l=$l+1;
}


echo "</td><td  align='right' width='30%'>";

if($this1 < $numrec) { 
print "<a href='$self?offset=$next'>NEXT</a>";} 
echo "</td></tr></table>";

}
?>

[eagled2]

I got it working now. Thanks for your help. Here is the final code in case anyone is following this thread.

<?

require "config.php";
include 'opendb.php';

echo '<link rel="stylesheet" href="/httest.css" type="text/css">';

include 'hthead.htm';

//Begin code for article preview

$offset=$_GET['offset'];
if(strlen($offset) > 0 and !is_numeric($offset)){
echo "Data Error";
exit;
}


$pages = ($offset - 0); 
$limit = 1;              // The layout of the results requires this to be one to look correct.
$this1 = $pages + $limit; 
$back = $pages - $limit; 
$next = $pages + $limit; 


// Find the number of records in table to determine total number of pages needed.
$query2=" SELECT * FROM htarticles where archived=0 order by date desc ";
$result2=mysql_query($query2);
echo mysql_error();
$numrec=mysql_num_rows($result2);

$query=" SELECT * FROM htarticles where archived=0 order by date desc limit $pages, $limit ";
$result=mysql_query($query);
echo mysql_error();

//Get a list of active articles to be displayed in a drop down menu
$sqlmonth="Select id, date_format(date, '%b %Y') as datelist from htarticles where archived=0 order by date desc";
$rsltmonth=mysql_query($sqlmonth);
$options=""; 

while ($rowmonth=mysql_fetch_array($rsltmonth)) { 

    $did=$rowmonth['id']; 
    $datelist=$rowmonth['datelist']; 
    $months.="<option value=\"$did\">".$datelist."</option>";
} 

//Display info from database
while($data = mysql_fetch_array($result))
{

//Change the date of the displayed article to show the month abbr and year
$selid=$data[id];
$datequery="Select date_format(date, '%b %Y') as seldate from `htarticles` where id=$selid";
$seldate=mysql_query($datequery);
while($data2 = mysql_fetch_array($seldate))
{
$artdate=$data2[seldate];
}
$seltitle=$data[title];
echo '<center><div id="main">
	<div id="bk-div1">
<div id="content"><h2><a href="'.$self.'">
'.$data[title].'</a></h2>
<h3>'.$artdate.'</h3>
<a href="'.$self.'?id='.$data[id].'"><img src="'.$data[picname].'.jpg" alt="'.$data[picname].'" title="'.$data[picname].'"></a><a href="comment.php"><img src="comment.jpg" alt="Leave A Comment" title="Leave A Comment"></a><br><br>
<div id="para"><b>Summary:</b><br>'.$data[summary].'</div>
<br><div id="info"><a href="'.$self.'">View Full Article</a></div>';
}

echo '</div></div></div></center>';

if($numrec > $limit ){ // Display links to switch between database entries


echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";

if($back >=0) { 
print "<a href='$self?offset=$back'>PREV</a>"; 
} 

echo '</td><form action="" method="post"><td>
<SELECT NAME=months> 
<OPTION VALUE=0>Choose Month</option>='.$months.' 
</SELECT><input type="submit" name="months" value="Go"></td></form>';

echo "<td  align='right' width='30%'>";

if($this1 < $numrec) { 
print "<a href='$self?offset=$next'>NEXT</a>";} 
echo "</td></tr></table><br>";
//End of Links for database entries
} //End of Article Preview Code

echo '<h2>Introduction</h2>
<p>Home Teaching is a way for members of the "Church of Jesus Christ of Latter Day Saints" to reach out and strengthen each other. The members of a ward (members in a specific geographical area) are each given families or individuals who they have responsibility over. They in turn are required to visit each of these families at least once a month to strengthen them spiritually and provide any assistance they may need. Because of this often times you gain many new friends even after one is no longer assigned to that family and they continue to keep in contact and visit them on special occasions.</p>
<i>Note:
<p>Sometimes it can be hard to visit every family when there are conflicting schedules. This site in no way tries to replace these visits with an online message. This is simply a place for anyone regardless of who there hometeacher is to find a personalized home teaching message based off the official message reccomended by the church. I welcome comments about any message you read or about this site. Please use the contact page if you would like to provide me any feedback. If you should find yourself in need of help and cannot get ahold of your home teachers, or do not know who they are please contact your bishop, elders quorum president, or any other leader of your local ward.</p></i>';

echo '<br><br>
<a href="archives.php"><h3>Archives</h3></a>
<p><center>In an effort to enhance this website there is a preview for each active message. Due to the time it would take for the site to load with a large list this site keeps each message active for a period of 6 to 8 months. After that period all messages are archived and can be viewed from a seperate page. To view all the archived messages click on "Archives" above.</center></p>';

?>