Selective Display

bpat1434

I am working on a database driven website.

I have a download area, and would like to have a page where the user chooses a category. From that, they are given their choices to download.

My question is this:
How would I code if I wanted to select the FIRST item of the table WHERE the category changes?

I was thinking perhaps....

SELECT FROM table ORDER BY Category;
SELECT FROM table WHERE Category changes LIMIT 1;

I know that it's wrong, but that's like something what I want.

Thanks for any help you can offer.

~Brett

paulnaj

Hi bpat1434,

Not quite sure exactly what you mean by - "How would I code if I wanted to select the FIRST item of the table WHERE the category changes? "

Are you saying that you only want one instance of each category?
If so use the DISTINCT keyword in the SELECT clause.

Paul.

bpat1434

Not exactly.

I will have a table called Downloads. In that table, I will have the following columns:
id, Category, iPath, Title, dlPath

So, a possible entry would look like:

id  |  Category  |  iPath  |  Title  |  dlPath  |
-----------------------------------------------------
1   |  Desktops  | [url]http://[/url] | Desk  |  [url]http://[/url]  |
     |            | URL     | top 1  |  URL      |

So, there would be multiple items in the database. And each item would have a corresponding category.

And I would only want to select the top entry within each cateogry. So possible categories would be:
Desktops, Themes, Images and the like.

Thanks for the help.

paulnaj

Hi again,

Ok ... last question ... what do you mean by 'top entry'? That is, what determines the order?

Paul.

bpat1434

I would order them by category, and whichever item is at the top, or the first entry for that category.

~Brett

devinemke

Originally posted by bpat1434
I would order them by category, and whichever item is at the top, or the first entry for that category.

again, what do you mean by "top" or "first"? what part of your data structure determines the order? it looks like you will have to add another field to your Downloads table to determine the order within each category.

bpat1434

Let me try this again.

I will have a database: dbName. In this database, there is a table: Downloads.

Now, in this table there is an ID field which is indexed, and there is a Category field.

I want to search the Database for 1 item from each category, then display them.

=============================

HTML Page Setup

Downloads page queries database asking for what categories there are, and what images to display as links.

The user clicks on the link and is taken to a page to where the query shows only those entries with the Category of which they selected.

If there was a way to do tables within tables that woudl be the easiest.

Perhaps it would be better to create a database: Downloads and then a table for each category and then pull the top row from each table and display that as the link.

I'd much rather do it within a table.

Thanks for the help.

~Brett

devinemke

Originally posted by bpat1434
I want to search the Database for 1 item from each category, then display them.

let me also try again: you still have not made this clear. you say you want to display 1 item from each category...which 1? what determines which 1 record from each category you want to pull? is it random? is it the highest ID number? the lowest ID number? the last updated? what?

when you use words like "top" and "first" that suggests an enumerated order. which field in the table determines that oder?

ahundiak

Brett is probably making the (not uncommon) mistake of thinking that the order in which records are entered into a table is some how perserved. So I think he wants the first record entered for a given category.

The only real way to do this (with just one table) would be to set a flag indicating that a given record is the first record in it's category.

SELECT * FROM Downloads WHERE first_flag = 1 ORDER BY category;

Of course you need application login to make sure the first get's set properly.

bpat1434

I would like it to be the lowest ID number (closest to 1).

I'm not sure as to how to do the flagging for mySQL, and don't know what you mean when you say:

Of course you need application login to make sure the first get's set properly.

I hope I cleared up a little.

~Brett

devinemke

Originally posted by bpat1434
I would like it to be the lowest ID number (closest to 1).

now we're geting somewhere:

SELECT *, MIN(ID) FROM Downloads GROUP BY Category

bpat1434

Thank you thank you thank you!!

Much appreciated.

Now, do I need to add the LIMIT 1, or not. I don't think I do if it's just picking the Min(ID).

~Brett

devinemke

if you add LIMIT 1 you'll only get 1 record (1 category) which i don't think is what you want. you want to see the lowest ID in all categories...right?

bpat1434

Yeah. That's right. I was just checking.

Thanks for the help, and your patience & last nerve.

~Brett