db2_num_rows - scrollable cursor

irish1381 · November 9, 2007

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 ' ';
     }

     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 ' ';
     }

     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

GingerRobot · November 9, 2007

The php manual suggests you do a SELECT COUNT(*) query to determine the number of rows.

irish1381 · November 9, 2007

Thanks GingerRobot for the reply!

If I did a SELECT COUNT(*) From ...

wouldn't that only return an integer of the affected rows? How count and select * to display the content that was searched?

Sorry, if I am missing something, I am somewhat new to the whole PHP world.

Thanks again!

John

GingerRobot · November 9, 2007

I think you'll need to do two separate queries.

Again, from the php manual "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."

Which suggest to me that two separate queries is the way to go.

Take a look for yourself: www.php.net/db2_num_rows

irish1381 · November 16, 2007

Hey Ginger - I got that all figured out, thanks!

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: "" . $uppercase . "" 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: "" . $uppercase . ""</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 ' '; 
     } 
     
  echo '<h3><font face=\"Arial, Helvetica, sans-serif\">'; 
  echo "$count.) $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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></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

irish1381 · November 17, 2007

are bumps allowed????

bump

wsantos · November 17, 2007

My approach for your conditions will be as followed:

1. Do the count query

2. use arithmetic to get the number of pages

$total = count($result);
if($numberofpages%$recordsperpage == 0?$numberofpages = $total/$recordsperpage:$numberofpages=$total/$recordsperpage + 1)

$start = 1 + $currentpage + $recordsperpage*$currentpage; // You have to make a test if its the extremes

3. Do a limited query upon request of page

$qry = "SELECT 'your fields' FROM 'your table' WHERE 'your filters' LIMIT $start,$recordsperpage";

haven't tested this though.

irish1381 · November 17, 2007

Hey wsantos - thanks for the reply.

I don't think I can use that though - I am using a DB2 database, not MySQL so the LIMIT query is out the window. That is why I am using the SELECT * FROM (SELECT ..... query.

Not really sure where to go from here?

Thanks though

irish1381 · November 26, 2007

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: "" . $uppercase . "" 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: "" . $uppercase . ""</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 ' '; 
     } 
     
  echo '<h3><font face=\"Arial, Helvetica, sans-serif\">'; 
  echo "$count.) $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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$uppercase\">Next 10 >></a>"; 
  } 

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

</body> 
</html>

Sign In

db2_num_rows - scrollable cursor

Recommended Posts

irish1381

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

irish1381

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

irish1381

Link to comment

Share on other sites

irish1381

Link to comment

Share on other sites

wsantos

Link to comment

Share on other sites

irish1381

Link to comment

Share on other sites

irish1381

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information