A

Anonymous

Question

Recordset - Several fields in one list...

Forum|Forum|15 years ago
April 6, 2010
2 replies
759 views

Hello...

I'm hoping there's a really simple answer to this but I can't seem to find anybody else asking it (unless I'm using the wrong search terms!)

I'm using Dreamweaver CS3 on OS X 10.6.2.

I have a PHPMyAdmin database with 4 fields that I want to display in a recordset...

category1

category2

category3

category4

I want to create one menu list of all distinct entries in all thoses fields, but also have it displayed in alphabetical order.

For example, if the following info appears in the fields (in 5 different entries)...

category1 - red, blue, green, red, red

category2 - black, white, brown, brown, brown

category3 - purple, purple, purple, purple, purple

category4 - pink, orange, null, null, null

I need the recordset to display the list as...

black

blue

brown

green

orange

pink

purple

red

white

So I think need to create one recordset to do this.

I've tried...

select DISTINCT category1, category2, category3, category4

FROM database

ORDER BY category1, category2, category3, category4 ASC

But it just shows the entries from category1 (each entry is repeated many times) e.g.

blue

green

red

Any help on how to write this Recordet would be great,

Thanks in advance

Server side applications

This topic has been closed for replies.

A

Anonymous

This (I think) is what you want:

SELECT category1 FROM database

UNION

SELECT category2 FROM database

UNION

SELECT category3 FROM database

UNION

SELECT category4 FROM database

ORDER BY 1 ASC

A

Anonymous

Hi Michael

Thanks for the help, its all sounding good but isn't quite working for me!

I put the code in as you say but when I do, in the 'Bindings' table it only shows 'category2' as a choice. category1, category3 and category4 aren't there???

Then when I bind my text to 'category2', repeat the region and test it in a browser I just get a empty space repeated many times.

Any ideas what I'm doing wrong?

A

Anonymous

As far as the bindings panel, don't worry about that. That's just how DW shows the

query.

Open your query panel, click on ADVANCED, then enter the query as stated. Then, click the TEST button and see what comes back from the DB. This should be the correct result. How you get that to display in your page isn't an SQL issue.

First, do that and verify that the SQL statement is returning the proper result. I think it should be. Once we know that it's right, then we tackle the next problem.

A

Anonymous

I believe what's happening is that the SQL is returning all distinct combinations of the selected columns... that can be difficult to see since all your columns contain similar data. Using an example from a different situation:

Let's say I have a database of USERS. If I create a similar query to yours, but using columns "city" and "first_name" like this:

SELECT DISTINCT city, first_name
FROM users
ORDER BY city, first_name ASC

what I get back is the following. Notice how it lists each city, then the unique first names within it. If a city has more than one first name in it, it lists the city for each first name (which is what you're seeing with your colors repeated):

			CITY	FIRST_NAME
			Apopka	Jill

			Cape Canaveral	Becky
			Cape Canaveral	Christy
			casselberry	Alexandra
			Casselberry	Brittney
			Cocoa Beach	Gator Joe
			Cocoa Beach	Leanna
			Cocoa Beach	Rob
			Cocoa Beach	Shayne
			Cocoa Beach	Terri
			Melbourne	Devra
			New York	Michael
			Ocoee	Drew
			Orlando	Belle
			Orlando	Betty
			Orlando	Jessica
			Orlando	Kelly
			Orlando	Mel
			Orlando	Naida
			Orlando	Sarah

I suggest UNIONS... (see next post.)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded