Recordset - Several fields in one list...

Report · Apr 06, 2010

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

Report · Apr 06, 2010

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.)

Report · Apr 06, 2010

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

Report · Apr 07, 2010

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?

Report · Apr 07, 2010

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.

Report · Apr 08, 2010

Hi again Michael

Okay, I've done the Test and it is showing the correct result so the code is working correctly

So how do I now display the result in a list???

Many thanks in advance.

Report · Apr 08, 2010

From your original post, I can't really tell exactly what you're trying to do on your page... can you describe it in detail? You said a "menu list," and to me that means a menu form field, but I get the feeling that's NOT what you're talking about... so, try to be as specific as possible... maybe take a screenshot of what you have and include that, as well.

Report · Apr 08, 2010

I basically just want to display the results in a list like...

black

blue

brown

green

orange

pink

purple

red

white

So i'm coding it like...

<?php do { ?>

<?php echo $row_recordset1['category2']; ?>

<?php } while ($row_recordset1 = mysql_fetch_assoc($recordset1)); ?>

But this is just returning blank results in the browser.

I'm guessing its to do with the <?php echo $row_recordset1['category2']; ?> but I don't know what to put in its place???

Report · Apr 08, 2010

Yeah, I'm not really sure about this part... I would normally just echo the statement, then add a repeating region, which would give me the same code you have:

<?php do { ?>

<?php echo $row_Recordset1['category2']; ?>

<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

I get the same result you do, though... blank.

Maybe someone else out there can help get this last bit figured out... Anyone?