Database display working wrong

[ryntak4536]

Hi all, I'm still new to PHP, so be gentle....

I'm trying to do this:  I've got a site, where I'm showing a list of authors.  The author names are hyperlinks.  When a name is clicked, I'd like the author's titles to appear below his/her name, but the list of authors to continue on below it.  Basically like an expanding/collapsing menu.

 

However, for some reason, the code I've got is not working right.  The page loads, and shows the list of authors (so far so good).  When an author's name is clicked, however, the title's appear above the list of authors, and several of the author's names drop off.  But not always...(yeah, confusing...)  I haven't been able to figure out what's driving whether they fall off or not.

 

Here's what I've got:

The page itself:

<?php require_once("includes/db_connection.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php require_once("includes/header.php"); ?>

<?php
	find_current_selection();
	
?>
<article>
	<h1>Books</h1>
	<div class = center>Select author by initial of last name.</div>
        <p class = "center">
		<?php echo author_navigation(); ?>
        </p>
        <ul class = "author"> 
		
		<?php echo author_display($current_author); ?>
		
</article>
</section><!--end #mainContent-->
<!-- InstanceEndEditable -->
                
                <!-- InstanceBeginEditable name="RightSidebar" -->
<aside>
	<h1>No sidebar material yet</h1>
    <p></p>
</aside>
                
<?php require_once("includes/footer.php"); ?>

Functions, as applicable to this:

function find_current_selection(){
		global $current_initial;
		global $current_author;
		
		if(isset($_GET["initial"])){
			$current_initial = $_GET["initial"];
			$current_author = null;
		} elseif(isset($_GET["author"])){
			$current_initial = " ";
			$current_author = $_GET["author"];
		}	else {
			$current_initial = " ";
			$current_author = null;
		}
	}

function author_display($author_array){
			//$output = "<ul class=\"author\">";
			$author_set = find_author_list();
			while($author = mysqli_fetch_assoc($author_set)){				
			$output .= "<li";
			if($author_array && $author["author_id"] == $author_array["id"]){
			$output .= " class=\"SelectedAuthor\"";
			} //END OF if($subject_array && $subject["id"]==$subject_array["id"])
			$output .= ">"; // CLOSE li tag
			$output .= "<a href=\"books.php?author=";
			$output .= urlencode($author["author_id"]);
			$output .= "\">"; 
			$output .= htmlentities($author["Last_Name"]. ", " . $author["First_Name"]); 
			$output .= "</a>";
			
			//titles start here
			if ($author_array["author_id"] == $author["author_id"]){
				$title_set = find_titles_for_author($author["author_id"]);
				$output = "<ul class=\"title_name\">";
				while($title = mysqli_fetch_assoc($title_set)){						
					$output .= "<li>";
					//$output .= "<a href=\"index.php?author=";
					//$output .= urlencode($title["Title"]);
					//$output .= "\">";
					$output .= htmlentities($title["Title"]) ;
					//$output .= "</a>
					$output .= "</li>";
					}// END OF while($page = mysqli_fetch_assoc($page_set))
					$output .= "</ul>"; //END OF 'title_name' class
					mysqli_free_result($title_set);
				}// END OF if
			//END OF titles
			
			$output .= "</li>";// END OF author li
			}// END OF while($author = mysqli_fetch_assoc($author_set))
			
			mysqli_free_result($author_set);
			$output .= "</ul>"; //END OF 'authors' class
			
			return $output;
			}

function find_titles_for_author($author_id){
		//Perform title query
		global $connection;
		
		$query = 	"SELECT * ";
		$query .= 	"FROM titles ";
		$query .=	"WHERE Author_ID = {$author_id} ";
		$query .=	"ORDER BY Series_ID ASC";
		$title_set = mysqli_query($connection, $query);
		//test if there was a query error
		confirm_query($title_set);
		return $title_set;
	}	

Would love it if someone can tell me what I've done wrong.

[rwhite35]

I would consider looking at your program logic.  The functionality you want is mainly client side (even though the data is coming from a database query).  Consider pulling all your author records on initial load.  Then re develop your UI so when a web user clicks (or hover over) an authors name, it will pull the sub names from the DOM memory.  That would be preferable then making another trip to the database, and will make the functionality you want easier to implement.  

 

Look into "separation of concerns" as a programming practice.  That will help direct your development.  

Good luck.

[ryntak4536]

Thanks.  But, I'm a little confused.

 

What do you mean by 'consider pulling all the author records on initial load'?  I thought that's what I did when I pulled the author data...?

[rwhite35]

Taking another look at your code and comparing that to my response, the dataset I'm suggesting would look something like. From this multidim array you can foreach loop over each array, constructing you navigation without making additional trips to the database.  In the outer loop [0]=>array('author'=>string...) the next author would be [1]=> array('author'=>"Jack Straw", 'id'=>102...).  My answer doesn't address your current code, however, I suggest try a different approach altogether.  

$results (
  [0] => array (
      [author]=>"douglas adams"
      [id]=>101
      [title]=> array (
                        [0]=>"Hitchhiker Guide to The Gallaxcy"
                        [1]=>"Dirk Gently's Holistic Detective Agency"
                       )
      )
)
 

[ryntak4536]

Thanks, and I'll definitely consider giving your suggestion a try.

However, I'm confused as to why my code isn't working properly.  For my intro into PHP, I've been working through video tutorials on Lynda.com.  I've tried to modify code that I saw on there (and which works for me) to do what I'm trying on my 'books' site.  And it looks to me like I've duplicated the code - for the most part - properly.  But it's not working, and I cannot for the life of me figure out what the difference is.  While I'm all for more efficient code, I'd like to understand what I've done differently than this other code, resulting in my different affect.  FYI, here's the code that I've tried to duplicate (the code below is what I tried to modify in the code above, the main exception is that I didn't need to hyperlink the pages - titles in my code - as has been done below):

function public_navigation($subject_array, $page_array){
		$output = "<ul class=\"subjects\">";
		$subject_set = find_all_subjects();
		while($subject = mysqli_fetch_assoc($subject_set)){			
			$output .= "<li";
			if($subject_array && $subject["id"]==$subject_array["id"]){
				$output .= " class=\"selected\"";
			} //END OF if($subject_array && $subject["id"]==$subject_array["id"])
			$output .= ">"; //CLOSE li tag
			$output .= "<a href=\"index.php?subject=";
			$output .= urlencode($subject["id"]);
			$output .= "\">"; 
			$output .= htmlentities($subject["menu_name"]);
			$output .= "</a>";
			
			//pages start here
			
			if ($subject_array["id"] == $subject["id"] || 
					$page_array["subject_id"] == $subject["id"]){
			
				$page_set = find_pages_for_subject($subject["id"]);
				$output .= "<ul class=\"pages\">";
				while($page = mysqli_fetch_assoc($page_set)){			
					$output .= "<li";
					if($page_array && $page["id"]==$page_array["id"]){
						$output .= " class=\"selected\"";
					}
					$output .= ">"; 
					$output .= "<a href=\"index.php?page=";
					$output .= urlencode($page["id"]);
					$output .= "\">";
					$output .= htmlentities($page["menu_name"]); 
					$output .= "</a></li>";
					}// END OF while($page = mysqli_fetch_assoc($page_set))
					$output .= "</ul>";
					mysqli_free_result($page_set);
			}// END OF if

				$output .= "</li>";// END OF subject li
		}// END OF while($subject = mysqli_fetch_assoc($subject_set))
		
		mysqli_free_result($subject_set);
		$output .= "</ul>";
		
		return $output;
	}

function find_all_subjects(){
		global $connection;
		
		$query = "SELECT * ";
		$query .= "FROM subjects ";
		//$query .= "WHERE visible = 1 ";
		$query .= "ORDER BY position ASC";
		// test if there was a query error
		$subject_set = mysqli_query($connection, $query);
		confirm_query($subject_set);
		return $subject_set;
	}

function find_pages_for_subject($subject_id){
		global $connection;
		
		$safe_subject_id = mysqli_real_escape_string($connection, $subject_id);
		
		$query = 	"SELECT * ";
		$query .= 	"FROM pages ";
		$query .= 	"WHERE visible = 1 ";
		$query .= 	"AND subject_id = {$safe_subject_id} ";
		$query .= 	"ORDER BY position ASC";
		$page_set = mysqli_query($connection, $query);
		confirm_query($page_set);
		return $page_set;
	}

[rwhite35]

Have you checked your error logs?  If the script isn't behaving the way you expect, then error reporting would be the first place to start.  Try locating your systems error logs at /var/log/apache/error.log or where ever httpd.conf has set that path.  

 

Alternatively, you can set error reporting locally by using the following:

error_log ("custom reports here",3,"/var/www/vhosts/httpdocs/yourdomain/devlog.txt") //note path specific to your environment

What this does is allow you to see how your script is behaving.  devlog.txt is a text file that gets created and contains the reported text ( ie custom reports here).  The '3' tell PHP to send error output to the text file (devlog.txt) instead of to system error reporting.  

 

Here is some of the best advice I have ever received, and I'll leave this thread at that:

 

"learn how to search error logs and generate custom error reporting, it will save you years"

[ryntak4536]

Thanks for the input.

I've checked the logs (apache error, and php error), and sadly, it's not showing there is any error in the code.  It's got something to do with the logic.  It's working, just not as I had intended, and not as is the code that I used as my template.

 

I'm trying the other way that you've suggested, using a foreach to iterate through the associative array.  However, I don't know how to echo just one value from the associative array.  So, it's kicking out the entirety of the table, rather than just the two fields I want displayed.

 

Ugh...