mySQL - This possible?

JonD

JonD

JonD

Associate
Joined
26 Jun 2003
Posts
1,140
Location
North West
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...
Click to expand...

Thx
 
Last edited:
It cann'y be done cap'n (alright - it can - must get in before Sic and Augmented.)

Code: 
<?php
//[db connection stuff snipped out, but our db connection is $conn]

$sql = "SELECT sectionid, name FROM sections";

$sResult = mysql_query($sql,$con) or die('It broke: '.mysql_error());

$sections = mysql_fetch_assoc($result);

echo '<ul>';

foreach($sections as $section){
	
	echo '<li>'.$section['name'].'</li>';
	
	$sql = "SELECT linkid, name FROM subsections WHERE sectionid = '${section['sectionid']}'";

	$ssResult = mysql_query($sql,$con) or die('It broke again: '.mysql_error());

	echo '<ul>';
	foreach($ssresult as $subsection){
		echo '<li><a href=?&id='.$subsection['linkid'].'>'.$subsection['name'].'</a></li>';
	}
	
	echo '</ul>';

}

echo '</ul>';

?>
(This isn't the best way of doing it - for one thing foreach() can be inefficient and recursive love is cooler, but it's the simplest way I could think of as a starting point...)
 
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>';
}
Click to expand...

Which prints:

Section 1
SubSection 1
Section 2
SubSection 1
Click to expand...

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
Click to expand...
 
Last edited:
I think from looking at your JOIN query you're keying the subsection id against the section id, whereas what you want to be doing is doing something like "LEFT JOIN subsections ON sections.sectionid = subsections.mainsectionid".

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.

Your code sort of works, but will only return subsections where a section and a subsection have (coincidentally) the same ID number.

Edit: Sorry, just had another gander at your table schema and it seems you have a number of ID fields defined. I think in your subsections table, you should use the 'subsectionid' field to hold the unique identifier of a subsection, and the 'sectionid' field to reference back to a section that a given subsection belongs to. A slight modification of your query and you're almost home :)
 
Last edited:
elkdanger said:
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.
Click to expand...

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
Click to expand...
 
Last edited:
JonD said:
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
....
Click to expand...

I just saw that and edited my post above. So.. why aren't you using them? :p
 
See above :p

Here is my final working function:

PHP: 
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
 
Last edited:
Code: 
<?php

$sql = "SELECT sections.name AS sname,subsections.linkid as sslinkid, subsections.name as ssname, subsections.subsectionid as ssid WHERE subsections.sectionid = sections.sectionid";

$result = mysql_query($sql,$con) or die('It broke again: '.mysql_error());

while($row = mysql_fetch_assoc($result)){
		$arr[$row['sname']][$row['ssid']]['linkid'] = $row['sslinkid'];
		$arr[$row['sname']][$row['ssid']]['name'] = $row['ssname'];
}

function printList($key,$value){

	echo '<li>'.$key.'</li><ul>';
	
	foreach($value as $li){
		echo '<li><a href="id='.$li['linkid'].'">'.$li['name'].'</a></li>';
	}
	echo '</ul>';
	
}

echo '<ul>';
array_walk($arr,'printList');
echo '</ul>';
?>
Will this work or am I on crack? :confused:


I really should be doing proper work....
 
You must log in or register to reply here.
Back
Top Bottom