Tricky One, order values from a table

[scraff]

Hello,

once again i need some help!

I have a table with products in it called CDBproducts

there are 3 main collums : productcode, cat1a, cat1b

all productcodes are unique but cat1a may have (for example) 20 that says Leather, 30 that says Stationery, 10 that say Misc. etc
and cat1b may have 10 that say Covers, 10 that says Vinyl, 30 that says Misc etc.
(This is so a products can be under Leather and Covers(if its a leather book cover)

I have an online shop i am workin on and these are the Categories so views can click on a category then it will show the productcodes under that category, so there would be a list to choose from like so:

Leather
Stationery
Misc
Covers
Vinyl

But instead it comes out:

Leather
Stationery
Misc
Covers
Vinyl
Misc

the code at the moment is:

$result = @mysql_query("SELECT * FROM CDBproducts WHERE cat1a != '' ORDER BY cat1a"); 
while ($row=@mysql_fetch_array($result)) { 
	$cat1a = $row[cat1a];
	$cat1a = strtoupper($cat1a); 
	if($cat1a == $prevcat1a){

}else{
	echo "<a href=\"?scat1a=$cat1a\">$cat1a</a><br>";
	$prevcat1a = $cat1a;
}
}
$result = @mysql_query("SELECT * FROM CDBproducts WHERE cat1b != '' ORDER BY cat1b"); 
while ($row=@mysql_fetch_array($result)) {
	$cat1b = $row[cat1b];
	$cat1b = strtoupper($cat1b); 
	if($cat1b == $prevcat1b){

}else{
	echo "<a href=\"?scat1a=$cat1b\">$cat1b</a><br>";
	$prevcat1b = $cat1b;
}
}

This looks through CDBproducts where cat1a is not empty and orders by cat1a
then prints the value of cat1a
then goes onto the next Row and if its the same as the last thing it printed ($prevcat1a) it misses it out untill it reaches the next category.
Then it does it again looking at cat1b.

These two work fine on there own, its just there may be Misc or Leather etc in cat1a AND in cat1b.

WHAT I WANT is something that maybe can do the two queries together and so order cat1a AND cat1b together

Thanks

[scraff]

so basicly all i need is to get the data from these 2 queries and stick em together then order them in alphabetical order together!

Thanks

P.s. the productcode isnt really important in the query, its only used later which doesnt come into it 🙂

[mourisj]

select distinct cat1a from CDBproducts
where cat1a is not null
union
select distinct cat1b from CDBproducts
where cat1b is not null
order by (1)

The distinct avoids you all the code to check if the actual record is equal to the previous.

Have a nice day,

JJ Mouris

[drawmack]

Or you could normalize that database and use three tables

categories
    cat_id -> auto_number
    cat_name

products
    pro_id -> auto_number
    pro_name
    etc.

prod2cat
    pro_id -> links to products (creates half the primary key)
    cat_id -> links to categories (creates half the primary key)

This has many advantages. You can have any number of categories for a single product. Your sorting problem goes away. You can do stats for number of products in each category very easily. It'll be faster cause it's normalized. Data repetition (i.e. category names) is minimized. If you change a category name it is automatically changed on all products associated with it. etc.

[mourisj]

Good suugestion inddeed.

I thonk I would have done this way, but I did not want to suggest a change of table structure this time . (I have seen worse in this forum)

JJ Mouris