[RESOLVED] Missing first row when db values are displayed

[jantheman]

Hi everyone,

I have some php code which retrieves data from an Access db and lists the results which are then paginated to 15 entries per page.

All works fine except that the first page only returns 14 values, with the first db not being displayed (ie. for 100 entries it only displays 99). The first part of the code determining the results per page:

$page = $_GET["page"];
if($page == ""){
	$page = 1;
	}

$maxResults= 15;
$from = (($page * $maxResults) - $maxResults);
$max = ($page * $maxResults);

$eventsTotal = "select * from Events $sqlKeywds ORDER by $orderA";

This is followed by the retrieve/display code:

$rs = odbc_exec($conn, $eventsTotal) or die (odbc_errormsg());
				if(!odbc_fetch_row($rs)){
			exit ("<b>No Records Found</b></br>");}
				$i=0;			
			while (odbc_fetch_row($rs)){

			if (($i>=$from) and ($i<$max)){
			$id=odbc_result($rs, "ID");
			$name=odbc_result($rs, "Name");
			$date=odbc_result($rs, "DateA");
			$loc=odbc_result($rs, "Loc");
			$cat=odbc_result($rs, "Cat");
			$formattedDate = date("d/m/Y", strtotime($date));
			$formattedTime = date("h:i A", strtotime($date));

			echo "<tr><td><font face='Verdana, arial' size='1'><a href=\"eventEdit.php?ID= $id\">Edit</a>&nbsp;&nbsp;<a href=\"delete.php?ID= $id\">Remove</a></font></td>";
			echo "<td bgcolor='$col1'><font face='Verdana, arial' size='1'>$name</font></td>";
			echo "<td bgcolor='$col2'><font face='Verdana, arial' size='1'>$formattedDate <br />$formattedTime</font></td>";
			echo "<td bgcolor='$col1'><font face='Verdana, arial' size='1'>$loc</font></td>";
			echo "<td bgcolor='$col2'><font face='Verdana, arial' size='1'>$cat</font></td></tr>";
			if($bg == 1){
			$col1 = "#eeeeee";
			$col2 = "#dddddd";
			$bg = 0;
			}
			elseif($bg == 0){
			$col1 = "#cccccc";
			$col2 = "#bbbbbb";
			$bg = 1;
			}
		}
		++$i;
	}

I have left out some code to make it easier to follow (hopefully!). I have tried changing the i value to 1 and -1 but this returned 15 rows on the first page but only 9 on the last (ie. still 99) and 16 rows/page respectively.

Can anyone help with this?

Thanks.

[laserlight]

You call odbc_fetch_row() once before the while loop that extracts the rows in the result set.

Incidentally, if it is possible to use a LIMIT clause, I suggest you use that. Retrieving the entire table is silly when you only want a few rows, especially if the rows are near the beginning.

[jantheman]

Thanks Laserlight, you mean like this?:

$rs = odbc_exec($conn, $eventsTotal) or die (odbc_errormsg()); 
                if(!odbc_fetch_row($rs)){ 
            exit ("<b>No Records Found</b></br>");} 
                $i=0;       

             $rsA = odbc_fetch_row($rs);

        while ($rsA){ 

            if (($i>=$from) and ($i<$max)){ 
            $id=odbc_result($rs, "ID"); 
            $name=odbc_result($rs, "Name"); 
            $date=odbc_result($rs, "DateA"); 
            $loc=odbc_result($rs, "Loc"); 
            $cat=odbc_result($rs, "Cat"); 
            $formattedDate = date("d/m/Y", strtotime($date)); 
            $formattedTime = date("h:i A", strtotime($date)); 
 etc......

Unfortunately ODBC doesn't support the LIMIT clause. I spent hours trying to find an odbc workaround to pagination without using LIMIT! I'm don't have a huge amount of experience with PHP so bear with me if I have misunderstood your response!

[laserlight]

More like:

$rs = odbc_exec($conn, $eventsTotal) or die (odbc_errormsg());
if (odbc_num_rows($rs) == 0) {
	exit ("<b>No Records Found</b></br>");
}
$i = 0;          

while (odbc_fetch_row($rs)) {

[jantheman]

Thanks laserlight - that's worked!

Just so that I understand, what was the code trying to do when I was calling the odbc_fetch_row twice? How was that affecting the first entry dropping off?

[laserlight]

ust so that I understand, what was the code trying to do when I was calling the odbc_fetch_row twice? How was that affecting the first entry dropping off?

Since odbc_fetch_row() was called, the internal result set pointer was moved by 1. Thus in your loop you started with the second row in the result set.