Trying to start fetching from a specific row.

[Phrozt]

I've searched through tons of stuff and I can't find something that accurately does what I want it to.

-I'm trying to display a certain number of results on a page, and then the next set on the next page and so on.
-I cannot use the limit function because of the way the URL query string is built

this is what I currently use to populate each page (simplified):

<?php
	$tuxCount = 0;
	$row_rs_tuxedos = mysql_result($rs_tuxedos, $startTux);
	$row_rs_tuxedos = mysql_fetch_assoc($rs_tuxedos);
do { ?>
	 <?php echo stuff about the products ?><br />
<?php
	$row_rs_tuxedos = mysql_fetch_assoc($rs_tuxedos);
	$tuxCount++;
	} while ($tuxCount < $maxTuxes);
?>

I'm missing my first result (not on every page, but on the first) and somehow I'm missing the last 2 results... which is just weird

Any help is much appreciated

[laserlight]

I think something along these lines would be easier to see, and be more conventional.

$tuxCount = 0;
while ($row_rs_tuxedos = mysql_fetch_assoc($rs_tuxedos)) {
	//echo stuff about the products
	$tuxCount++;
	if ($tuxCount < $maxTuxes) {
		break;
	}
}

I'm assuming that $maxTuxes is some external value - if you obtained it from the result set, then you should just omit it and omit the ($tuxCount < $maxTuxes) test since the while loop takes care of looping through the result set.

[Roger_Ramjet]

laserlight wrote:

I think something along these lines would be easier to see, and be more conventional.

$tuxCount = 0;
while ($row_rs_tuxedos = mysql_fetch_assoc($rs_tuxedos)) {
	//echo stuff about the products
	$tuxCount++;
	if ($tuxCount < $maxTuxes) {
		break;
	}
}

I'm assuming that $maxTuxes is some external value - if you obtained it from the result set, then you should just omit it and omit the ($tuxCount < $maxTuxes) test since the while loop takes care of looping through the result set.

Laserlight, I think you mean

if ($tuxCount > $maxTuxes) {
		break;
	} 

[laserlight]

hmm... yeah. Actually, to keep to the original range, it should be:

if ($tuxCount >= $maxTuxes) {
	break;
}

But I suspect the whole check is unnecessary in the first place.

[Phrozt]

I'm sorry, I should have been more thorough; let me explain my values better:

$tuxCount = 0; //initializes the loop to 0
$row_rs_tuxedos = mysql_result($rs_tuxedos, $startTux); //row selected of the result set... this is the part I'm trying to fix.
$row_rs_tuxedos = mysql_fetch_assoc($rs_tuxedos);
$maxTuxes //the max amount of tuxes displayed on a page (in this case, 9)

Therefore ($tuxCount < $maxTuxes); checks to make sure that only 9 tuxes display

[Roger_Ramjet]

Phrotz, you are missing rows because you are moving the resultset cursor and then fetching the next row, so first time in you bypass row[0]. I guess the same sort of thing is happening when you reach the end of your resultset.

You don't show how you build your query so I'll take your word for it that you can't use Limit. Personally, I'd change the way the query is built such that you can use Limit.

Now, I've just finished writing a script to paginate results and I'm quite chuffed with it, so I'm full of 'advise' for you.

I store all the info needed from page to page in session vars, and then only have to pass the required page number in my back and forward links.

I even store the recordcount, Where and Order By clauses in session vars so that I can reconstruct the query from page to page and therefore use Limit.

I've attached my search and pagination scripts for you to see and hope it helps you. When the search results in more than one record the pagination script is included, otherwise it just includes the script to display the single record in full.

See if it helps

[Phrozt]

sigh.. it's Phrozt... I don't know why no one can spell it.. but anyway.

here's how I build my queries:

mysql_select_db($database_conn_seno, $conn_seno);
if (isset($_GET['brand_id'])) {
	$colname_rs_brand = (get_magic_quotes_gpc()) ? $_GET['brand_id'] : addslashes($_GET['brand_id']);
	$query_rs_tuxedos = sprintf("SELECT tuxStyles.style, brands.brand, tuxedos.id, tuxedos.name, tuxedos.style_id, tuxedos.brand_id, tuxedos.photoAlt, tuxedos.photoURL,  tuxedos.visible FROM tuxedos LEFT JOIN tuxStyles ON tuxedos.style_id = tuxStyles.id LEFT JOIN brands ON tuxedos.brand_id = brands.id WHERE tuxedos.brand_id = %s AND tuxedos.visible = 1 ORDER BY tuxStyles.style", $colname_rs_brand);
} else if (isset($_GET['color_id'])){
	$colname_rs_color = (get_magic_quotes_gpc()) ? $_GET['color_id'] : addslashes($_GET['color_id']);
	$query_rs_tuxedos = sprintf("SELECT tuxStyles.style, brands.brand, tuxedos.id, tuxedos.name, tuxedos.style_id, tuxedos.color_id, tuxedos.photoAlt, tuxedos.photoURL, tuxedos.visible FROM tuxedos LEFT JOIN tuxStyles ON tuxedos.style_id = tuxStyles.id LEFT JOIN brands ON tuxedos.brand_id = brands.id WHERE tuxedos.color_id = %s AND tuxedos.visible = 1 ORDER BY brands.brand ASC, tuxStyles.style", $colname_rs_color);
} else {
	$query_rs_tuxedos = "SELECT tuxStyles.style, brands.brand, tuxedos.id, tuxedos.name, tuxedos.style_id, tuxedos.photoAlt, tuxedos.photoURL, tuxedos.visible FROM tuxedos LEFT JOIN tuxStyles ON tuxedos.style_id = tuxStyles.id LEFT JOIN brands ON tuxedos.brand_id = brands.id WHERE tuxedos.visible = true ORDER BY brands.brand ASC, tuxStyles.style";
}
$rs_tuxedos = mysql_query($query_rs_tuxedos, $conn_seno) or die(mysql_error());
$row_rs_tuxedos = mysql_fetch_assoc($rs_tuxedos);
$numRows_rs_tuxedos = mysql_num_rows($rs_tuxedos);

The reason i do it as such is because there are three different ways to select tuxes; show them all, show tuxes of a certain brand, show tuxes of a certain color.

There can, of course, be multiple pages, which have to factor in the sort type as well... if you're interested, here's the code for that:

<?php
if ($numRows_rs_tuxedos > 9) {
	$maxTuxes = 9;
	$numTuxes = $numRows_rs_tuxedos; // Number of tuxes left to display
	$pageNum = 1;
	$catSet = isset($_GET['brand_id']) || isset($_GET['color_id']) ? true : false; //if a category is set or not
	$uriQuery = strpos($_SERVER['QUERY_STRING'], "&") > 0 ? substr($_SERVER['QUERY_STRING'], 0, strpos($_SERVER['QUERY_STRING'], "&")) : $_SERVER['QUERY_STRING']; //grabs the set category
	$pageURI = $catSet ? $_SERVER['PHP_SELF'] . "?" . $uriQuery : $_SERVER['PHP_SELF'];
	if (isset($_GET['page_num']) && $_GET['page_num'] * 9 <= $numTuxes && $_GET['page_num'] != 1)  {
		$setPage = $_GET['page_num'];
		$startTux = $setPage * $maxTuxes - $maxTuxes;
		if ($catSet) {
			echo "Page: <a href=\"" . $pageURI . "&page_num=1\">1</a>";
		} else {
			echo "Page: <a href=\"" . $pageURI . "?page_num=1\">1</a>";
		}
		$pageNum++;
		do {
			if ($pageNum == $setPage) {
				echo " | " . $pageNum;
			} else {
				if ($catSet) {
					echo " | <a href=\"" . $pageURI . "&page_num=" . $pageNum . "\">" . $pageNum . "</a>";
				} else {
					echo " | <a href=\"" . $pageURI . "?page_num=" . $pageNum . "\">" . $pageNum . "</a>";
				}
			}
			$pageNum++;
			$numTuxes -= $maxTuxes;
		} while ($numTuxes - $maxTuxes > 9);
	} else {
		$startTux = 0;
		echo "Page: " . $pageNum;
		$pageNum++;
		do {
			if ($catSet) {
				echo " | <a href=\"" . $pageURI . "&page_num=" . $pageNum . "\">" . $pageNum . "</a>";
			} else {
				echo " | <a href=\"" . $pageURI . "?page_num=" . $pageNum . "\">" . $pageNum . "</a>";
			}
			$pageNum++;
			$numTuxes -= $maxTuxes;
		} while ($numTuxes - $maxTuxes > 9);
	}
}
?>

[Roger_Ramjet]

Phrozt wrote:

sigh.. it's Phrozt... I don't know why no one can spell it.. but anyway.

sigh cos I'm dyslexic, :bemused:

Anyway, first thing is to change to using the function from the manual for escaping user input - safer than your addslashes

<?php
// Quote variable to make safe
function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

?> 

Now, it is still possible to build your query dynamically and pass the parameters form page to page even when you have multiple search criteria possible. I have exactly the same scenario in the code I posted. If we re-write your query this way it is both more elegent and more versatile

if (isset($_GET['brand_id'])) {
	$strW = ' tuxedos.brand_id = ' . quote_smart($_GET['brand_id']) . ' AND';
	$strO = ' tuxStyles.style';
} else if (isset($_GET['color_id'])){
	$strW = ' tuxedos.color_id = ' . quote_smart($_GET['color_id']) . ' AND';
	$strO = ' brands.brand ASC, tuxStyles.style';
} else {
	$strW = '';
	$strO = ' brands.brand ASC, tuxStyles.style';
}

$_SESSION['where'] = $strW;
$_SESSION['order'] = $strO;
$_SESSION['page'] = 1;
$_SESSION['$rpp'] = 9;

$query_rs_tuxedos = "SELECT tuxStyles.style, brands.brand, tuxedos.id, tuxedos.name, tuxedos.style_id, tuxedos.photoAlt, tuxedos.photoURL, tuxedos.visible 
					FROM tuxedos LEFT JOIN tuxStyles ON tuxedos.style_id = tuxStyles.id 
					LEFT JOIN brands ON tuxedos.brand_id = brands.id 
					WHERE" . $strW . " tuxedos.visible = true ORDER BY" . $strO;
$rs_tuxedos = mysql_query($query_rs_tuxedos, $conn_seno) or die(mysql_error());
$numRows_rs_tuxedos = mysql_num_rows($rs_tuxedos);
$_SESSION['count'] = $numRows_rs_tuxedos;

Now you have your Where and Order By parameters for this search stored in session vars and can reconstruct the query from page to page while using a Limit clause for pagination.

I do not use a limit clause the first time the query is run so that we get a true row count to control the pagination in later pages.

I have also ommitted the mysql_fetch_assoc cos that it what is causing you to loose the first row.

I am assuming that the script to display the results is elsewhere. I personally test rowcount and then include the pagination script if it is required. You can do likewise, or modify accordingly.

[Phrozt]

Thanks a lot for all your efforts.. I'm seeing some things I can use.

It appears that I need to study up a bit on sessions, because after looking at some of what you wrote, I can see some of the answers to my problems being simplified. I'd kinda like to stay away from using limit if I possibly can, but I think I might have to use it anyway :/.

I'm just really suprised that there isn't some easy command to sijmply navigate to a specific row..

Thanks again.

[Roger_Ramjet]

Yes, sessions are a wonderfull thing.

The only problem I've ever had with them was trying to use a session var in the same script right after creating it for the first time. Seems to be a timing problem cos it comes back empty. That is why I use the $strW system in the code I posted.