MySQL: how would I go about displaying simple "categories" for this?

Question

I'm wishing for a simple index more or less with my PHP note taking software, each note has a "language" field for the programming language its written in. When I iterate through each entry in a while loop, I'm not sure how i'd list categories of them such as:

PHP:
  Note 1
  Note 5
SQL: 
  Note 2
  Note 3

Do I need to place all entries in a temporary array with ifs, like this?

if($field['language'] == "PHP")
    $PHPsection[] = $field;

Although that doesn't look the best as I'd need to hardcode each section.. I'm stuck.

Answer 1

you could group already in the database. or sort by section and then iterate through and output the proper seperation code when the section changes. a temporary array is also a solution. given your example code maby this can point you in one possible directino (altough the temporary array is of course not an elegant solution but if the data is not that much it doesnt really matter)

<?
$result = array();
$result[] = array('section' => 'php','note' => 'bla');
$result[] = array('section' => 'php','note' => 'bla');
$result[] = array('section' => 'perl','note' => 'bla');
$result[] = array('section' => 'java','note' => 'bla');

$grouped = array();
for($i=0;$i<count($result);$i++) {
  $grouped[$result[$i]['section']][] = $result[$i]['note'];
}
print_r($grouped);

?>

Answer 2

Sounds like you need to do a lookup table here. Such as the following.

Note_id | Language_id 
--------+------------
 1      |   1
 1      |   4
 2      |   3 
 3      |   1

This, then, links these two tables

Note_id | Note_Name| ...
--------+----------+----
    1   | Testing  | ...
    2   | Groceries| ...

Language_ID | Language
------------+---------
    1       | English
    2       | German
    3       | Klingon

And from there, you can write queries like:

SELECT
   Note_name, Language
FROM
   languages l, notes n, language_note_lookup lnl
WHERE
   l.Language_ID = lnl.Language_ID AND
   n.Note_ID = n.Note_ID AND
   l.Language = "German"

You can also get a list of all languages easily by simply doing a

SELECT Language FROM languages