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