Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    11

    Question Unanswered: PHP - DB2 - Pagination - UCASE???

    Hello All,

    I am trying to create a simple search using PHP to access my DB2 database and that is all fine and good BUT it is a large DB and I want to implement some pagination and only show the first 10 records - again that is all good!

    Here is my problem:
    I want to ucase columns b/c I have no idea what people are going to search and my DB is in both upper and lowercase. So, with my newly create query to limit my results, when I add ucase or lcase i get this error:

    - Function not supported for query. SQLCODE=-255

    w/o ucase or lcase all is fine.

    Here is my code:


    PHP Code:
    <?php

      
    // Get the search variable from URL

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

    //set a constraint
    $lim 10;

    // rows to return
    if (empty($limit)){
    $limit 10;
    }
    else{
    $limit $s 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("bob",   
                         
    "lob",
                         
    "law",
                         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 ucase(PKEYWD) LIKE '%$uppercase%' OR ucase(PITEM) LIKE '%$uppercase%' OR ucase(PSDESC) LIKE '%$uppercase%' OR ucase(PGENDR) LIKE '%$uppercase%') AS COL WHERE RN BETWEEN $s AND $limit ";


    $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] ;
       


    // 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 $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 />"

      
    // links to other results 
      
    if ($s >= 1) { // bypass PREV link if s is 0 
      
    $prevs=($s-$lim); 
      
    $prevs2=($limit-$lim);
      print 
    "&nbsp;<a href=\"$PHP_SELF?s=$prevs&limit=$prevs2&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%$lim) { 
      
    // has remainder so add one page 
      
    $pages++; 
      } 

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

      
    // not last page so give NEXT link 
      
    $news=$s+$lim
      
    $news2 $news 10;

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

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

    </body> 
    </html>

    Any thoughts or Ideas? Or possibly a different solution to accomplish pagination? Is this possibly a DB2 bug?

    A huge thanks in advance!!!
    j

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is UCASE a valid term in DB2 SQL?
    according to IBM it is

    Im just wondering if its a versioning thing.. that IBM used to use UPPER & LOWER and these may work with your db?

    it may be smarter to ask this sort of question in the DB2 section as its a DB2 issue not PHP, mind you it would help if you limit your posting of code to the sections that are causing the problems rather than the whole script. ... makes it easier for people responding to the question to nderstand whats going on.


    incidentally have you viewed the SQL you are sending to DB2.. are you certain its valid SQL, and its waht you think you have sent to the SQL engine

  3. #3
    Join Date
    Jan 2008
    Posts
    11
    Hello healdem,

    Thanks for the reply - i appreciate it.

    I have checked my SQL and it is correct as it will excute fine w/o the ucase() in place.

    Also, as far as ucase working on my verison db2 - I have used it in different query's such as:

    PHP Code:

    $sqlStatment 
    "SELECT * FROM O.ITEMPICT 
                                  WHERE ucase(PKEYWD) LIKE '%
    $words[$i]%' 
                                  OR ucase(PSDESC) LIKE '%
    $words[$i]%' 
                                  OR ucase(PITEM) LIKE '%
    $words[$i]%' 
                                  OR ucase(PSIZE) LIKE '%words[
    $i]%'"
    and it worked fine.

    for some reason when I attempt to limit my result w/

    ROW_NUMBER() OVER( ORDER BY )..... it errors w/ the usage of ucase/lcase. As far as using UPPER or LOWER I get an identical error SQLCODE-255.

    I will post this same thread in the db forum.


    Thanks again,
    j
    Last edited by jeepin81; 01-10-08 at 16:06.

  4. #4
    Join Date
    Dec 2008
    Posts
    10
    Hi Jeepin,
    I ran into a similar problem using the row_number and upper together. Could you please suggest how you solved it.


    Thanks
    -R

  5. #5
    Join Date
    Jan 2008
    Posts
    11
    Hello Ramanay,

    After lots of back and forth w/ this issue, I finally spoke to IBM and this is an error w/ DB2 and V5R4 - as of last year they were going to look into it and send out a patch.

    Otherwise, it is fixed V6R1.

    You can always use logic in your PHP coding to compensate for this issue and implement pagination.

    Hope that helps,
    j

  6. #6
    Join Date
    Dec 2008
    Posts
    10
    We use db2 with webmethods..we dont use php.. so i was wondering if i could do it on DB2.Ours is V5R4

    Thanks
    -R

  7. #7
    Join Date
    Jan 2008
    Posts
    11
    I am not familiar webmethods but I would have to assume it use SQL(Structured Query Language) which is actually where the root of this problem lies and I haven't seen any patches yet from IBM to deal w/ it.

    I'm also guessing webmethods is a template based software for development which would also leave you limited as far as resolving the problem w/ coding logic. Maybe contact webmethods support and see if they have some logic in place to get around this issue.

    Otherwise, upgrading to V6R1 might be your only solution as far as I know.

    Best,
    j

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •