Stacking Mysql Results

Anon

I have been trying unsuccessfully the past few days to stack mysql results.

Basically what I am trying to accomplish is this.

I have 3 categories each with about 10 subcategories.

What I am trying to do is stack the results by subcategory. I tried pumping the results into an array and then looping through the array but this did not work.

The outcome I want would look something like this.

FOO 1:
result
result
result

FOO 2:
result
result
result

FOO 3:
result
result
result

I am sure you get the general idea. If anyone has an idea of how I can accomplish that please fill me in.

Cheers!

John

Anon

More information is needed to fully understand what you're doing. I understand how you want it listed, but how is it stored in the database? That is the key to this problem.

Anon

The Information is stored in 1 table.

I will post the code that I am working with.

$result = mysql_db_query($DB, "SELECT * FROM domain_listings WHERE user_no='$user' and closing_time < $now")or die(mysql_error());

Basically what I am trying to do here is pull all listings that have expired out of the database and organize them by subcategory. This table has the following fields.

lot_no - unique to each item

domain_name
closing_time
price
description
url
subcategory

So basically what I need to do is have it spit out the results under each subcategory.

IE. Business Listings
result
result
result

Family Listings
result
result
result

I need to print the subcategory and then the results underneath for each match, without printing subcategories with no matches.

Hope i am making sense here.

I tried to use an array to do this.. but it did not work correctly.

Thanks for your help.

Cheers

John

Anon

Hi!

This is the SQL clause you need:
SELECT * FROM domain_listings WHERE user_no='$user' and closing_time < $now ORDER BY subcategory;

After that, iterate through the results set. At the beginning, set a variable $prev_subcategory to "", on each iteration check whether the $prev_subcategory is different from subcategory in current row, if yes, print the header for the subcategory and set $prev_subcategory to subcategory in current row. After that, exit the if statement and process the current domain normally:

$row = GetFirst();//this is pseudo code

$prev_cat = "";

while($row)
{
if ($prev_cat != $row['category'])
{
print "<b>".$row['category']."</b><br>\n";
$prev_cat = $row['category'];
}
print_r($row); // just format domain as you need
$row = GetNext();
}

Hope this has helped,
Stas

Anon

You may also want to look into the GROUP BY clause in SQL. It can produce something close to what you want in the actual result set, no PHP manipulation required.