[RESOLVED] Loop not producing correct results

[dmacman1962]

Hi All,

I have an if, while loop and I am getting results, just not correct results.

Like...

If I choose the the video tab, from the page that leads to this one, I should get all the video links, except the page I am on.

If I choose videos (prodType=1) and click on the first video, (prodID=1), I do not get prodID=2, but I do get 3-5?

If I choose the books, (prodType=2) and go to this page, I get videos 2-4 (which I should not get any, but why did 1 not show up and the rest did?

I hope this makes more sense after yo read my code for this query/loop.

$sql2 = "Select prodName, prodID, prodType, prodGroup from tblProduct Where prodType='1' AND prodID !=". $prodID ." ORDER BY prodName ASC";  

    $result2 = mysql_query($sql2);
		 echo "<hr>";
			if (mysql_result($result2,0,"prodType")=="1"){
	while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
   echo "<a href=\"http://www.intheclassroom.org/noFrames/products/product.php?prodID=".$row2['prodID']."\">".$row2['prodName']."</a>"; 
	echo "<br>";
}  

}

This code should only display results if the prodType=1 and the results should not show the current prodID.

Thanks,

Don

[konsu]

hello, you use mysql_result() in your if statement that checks if prodType is 1. first of all, according to your query all results are of type 1, so you do not need to check, second, when you call mysql_result() it retrieves the current row from the result set, so that is why you miss the first row.

[dmacman1962]

Thanks Konsu,

I appreciate the insights.

I understand that I do not need to check to see if the prodType = 1 since that is what I am grabbing, but I do not understand this part.

"second, when you call mysql_result() it retrieves the current row from the result set, so that is why you miss the first row."

When I rem out the "if (mysql..." out, I get all the products when I am in prodType=1, but when I am viewing prodType=2 or 3, I get the list of prodType=1 still.

What I am trying to do is when viewing prodType=1 (videos) I get the other video links that I am not viewing.

When I am viewing prodType=2 or other, I do not want the links to prodType=1 to display.

I know I am close and your insight helped me keep my code lean.

Thanks in advance, again.

Don

[laserlight]

Basically, your unneeded call to mysql_result() causes the row pointer to move to the first row. When you start looping with mysql_fetch_array(), you are now processing the second row onwards.

You should provide an overview of what you are trying to do. In this case, you are not clear on what exactly you expect should happen, and you do not explain your database schema at all.

[dmacman1962]

Hi Laserlight,

Thanks for taking the time to guide me in the right direction.

I will try to explain what I trying to achieve.

Here is the test site I am working on.

(Disclaimer) I did not build the original site or database. I just started working for this company and I am trying to help them design a more user-friendly site for the teachers.

http://intheclassroom.org/noFrames/products/index.php

When you go to this page there are 3 categories.

Videos (prodType=1)

Books (prodType=2)

Merchandise & Apparel (prodType=3)

When you go to any of the Videos, you will get the info for that item and the links at the bottom, middle are for the "other" items in the Video category.

But, when you go to the Books or Merchandise category, it should not display the links to the videos.

This is where I am stuck. If you go to the books, it will show the video links at the bottom.

I am trying to build a loop to show only the links for that category.

Here is my code for that section (at the bottom of the page for the links):

<?PHP  

$sql2 = "Select prodName, prodID, prodType, prodGroup from tblProduct Where prodType='1' AND prodID !=". $prodID ." ORDER BY prodName ASC";  

    $result2 = mysql_query($sql2);
		 echo "<hr>";
	while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
   echo "<a href=\"http://www.intheclassroom.org/noFrames/products/product.php?prodID=".$row2['prodID']."\">".$row2['prodName']."</a>"; 
	echo "<br>";
}  

?>

The databse shcema is as follows:

There are 5 tables in the database.

1) tblItems (this is the pricing, taxable, info, etc for each item)
2) tblMedia (this is the video links for the realmedia snippets of video)
3) tblProduct (this is where the descriptions, comments, etc are for each item)
4) tblSchool (this is the info for all the schools, locations, etc.)
5) tblStates (this is info for each state, tax, regulations, etc)

I hope this is comprehensive enough to show what I have and where I am trying to go.

As always, I appreciate your knowledge and gift in helping.

Don

[laserlight]

Oh, ok.

Let's take a look at your SQL statement:

SELECT prodName, prodID, prodType, prodGroup
	FROM tblProduct
	WHERE prodType='1' AND prodID != $prodID
	ORDER BY prodName ASC;

It looks like you have hardcoded prodType in the WHERE clause. You need to change it such that prodType takes in the current selection.

[dmacman1962]

Hi Laserlight,

I hardcoded the data for prodType, because I tried to encode it in the url from the index.php page and could not get it to work.

I could get this to work:

http://www.intheclassroom.org/noFrames/products/product.php?prodID=1

but not this...

http://www.intheclassroom.org/noFrames/products/product.php?prodID=1?prodType=1

If I could encode the prodType and the prodID, then use that to select the correct item AND only display the correct prodType, that would be what I am trying to achieve.

Unfortunately, I am not that advanced and spent quite a bit of time trying to get it to work, then decided to harcode the prodType to at least get it to only show the links if they applied.

Then I was going to go back and try to fix the url route.

I could not conquer both issues at once and thought one at a time would be achievable.

I am on track with this?

Thanks,

Don

[laserlight]

oh.
You are looking to use:
http://www.intheclassroom.org/noFrames/products/product.php?prodID=1&prodType=1

[dmacman1962]

Yes, if I could get it to work.

I think that would be the most logical way to have the products.php page show the correct item (prodID) and the links if they exist (prodType).

I just could not get the loop to work correctly on the products.php page.

Also, when I add the prodType=1 to the url, all my selects for the products.php page do not work and I get a mysql error.

I think I am starting to exaplain myself a little better.

Thanks,

Don

[laserlight]

Also, when I add the prodType=1 to the url, all my selects for the products.php page do not work and I get a mysql error.

That's because you were using the wrong separator for query string name/value pairs.
The '?' starts the query string, the '&' separates name/value pairs.

[dmacman1962]

I got it, with YOUR help!

Thanks!!

Here is the code for the url from the index.php page:

<?PHP 
				  echo "<tr><td><h1>Video</h1></td>";
				  echo "<tr><td><h1>".$row['prodgroup']."</h1></td>";
 $sql = "Select prodName, prodID, prodType, prodGroup from tblProduct Where prodType='1' ORDER BY prodName ASC";  

    $result = mysql_query($sql);
	while($row=mysql_fetch_array($result)){
   echo "<tr>
           <td><a href=\"http://www.intheclassroom.org/noFrames/products/product.php?prodID=".$row['prodID']. "&prodType=".$row['prodType']. "\">".$row['prodName']."</a></td>
         </tr>"; 
}  

				  echo "<tr><td><br></td></tr>";
				  echo "<tr><td><h1>Books</h1></td>";
 $sql = "Select prodName, prodID, prodType  from tblProduct Where prodType='2' ORDER BY prodName ASC";  

    $result = mysql_query($sql);
	while($row=mysql_fetch_array($result)) {
   echo "<tr>
           <td><a href=\"http://www.intheclassroom.org/noFrames/products/product.php?prodID=".$row['prodID']. "&prodType=".$row['prodType']. "\">".$row['prodName']."</a></td>
         </tr>"; 
} 

			  echo "<tr><td><br></td></tr>";
			  echo "<tr><td><h1>Merchandise & Apparel</h1></td>";
 $sql = "Select prodName, prodID, prodType from tblProduct Where prodType='3' ORDER BY prodName ASC";  

    $result = mysql_query($sql);
	while($row=mysql_fetch_array($result)) {
   echo "<tr>
           <td><a href=\"http://www.intheclassroom.org/noFrames/products/product.php?prodID=".$row['prodID']. "&prodType=".$row['prodType']. "\">".$row['prodName']."</a></td>
         </tr>"; 
} 
?>

And here is the code for displaying the results:

<?PHP 
$sql2 = "Select prodName, prodID, prodType, prodGroup from tblProduct Where prodType=" . $prodType . " AND prodID !=". $prodID ." ORDER BY prodName ASC";  

    $result2 = mysql_query($sql2);
		 echo "<hr>";
	while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
   echo "<a href=\"http://www.intheclassroom.org/noFrames/products/product.php?prodID=".$row2['prodID']."\">".$row2['prodName']."</a>"; 
	echo "<br>";
}  

//}
?>

Thanks a bunch laserlight.

Don