How do i use relations between tables in the db in my navigation?

[klemme]

Looking for an answer about how I can use relations in my DB, when a link is clicked.

I have the main navigation in the top of the page, the table is called:"SUBJECTS", primary key is ID.

I also have a local navigation in the left side of the page.

The table is called "PAGES",
Primary key is ID,
And then I made a SUBJECTID in the PAGES table too.
I want to use the SUBJECTID in my query, to use that relation from the PAGES table, and show the relevant pages in the local navigation, after a subject is clicked.

I guess I need to check if a subject is set, and if it is, then I want to call out from the PAGES table, the pages that has a SUBJECTID which is equal to the id in the SUBJECTS table.

How can a query like that going to look like?

Now I am ordering the SUBJECT NAVIGATION like this:

// Query the body section for the proper subject------------------------------------------
$sqlCommand = "SELECT subjecttitle, subjectbody FROM subjects WHERE id='$subjectid' LIMIT 1"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
while ($row = mysqli_fetch_array($query)) { 
    $subjecttitle = $row['subjecttitle'];
	$subjectbody = $row["subjectbody"];
} 
mysqli_free_result($query); 
//------------------------------------------------------

// Build SUBJECT navigation and gather SUBJECT data here--------------------------------
$sqlCommand = "SELECT id, linklabel, pos FROM subjects WHERE showing='1' ORDER BY pos ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$SubjectMenuDisplay = '';
while ($row = mysqli_fetch_array($query)) { 
    $sid = $row["id"];
    $linklabel = $row["linklabel"];
	$position = $row['pos'];
	
	$SubjectMenuDisplay .= '<li><a href="index.php?sid=' . $sid . '">' . $linklabel . '' . $position . '</a></li>';
	
} 
mysqli_free_result($query); 

// Getting SUBJECT = 1, FOR INDEX PAGE
$sqlIndexCommand = "SELECT * FROM subjects WHERE id='1'"; 
$query = mysqli_query($myConnection, $sqlIndexCommand) or die (mysqli_error()); 

while ($row = mysqli_fetch_array($query)) { 
    $Index = $row["id"];
	$SubjectIndexTitles = $row['subjecttitle'];
	$SubjectIndexBody = $row["subjectbody"];
}
//******************SUBJECT DATA ENDS HERE*******************************************************************************
?>

The PAGE navigation looks like this:

// Query the body section for the proper page------------------------------------------
$sqlCommand = "SELECT pagetitle, pagebody FROM pages WHERE id='$pageid' LIMIT 1"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
while ($row = mysqli_fetch_array($query)) { 
    $pagetitle = $row['pagetitle'];
	$pagebody = $row["pagebody"];
} 
mysqli_free_result($query); 
//-----------------------------------------------------

// Here I need some help, I Want to choose the page which subjectid = id in the //SUBJECTS table - So only the relevant links to each subject is displaying

// Build PAGE navigation relevant to clicked subject Here--------------------------------
$sqCommand = "SELECT * FROM subjects ORDER BY id ASC";
$SqCommand2 = mysqli_query($myConnection, $sqCommand) or die (mysqli_error());
while ($row = mysqli_fetch_array($SqCommand2)){
	$subjectid = $row["id"];
}
mysqli_free_result($SqCommand2); 

$sqlCommand = "SELECT id, linklabel, pos FROM pages WHERE showing='1' ORDER BY pos ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$PageMenuDisplay = '';
while ($row = mysqli_fetch_array($query)) { 
    $pid = $row["id"];
    $linklabel = $row["linklabel"];
	$position = $row['pos'];
	
	$PageMenuDisplay .= '<li><a href="index.php?pid=' . $pid . '">' . $linklabel . '' . $position . '</a></li>';
	
} 
mysqli_free_result($query); 

//******************PAGE DATA ENDS HERE*************************************
?>

The way I check if either a subject or a page has been set is like this:

if (isset($_GET['sid'])) {
	$subjectid = ($_GET['sid']);
	$pageid = NULL;
	$subjectid = preg_replace("[^0-9]", "", $_GET['sid']); // filter everything but numbers for security
} elseif (isset($_GET['pid'])){
	$subjectid = NULL;
	$pageid = ($_GET['pid']);
	$pageid = preg_replace("[^0-9]", "", $_GET['pid']); // filter everything but numbers for security
}	else {
	$subjectid = NULL;
	$pageid = NULL;
}

As this code is now: ALL the subjects display, placed in a top nav, which is the point.

BUT also ALL the PAGES display in the left side = local navigation..

AND I only need to show FROM the PAGES table, where subjectid is equal to the unique id in the SUBJECTS table.

If my explanation made sense, I hope someone can see where I am heading with this :-)

Klemme

[madCoder]

SELECT * FROM pages
WHERE subjectid = '{$sid}'
AND showing='1' ORDER BY pos ASC

That will let you filter for just pages keyed to a supplied subject where $sid is the subject id.

[klemme]

Thanks!

Can you perhaps also see how I can change the code below so the page navigation doesnt dissapear after it is clicked.

Having some massive problems figuering this out :-/

if (isset($_GET['sid'])) {
	$subjectid = ($_GET['sid']);
	$pageid = NULL;
	$subjectid = preg_replace("[^0-9]", "", $_GET['sid']); // filter everything but numbers for security
} elseif (isset($_GET['pid'])){
	$subjectid = NULL;
	$pageid = ($_GET['pid']);
	$pageid = preg_replace("[^0-9]", "", $_GET['pid']); // filter everything but numbers for security
}	else {
	$subjectid = '1';
	$pageid = NULL;
}

What happens now is that a page is only set, after a subject is set. So after i click a page, then the subject is unset, meaning the pages are gone..

[madCoder]

What happens now is that a page is only set, after a subject is set. So after i click a page, then the subject is unset, meaning the pages are gone..

Umm, then don't do that. You can use the ternary operator when initializing the variables instead of setting them to null. You should also, of course always pass the settings needed with each link.

// example of ternary
$subjectid = isset($_GET['sid']) ? intval($_GET['sid'] : 1;  // Use provided subject or initialize it to default 1
$pageid = isset($_GET['pid']) ? intval($_GET['pid'] : null;

I use intval() instead of preg_replace() which is much slower. Intval will always return an integer, which is what you want.

In addition to replacing your if{} with the code above, all your links with pid should also pass the relevant sid. I think that should solve your problem.