Hi All,

Can someone tell my how I can make this work:

I have subjects AND pages from the Database.

I am in this small script deciding which link is set, a subject or a page.

Also I am showing the relevant pages under each subject, by ordering the pages like this:

$sqlCommand = "SELECT * FROM pages WHERE subjectid='" . $subjectid . "' AND showing='1' ORDER BY pos ASC";

As you can see below, when a subject is set, a page is NUll.
When a page is set, a subject is NULL.

The problem is, that the page navigation only is visible when a subject is set.

So what happens when I click a subject is: The relevant page navigation shows up = Perfect! Then I click a page, and Then the page navigation is GONE, Because then i have set a page, and that means in my script that the subject is unset, right..And when I only show the page when a subject is set, the navigation dissapears! Do you follow me....

I want to write this so when a subject is set, the relevant pages/links appears, and then I want the pages/links to stay there after one of them has been clicked.

Now, they dissappear...

Script:

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

Recommended Answers

All 18 Replies

Member Avatar for diafol

When page is set, get the subject from the query which can then be used to get the other pages:

"SELECT subjectid FROM pages WHERE pageid='$pageid'"

extract subjectid as $subjectid then do your:

"SELECT * FROM pages WHERE subjectid='" . $subjectid . "' AND showing='1' ORDER BY pos ASC"

You could proabably do this in one statement, either with a JOIN or a nested select on the WHERE clause.

The whole structure of my code looks like this, and I am a bit confused on how and where to implement this?

At the moment at decide the page/subjects 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 = '1';
	$pageid = NULL;
}

I have made my subject nav like this:

$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);

I have made the Page nav like this:

$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--------------------------------
$sqlCommand = "SELECT * FROM pages WHERE subjectid='" . $subjectid . "' AND 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);

In my HTML, I echo out the nav like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php if (isset($_GET['pid'])){	
	echo $pagetitle;
} elseif (isset($_GET['sid'])){
	echo $subjecttitle;
} else echo $SubjectIndexTitle;
?></title>
<link href="styles/style.css" rel="stylesheet" type="text/css" media="all" />
</head>
<body>
<div id="wrapper2">
<div id="wrapper">
	<div id="header"><div align="right"><a href="administrator/login/login-form.php">Admin</a></div></div>
    
    <div id="top_nav">
    <ul>
	<?php echo $SubjectMenuDisplay; ?>
    </ul>
    </div> 
      
    <div id="left"> 
    <ul>  
    <?php 	
	if (isset($_GET['sid'])){
	echo $PageMenuDisplay;
	} 

	?>
    </ul>
    </div> 
       
    <div id="right">    
	<?php 
	if (isset($_GET['pid'])){	
	echo $pagebody; 
	} elseif (isset($_GET['sid'])){
	echo $subjectbody;
	} else echo $SubjectIndexBody;
	?>
    </div>  

  	<div id="footer"><address><?php echo $footer; ?></address></div>
</div>
</div>

</body>
</html>

It is in this ORDER on my page, this code appears, its the exact structure.

Klemme

Member Avatar for diafol

So what's wrong? Did you follow my idea?

Hey Ardav!

Ive just seen your answer to my thread now, on how to keep the relevant pages on display after a subject has been set.

I am out on a little deep water here, as I have only known PHP for a couple of months.

Can you please, check my code above if you have the time.

Ive put in the code on how to deside which is selected, which unfortunately unsets the page navigation after a page has been clicked :-/

Then the subject nav.

Then the page nav.

Where and HOW, in my code would you integrate your suggestion???

I can follow your thoughts, on extracting the subjectid, after a page has been set, but I am struggling a bit here to execute it, to be honest :-(

It is just about the last part I am working on to finish my project, so I am really itching to move on!

I just tried to write it like this - My intention was to make the $pagesid = $subjectid as it is written, but the pages/navigation still dissappears after it is set?!?

if (isset($_GET['sid'])) {
       $subjectid = ($_GET['sid']);
       $subjectid = preg_replace("[^0-9]", "", $_GET['sid']); // filter everything but numbers for security
   
       if (isset($_GET['pid'])){
            $pageid = ($_GET['pid']);
            $pageid = preg_replace("[^0-9]", "", $_GET['pid']); // filter everything but numbers for security
       }    
       else 
       {
            $pageid = $subjectid = ($_GET['sid']); //subject standardside
       }
  }
  else {
  //Standard pages, deciding index
       $subjectid = '1';//standardside;
       $pageid = NULL;//standardside;
   }
/*
Member Avatar for diafol

Your code is a little difficult to follow, so I'll give you my best interpretation:

//do connection details

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

}elseif (isset($_GET['pid'])){
    $pageid = preg_replace("[^0-9]", "", $_GET['pid']); // filter everything but numbers for security
    $r = mysqli_query($myConnection,"SELECT subjectid FROM pages WHERE pageid=$pageid");
    if(mysqli_num_rows($r)>0){
        $d = mysqli_fetch_array($r);
        $subjectid = $d['subjectid'];
     }else{
        $subjectid = 1; //default subject id
        $pageid = 1; //default page id for subject_id = 1 - change this possibly
     }
}else{
     $subjectid = 1; //default subject id
     $pageid = 1; //default page id for subject_id = 1 - change this possibly
}

//now the SUBJECT data
$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"];
} 

// now the SUBJECT navigation data
$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>';

} 

//now the PAGE data
$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"];
} 
//now the PAGE navigation data
$sqlCommand = "SELECT * FROM pages WHERE subjectid=$subjectid  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);

I get this warning with the code after doing like the above written:

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

And the page just shows a white screen, and displaying that error/warning.

Line 53 is like this:
It is the last line.

//now the PAGE navigation data
$sqlCommand = "SELECT * FROM pages WHERE subjectid=$subjectid  WHERE showing='1' ORDER BY pos ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());

I had NO idea this could be such a struggle to decide what is set or not.

I follow your suggestion, and I can see that it maks perfect sense :-)

I just get errors, and a blank screen after integrating it.

Any idea what might cause this?

One more thing:

Is it possible to make just one query, and JOIN the two tables, so the relevant pages for each subject, simply works like it is "attached" to it, and make the navigation that way?

I havent used joins before either.. :-)
something like this:

// joining the two tables
$sql = "SELECT * FROM subjects INNER JOIN pages ON subjectid = subject.id";

while ($row = mysqli_fetch_array($sql))  {
       /*AND HERE SOMEHOW PULL OUT THE DATA, AND BUILD THE NAVIGATION, SO THE PAGES GET TO "BELONG TO A SUBJECT" WITHOUT FURTHER NEED TO CLARIFY IF SUBJECT IS SET ETC ETC*/
}

//Somehow change this to fit
$SubjectMenuDisplay .= '<li><a href="index.php?sid=' . $sid . '">' . $linklabel . '' . $position . '</a></li>';

//Somehow change this to fit to what subject it belongs to, and integrate it in tis line
$PageMenuDisplay .= '<li><a href="index.php?pid=' . $pid .//According to subject here, somehow? '">' . $linklabel . '' . $position . '</a></li>';

I have no idea if that would be possible, if it is, then it would be a huge saver on the code, and only one query..

Do you reckon it can be done in some way?

Member Avatar for diafol

OK, give the table structures.
If you've got a mockup (image) of what you're look for, include that.

At the moment, I've got an idea that you want a list of subjects, each with a list of pages beneath them. Is that right?

Yes, that is correct, like this:

I have a global navigation in the top of my site = SUBJECTS.

After a subject is clicked, I want to display the pages that have a relation to that subject.

I have set the pages to echo in a left navigation (like a local navigation for each subject).

I can easily take a screenshot of the page, but is it possible to include an imagefile here?

This is my two tables:
SUBJECTS AND THE ROWS
ID (Primary Key)
SUBJECTTITLE
LINKLABEL
SUBJECTBODY
POS (//short for position)
SHOWING
KEYWORDS
DESCRIPTION
LASTMODIFIED

_______________________________________________________________
PAGES AND THE ROWS

ID (Primary Key)
SUBJECTID
SUBJECTTITLE
LINKLABEL
SUBJECTBODY
POS (//short for position)
SHOWING
KEYWORDS
DESCRIPTION
LASTMODIFIED

As it is now:

My pages show perfectly when a subject is clicked, but they dissappear again after one of the pages is clicked. = The Problem.. :-)

Member Avatar for diafol

OK, let me get my thinking cap on, I'll have a little thinky. May take a little while, I'm working on my own project at the moment. :(

BTW - yes you can show an image. Use the Advanced Editor in the reply box below (or select Go Advanced). Then choose the paperclip button. Choose an image file via the popup. Once uploaded, close the popup. Your attachment will then appear under the paperclip downarrow. You can either leave the attachment as is or you can insert the image into the body of the message by selecting the image from the downarrow.

Thats great!

I look forward to hear from you, and hopefully find some way to deal with that famous menu :-)

Ill attach a screen dump, when I get back from work..

Later,

Klemme

Member Avatar for diafol
if(isset($_GET['sid'])){
	$sid = intval($sid);
	$q = mysql_query("SELECT * FROM `subjects` WHERE `id` = $sid AND showing = 1");
	if(mysql_num_rows < 1){
		$subjecttitle = "";
		$subjectbody = "No subject with this id exists";
		$sid = 0;
	}else{
		$subjectlist = "";
		while($d = mysql_fetch_array($q)){
			if($sid == $d['id']){
				$subjecttitle = "<h3>{$d['subjecttitle']}</h3>";
				$subjectbody = $d['subjectbody']; //if no html included, place inside <p> or something
				$subjectlist .= "<li>{$d['linklabel']}</li>";
			}else{
				$subjectlist .= "<li><a href=\"index.php?sid={$d['id']}\" title=\"{$d['description']}\">{$d['linklabel']}</a></li>";
			}
		}
	}
}
if(isset($_GET['pid']) && $sid != 0){
	$pid = intval($pid);
	$q = mysql_query("SELECT * FROM `pages` WHERE `id` = $pid AND `subjectid` = $sid AND showing = 1");
	if(mysql_num_rows < 1){
		$pagetitle = "";
		$pagebody = "No page with this id exists";
	}else{
		$pagelist = "";
		while($d = mysql_fetch_array($q)){
			if($pid == $d['id']){
				$pagetitle = "<h3>{$d['pagetitle']}</h3>";
				$pagebody = $d['pagebody']; //if no html included, place inside <p> or something
				$pagelist .= "<li>{$d['linklabel']}</li>";
			}else{
				$pagelist .= "<li><a href=\"index.php?sid={$d['subjectid']}&pid={$d['id']}\" title=\"{$d['description']}\">{$d['linklabel']}</a></li>";
			}
		}
	}
}
/*NOW you have 4 variables:
$subjectdata
$pagedata
$subjectlist
$pagelist*/

AN easy workaround was to place the sid in the page reference. It;s proabbaly best not to use * in sql, but to include all the fields required.

I have implenented your script, but the entire navigation doesnt show, not subject or pages..

It looks like this now:

if(isset($_GET['sid'])){
	$sid = intval($sid);  
	$q = mysqli_query("SELECT * FROM subjects WHERE id = $sid AND showing = 1");
	if(mysqli_num_rows < 1){
		$subjecttitle = "";
		$subjectbody = "No subject with this id exists";
		$sid = 0;
	}else{
		$SubjectMenuDisplay = "";
		while($d = mysqli_fetch_array($q)){
			if($sid == $d['id']){
				$subjecttitle = $d['subjecttitle'];
				$subjectbody = $d['subjectbody']; //if no html included, place inside <p> or something
				$subjectlinklabel = $d['linklabel'];
			}else{
				$SubjectMenuDisplay .= "<li><a href=\"index.php?sid={$d['id']}\">{$d['subjectlinklabel']}</a></li>";
			}
		}
	}
}
if(isset($_GET['pid']) && $sid != 0){
	$pid = intval($pid);
	$q = mysqli_query("SELECT * FROM pages WHERE id = $pid AND subjectid = $sid AND showing = 1");
	if(mysqli_num_rows < 1){
		$pagetitle = "";
		$pagebody = "No page with this id exists";
	}else{
		$PageMenuDisplay = "";
		while($d = mysqli_fetch_array($q)){
			if($pid == $d['id']){
				$pagetitle = $d['pagetitle'];
				$pagebody = $d['pagebody']; //if no html included, place inside <p> or something
				$pagelinklabel .= $d['linklabel'];
			}else{
				$PageMenuDisplay .= "<li><a href=\"index.php?sid={$d['subjectid']}&pid={$d['id']}\">{$d['pagelinklabel']}</a></li>";
			}
		}
	}
}

When I write in the URL, maybe: index.php?sid=1, its still gone?

I can show you how i implement the php and echo it out in my html:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php if (isset($_GET['pid'])){	
	echo $pagetitle;
} elseif (isset($_GET['sid'])){
	echo $subjecttitle;
} else echo $SubjectIndexTitle;
?></title>
<link href="styles/style.css" rel="stylesheet" type="text/css" media="all" />
</head>
<body>
<div id="wrapper2">
<div id="wrapper">
	<div id="header"><div align="right"><a href="administrator/login/login-form.php">Admin</a></div></div>
    
    <div id="top_nav">
    <ul>
	<?php echo $SubjectMenuDisplay; ?>
    </ul>
    </div> 
      
    <div id="left"> 
    <ul>  
    <?php 	
	if (isset($_GET['sid'])){
	echo $PageMenuDisplay;
	} 
	?>
    </ul>
    </div> 
       
    <div id="right">    
	<?php 
	if (isset($_GET['pid'])){	
	echo $pagebody; 
	} elseif (isset($_GET['sid'])){
	echo $subjectbody;
	}
	?>
    </div>  

  	<div id="footer"><address><?php echo $footer; ?></address></div>
</div>
</div>
<?php mysqli_close($myConnection); ?>
</body>
</html>

Does my it make more sense after seeing hoe i am integrating it with html?

Member Avatar for diafol

It seems to me that you don't have an include file to provide a default - nothing will show up if sid /pid aren't in the querystring. So, you need to run the script at the start anyway. provide a default value for sid if get not set.

You need to make a few things clear:

what should be displayed if:
the page opens (no sid/pid)
only sid (but not found in DB)
sid only and found in DB (show subject info?)
sid and pid found in DB (show subject info and page info?)
sid and pid combo not found in DB

Guess there is no landing page as it is now.

But then when I write the URL: index.php?sid=1

Which exists in the database, shouldnt the navigation at least then be visible?

This line again: index.php?sid=1
Gives me this error message from the script: "No subject with this id exists".

Member Avatar for diafol

Yes it should. Hmm, you're using mysqli and I was using mysql. Don't you have to include the connection object in the query function?

Anyway:

$q = mysqli_query("SELECT * FROM subjects WHERE id = $sid AND showing = 1");

do this before the above:

echo "SELECT * FROM subjects WHERE id = $sid AND showing = 1";

see what comes up. Paste it into phpMySQL SQL window or run it as a query in your GUI. See what happens.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.