problems with coding list from mysql

[TiMeZeRo]

im trying to create a page on my website to list beers that have been on at my local pub

i want to create a mysql database that is similar to this

ID l Brewery l Beer l
1 l Potbelly l Potbelly best l
2 l Oakham l JHB l

i want the page to pull the data from the mysql table and list it as follows

Brewery
- Beer 1
- Beer 2
- Beer 3

Brewery 2
- Beer 1
- beer 2

basically i want all the beers by the brewery under the brewery name
if possible id like everything alphabetically ordered or ordered by the brewerys with the most beer but i am havin huge problems coding this as im a slight novice when its comes to making lists and pulling data from the mysql database

any suggestions would be greatly appreaciated

one other question dependin on how its done will it matter if for exmaple one brewery has beers wiht ids 3 , 46 , 107 or will they have to be 2,3,4,5,6

[smush]

The ID numbers won't matter what they are. As for this list is it that you want the data from the mysql sorted into a html table with one html table per brewery?

so on the web page something like...
______________
|Brewery A |
|_____________|
| Beer 1 |
|_____________|
| Beer 2 |
|_____________|


______________
|Brewery B |
|_____________|
| Beer 1 |
|_____________|
| Beer 2 |
|_____________|

What is the language you're using for your website?

[aidanjt]

Use two separate queries, you should be able to do this in two for/list loops.

So entering the first loop you query MySQL to SELECT Brewery FROM mmmmbeer;, and use the Brewery string to match the beers for that brewery in the proceeding sub-loop with something like SELECT Beer FROM mmmmbeer WHERE Brewery = '$Brewery[i]'.

You should be able to figure it out from there.

[TiMeZeRo]

smush im coding in php thanx for the advise aidanjt ill look into it and try and get it to list them nice thanx guys

[scaryjim]

aidanjt's method will work nicely but require quite a lot of calls to the database: shouldn't be an issue but possibly not the most effective way to do it. You could run a single query ordered by brewery then beer, and then step through the single table checking the value of brewery on each step - when it changes you stop the current list, start a new one, then carry on listing beers.

... if possible id like everything alphabetically ordered or ordered by the brewerys with the most beer ...

If you use aidanjt's multiple query technique, you can a list of breweries ordered by number of beers using this query:

SELECT Brewery, COUNT(*)
FROM beerstable
ORDER BY COUNT(*) DESC

And I'd strongly recommend using stored procedures / prepared queries if your version of PHP / MySQL supports it (if you're using any recent version it should). While the data stuff you're doing with this shouldn't pose a security risk (as you'd be building your new query using results from your database), it will get you into good habits if you ever want to process user input through your database