Sub Headings in a Dynamic List

How do I amend my script to take into account sub headings

[FireNet]

I wonder if someone can help me with a problem I am having whilst displaying some external links that are being taken from a MySql database. The code below may not be the best writen but it does work. I have a database of categories (See SQL below) that are highlighted in blue with yellow text. The contents (See SQL below) of each category are listed below each blue header using alternate green and yellow rows using a type number for each category.

What I want to do now is have another list of categories with say a red background but carry on with the alternate row colours i.e.
United Kingdom
__England
___link goes here
__Scotland
___link goes here
USA
__New York
___link goes here
__California
___link goes here

I guess it is an array that has to be inserted somewhere but it does seem to be getting me foxed.

Any help appreciated

<?
///////////////////////////////////////
//Present default page if no id given//
///////////////////////////////////////
if ($id=="") {
$id=1;
};
///////////////////////
//Set a few variables//
///////////////////////
$rowcolor="FFFF00";

////////////////////////////////////
//Connect to the relevant Database//
////////////////////////////////////
$connection = mysql_connect("localhost", "Username", "Password");
mysql_select_db("DB_Name");

////////////////////////////////
//Do Query on members pages id//
////////////////////////////////
$page_id=$id;

///////////////////////////////////
//Do Query on Categories (Header)//
///////////////////////////////////
$get_categories = @mysql_query("
SELECT *
FROM categories
WHERE page_id = $page_id
ORDER BY id", $connection)
or die (mysql_error());

/////////////////////////////
//Put categories into array//
/////////////////////////////
while($row = mysql_fetch_array($get_categories)) {
$id = $row['id'];
$category = $row['category'];

////////////////////////////////////
// DISPLAY THE Categories (Header UK / USA)//
////////////////////////////////////
echo "<table width=\"100%\" bgcolor=\"blue\" border=\"0\">
<tr><td width=\"100%\"><Font size=4 color=\"yellow\">" .
stripslashes(htmlentities($category)) . "</font></td></tr>
</table>";

////////////////////////
//Do Query on Contents//
////////////////////////
$get_contents = @mysql_query("
SELECT *
FROM contents
WHERE type='$id'
ORDER BY name ASC", $connection)
or die (mysql_error());

///////////////////////////////////////
// Set Row Colour's & Row Marker to 1//
///////////////////////////////////////
$num=mysql_num_rows($get_contents);
$cur = 1;
////////////////////////////////////////////
// Loop through Contents and change colour//
////////////////////////////////////////////
while ($num >= $cur) {
///////////////////////////////
// Put contents into an array//
///////////////////////////////
while($row = mysql_fetch_array($get_contents)) {
$conid = $row['conid'];
$url = $row['url'];
$name = $row['name'];
$country = $row['country'];
$region = $row['region'];
$catnum = $row['catnum'];
$cname = $row['cname'];
$cemail = $row['cemail'];
$cphone = $row['cphone'];
$memberid = $row['memberid'];
$notes = $row['notes'];
$type = $row['type'];

echo "<table width=\"100%\" bgcolor=\"#" . $rowcolor . "\" border=\"0\">
<tr><td width=\"10%\"><img src=\"$country\" border=\"0\" width=\"35\" height=\"22\" ALT =\"$country\"></td>
<td valign=\"top\" width=\"20%\"><a href=\"mem-view.php?&id=$conid\">$name</a></td>
<td width=\"20%\">
$pos
</td>
</tr>";
/////////////////////
// Set Row Colour's//
/////////////////////
if ($rowcolor==FFFF00):
$rowcolor=CCFF99;
else:
$rowcolor=FFFF00;
endif;
////////////////////////////////
// Increment row No for Colour//
////////////////////////////////
$cur++;
}
///////////////////////////////
// Complete Table of Contents//
///////////////////////////////
echo "</table>";
}
}
?>

#Excerpt from Contents SQL
CREATE TABLE `contents` (
`conid` int(11) NOT NULL auto_increment,
`url` text NOT NULL,
`name` text NOT NULL,
`country` text NOT NULL,
`flag` text,
`region` text,
`catnum` text,
`cname` text,
`cemail` text,
`cphone` text,
`memberid` text NOT NULL,
`notes` text,
`type` text NOT NULL,
PRIMARY KEY (`conid`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `contents` VALUES (1, 'http://www.example.com/', 'Example', 'united kingdom.gif', '', '', '', '', '', '', '1', 'Basic link only.', '1');

#Excerpt from Categories SQL
CREATE TABLE `categories` (
`id` int(11) NOT NULL auto_increment,
`category` text NOT NULL,
`page_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

INSERT INTO `categories` VALUES (1, 'Local Authority (UK)', 1);
INSERT INTO `categories` VALUES (2, 'Local Authority (International)', 1);
INSERT INTO `categories` VALUES (3, 'Training Establishments (UK)', 1);
INSERT INTO `categories` VALUES (4, 'Training Establishments (International)', 1);
INSERT INTO `categories` VALUES (5, 'Private Brigades (International)', 1);
INSERT INTO `categories` VALUES (6, 'Others (International)', 1);

[edited by: jatar_k at 11:39 pm (utc) on Oct. 24, 2004]
[edit reason] generalized url [/edit]

[jatar_k]

Welcome to WebmasterWorld FireNet,

Is it just getting the alternating colours to work?
Is the array being constructed and displayed properly, colours aside?

just looking for a little more detail into the actual misbehaving of the script

[FireNet]

Hi jatar_k
Many thanks for the reply.
The script is working fine as are the alternate colours for the rows. My problem is to insert another set of headings between 2 of the existing ones to give the effect of nesting 1 set of categories inside another which will then give me greater detail of a paricular main heading.

Hope this makes things clearer

[jatar_k]

I reformatted your code and made a couple small personal preference changes, figured I'd post it, it's much easier for people to read ;)

[perl]$sql = "SELECT * FROM categories WHERE page_id = $page_id ORDER BY id";
$get_categories = @mysql_query($sql, $connection) or die ('<p>' . mysql_error());
while($row = mysql_fetch_array($get_categories)) {
$id = $row['id'];
$category = $row['category'];
// DISPLAY THE Categories (Header UK / USA)
echo "<table width=\"100%\" bgcolor=\"blue\" border=\"0\"><tr><td width=\"100%\">
<Font size=4 color=\"yellow\">" . stripslashes(htmlentities($category))
."</font></td></tr></table>";
$sql2 = "SELECT * FROM contents WHERE type='$id' ORDER BY name ASC";
$get_contents = @mysql_query($sql2, $connection) or die (mysql_error());
$num=mysql_num_rows($get_contents);
$cur = 1;
while ($num >= $cur) {
while($row = mysql_fetch_array($get_contents)) {
$conid = $row['conid'];
$url = $row['url'];
$name = $row['name'];
$country = $row['country'];
$region = $row['region'];
$catnum = $row['catnum'];
$cname = $row['cname'];
$cemail = $row['cemail'];
$cphone = $row['cphone'];
$memberid = $row['memberid'];
$notes = $row['notes'];
$type = $row['type'];
// echo category result row
echo "<table width=\"100%\" bgcolor=\"#" . $rowcolor
."\" border=\"0\"><tr><td width=\"10%\"><img src=\"$country\" border=\"0\"
width=\"35\" height=\"22\" ALT =\"$country\"></td><td valign=\"top\" width=\"20%\">
<a href=\"mem-view.php?&id=$conid\">$name</a></td><td width=\"20%\">$pos</td></tr>";
if ($rowcolor==FFFF00) $rowcolor=CCFF99;
else $rowcolor=FFFF00;
$cur++;
}
// close table
echo "</table>";
}
}[/perl]

[jatar_k]

ok so,

is this the style now?

United Kingdom
__England
___link goes here
__Scotland
___link goes here

I need a better image of what it is now and what it is going to, since I am really not going set up a db and do a ton of testing, no offense but I do have a job. ;)

so we are primarily looking at the primary cat select

SELECT * FROM categories WHERE page_id = $page_id ORDER BY id

which shouldn't need to change as there will be no difference to the top level

the selct for the content would be what might need to be changed
SELECT * FROM contents WHERE type='$id' ORDER BY name ASC

have you had any luck with a select that grabs all the info you want to display?