Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    24

    Unanswered: Headache trying to skip records

    Hi Folks,

    I'm in a situation which is becoming a SERIOUS headache, let me explain a bit. Basically I need to do a query within a query, on the same page but in 2 different spots. In the original result set, the column 'catcode' holds anything from a 5 character to a 12 character code. This catcode is a combination, the first 5 denotes a category, and the other 7 would denote a different key for a different record. For example:

    Results for catcode 1001111:
    1MB S | this means it's a member
    1MFG11001718 | would be a related manufacturer (1MFG) with the key 1001718.
    PC M7M 7M7 | This means it's postal code is M7M 7M7

    I know it sounds messed up, but what I need to accomplish is query the catcode of these records, and find out if they're members or not. Here is the code:

    Code:
      <?php
      while (!$newrs->EOF) {
    ?>
    				<?
    				if (strlen($newrs->Fields('catcode')) < 7) {
    				$newrs->MoveNext();
    				}
    				$newkey = (right($newrs->Fields('catcode'),7));
    				$valrs = $db->get_col("SELECT category.contcode, category.catcode, ccontrib.busname, ccontrib.bcity, ccontrib.bprov FROM ccontrib, category WHERE ccontrib.contcode=category.contcode AND category.contcode = '" . $newkey . "' ORDER BY catcode", 1) ;
    				if (array_search("1MB O", $valrs)) {
    				 $validated = "Not a member";
    				 }
    				if (array_search("1MB P", $valrs)) {
    				 $validated = "Not a member";
    				 }
    				if (array_search("1MB N", $valrs)) {
    				 $validated = "A member";
    				 }
    				if (array_search("1MB S", $valrs)) {
    				 $validated = "A member";
    				 }
    				if (array_search("1MB V", $valrs)) {
    				 $validated = "A member";
    				 }
    				?>
    		<? if ($validated) {
    			echo $validated ;
    //			$newrs->EOF;
    			break;
    			}
    			?>
      <tr>
        <td><?php echo $newrs->Fields('catcode'); ?></td>
      </tr>
      <?php
        $newrs->MoveNext();
      }
    ?>
    I use the ez_sql library from http://php.justinvincent.com/ to grab the column catcode as an array, and look for the "1MB O" or "1MB P" flags to tell me if the record is valid or not. There are a couple of trouble spots in particular I have.

    First, why doesn't it skip go to the next record when it finds one with a length of less than 7?
    Code:
    <?
    				if (strlen($newrs->Fields('catcode')) < 7) {
    				$newrs->MoveNext();
    				}
    This is truly the main problem I have here! I have stared at this and rewritten it a thousand times, but it never works. It's gotta be my syntax.

    Thanks a ton guys, hope you can help!
    -DW

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Re why it doesn't skip a record, well, it should but it will only skip one. To make it work like that you need the following code;

    PHP Code:
    <?php
    //firstly where is the SQL for this statement...
    while (!$newrs->EOF)
    {
        if (
    strlen($newrs->Fields('catcode')) >= 7)
        {

            
    $newkey = (right($newrs->Fields('catcode'),7));
            
    $valrs $db->get_col("SELECT category.contcode, category.catcode, ccontrib.busname, ccontrib.bcity, ccontrib.bprov FROM ccontrib, category WHERE ccontrib.contcode=category.contcode AND category.contcode = '" $newkey "' ORDER BY catcode"1) ;
        
            if (
    array_search("1MB O"$valrs))
            {
                
    $validated "Not a member";
            }
        
            if (
    array_search("1MB P"$valrs))
            {
                
    $validated "Not a member";
            }
        
            if (
    array_search("1MB N"$valrs))
            {
                
    $validated "A member";
            }
        
            if (
    array_search("1MB S"$valrs))
            {
                
    $validated "A member";
            }
        
            if (
    array_search("1MB V"$valrs)) {
                
    $validated "A member";
            }


            if (
    $validated) {
                echo 
    $validated ;
                
    //            $newrs->EOF;
                
    break;
            }
    ?>
      <tr>
        <td><?php echo $newrs->Fields('catcode'); ?></td>
      </tr>
    <?php
        
    }
        
    $newrs->MoveNext();
    }
    ?>
    This should solve the record skipping problem, which was an obvious (to 3rd party - the most annoying) error...assuming I read your code right...

    But to be honest, the way your doing it seems mad, as you can do (seemingly) the queries you are trying to do with just SQL itself. Post both your SQL statements (one is missing from the above), and I will have a look. Also what database are you using, as some things may not be possible in such db's as access.

  3. #3
    Join Date
    Oct 2002
    Posts
    24

    Worked

    Hi rhs98, I see the logic now, thanks for the direction! You basically need to put the loop in the entire code, not just a tiny one at the beginning! Heh. Oops!

    Now for the new problem - new post. Thanks for your help!

Posting Permissions

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