[SOLVED] for loop increment problem

[flakturnal]

ok Hi. (appologies for the wall)

I'm new to php and am creating an inventory system for work displaying (at the moment) brands, styles and style details.

 

I'm using php5 on XP with xampp.

 

My start page asks the user to select a brand from a drop down list and submits, where it will then display the brandName (taken from brandID), the styleName, styleNumber, styleDescription, colour, cost and price.

 

database is as follows:

 

CREATE TABLE `brand` (

  `brandID` int(4) NOT NULL auto_increment,

  `brandName` varchar(50) collate latin1_general_ci NOT NULL,

  `description` varchar(200) collate latin1_general_ci default NULL,

  PRIMARY KEY  (`brandID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=33 ;

 

CREATE TABLE `details` (

  `styleID` int(7) NOT NULL auto_increment,

  `desc` varchar(50) collate latin1_general_ci NOT NULL,

  `price` double NOT NULL,

  `colour` varchar(20) collate latin1_general_ci NOT NULL,

  `cost` float NOT NULL,

  `brandID` int(4) NOT NULL,

  PRIMARY KEY  (`styleID`)

) ENGINE=MyISAM DEFAULT

 

CREATE TABLE `style` (

  `styleID` int(7) NOT NULL auto_increment,

  `styleNo` varchar(4) collate latin1_general_ci NOT NULL,

  `styleName` varchar(30) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`styleID`)

) ENGINE=MyISAM

Im using a for loop to display these details.

 

	for ($i=0; $i <$num_results; $i++)
{
			//$row = $result->fetch_assoc();
?>
	<tr><td> <? 
			echo $i+1;
			$styleID = $row['styleID'];
		          //$styleNo = $row['styleNo'];

			$query = "select * from style where styleID = '".$styleID."'";
			$result = $db->query($query);
			$row = $result->fetch_assoc();
			echo $row['styleNo'];?>-<?

			//$styleNo = $row['styleNo'];
			//$query = "select styleName from style where styleNo = '".$styleNo."'";
			//$result = $db->query($query);
			//$row = $result->fetch_assoc();
			echo $row['styleName'];?></td>
	</tr>		
	<tr bgcolor="#CCCCCC">
			<?
			$query = "select * from details where brandID = '".$brandID."'";
			$result = $db->query($query);
			$row = $result->fetch_assoc();

			?><td>Description</td><td>Colour</td><td>Cost</td><td>Price</td></tr>
			<tr><td><? echo $row['desc']; ?></td>
			<td><? echo $row['colour']; ?></td>
			<td>$<? echo number_format($row['cost'],2); ?></td>
			<td>$<? echo number_format($row['price'],2); ?></td>	
	</tr>
	<tr>
			<td></td>
	</tr>
	<?					
}

 

Currently if the brand selected ($_POST) has 14 styles, it prints out the correct amount of styles. However it prints out the first style 14 times.

Also the first row displayed does not show the stylename or number only the details(desc, colour, cost, price).

 

so it prints

Brand: <BrandName>

1 -                                                      <-- does not show styleName or No.

description: Colour: Cost:      Price:

boot          black    $##.00  $##.00

 

2.0061-Wadonga                                <-- correct row but repeats same style when it should print each style

description: Colour: Cost:      Price:

boot          black    $##.00  $##.00   

 

3.0061-Wadonga

description: Colour: Cost:      Price:

boot          black    $##.00  $##.00 

etc.

any help or advice would be greatly appreciated.

 

The commented out //$row = $result->fetch_assoc(); directly after the FOR statement condition. when placed back into the code makes the returned result skip the first style ie (0061) and begins with 0062 as the first style. (which is incorrect)

thanks again

[btherl]

Can you show us the code between your sql query and this for loop?

[flakturnal]

certainly.

 

$query = "select * from details where brandID = '".$brandID."'";  <-- $brandID = $_POST['searchterm'];
$result = $db->query($query);
$num_results = $result->num_rows;

if ($num_results == 0)
         {
	... back
                      }
echo '<p>Number of rows: '.$num_results.'</p>';
?>
<table border="1" align="left">
            <tr><th align="left">Brand: <? 
			$brandName = $row['brandName'];
			$query = "select brandName from brand where brandID = '".$brandID."'";
			$result = $db->query($query);
			$row = $result->fetch_assoc();
			echo $row['brandName'];?></th>
		</tr>
<?
	for ($i=0; $i <$num_results; $i++)

hope this helps

[btherl]

Thanks, I can see a problem already.  You are fetching one row before the for loop, which will explain why the first item is skipped.  You can solve that either by resetting the data pointer or by explicitly requesting your rows by number.

 

I'm not familiar with the OO interface you are using, so I can't give specifics on how to do that.

 

Also I just want to check, are you intending to reuse $result for both the first query and second query?

[flakturnal]

thanks for your replies.

 

I have thought of using $result->free();  to reset the resultset but found I can only use it once and not after every fetch->assoc();.  I have it at the end of my code (not included) before my $db close statement.

 

I am planning to reuse $result for both queries.  Is it possible to use different names? ie  $result1 ...$result2?

 

 

 

 

[btherl]

Yes you can use any name you want.  $result->free() frees every row in the result, so it can only be used when you are finished completely with that result.

 

If you want to reset the row pointer, there's probably a method like $result->data_seek()

[flakturnal]

Gotcha.  that makes sense.

I changed the $result to their own $result names.

Increments perfectly.

thanks again

 

 $query = "select * from details where brandID = '".$brandID."'";
$result5 = $db->query($query);
$num_results = $result5->num_rows;


?>
<table border="1" align="left">
            <tr><th align="left">Brand: <? $brandName = $row['brandName'];
	$query = "select brandName from brand where brandID = '".$brandID."'";
	$result = $db->query($query);
	$row = $result->fetch_assoc(); 				
	echo $row['brandName'];
                                              ?></th>
            </tr>
	<?
	for ($i=0; $i <$num_results; $i++)
{
$row = $result5->fetch_assoc();		
	?>		
	<tr><td><?
			$styleID = $row['styleID'];
			$query = "select * from style where styleID = '".$styleID."'";
			$result1 = $db->query($query);
			$row = $result1->fetch_assoc();
			echo $row['styleNo'];?> - <?
			echo $row['styleName'];?></td>		
	</tr>		
	<tr bgcolor="gray" style="color:white">
			<?
                           $query = "select * from details where styleID = '".$styleID."'";
			$result2 = $db->query($query);
			$row = $result2->fetch_assoc();

			?><td>Description</td><td>Colour</td><td>Cost</td><td>Price</td></tr>
			<tr><td><? echo $row['desc']; ?></td>
			<td><? echo $row['colour']; ?></td>
			<td>$<? echo number_format($row['cost'],2); ?></td>
			<td>$<? echo number_format($row['price'],2); ?></td>	
	</tr>
	<tr>
			<td></td>
	</tr>
	<?