PHP, MySQL, Previous, Next...

[ectimo]

Hi all,

I have a fairly simple problem, but i cannot get to it. I have a page, that displays one (1) record at the time, and I want to have forward and back buttons (links) at the top and bottom of the page. Can anyone help me with that???

Here is a part of my code.

 

<form method="get">
<table border="0" width="90%" cellpadding="2" cellspacing="2" align="center"> 
<tr><td align="center">
  Select first letter or kind of the story: 
  <input type="hidden" name="link" value="stories.php">
<input type="hidden" name="Pripadnost" value="3">
<select name="letter" onchange="this.form.submit()">
<?PHP 
$letter =  trim($_REQUEST["letter"]);
?>

<option value="Sve" <?PHP echo $letter == "All" ? " selected " : "" ; ?>>All</option>
<?PHP 
include_once ("/includes/databaseMySQL.php");
mysql_set_charset('utf8');

$SQL = "Select distinct ucase(left(title,1)) as sl FROM tblStoryes order by sl";

$rs_data = mysql_query($SQL) or die(mysql_error());
while ($row_data = mysql_fetch_assoc($rs_data)) {
      ?>
      <option value="<?PHP echo $row_data["sl"]; ?>"<?PHP echo $letter == $row_data["sl"] ? " selected " : "" ; 
  echo ">".$row_data["sl"] ;?></option>
      <?PHP 
// ******************  Listed all the first letters of the titles into a drop box. *********************** //
} ?>
</select>       
Bosnian <input type="checkbox" name="Bosnian " <?PHP echo isset($_REQUEST["Bosnian "]) ? "checked=checked" : "" ;?> onclick="this.form.submit()" />    
Turkish <input type="checkbox" name="Turkish "  <?PHP echo isset($_REQUEST["Turkish "]) ? "checked=checked" :  "" ;?> onclick="this.form.submit()" />    
Arabian <input type="checkbox" name="Arabian "  <?PHP echo isset($_REQUEST["Arabian "]) ? "checked=checked" : ""  ;?> onclick="this.form.submit()" /> 

</td></tr>
</table>

<table border="0" width="90%" cellpadding="2" cellspacing="2" align="center">
<?PHP 
$id = -1;
$rs_data = mysql_query("Select max(id) as maxid from  tblStories WHERE aktivno = True") or die(mysql_error());
$row_data = mysql_fetch_assoc($rs_data);
$maxid = (int) $row_data["maxid"];

// ******************  Found the largest ID from the table. *********************** //

$SQL = "SELECT * FROM tblStories WHERE aktivno = True ";


if ( strlen($letter) < 3 && strlen($letter) > 0 ) 	{
	$SQL = $SQL . " and ucase(left(`title`, 1)) = '" . $Letter . "'";
}

	$lang = 0;
	$lang = $lang + isset($_REQUEST["Bosnian"]) * 4;
	$lang = $lang + isset($_REQUEST["Turkish"]  ) * 2;
	$lang = $lang + isset($_REQUEST["Arabian"] );
	switch ($lang) {
    		case 6:			// bos i tur
        		$SQL = $SQL . " and ( Bosanski = 1 or Turski = 1 ) ";
        	break;
    		case 5:			// bos i ar
        		$SQL = $SQL . " and ( Bosanski = 1 or Arapski = 1) ";
        	break;
		case 4:			// bos
        		$SQL = $SQL . " and Bosanski = 1 ";
        	break;
    		case 3:			// tur i ar
        		$SQL = $SQL . " and ( Turski = 1 or Arapski =1) ";
        	break;
    		case 2:			// tur
        		$SQL = $SQL . " and Turski = 1 ";
        	break;
    		case 1:			// ar
        		$SQL = $SQL . " and Arapski =1 ";
        	break;
	}


if (trim($_REQUEST["DBid"]) <> ""  or  $_REQUEST["DBid"] > 0)
	$SQL = $SQL . " and  id > " . trim($_REQUEST["DBid"]) ;
	$SQL = $SQL . " LIMIT 1"; //order by naslov

$rs_data = mysql_query($SQL) or die(mysql_error());
echo "<!--" . $SQL . " : Jezik je:" . $lang . "-->";
while ($row_data = mysql_fetch_assoc($rs_data)) {
$id = (int) $row_data["ID"];
?>
  <tr>
	<td align="center" colspan=3>
       <h3><?PHP echo $row_data["Title"];?> </h3>
    </td>
    </tr>    
    <tr>
      <td width=20%> 
      </td>
      <td align="justify" width=60%>
        <?PHP  echo $row_data["tekst"]; // tekst = text, but not keyword ?>
      </td>
      <td width=20%> 
      </td>
    </tr>
<?PHP 
}
?>
</table>
<p>

<?PHP 
// ****************************** HEEEEELLLLPPPPPP ******************************* //
?>

<table border="0" width="90%" cellpadding="1" align="center">
  <tr>
    <td align="center"><a href="?DBid=1<?PHP 
	foreach($_REQUEST as $arr => $elem){
		if ( $arr != "DBid") echo "&" . $arr . "=" . $elem;
	} ?>"> <<</a></td>
    <td align="center"><a href="?DBid=<?PHP 
	echo $_REQUEST["id"];
	foreach($_REQUEST as $arr => $elem){
		if ( $arr != "DBid") echo "&" . $arr . "=" . $elem;
	}
	?>" ><</a></td>
<?PHP if (  $maxid <=  $id ) { ?>
    <td align="center">></td>
    <td align="center">>></td>
<?PHP }
  else
  {?>
    <td align="center"><a href="?DBid=<?PHP 
echo $id ; 
foreach($_REQUEST as $arr => $elem){
		if ($arr != "DBid") echo "&" . $arr . "=" . $elem;
	}
	?>">></a></td>
   	<?PHP if ( ($id + 9) <  $maxid ) { ?>
    	<td align="center"><a href="?DBid=<?PHP 
	echo $id+10 ;
	foreach($_REQUEST as $arr => $elem){
		if ($arr != "DBid") echo "&" . $arr . "=" . $elem;
	}
	?>">>></a></td>
    <?PHP
    } 
    else
      {?>
   	    <td align="center">>></td>
<?PHP }?>    
<?PHP }?>
  </tr>
</table>
</form>

[QuickOldCar]

You are best off taking a look here at a pagination tutorial.

http://www.phpfreaks.com/tutorial/basic-pagination

 

Since you only are doing a 1 result per page, I'm sure you can just create href links designating your page number, and adding substracting that page number by 1. Although this isn't the best way to do it.

[litebearer]

sample of 1 record at a time --- http://www.nstoia.com/sat/disp_pag/one.php

[ectimo]

Well, paging is ok, but doesnt help much, since I need to order, reorder things by title, and select only one, two or all three kinds of stories.

idkindtitle[/td]

1BA story

2TB story

3TC story

4BB story

5AE story

6TF story

7TG story

8BD story[td]

 

 

If i order by title, IDs tend to get mixed up (1,2,3,8,5,4,6,7), so i cannot say "Select * from ... where ID > #-current ID  limit 1" ... for 1,2,3 and 8 it will do ok, but after 8 it will search for 9, instead of 5.

 

In case I select only one kind, then i get holes in IDs (2,3,6,7) and to go back, i need to go  "Select * from ... where ID > #-2  limit 1" again for 2 and 3 it will do, but for 6 it will not, since 6-2 is 4, and 4 is not in the list at all. You see my trouble...

[jcbones]

Paging does NOT affect ordering.

 

Off the cuff.

Example ONLY, UN-TESTED

<?php
//your code
if (trim($_REQUEST["DBid"]) <> ""  or  $_REQUEST["DBid"] > 0)
	$SQL = $SQL . " and  id > " . trim($_REQUEST["DBid"]) ;
	//remove this line -> $SQL = $SQL . " LIMIT 1"; //order by naslov

//added code
$pageResult = mysql_query(str_replace('*','COUNT(*)',$SQL)); //change query and select count of rows from database.
$count = mysql_result($pageResult,0); //get the row count from the resource.
$currentPage = (isset($_GET['page'])) ? (int)$_GET['page'] : 1; //if the GET array has a page index, get it, otherwise we are on page 1.
$currentPage = ($currentPage > $count) ? $count : $currentPage; //if the requested page is greater than the total pages, return it to the last page.
$currentPage = ($currentPage < 1) ? 1 : $currentPage; //if the requested page is less than 1, reset it to 1.
$limit = ' LIMIT ' . ($currentPage - 1) * 1 . ',' . 1;  //Limit is the current page minus 1 multiplied by the number per page (1), returning the number per page rows (1).
$navigation = (($currentPage > 1) ? '<a href="?page=' . ($currentPage - 1) . '">Previous</a> ' : NULL) . '|' .  (($currentPage < $count) ? ' <a href="?page=' . ($currentPage + 1) . '">Next</a>' : NULL); //variable $navigation now contains your Previous and Next links, you can add them where you wish.
$SQL .= $limit; //add the limit to the SQL.

//back to your code
$rs_data = mysql_query($SQL) or die(mysql_error());
echo "<!--" . $SQL . " : Jezik je:" . $lang . "-->";

[ectimo]

Thanks, a bunch. 

I actually managed to get it working (not really the way I wanted  , but it works)  last night, using paging from one of the previous posts.

None the less, I will try this as well, since it may to prove to be more like what I wanted it to be.