display database info help

[soddengecko]

hi all

I have a database (links) with two tables (catagories) and (info)

I have set up a form that allows the creation of a category, which is put into the catagory table and outputted into a drop down list.

then you select an option from the list, type in your url and title and submit the data. this data is then added to the info table under (id) (category) (url) and (title)

this form is working well.

The issue i have is displaying the data. i want each category to be displayed seperatly like it does with a link manager. so everythign that has a catagory MISC is displayed in one list.

I can do this when i know what the catagories are going to be, but this will be a production app for myself and some colleagues to store links to different stuff.

how do i call the links fromt he database and have each category split into a different list and obviously have the name of the category displayed once as the title to the list?

does anyone have any info? i'm pulling my hair out puzzling over this

thank in advance

[printf]

(2) options

Option (1), fetch the categories, then when looping that result, fetch the topics in category loop (1 query for the categories, 1 query per category topic set)
Option (2), Join the tables and order by the category, out side your loop assign a category test variable to '', then inside your query loop have a if() block that tests the current category against the test variable, if it doesn't match, print the new header and store that value into the test variable. (1 query)

[code]
test_category = ''

while (db_loop)
{
if ( test_category != db_result_category )
{
// print header, db_result_category

// test_category = db_result_category
}

// print category topic, topic data
}
[code]


me![/code][/code]

[soddengecko]

hi

thanks for the reply. I am not really following what you mean, im pretty new to all this. this is the script i am using to display the links and categories

<?php

$sql = "SELECT * FROM info ORDER BY catagory";
$result = mysql_query($sql) or die('There are currently no links in the database.');
while ($row = mysql_fetch_assoc($result))
{
  $catagory = $row['catagory'];
  $url = $row['url'];
  $title = $row['title'];
  if ($catagory != $temp)

  echo "</ul>\n</div>\n\n<div class='left'>\n<h1>$catagory</h1>\n<ul>\n";
  $temp = $catagory;
  echo "<li><a href='$url' target='_blank'>$title</a></li>\n";

}
echo "</ul>\n";
echo "</div>\n\n";
?>

as long as my sql SELECT statment is ordered by category everythig is printed to the page correctly, but not in alphabetical order which is what i need.

could you explain your code a little further please?

kind regards

[fenway]

That if() is just hanging there... but how can it be incorrectly ordered?

[soddengecko]

i have no idea why it is listed incorrectly. if i change the sql statement from ORDER BY catagory it shows each individual link in a seperate list