1,105,281 Community Members

JOIN tables in navigation, problem

Member Avatar
klemme
Posting Whiz
376 posts since Mar 2011
Reputation Points: 3 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

I am creating a cms, where I have a Global horizontal navigation(SUBJECTS) in the top, and a local navigation(PAGES) on the left, all database driven.

First I decide if either a subject or a page has been set, to display the correct information:

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;
}

SUBJECTS are pulled from the DB, from a table called SUBJECTS.
The code for creating the subject navigation looks 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 AND MODULE DATA ENDS HERE*******************************************************************************
?>

Next I have a table called pages. I want this to display as a local navigation in the left side of my site.
The table PAGES has got a "SUBJECTID"(SUBJECTID in the table PAGES, is identical to one of the ID's in the table called SUBJECTS), which I want to use in a join, so I end up making a query which only displays the PAGES which are relevant for the chosen SUBJECT.

NOW, I get the PAGES like this, which displays ALL of the pages, and NOT the ones relevant for each SUBJECT according to SUBJECTID and which SUBJECT is set.

THIS IS WHERE I THINK THE QUERY SHOULD CONTAIN A JOIN SOMEHOW??

<?php //******************PAGE AND MODULE DATA STARTS HERE*****************************************************************************
// 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); 
//-------------------------------------------------------------------------------------

// Build PAGE navigation relevant to subject-------------------------------- 
// [B]THIS IS WHERE I SHOULD MAKE A JOIN; ISNT IT?[/B]
$sqlCommand = "SELECT id, linklabel, pos FROM pages INNER JOIN subjects ON subjectid = subjects.id 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); 

//******************PAGES AND MODULE DATA ENDS HERE*******************************************************************************
?>

I get this error when I try to see my page, after I made the JOIN:

Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\wamp\www\mycms\includes.php\PageAndModuleData.php on line 26

Line 26 is the bottom one here:

$sqlCommand = "SELECT id, linklabel, pos FROM pages INNER JOIN subjects ON subjectid = subjects.id ORDER BY pos ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());

When I want to echo out the $PageMenuDisplay in my website, I try to do it like this:

<?php 
	if (isset($_GET['pid'])){
	echo $PageMenuDisplay;//THIS SHOULD ONLY CONTAIN PAGES RELEVANT FOR SET SUBJECT
	}
	?>

In short:

WHEN a SUBJECT is set, HOW can I decide to only show the relevant PAGES for the SUBJECT.

HOW can I make a query like that, the one now is not working with the rest of my code.

Anyone? :-)
Klemme

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: