Go back one row

[bulrush]

I'v having trouble with a loop to group my results. I want to output multiple model numbers with a single model name but am having problems. I think if I go back one row when my model number loop (LOOP2) is done, that will fix my problem.

 

How do I go back one row? Would "$b=mysqli_data_seek($result,-1)" work?

 

Anyway, here is my code to construct a table. I want a new row when either $prodname, $prodsubname, or $modelnumcat changes.

    echo '<table border=1>';
    $s='<tr valign="bottom"><td width="40%">'.
    //'<b>Product category</b><br/>'.
    '<b>Name</b><br/>'.
    'Subname<br/>'.
    'Model Number Category<br/>'.
    '<b>Model(s)</b><br/>'.
    '<td valign="bottom"><b>Bullets</b>';
    echo "$s\n";
    
    // Loop through the records, formatting it as HTML 
    $oldprodname='';
    $oldprodsubname='';
    $oldmodelnumcat='';
    $oldgroup=''; //Determines when we make a new group.
    $newgroup='';
    $lcnt=0; //Count rows processed
    $MAXCNT=50; //Max records to show
    while (($row = mysqli_fetch_array($result)) and ($lcnt<=$MAXCNT)) 
        { 
        $partid=$row['partid'];
        $modelvar=$row['modelnum'];
        $modelnumcat=$row['modelnumcat'];
        $oldmodelnumcat=$modelnumcat;
        $prodname=$row['prodname'];
        $prodsubname=$row['prodsubname'];
        $newgroup=$prodname.$prodsubname.$modelnumcat;
        //$oldgroup=$newgroup;
        
        // Display new group if group changed.
if ($oldgroup <> $newgroup)
{
            echo "\n".'<tr>';
            echo '<td valign="top">';
            //echo crHtmlBold($row['prodcat']) . '<br/>'."\n";
            
            $s='<b>'.$row['prodname'].'</b><br/>';
            echo "$s\n";
            
            echo $row['prodsubname'].'<br/>'."\n";

            $s=$row['modelnumcat'].'<br/>';
            //$s=$oldmodelnumcat;
            echo "$s\n";

            //Loop through parts in this group.
            $oldgroup=$newgroup;
    do     			
            { //LOOP2
                $partid=$row['partid'];
                $modelvar=$row['modelnum'];
                $modelnumcat=$row['modelnumcat'];
                $prodname=$row['prodname'];
                $prodsubname=$row['prodsubname'];
                $newgroup=$prodname.$prodsubname.$modelnumcat;

	if ($oldgroup==$newgroup)
		{
                    $s='';
                    //$s.='('.$partid.') ';
                    $s.=crHtmlBold($modelvar).'<br/>';
                    echo "$s\n";
                    $s='model='.$modelvar.', <br/>oldgroup='.$oldgroup.
                    ', <br/>newgroup='.$newgroup;
                    //crDebug($s); //DEBUG

                    $lcnt++;
                	}
    			//$row=mysqli_fetch_array($result);
            	} //while LOOP2
            while (($row = mysqli_fetch_array($result)) 
                  and ($oldgroup==$newgroup) and ($lcnt<=$MAXCNT));

            echo '</td>'."\n";
        	} //if $oldgroup <> $newgroup
        	
        if (strlen($partid)==0)
        	{
        	$msg='Partid is missing: '.$partid.'<br/>'.$query;
            crError(basename($_SERVER['PHP_SELF']).' line '.__LINE__,$msg,true);
        	}
        
        //Now show bullets for this part group.
        echo '<td valign="top">'; //Start bullets
        $query2="SELECT bullettext FROM bullets WHERE partid=".$partid;
        $query2.=" ORDER BY bullid";
        $query2.=";";
        //crDebug($query2); //DEBUG
        
echo '<ul>';

        if (!$result2=mysqli_query($dbc,$query2))
            {
            $msg=mysqli_error($dbc);
            $msg.=$query2;
            crError(basename($_SERVER['PHP_SELF']).' line '.__LINE__,$msg,true);
            }
        $num2=mysqli_num_rows($result2);
        if ($num2==0)
            {
            //$msg='No bullets found for partid='.$partid.'<br/>'.$query2;
            //crError(basename($_SERVER['PHP_SELF']).' line '.__LINE__,$msg,false);
            $msg='(No bullets)';
            echo "$msg\n";
            }
        while ($row2 = mysqli_fetch_array($result2)) 
            { 
            // Display the bullets
            $b=$row2['bullettext'];
            //$b=preg_replace('/</','<',$b);
            //$b=preg_replace('/>/','>',$b);
            $b=preprocall($b);
            echo '<li>'.$b.'</li>';
            } # while $row2 (bullets)
            
        echo '</ul></td>';
        echo '</tr>'."\n";
        //$lcnt++;
        $oldgroup=$newgroup;
        } # while
    echo '</table><p/><p/>'."\n";
    

 

[shlumph]

Instead of going back one row, perhaps you should wait to go forward for the first iteration in the second loop.

[bulrush]

shlumph,

My "LOOP2" is a DO loop, which executes the mysqli_fetch_array at the end of the loop, so I'm already waiting to get the next record at the bottom of the inside loop. I'm not sure what you mean.

 

The mysqli_fetch_array needs to be part of the while condition in order to check for the end of the query set. Doing this:

do {
...
$row=mysqli_fetch_array(...)
}
while (($row) and ($oldgroup==$newgroup) and ($lcnt<=$MAXCNT));

 

did not exit the loop properly.

 

Also, the $lcnt check is simply used to exit the loop early during debugging. crDebug displays data to the screen in a different color, and is used only for debugging.

 

 

[shlumph]

Well, you can't go back one row, because what happens to the first item? That's why I suggested wait to go forward. Unless I'm misunderstanding something. There's probably a better design solution all together, but with what you already have, I suggest the above.

[bulrush]

You're right. I can't go back one row. After reading the docs again, mysqli_data_seek() only jumps to an absolute row, not a relative row.

 

I'm still stumped. It's not doing what I want.

 

[shlumph]

Keep a boolean field, $is_first_loop, initialized to true. Mark it as false at the end of the first loop. Then, only execute the second loop if !$is_first_loop.

 

It's a hackish solution, but I think this is what you are looking for.

 

[bulrush]

Shlumph,

Can you think of a better design solution, as far as constructing the loops? I have a recordset ordered by prodname, prodsubname, and modelnumcat. These 3 variables form a group, which can have multiple modelnum's.

 

So each time the group changes, I need to write a new table row. In the first column I write prodname, prodsubname, modelnum cat, then all model numbers. In the 2nd column I write out all bullet items related to this group.

 

Any ideas? I've never done grouping manually like this.