Confusing select statement - need help

[rob]

I'm kind of this problem that I have and I am hoping someone can help me out.

Here's what I got: I made a table with the following fields in it.
id, category, subcategory, cid; id autoincrements and cid is just a category id.

I want to select the "category" so that no duplicate categories are shown but listed under each category I want to show all or most of the subcategories for each category.

Lets say I have a categories called "A" and "B" and in categories "A" I have subcategories "1", "2", & "3" and in "B" I have subcategories "7", "8", & "9". "A" has a "CID" that is equal to 1. "B" has a "CID" that is equal to "2". There are a total of 6 entries in the database.

An entry would look like this:
id|category|subcat|cid
1|A|1|1

I want it to display something like this:

A
1, 2, 3

B
7, 8, 9

I was trying to use the DISTINCT which ended limiting everything.

Hope someone can help...

Rob

[bad76]

Hi,
to do this you have two way:

1) use two query, one with distinct categories, the second with subcategories where categories=anyvalue. For each row of first, you make the second. It's easy to program but a lot heavy for db.

2) just one query, select * and when loop get the current categorie value. When change, echo it, else echo only subcat.

i hope this help you.

see you

[rob]

Hey thanks for the help, but can you give me some example code?

I'm not so good at translating directions into code.

This is the solution I want to go with...

2) just one query, select * and when loop get the current categorie value. When change, echo it, else echo only subcat.

[bad76]

Sure. Look this:

// ... do connection
$result=mysql_query("select * from mytable order by category, subcategory");

$oldcat=-1;

while($row=mysql_fetch_array($result))
{
    if($oldcat!=$row["category"])
    {
            $oldcat=$row["category"];
            echo $row["category"];
    } 
    echo $row["subcategory"];
}

see you.

[rob]

Geez, I still can't seem to get this to work.

I tell you what I am trying to make. If you go to yahoo.com and look at there main page you'll see they have lots of different categories with subcategories listed under them in smaller text. I want to know how to do that...

Do you have any more suggestions?

[rob]

I did make some changes to my system.

The first thing I did was I made a categorylookup table.

This has two columns, SID and CID. SID is for subcategory id # and CID is for Category ID #.

I also made a category table which has three columns in it.
id, catid, and category.

I also made a subcategory table which has three columns.
id, subid, and subcategory.

                              CATEGORY LOOKUP
SID=>subid=>subcategory          CID=>catid=>category

=> these are arrows btw not greaten then or equal too.

I guess the two stumpling blocks i have right now are how do i write the select statement because I keep getting the category repeating for every subcategory there is and i don't want that. I want it to list the category once and then all subcategories in smaller text underneath it. I want to have it do this in two columns as well so formatting it is the other issue.

You know of any examples anywhere?

[crosbystillsnas]

$db = mysql_connect(SERVER, USER, PASS);

// Get all cateories distinct
$query = "SELECT DISTINCT category from TABLE order by category ASC";
$res = mysql_db_query(DATABASE, $query);
$i = 0;
while($cat = mysql_fetch_row($res)){

$category[$i] = $cat;
echo "CATEGORY: ".$category[$i]."<br />";

 // get subcategories for each category
 $query2 = "SELECT subcat FROM TABLE WHERE category='".$category[$i]."' ORDER BY subcat ASC";
 $res2 = mysql_db_query(DATABASE, $query);
 $j = 0; 
 while($sub = mysql_fetch_row($res2)){
 $category[$i][$j] = $sub;
 echo "SUBCATEGORY: ".$category[$i][$j]."<br />";
 $j++;
 }

$i++;
}

mysql_close($db);

with a bit luck this should work.
there should be a 2-dim-array $category[$i][$j], where $category[0] to $category[X] are the main categories and $category[0][0] to $category[0][Y] are the subcategories for the first category (0).

well, looks confusing, but good luck.

[rob]

I will try this later tonight and let you know how it goes, it looks like it will work!

Thanks!

Rob

[rob]

All it spits back is

"Category: Array
Subcategory: Array"

It repeats it like four times.

[crosbystillsnas]

sorry. some changes.

$db = mysql_connect(SERVER, USER, PASS);

// Get all cateories distinct
$query = "SELECT DISTINCT category from TABLE order by category ASC";
$res = mysql_db_query(DATABASE, $query);
$i = 0;
while($cat = mysql_fetch_row($res)){
echo "CATEGORY: ".$cat[0]."<br />";

 // get subcategories for each category
 $query2 = "SELECT subcat FROM TABLE WHERE category='".$cat[0]."' ORDER BY subcat ASC";
 $res2 = mysql_db_query(DATABASE, $query2); // <- $query2 !!!
 while($sub = mysql_fetch_row($res2)){
 echo "SUBCATEGORY: ".$sub[0]."<br />";
 }

}
mysql_close($db);

...and forget about the array-thing.

this just puts it out.

[rob]

Thanks Mr Nash, I'll give this a try and let you know how it goes.

Rob

[rob]

We are getting close mr nash.

I edited the script and put in my values but when i load up the page, it says.

"Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /path/to/my/page/mypage.php on line 14"

The Categories output fine, I'm going to try some different things, but if you have any more suggestions, I'd appreciate it.

This is line 14 btw,

 while($sub = mysql_fetch_row($res2)){

[rob]

hey hey -- i got it to work!

Here's the final code:

$query = "SELECT DISTINCT categ from TABLE1 order by categ ASC"; 
$res = mysql_db_query(DB, $query); 
$i = 0; 
while($cat = mysql_fetch_row($res)){ 
echo "CATEGORY: ".$cat[0]."<br />"; 

 // get subcategories for each category 
 $query2 = "SELECT subcat FROM TABLE1 WHERE categ='".$cat[0]."' ORDER BY subcat ASC"; 
 $res2 = mysql_db_query(DB, $query2); // <- $query2 !!! 
 while($sub = mysql_fetch_row($res2)){ 
 echo "SUBCATEGORY: ".$sub[0]."<br />"; 
 } 
}

Be sure to keep it all in one table too.

The table should be setup like id, categ, subcat --- something like that.

thanks Nash for the help!

Now all i need to do is figure out how to format this thing. 🙂

[crosbystillsnas]

...my, my. here's a simple idea for the beginning.

$query = "SELECT DISTINCT categ from TABLE1 order by categ ASC";  

$res = mysql_db_query(DB, $query);  

$i = 0;  

// start Main Table
echo "<table border=\"0\" cellspacing=\"1\" cellpadding=\"2\">\n";
while($cat = mysql_fetch_row($res)){  

// Row1 Main Table
echo "<tr>\n";
echo "<td>\n";
 echo "CATEGORY: ".$cat[0];
echo "</td>\n";
echo "</tr>\n";
// Row2 Main Table
echo "<tr>\n";
echo "<td>\n";

 // get subcategories for each category 
 $query2 = "SELECT subcat FROM TABLE1 WHERE categ='".$cat[0]."' ORDER BY subcat ASC";  

 $res2 = mysql_db_query(DB, $query2);
 // start Subtable
 echo "<table border=\"0\" cellspacing=\"1\" cellpadding=\"2\">\n";
 while($sub = mysql_fetch_row($res2)){
 echo "<tr>\n";
 echo "<td>\n";
 echo "<small>SUBCATEGORY: ".$sub[0]."</small>";  

 echo "</td>\n";
 echo "</tr>\n";
 } 
 echo "</table>\n";
 // end Subtable

echo "</td>\n";
echo "</tr>\n";
}

echo "</table>\n";
// end Main Table