[SOLVED] While loop question.

[zhTonic]

hey guys,

 

I'm coming up to a part in my application where i will have to pull multiple results for a category, but the category is part of the query itself.

 

It's structured like this.

 

CATEGORY

result

 

CATEGORY

result

 

I have no problem with looping that, but my question is what if i have more then one "result"? How can i make it loop more then one result under a category without it making a new

 

CATEGORY

result

 

every time?

 

ideally it would look like this..

 

CATEGORY

result

result

result

 

CATEGORY

result

result

[genericnumber1]

Can you show us your query? Perhaps it would be clearer to me what you want.

[zhTonic]

Ok sure here is the query and my while loop, i'll also explain a bit about how it's getting the appropriate data.

 

SQL QUERY:

$sellsql2 = mysql_query("SELECT * FROM auctions WHERE selling='".$sbcheck."' AND serverid='".$serverid."' AND itemtype='".$itemid."' AND jobclass='".$jobclass."'");

 

Ok now basically it reads from all of the auctions and each auction will have an itemid which will grab the according data from the items table.

 

Now lets say i have 4 auctions that have the same exact item, we'll call it "Big Axe".

 

Well what i want to do is if there is more then one i don't want it to loop it and have it like this:

 

Big Axe

auction1

 

Big Axe

auction2

 

etc..

 

I would like it to be

 

Big Axe

auction1

auction2

auction2

 

etc.

 

and if there are more then one type of item up for auction it would display like this:

 

Big Axe

auction1

auction2

 

Mega Axe

auction1

 

and so on...

 

 

while($sellsqlly1 = mysql_fetch_array($sellsql2)){
$itemid = $sellsqlly1["itemid"];
$itnfosql = mysql_query("SELECT * FROM items WHERE id='".$itemid."'");
$itnfosqlly = mysql_fetch_array($itnfosql);
//ITEM RESULTS//
$itemname = $itnfosqlly["name"];
//AUCTION RESULTS//
$stats = $sellsqlly1["stats"];
$price = number_format($sellsqlly1["price"]);
$topbid = number_format($sqllsqlly1["topbid"]);
$auctioner = $sellsqlly1["Auctioner"];
if(!$topbid){
$topbid = "";
} else {
$topbid = $topbid;
}
echo "<tr class=\"misc1 normal\">
<td colspan=\"5\">
<a name=\"an21679\"></a>
<a href=\"?action=auctions\"><img border=\"0\" src=\"item/item-1679-1.gif\" title=\"$itemname\" align=\"absmiddle\" class=\"mr10\"/>
<strong>$itemname</strong></a>
</td>
</tr>

<tr>
<td class=\"normal\"><a href=\"?action=auctions\">$stats </a></td>
<td class=\"ps2 normal\"><a href=\"?action=auctions\">$price</a></td>
<td class=\"ps1 normal\"><a href=\"?action=auctions\">$topbid</a></td>
<td class=\"center ps2 small\">1 hour, 38 minutes</td>
<td class=\"normal small\"><a href=\"?action=basilid&user=$auctioner\">$auctioner</a></td>
</tr>
<tr><td colspan=\"5\" class=\"sep\"></td></tr>";

}

[genericnumber1]

First, add a group by category/itemtype/whateveritisi'mtolazytocheckyourtabledesignsorry to the query

 

I use "category" in the code below for the category. eg. big axe

I use "item" in the code below for whatever value you want.

 

Then, the general idea of what you'd want to do is this... (untested)

 

<?php
// query and assign the result to $result

$category = 0;

while($row = mysql_fetch_assoc($result)) {
if($category != $row['category']) {
	echo '<b>' . $row['category'] . '</b><br />';
	$category = $row['category'];
}
echo $row['item'] . '<br />';
}
?>

[cooldude832]

There are a lot of ways to do it  a group by is not the right way because GROUP BY is meant to be used to GROUP items ie when you go to find MAX(Field)  based by UserID  you use group by not in this case

 

 

If your categories is a secondary table you should use a LEFT JOIN here to join the auctions into the cat's table.

 

If not you should use an ORDER BY category and then you are all set to get they are in order by category.  You will need to use a logical statement in the php in the while loop to see if the category changes from each itteration if it does splash out the category heading.

[zhTonic]

There are a lot of ways to do it  a group by is not the right way because GROUP BY is meant to be used to GROUP items ie when you go to find MAX(Field)  based by UserID   you use group by not in this case

 

 

If your categories is a secondary table you should use a LEFT JOIN here to join the auctions into the cat's table.

 

If not you should use an ORDER BY category and then you are all set to get they are in order by category.  You will need to use a logical statement in the php in the while loop to see if the category changes from each itteration if it does splash out the category heading.

 

What would the logical statement look like? just to note that there would be thousands of category names depending on the itemid.

[genericnumber1]

Yeah, I totally meant order by sorry about that, good catch cool.

[cooldude832]

sorta like

 

<?php

while($row = mysql_fetch_assoc($r)){
if($row['Cat'] != $last_cat){
echo "<br /><h1>".$row['Cat']."</h1><br />";
}
$last_cat = $row['Cat'];
echo $row['Item'];
}
?>

[genericnumber1]

slight flaw in your code... you'd want to move the $last_cat = into the if statement, or you could just use the code I posted in my first comment, same thing.

[zhTonic]

sorta like

 

<?php

while($row = mysql_fetch_assoc($r)){
if($row['Cat'] != $last_cat){
echo "<br /><h1>".$row['Cat']."</h1><br />";
}
$last_cat = $row['Cat'];
echo $row['Item'];
}
?>

 

OMG thank you for the help, that worked out perfectly.

[genericnumber1]

Where's the love ?

[cooldude832]

$last_cat needs not be in the if statement

 

It does save an aggregate amount of resources but not necessary.

 

[genericnumber1]

As you said, it saves a pointless assignment fairly often for a simple copy/paste.