missing rows...

[buraks78]

hello,

i have a paging section in my website. everything works fine except one problem. the problem is that if there are multiple pages there is one row missing except at the last page. the last page is missing two rows.

weird huh?

any help would be greatly appreciated...

here is the code that i use for paging through results.

sql query:

  If (!isset($_GET["start"])){
  $start = 1;
  $page = 1;
  } else {
  $start = $_GET["start"];
  $page = (($start-1)*10)+1;
  }

$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class')";
$result = mysql_query($sql, $db) or die (mysql_error());
$row = mysql_fetch_array($result);
$n = mysql_num_rows($result); 

$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class') LIMIT $page,10";
$result = mysql_query($sql, $db) or die (mysql_error());
$row = mysql_fetch_array($result);
$num_rows = mysql_num_rows($result);

here is the part that i publish the results

      <?php
      while ($row = mysql_fetch_array($result))
      {
      echo "<font color='#800000'><a href='company.php?id=" . $row['id'] . "'><b>" . strtoupper($row['CompanyName']) . "</b></a>" . " " . "<font color='#000080'>" . "-" . "</font>" . " " . $row['CompanyType'] . "</font><br>";
      echo "<font color='#800000'>Products:</font>" . " " . strtolower($row['Products']) . "<br>";
      echo "<font color='#800000'>Introduction:</font>" . " " . $row['Introduction'] . "<br>";
      echo "<hr>";
      }
      ?>   

and here is my prev and next links

      <?php
      $a = ceil($n/10);

  If($start != 1){
  $prev = $start-1;
  echo "<a href='list.php?class=" . $class . "&start=" . $prev . "'>" . "Prev" . "</a>" . " ";
  } else {
  echo "Prev ";
  }

  for ($i=1; $i<=$a; $i++){
  	If ($i != $start){
  	echo "<a href='list.php?class=" . $class . "&start=" . $i . "'>" . $i . "</a>" . " ";
  	} else {
  	echo "<font color='#800000'><b>" . $i . "</b></font>" . " ";  
  	}
  }


  If($start != $a){
  $next = $start+1;
  echo "<a href='list.php?class=" . $class . "&start=" . $next . "'>" . "Next" . "</a>" . " ";
  }	else {
  echo "Next ";
  }
  ?>     

btw: i tried to set $page variable like 0, 10, 20 ... but no luck. the only remedy i had was displaying 11 results so i got 10 results but that was not what i was looking for.

[siwis]

Well, here's a very similar one to what you're trying to do that I created for a guestbook.

$gb_disp_num is a global variable and determines how many records to show per page.

#################	DISPLAYS THE CURRENT COMMENTS (10 PER PAGE IS DEFAULT)	################
			if (!isset($page))	{
				$page = 1;
			};
		$offset = ($page * $gb_disp_num) - $gb_disp_num;
		$gb_query = mysql_query("SELECT * FROM $gbtable ORDER BY id DESC LIMIT $offset, $gb_disp_num", $db);
		$gbentries = mysql_num_rows($gb_query);
		//Work out if we need another page:
		$gb_disp_num_new = $gb_disp_num + 1;
		$gb_next_query = mysql_query("SELECT * FROM $gbtable ORDER BY id DESC LIMIT $offset, $gb_disp_num_new", $db);
		$more_gbentries = mysql_num_rows($gb_next_query);
			if ($more_gbentries >  $gb_disp_num)	{
				$nextpage = 1;
			}	else	{
				$nextpage = 0;
			};
			if ($page != "1")	{
				$pageprev = $page - 1;
				$prevlink = "[<a href=\"$PHP_SELF?action=read&page=$pageprev\">Previous Page</a>]";
			};
			if ($nextpage == "1")	{
				$pagenext = $page + 1;
				$nextlink = " [<a href=\"$PHP_SELF?action=read&page=$pagenext\">Next Page</a>]";
			};

In your code, it will always miss 1 record because you're starting from key 1, whereas the rows are numbered from 0 therefore, row 0 (the first result) will always be missing. Likewise, on your second page, you'll omit key 10 and start at key 11 etc etc

For pages of 10 results, your looking for the following value sets:
0-9
10-19
20-29
etc etc

[buraks78]

you are right, however if you look at my query here

$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class') LIMIT $page,10";

i should be listing 10 results because of the 10 in "LIMIT $page,10"

$page variable is only the starting point. whatever i set it I should be seeing 10 records...

as a matter of fact setting the $page like 0, 10 , 20 was the first thing i did but it didnt work either...

thanks for the reply but thats not the answer i am looking for.

do you have any other idea?

Regards...

[siwis]

Yes, I'd noticed your wquery and quite agree that you are pulling out 10 records:

$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class') LIMIT $page,10";

BUT as you initially assign $page as being 1, then you start from record 1 NOT record 0 which is where you should start from.

In your code, $page will only ever be 1, 11, 21, 31, 41, 51 etc etc when ideally it should be 0,10,20,30 etc.

I know its not the answer to your main problem.

Have you checked that your LIMIT is indeed pulling out 10 records? in which case you can rule out the sql from being incorrect.
Have you tried doing a dummy count loop to check that your array contains 10 key/value pairs?
Does the number of pages generated correspond to the total number of records?
Does referencing the 2 sql's by different variable names have any effect? ie not re-using variable names in the 2 queries for $sql , $result and $row

[cgraz]

Try this

@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");
@mysql_select_db($database) or die("Couldn't Select Database");

$pagelimit = "10";

   $strSQL = mysql_query("SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class')");

   $totalrows = mysql_num_rows($strSQL);
   $pagenums = ceil ($totalrows/$pagelimit);

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

$start = ($page-1) * $pagelimit;

echo "<b>" . $totalrows . " matches found</b><br>\n";

$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}


echo "Results $starting_no to $end_count shown.<br>\n";

// next, previous, and page links

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$space = "&nbsp;";

// previous link 
if ($page>1) {
        echo "« <a href='list.php?class=".$class."&page=".($page-1)."'>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
    }

// dynamic page number links

for ($i=1; $i<=$pagenums; $i++) {
    if ($i!=$page) {
        echo " <a href='list.php?class=".$class."&page=$i'>$i</a>";
    }
    else {
        echo " <font color='#800000'><b>$i</b></font>";
    }
}


// next link

if ($page<$pagenums) {
    echo "" . $space . $space . $space . $space . " <a href='list.php?class=".$class."&page=".($page+1)."'>Next " . $var2 . "</a> »";
}


$query = mysql_query("SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class') LIMIT $start,$pagelimit");


  while ($row = mysql_fetch_array($query)) {

  echo "<font color='#800000'><a href='company.php?id=" . $row['id'] . "'><b>" . strtoupper($row['CompanyName']) . "</b></a>" . " " . "<font color='#000080'>" . "-" . "</font>" . " " . $row['CompanyType'] . "</font><br>";
  echo "<font color='#800000'>Products:</font>" . " " . strtolower($row['Products']) . "<br>";
  echo "<font color='#800000'>Introduction:</font>" . " " . $row['Introduction'] . "<br>";
  echo "<hr>";

  }

?> 

Cgraz

[buraks78]

i checked the limit query. it returns 10 records.
i also renamed the variables
the number of pages seems ok.

by the way thx for the codes but i guess no luck for me today.

do you think this problem is database (mysql) related?

Regards...

[cgraz]

Did you try my code? If you haven't give it a try. It should work no problem.

Aside from that, the only reason I can think of for it appearing to not return two rows is if those last two rows have no values in the particular fields you are outputting. YOu could also add a few fake entries, see if those display, or if it displays all except for the last two.

Cgraz

[xblue]

Hi,

remember that mysql_fetch_array returns one row each time it is called, always the next one. You call it once outside the while loop without displaying anything. That's probably where the first row went... And I can't see it's needed for anything, so try:

$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class') LIMIT $page,10";
$result = mysql_query($sql, $db) or die (mysql_error());
// $row = mysql_fetch_array($result);
$num_rows = mysql_num_rows($result);

This may not solve the particular problem with the second missing row on the last page, but might be a step into the right direction.

[buraks78]

thx xblue that is the answer
got the dam problem solved at last
i was calling the mysql_fetch_array twice even though i didnt need it

here is the latest version of the code

	$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class')";
	$result = mysql_query($sql, $db) or die (mysql_error());
	$n = mysql_num_rows($result); 

$offset = 10;
$sql = "SELECT * FROM Textile WHERE MATCH(Class) AGAINST('$class') LIMIT $page,$offset";
$result = mysql_query($sql, $db) or die (mysql_error());

and publish the results

      while ($row = mysql_fetch_array($result)){
      echo "<font color='#800000'><a href='company.php?id=" . $row['id'] . "'><b>" . strtoupper($row['CompanyName']) . "</b></a>" . " " . "<font color='#000080'>" . "-" . "</font>" . " " . $row['CompanyType'] . "</font><br>";
      echo "<font color='#800000'>Products:</font>" . " " . strtolower($row['Products']) . "<br>";
      echo "<font color='#800000'>Introduction:</font>" . " " . $row['Introduction'] . "<br>";
      echo "<hr>";
      }

thx all for your help...