Sorting MySQL Data

[kneff]

I have two tables, 'categories' and 'records' tied together by 'recordFormat'. There are no subcategories and no record associated with more than 1 category so I thought this would be easy but I cannot find any tutorials on how to do this. I want to be able to pull all the category titles from 'categories' and then pull all the entries in 'records' to be displayed under the correct category title, like:

Category1
record1
record2
record3

Category2
record4
record5
record6

[devinemke]

if any one single "record" can only be related to one "category" then you do not really need the third table to link them togther. you just need a field in the "records" table that relates to the primary key field from the "category" table.

[kneff]

I'm sorry I didn't specify clearer in my first post, but "'recordFormat" is not a third table, and is a field in 'records' that corresponds to the primary key in 'categories'.

What I'm trying to do can be seen in action here -> http://www.topsecretdistro.com/inventory.php

Right now I have every category hard coded into the html and a mysql select statement underneath each category down the page. But as we add records there are other vinyl sizes so I'd like to be able to pull the list with 1 mysql select statement, rather than going into the html and adding another category name and mysql select statement.

[devinemke]

here is a simple example. i'm just guessing here at the field names:

<?php
// connect to db here

$result = mysql_query('SELECT * FROM categories') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$categories[$row['category_id']] = $row['category_name'];}

$result = mysql_query('SELECT * FROM records') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$records[$row['recordFormat']][] = $row['record_name'];}

foreach ($records as $key => $value)
{
	echo '<b>' . $categories[$key] . '</b><br>';
	foreach ($value as $subvalue)
	{
		echo $subvalue . '<br>';
	}
	echo '<br>';
}
?>

[kneff]

Thank you that worked! However is there any way to list the categories alphabetically? I tried sort($categories) but the category titles ended up over the wrong records. And ORDER BY in the select statement seems to have no effect. Here's the code I'm using:

$result = mysql_query('SELECT * FROM distro_format') or exit(mysql_error());
    while ($row = mysql_fetch_assoc($result)) {
        $categories[$row['formatId']] = $row['formatName'];}

$result = mysql_query('SELECT * FROM distro_info ORDER BY "recordBand" ASC') or exit(mysql_error());
    while ($row = mysql_fetch_assoc($result)) {
	$records[$row['recordFormat']][] = '<strong><a href\="record_info.php?id=' . $row['recordId'] . 'target=\"_blank\" onclick=\"link_popup(this); return false\">' . $row['recordBand'] . '</a></strong> - ' . $row['recordName'];}

foreach ($records as $key => $value)
{
     echo '<b>' . $categories[$key] . '</b><br>';
     foreach ($value as $subvalue)
          {
               echo $subvalue . '<br>';
          }
     echo '<br>';
}

[devinemke]

<?php
$result = mysql_query('SELECT * FROM distro_format ORDER BY formatName') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$categories[$row['formatId']] = $row['formatName'];}

$result = mysql_query('SELECT * FROM distro_info ORDER BY recordBand') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$records[$row['recordFormat']][] = $row;}

foreach ($categories as $key => $value)
{
	echo '<b>' . $value . '</b><br>';
	foreach ($records[$key] as $subvalue)
	{
		echo '<strong><a href="record_info.php?id=' . $subvalue['recordId'] . ' target="_blank" onclick="link_popup(this); return false;">' . $subvalue['recordBand'] . '</a></strong> - ' . $subvalue['recordName'] . '<br>';
	}
	echo '<br>';
}
?>