mySQL - This possible?

[JonD]

I have the following setup which stores my navigation for my Content Management System im creating. I want to extract the results so they print something like this: (PHP & mySQL)

Section Name

• <a href="?&id=LinkID">SubSection Name</a>
• <a href="?&id=LinkID">SubSection Name</a>

Section Name

• <a href="?&id=LinkID">SubSection Name</a>
• <a href="?&id=LinkID">SubSection Name</a>

Now im struggling to select this by a single query so I can interate each row and print it, can it be done, if so how?

Basically need a 2D array to interate over?

Here are the tables:

CREATE TABLE sections (
sectionid int(10) NOT NULL auto_increment,
name varchar(64) NOT NULL,
PRIMARY KEY(sectionid)
) ENGINE = InnoDB;

CREATE TABLE subsections (
subsectionid int(10) NOT NULL auto_increment,
sectionid int(10) NOT NULL,
linkid int(10) NOT NULL,
name varchar(64) NOT NULL,
PRIMARY KEY (subsectionid),
FOREIGN KEY (sectionid) REFERENCES sections (sectionid),
FOREIGN KEY (linkid) REFERENCES links (linkid)
) ENGINE = InnoDB;

The links table I havnt created yet...

Thx

 

[JonD]

Because im using a Database class, I cannot use nested queries because the result resource will be overwritten. Is there a way around this when using DB classes? Also you executre a query on every iteration, but what if my navigation list has 100 sections (It wont be) making 100 queries? Inneffeicient, tut tut lol

Anyway back on track, the following method works but only selects one from each subsection, each section has 2 other subsections:

function getNavigation()
{
global $db;

$db->query("SELECT sections.name, subsections.name FROM sections LEFT JOIN subsections ON sections.sectionid = subsections.subsectionid");

echo '<dl>';

while ($result = $db->fetchArray())
{
echo '<dt>'.$result[0].'</dt>';
echo '<dd>'.$result[1].'</dd>';
}

echo '</dl>';
}

Which prints:

Section 1
SubSection 1
Section 2
SubSection 1

which should print: ( Yeah I know it prints the section name twice but a simple if statement **** fix this, but you get the idea...)

Section 1
SubSection 1
Section 1
SubSection 2
Section 2
SubSection 1
Section 2
SubSection 2

 

[JonD]

So basically in your subsections table you would have two ID fields; one which is a unique identifier for your subsection, and one which relates to the sections table, and basically describes which section a given subsection belongs to.

My subsection table has that:

CREATE TABLE subsections (
subsectionid int(10) NOT NULL auto_increment, <--- Unique identifier
sectionid int(10) NOT NULL, <-- Refers to parent
....

but yeh after you said that I checked my query and i was using the wrond field

SELECT sections.name, subsections.name, subsections.linkid FROM sections LEFT JOIN subsections ON sections.sectionid = subsections.subsectionid

should have been

SELECT sections.name, subsections.name, subsections.linkid FROM sections LEFT JOIN subsections ON sections.sectionid = subsections.sectionid

 

[JonD]

See above

Here is my final working function:

function getNavigation()
{
	global $db;
	
	$db->query("SELECT sections.name, subsections.name, subsections.linkid FROM sections LEFT JOIN subsections ON sections.sectionid = subsections.sectionid");

	echo '<dl>';
	
	$previousSection;

	while ($result =  $db->fetchArray())
	{	
		$section = $result[0];
		
		if ( $previousSection == $section )
			echo '<dd><a href="?a='.$result[2].'">'.$result[1].'</a></dd>';		
		else
		{				
			echo '<dt>'.$result[0].'</dt>';
			echo '<dd><a href="?a='.$result[2].'">'.$result[1].'</a></dd>';
		}
		
		$previousSection = $section;
	}	
	echo '</dl>';
}

Thx for your help
JD

 

[JonD]

All working now then?

Yeah 'tis all good!

Beansprout, that looks good, ill try after my tea