dbs_num_rows / scrollable cursors

[jeepin81]

Hello All,

I am working on making a little search fuction for a site and I am running into an issue w/ db2_num_rows(). I want to count my results using this fuction but it only returns a boolen of true and errors.

Being new to db2 and somewhat new to PHP, I did some research and found you can't run a SELECT statement w/ the db2_num_rows, unless you are using a scrollable cursor. I tried that but that errors on my db2_prepare statement.

Is there different way to count my results or way to use the db2_num_rows function?

Here is my code w/ and w/o the scrollable cursor.

<?php

// change search criteria to all uppercase  

$uppercase = strtoupper($rdmitm);

// check for a valid search word != valid redirect back to search again
if (strlen($uppercase) <= 2){ 
header('Location: http://127.0.0.1:8000/TEST/ErrorSearch.html');
}

else{ 
$words = explode(' ',$uppercase); 
$totalwords = count($words); 
$i = 0; 
$searchstring = ""; 


// Loop Through Search String
while ($i != $totalwords){ 
if ($i != 0 and $i != $totalwords){ 
$searchstring .= " and "; 
} 
$searchstring .= "ucase(PKEYWD) LIKE '%$words[$i]%' OR ucase(PSDESC) LIKE '%$words[$i]%' OR ucase(PITEM) LIKE '%$words[$i]%' OR ucase(PSIZE) LIKE '%words[$i]%'"; 


// Incrementing the value 
$i = $i + 1; 
} 
}

$i5db2 = db2_connect("BLA", 
                     "BLAUSER",
                     "BLAPASS",
                     array("i5_lib"=>"files")) 
                     or die("Connect error: " . db2_conn_errormsg());



$sqlStatment = "SELECT * FROM O.ITEMPICT 
				WHERE $searchstring";     





$stmt = db2_prepare($i5db2, $sqlStatment) 
                    or die("Prepare error: " . db2_stmt_errormsg());

$result = db2_execute($stmt) 
          or die("Execute error: " . db2_stmt_errormsg());



$num_results = db2_num_rows($result);




  for ($i=0; $i <$num_results; $i++)
  {
     $row = db2_fetch_assoc($stmt);


} 

  echo "<p><h2>Here Are The Results For: <font color=\"red\">$uppercase</font></h2><br />";
  echo 'Number of Results Found: '.$num_results.'</p>';

  while ($row = db2_fetch_assoc($stmt))
{ 

   if (@file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) {
    echo '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />';
    }

 else{
  echo '&nbsp;';
 }

 echo '<p><h3><strong>'.($i+1).'. Product Number: ';
 echo htmlspecialchars(stripslashes($row['PITEM']));
 echo '</h3></strong><br /><strong>Description:</strong><br /> ';
 echo ucfirst($row['PSDESC']);
 echo '<br /><br /><strong>Size: </strong>';
 echo stripslashes($row['PSIZE']);
  echo '<br /><strong>Color: </strong>';
 echo ucfirst($row['PCOLOR']);
  echo '<br /><strong>Gender: </strong>';
 echo ucfirst($row['PGENDR']);
  echo '<br /><strong>Material: </strong>';
 echo ucfirst($row['PMATRL']);
 echo '<br /><strong>2007 Catalog Page:  </strong>';
 echo ucfirst($row['PCCPGE']);
 echo "<hr>";
 echo '</p>';

  }
db2_close($i5db2);

############### with cursor I think #################

<?php

// change search criteria to all uppercase  

$uppercase = strtoupper($rdmitm);

// check for a valid search word != valid redirect back to search again
if (strlen($uppercase) <= 2){ 
header('Location: http://127.0.0.1:8000/TEST/ErrorSearch.html');
}

else{ 
$words = explode(' ',$uppercase); 
$totalwords = count($words); 
$i = 0; 
$searchstring = ""; 


// Loop Through Search String
while ($i != $totalwords){ 
if ($i != 0 and $i != $totalwords){ 
$searchstring .= " and "; 
} 
$searchstring .= "ucase(PKEYWD) LIKE '%$words[$i]%' OR ucase(PSDESC) LIKE '%$words[$i]%' OR ucase(PITEM) LIKE '%$words[$i]%' OR ucase(PSIZE) LIKE '%words[$i]%'"; 


// Incrementing the value 
$i = $i + 1; 
} 
}

$i5db2 = db2_connect("BLA", 
                     "BLAUSER",
                     "BLAPASS",
                     array("i5_lib"=>"files")) 
                     or die("Connect error: " . db2_conn_errormsg());




$sqlStatment = "DECLARE name SENSITIVE SCROLL CURSOR FOR  SELECT * FROM O.ITEMPICT 
				WHERE $searchstring";     





$stmt = db2_prepare($i5db2, $sqlStatment) 
                    or die("Prepare error: " . db2_stmt_errormsg());

$result = db2_execute($stmt) 
          or die("Execute error: " . db2_stmt_errormsg());



$num_results = db2_num_rows($result);




  for ($i=0; $i <$num_results; $i++)
  {
     $row = db2_fetch_assoc($stmt);


} 

  echo "<p><h2>Here Are The Results For: <font color=\"red\">$uppercase</font></h2><br />";
  echo 'Number of Results Found: '.$num_results.'</p>';

  while ($row = db2_fetch_assoc($stmt))
{ 

   if (@file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) {
    echo '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />';
    }

 else{
  echo '&nbsp;';
 }

 echo '<p><h3><strong>'.($i+1).'. Product Number: ';
 echo htmlspecialchars(stripslashes($row['PITEM']));
 echo '</h3></strong><br /><strong>Description:</strong><br /> ';
 echo ucfirst($row['PSDESC']);
 echo '<br /><br /><strong>Size: </strong>';
 echo stripslashes($row['PSIZE']);
  echo '<br /><strong>Color: </strong>';
 echo ucfirst($row['PCOLOR']);
  echo '<br /><strong>Gender: </strong>';
 echo ucfirst($row['PGENDR']);
  echo '<br /><strong>Material: </strong>';
 echo ucfirst($row['PMATRL']);
 echo '<br /><strong>2007 Catalog Page:  </strong>';
 echo ucfirst($row['PCCPGE']);
 echo "<hr>";
 echo '</p>';

  }
db2_close($i5db2);

Any insight would be greatly appreciate!

Thanks In Advance!
john

[bpat1434]

db2_num_rows uses the statement resource, not the result resource 😉

[jeepin81]

Thanks bpat - that cleared up my error but it only returns a value of -1, which I am guessing could refer to a boolean of false??? Wouldn't false return 0?

Here is a different search script I am having the same problem with. Very similar script but it limits the results to 10 per page and carrys the rest. I went ahead and made the same change here, and it also errors on db2_num_rows but completes the script??

Could this be a DB2 database issue, when using the db2_num_rows? I can get it to work on a MySQL database;

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable
  $uppercase = strtoupper($trimmed);

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($uppercase == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }



$i5db2 = db2_connect("bla",   

                     "blauser",
                     "blapass",
                     array("i5_lib"=>"files")) 
                     or die("Connect error: " . db2_conn_errormsg());



// SQL Query  

$query = "SELECT * 
		  FROM O.ITEMPICT 
		  WHERE ucase(PKEYWD) like '%$uppercase%' or ucase(PITEM) like '%$uppercase%' or ucase(PSDESC) like '%$uppercase$' or ucase(PGENDR) like '%$uppercase%' "; 

 $run=db2_prepare($i5db2, $query);
 $numresults=db2_exec($i5db2, $query);
 $numrows=db2_num_rows($run);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $uppercase . "&quot; returned zero results</p>";

// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $uppercase . "\" target=\"_blank\" title=\"Look up 
  " . $uppercase . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

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


// *****************************PROBLEM AREA*******************
// *****************************PROBLEM AREA*******************

 //$query .= " limit $s,$limit";
 //$result = db2_exec($i5db2, $query) or die("Couldn't execute query");

// *****************************PROBLEM AREA*******************
// *****************************PROBLEM AREA*******************



// display what the person searched for
echo "<p>You searched for: &quot;" . $uppercase . "&quot;</p>";

// begin to show results set
echo "<h3><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">Here Are Your Results:</font></h3>";
$count = 1 + $s ;

// now you can display the results returned
  while ($row = db2_fetch_assoc($numresults)) {
  $title = $row['PITEM'];



   if (@file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) {
        echo '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />';
		echo '<br />';
        }

 else{
  echo '&nbsp;';
 }

  echo '<h3><font face=\"Arial, Helvetica, sans-serif\">';
  echo "$count.)&nbsp;$title" ;
  $count++ ;
  echo '<br />';
  echo '</font></h3>';
   //echo '<p><h3><strong>'.($count++).'. Product Number: ';
     //echo htmlspecialchars(stripslashes($row['PITEM']));
     echo '<br /><strong><font face=\"Arial, Helvetica, sans-serif\">Description:</strong><br /> ';
     echo ucfirst($row['PSDESC']);
     echo '<br /><br /><strong>Size: </strong>';
     echo stripslashes($row['PSIZE']);
	  echo '<br /><strong>Color: </strong>';
     echo ucfirst($row['PCOLOR']);
	  echo '<br /><strong>Gender: </strong>';
     echo ucfirst($row['PGENDR']);
	  echo '<br /><strong>Material: </strong>';
     echo ucfirst($row['PMATRL']);
	 echo '<br /><strong>2007 Catalog Page Number:  </strong></font><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">';
	 echo ucfirst($row['PCCPGE']);
	 echo "</font><hr>";
	 echo '</p>';
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;";
  }

// 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++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";

?>

</body>
</html>

Also, w/ the above I am having an issue w/ the "problem area" - when those aren't commented out - it also errors on the db2_num_rows?

Here is the error message.

Warning: db2_num_rows() [function.db2-num-rows]: SQLRowCount failed in /www/zendcore/htdocs/TEST/search.php on line 44

Results
Sorry, your search: "BASEBALL" returned zero results

Click here to try the search on google

Warning: db2_execute() expects parameter 2 to be array, string given in /www/zendcore/htdocs/TEST/search.php on line 70
Execute error:

From what I read in regards to the db2_num_rows function, it appears when I run a SELECT statement, I need to declare a scrolling cursor b/c the with the DB2 the db2_num_rows will only return a boolean value.

If that is the case would I declare that in my SELECT statement or does that need to be declared before hand? Actually I am not too familar with cursors, so I am not sure where to start - maybe:

$sqlStatment = "DECLARE name SENSITIVE SCROLL CURSOR FOR  SELECT * FROM O.ITEMPICT 
                WHERE $searchstring";   

Sorry if the is vague.

Thanks for your help!!
John

[bpat1434]

Okay, the problem area most likely comes from the fact that you prepare the $query and then try to append to it and don't re-prepare it. So the query doesn't have the limit. But i can't be sure.

I'm not a db2 expert, the only thing I can do is go to the manual and look at the code. I don't use db2, I use mySQL. Now, in reading the manual, db2_num_rows is for DELETE, UPDATE, or INSERT statements. Now, here's a direct quote:

Returns the number of rows deleted, inserted, or updated by an SQL statement.

To determine the number of rows that will be returned by a SELECT statement, issue SELECT COUNT(*) with the same predicates as your intended SELECT statement and retrieve the value.

Now, to get the number of rows you'd want execute a SELECT COUNT(*) .... and use that.

[jeepin81]

Ok, I went ahead a created a second query issuing the SELECT COUNT(*) and I am able to get my count using a while loop but I cannot pass that variable throughtout my script?? I can echo it out while encapsulated in the while loop but it stops there and I get no data for the rest of my $num_results?

Is this just awful coding or am I missing something on why this wont pass or better yet possibly both???

here is where I am at:

  <?php

// change search criteria to all uppercase  

$uppercase = strtoupper($rdmitm);

// check for a valid search word != valid redirect back to search again
if (strlen($uppercase) <= 2){ 
header('Location: http://10.1.2.3:8000/TEST/ErrorSearch.html');
echo "Search Term Error - Please Enter Valid Search Term"; 
}

else{ 
$words = explode(' ',$uppercase); 
$totalwords = count($words); 
$i = 0; 
$searchstring = ""; 



while ($i != $totalwords){ 
if ($i != 0 and $i != $totalwords){ 
$searchstring .= " and "; 
} 
$searchstring .= "ucase(PKEYWD) LIKE '%$words[$i]%' OR ucase(PSDESC) LIKE '%$words[$i]%' OR ucase(PITEM) LIKE '%$words[$i]%' OR ucase(PSIZE) LIKE '%words[$i]%'"; 


// Incrementing the value 
$i = $i + 1; 
} 
}



$i5db2 = db2_connect("name", 
                     "user",
                     "pass",
                     array("i5_lib"=>"files")) 
                     or die("Connect error: " . db2_conn_errormsg());





$sqlStatment = "SELECT *
			    FROM O.ITEMPICT 
				WHERE $searchstring";     





$stmt = db2_prepare($i5db2, $sqlStatment) 
                    or die("Prepare error: " . db2_stmt_errormsg());
$result = db2_execute($stmt) 
                    or die("Execute error: " . db2_stmt_errormsg());


$sqlCount = "SELECT COUNT(*)
			    FROM O.ITEMPICT
				WHERE $searchstring ";     




$resultsC = db2_exec($i5db2, $sqlCount);


while ($num_results = db2_fetch_array($resultsC)){
echo  $num_results[0] ;
}    


//$num_results = db2_num_rows($stmt);




 for ($i=0; $i <$num_results; $i++)
  {
     $row = db2_fetch_assoc($stmt);


} 

  echo "<p><h2>Here Are The Results For: <font color=\"red\">$uppercase</font></h2><br />";
  echo 'Number of Results Found: '. $num_results .' </p>';

  while ($row = db2_fetch_assoc($stmt))
{ 

   if (@file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) {
    echo '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />';
    }

 else{
  echo '&nbsp;';
 }

 echo '<p><h3><strong>'.($i+1).'. Product Number: ';
 echo htmlspecialchars(stripslashes($row['PITEM']));
 echo '</h3></strong><br /><strong>Description:</strong><br /> ';
 echo ucfirst($row['PSDESC']);
 echo '<br /><br /><strong>Size: </strong>';
 echo stripslashes($row['PSIZE']);
  echo '<br /><strong>Color: </strong>';
 echo ucfirst($row['PCOLOR']);
  echo '<br /><strong>Gender: </strong>';
 echo ucfirst($row['PGENDR']);
  echo '<br /><strong>Material: </strong>';
 echo ucfirst($row['PMATRL']);
 echo '<br /><strong>2007 Catalog Page:  </strong>';
 echo ucfirst($row['PCCPGE']);
 echo "<hr>";
 echo '</p>';

  }
db2_close($i5db2);

Many thanks in advance and if I figure it out, I will post solution:

Thanks again,
j

[bpat1434]

$num_results = db2_result($stmt, 0);

[jeepin81]

Thanks Bpat - that worked!

I now have a new problem though - that being pagination. Apologies for the all question but it seems to be one road bump after another.

My query will run and the the counts will display correctly but no data shows at all?

Maybe another set of eyes will be able to see an issue.

here is the code:

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable
  $uppercase = strtoupper($trimmed);

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($uppercase == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }



$i5db2 = db2_connect("xxx",   

                     "xxx",
                     "xxx",
                     array("i5_lib"=>"files")) 
                     or die("Connect error: " . db2_conn_errormsg());




// SQL Query   



$query = "select * from (SELECT PKEYWD, PITEM, PSDESC, PGENDR, PSIZE,
 PCOLOR, PMATRL, ROW_NUMBER() OVER() AS RN FROM O.ITEMPICT WHERE 
PKEYWD LIKE '%$uppercase%' OR PITEM LIKE '%$uppercase%' OR PSDESC 
LIKE '%$uppercase%' OR PGENDR LIKE '%$uppercase%') AS COL WHERE RN 
BETWEEN 1 AND 10";


$run=db2_prepare($i5db2, $query); 
$numresults=db2_exec($i5db2, $query)
   or die("Execute error: " . db2_stmt_errormsg()); 


$sqlCount = "SELECT COUNT(*) 
          FROM O.ITEMPICT 
          WHERE ucase(PKEYWD) like '%$uppercase%' or ucase(PITEM) like '%
$uppercase%' or ucase(PSDESC) like '%$uppercase%' or ucase(PGENDR) like '%
$uppercase%' ";     




$resultsC = db2_exec($i5db2, $sqlCount);





$num_results = db2_fetch_array($resultsC);
$num =  $num_results[0] ;


// offer a google search as an alternative 

if ($num == 0) 
  { 
  echo "<h4>Results</h4>"; 
  echo "<p>Sorry, your search: &quot;" . $uppercase . "&quot; returned zero results</p>"; 

// google 
echo "<p><a href=\"http://www.google.com/search?q=" 
  . $uppercase . "\" target=\"_blank\" title=\"Look up 
  " . $uppercase . " on Google\">Click here</a> to try the 
  search on google</p>"; 
  } 

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



// display what the person searched for 
echo "<p>You searched for: &quot;" . $uppercase . "&quot;</p>"; 

// begin to show results set 
echo "<h3><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">Here Are Your Results:</font></h3>"; 
$count = 1 + $s ; 

// display the results returned 
  while ($row = db2_fetch_assoc($numresults)) { 
  $title = $row['PITEM']; 



   if (@file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) { 
        echo '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />'; 
        echo '<br />'; 
        } 

 else{ 
  echo '&nbsp;'; 
 } 

  echo '<h3><font face=\"Arial, Helvetica, sans-serif\">'; 
  echo "$count.)&nbsp;$title" ; 
  $count++ ; 
  echo '<br />'; 
  echo '</font></h3>'; 
   //echo '<p><h3><strong>'.($count++).'. Product Number: '; 
     //echo htmlspecialchars(stripslashes($row['PITEM'])); 
     echo '<br /><strong><font face=\"Arial, Helvetica, sans-serif\">Description:</strong><br /> '; 
     echo ucfirst($row['PSDESC']); 
     echo '<br /><br /><strong>Size: </strong>'; 
     echo stripslashes($row['PSIZE']); 
      echo '<br /><strong>Color: </strong>'; 
     echo ucfirst($row['PCOLOR']); 
      echo '<br /><strong>Gender: </strong>'; 
     echo ucfirst($row['PGENDR']); 
      echo '<br /><strong>Material: </strong>'; 
     echo ucfirst($row['PMATRL']); 
     echo '<br /><strong>2007 Catalog Page Number:  </strong></font><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">'; 
     echo ucfirst($row['PCCPGE']); 
     echo "</font><hr>"; 
     echo '</p>'; 
  } 

$currPage = (($s/$limit) + 1); 

//break before paging 
  echo "<br />"; 

  // do links to other results 
  if ($s>=1) { // bypass PREV link if s is 0 
  $prevs=($s-$limit); 
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;"; 
  } 

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

// $pages now contains int of pages needed unless there is a remainder from division 

  if ($num%$limit) { 
  // has remainder so add one page 
  $pages++; 
  } 

// check to see if last page 
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) { 

  // not last page so give NEXT link 
  $news=$s+$limit; 

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>"; 
  } 

$a = $s + ($limit) ; 
  if ($a > $num) { $a = $num ; } 
  $b = $s + 1 ; 
  echo "<p>Showing results $b to $a of $num</p>"; 

?> 

</body> 
</html> 

Again, thanks for the help - I appreciate it!

j

EDIT: minor sql syntax errors corrected.

[bpat1434]

I have no idea.... looks good to me... I'm no db2 expert though. Have you tried running the query directly on the database rather than through PHP to see what it spits out? You know, running it through a utility like phpMyAdmin; however, obviously it'd be for your database type db2.

[jeepin81]

I think I am somewhat close here. I can get this to limit my result to ten but when I hit the next button my $limit variable doesnt change/add to the right amount in my query?? It stays at ten, so my results don't show?

My $s variable is adding fine which in turn should change my limit?

Any ideas?

Also, can use I "ucase" or "lcase" in this type of query? When I do that now, i get a db error?

<?php

  // Get the search variable from URL

$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
$uppercase = $trimmed;
//$uppercase = strtoupper($trimmed);

// rows to return
$limit = 10;

  if (empty($s)) { 
  $s=0; 
  } 

// check for an empty string and display a message.
if ($uppercase == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }


//connect to database
$i5db2 = db2_connect("arrested",   

                     "development",
                     "bobloblaw",
                     array("i5_lib"=>"files")) 
                     or die("Connect error: " . db2_conn_errormsg());



//search query
$query = "select * from (SELECT PKEYWD, PITEM, PSDESC, PGENDR, PSIZE, PCOLOR, PMATRL, PCCPGE, ROW_NUMBER() OVER( ORDER BY PSIZE) AS RN FROM O.ITEMPICT WHERE PKEYWD LIKE '%$uppercase%' OR PITEM LIKE '%$uppercase%' OR PSDESC LIKE '%$uppercase%' OR PGENDR LIKE '%$uppercase%') AS COL WHERE RN BETWEEN $s AND $limit FOR READ ONLY";


$run=db2_prepare($i5db2, $query); 
$numresults=db2_exec($i5db2, $query)
   or die("Execute error: " . db2_stmt_errormsg()); 




$sqlCount = "SELECT COUNT(*) 
          FROM O.ITEMPICT 
          WHERE PKEYWD like '%$uppercase%' or PITEM like '%$uppercase%' or PSDESC like '%$uppercase$' or PGENDR like '%$uppercase%' ";     


$resultsC = db2_exec($i5db2, $sqlCount)
	 or die("Execute error on count query: " . db2_stmt_errormsg());



$num_results = db2_fetch_array($resultsC);
$num =  $num_results[0] ;


// google search as an alternative 

if ($num == 0) 
  { 
  echo "<h4>Results</h4>"; 
  echo "<p>Sorry, your search: &quot;" . $uppercase . "&quot; returned zero results</p>"; 

// google 
echo "<p><a href=\"http://www.google.com/search?q=" 
  . $uppercase . "\" target=\"_blank\" title=\"Look up 
  " . $uppercase . " on Google\">Click here</a> to try the 
  search on google</p>"; 
  } 


// display what the person searched for 
echo "<p>You searched for: &quot;" . $uppercase . "&quot;</p>"; 

// begin to show results set 
echo "<h3><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">Here Are Your Results:</font></h3>"; 
$count = 1 + $s ; 

// now display the results returned 
  while ($row = db2_fetch_assoc($numresults)) { 
  $title = $row['PITEM']; 



   if (@file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) { 
        echo '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />'; 
        echo '<br />'; 
        } 

 else{ 
  echo '&nbsp;'; 
 } 

  echo '<h3><font face=\"Arial, Helvetica, sans-serif\">'; 
  echo "$count.)&nbsp;$title" ; 
  $count++ ; 
  echo '<br />'; 
  echo '</font></h3>'; 
   //echo '<p><h3><strong>'.($count++).'. Product Number: '; 
     //echo htmlspecialchars(stripslashes($row['PITEM'])); 
     echo '<br /><strong><font face=\"Arial, Helvetica, sans-serif\">Description:</strong><br /> '; 
     echo ucfirst($row['PSDESC']); 
     echo '<br /><br /><strong>Size: </strong>'; 
     echo stripslashes($row['PSIZE']); 
      echo '<br /><strong>Color: </strong>'; 
     echo ucfirst($row['PCOLOR']); 
      echo '<br /><strong>Gender: </strong>'; 
     echo ucfirst($row['PGENDR']); 
      echo '<br /><strong>Material: </strong>'; 
     echo ucfirst($row['PMATRL']); 
     echo '<br /><strong>2007 Catalog Page Number:  </strong></font><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">'; 
     echo ucfirst($row['PCCPGE']); 
     echo "</font><hr>"; 
     echo '</p>'; 
  } 

$currPage = (($s/$limit) + 1); 

//break before paging 
  echo "<br />"; 

  // next we need to do the links to other results 
  if ($s >= 1) { // bypass PREV link if s is 0 
  $prevs=($s-$limit); 
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;"; 
  } 

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

// $pages now contains int of pages needed unless there is a remainder from division 

  if ($num%$limit) { 
  // has remainder so add one page 
  $pages++; 
  } 

// check to see if last page 
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) { 

  // not last page so give NEXT link 
  $news=$s+$limit; 

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$uppercase\">Next 10 &gt;&gt;</a>"; 
  } 

$a = $s + ($limit) ; 
  if ($a > $num) { $a = $num ; } 
  $b = $s + 1 ; 
  echo "<p>Showing results $b to $a of $num</p>"; 

?> 

</body> 
</html> 

[bpat1434]

$limit will never change unless you change your coding.... notice how you statically set $limit to 10 on like the 4th line.