Pages under subject

PHP
1

hi, i was expecting the pages to show under subjects but its showing as a subject


<?php require_once("include/connection.php"); ?>
<?php require_once("include/functions.php"); ?>
<?php include("include/header.php"); ?>
<div id="navigation">
<ul>
       <?php
	//3. perform database querry
		$subject_set = mysql_query("SELECT * FROM subjects", $connection);
	if (!$subject_set) {
		die("Database query failed: " . mysql_error());
		}
		//4 use returned data
	while($subject = mysql_fetch_array($subject_set)) {
		echo "<li>{$subject["menu_name"]}</li>";
	$page_set = mysql_query("SELECT * FROM subjects, pages WHERE subjects.id = pages.subject_id", $connection);
	if (!$page_set) {
		die("Database query failed: " . mysql_error());}
		}
		//4 use returned data
		
	while($page = mysql_fetch_array($page_set)) {
		echo "<li>{$page["menu_name"]}</li>";
		}
		
		?>
	</ul>
</div> <!--end of navigation div -->
<div id="bodycontent">
 <h2> Content Area</h2>
</div>
</div>
<?php require("include/footer.php"); ?>
2

i was expecting the pages to show under subjects but its showing as a subject

I have no idea what you mean by that.
But:

  1. You have a query inside a while-loop, and that’s a bad idea. Even more so, because the query inside the loop extracts every time the same thing (since there’s no WHERE condition based on what you got from the first query.
    Get rid of the first query, and move the query that’s inside the loop, outside the loop.
  2. The query with the join between two tables should not use an ‘*’ in the SELECT clause. Specify the columns you need. That way you won’t get unexpected column names in the result set when both tables contain columns with the same name (like ‘id’).
  3. Moving the query outside of the loop, you’ll have to change your code a bit, because I guess you’ll want to show each subject only once, and then list the pages for that subject.
3

guido, i am a newbie, and was following a tutorial, when i got to this extent


<div id="navigation">
<ul>
    <?php
	//3. perform database querry
		$subject_set = mysql_query("SELECT * FROM subjects", $connection);
	if (!$subject_set) {
		die("Database query failed: " . mysql_error());
		}
		//4 use returned data
	while($subject = mysql_fetch_array($subject_set)) {
		echo "<li>{$subject["menu_name"]}</li>";}
		?>	
</ul>
</div> <!--end of navigation div -->
<div id="bodycontent">
<h3> content area</h3>
<p> i hope this works. </p>
</div>
<?php
require("include/footer.php"); ?>

the mark up works well, it pulls the subjects fro the database, but when i add the markup that is suppose to pull the associated database under the subect, it does not disply it as a sub under the subject but displays it as a subject


<div id="navigation">
<ul>
       <?php
	//3. perform database querry
		$subject_set = mysql_query("SELECT * FROM subjects ORDER BY position ASC", $connection);
	if (!$subject_set) {
		die("Database query failed: " . mysql_error());
		}
		//4 use returned data
	while($subject = mysql_fetch_array($subject_set)) {
		echo "<li>{$subject["menu_name"]}</li>";}
	$page_set = mysql_query("SELECT * FROM subjects, pages WHERE subjects.id = pages.subject_id ", $connection);
	if (!$page_set) {
		die("Database query failed: " . mysql_error());}
		
		//4 use returned data
		
	while($page = mysql_fetch_array($page_set)) {
		echo "<li>{$page["menu_name"]}</li>";
		}
		
		?>
	</ul>
</div> <!--end of navigation div -->
<div id="bodycontent">
 <h2> Content Area</h2>
</div>
</div>
<?php require("include/footer.php"); ?>
4

SELECT * on multiple tables. Ew.

Lets clean it up a bit at the very least (Guido’s right about the queries-within-loops bit, btw, but this is a first step to show you how to work with the example you gave.)


        $subject_set = mysql_query("SELECT id,menu_name FROM subjects ORDER BY position ASC", $connection);
    if (!$subject_set) {
        die("Database query failed: " . mysql_error());
        }
        //4 use returned data
    while($subject = mysql_fetch_array($subject_set)) {
        echo "<li>{$subject["menu_name"]}</li>";}
    $page_set = mysql_query("SELECT menu_name FROM pages WHERE ".$subject["id"]." = pages.subject_id ", $connection);
    if (!$page_set) {
        die("Database query failed: " . mysql_error());}
       
        //4 use returned data
       
    while($page = mysql_fetch_array($page_set)) {
        echo "<li>{$page["menu_name"]}</li>";
        }
       
        ?>

So take a look at that, and see if you have more questions.

5

it gave this error, i am taking a look at it.

6

Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘= pages.subject_id’ at line 1

7

is your subject table’s ID field a non-numeric?

SELECT menu_name FROM pages WHERE subject_id = ".$subject[“id”],$connection);