PHP Reads MySQL Data on Page A, But NOT Page B

PHP
1

Sorry about the thread title, couldn’t think of anything else that was descriptive or helpful and the actual error message “Notice: undefined variable: blah blah blah” isn’t very helpful either as it’s so common!

Anyway…

I have a PHP page that simply displays some data from a MySQL DB. The PHP page takes a unique “slug” as a parameter in the URL to determine which data it should display. So, for example if the URL reads http://example.com/library/title/?slug=dracula then the PHP page displays the data corresponding to the Dracula book. If the URL reads http://example.com/library/title/?slug=a-christmas-carol then it display the data corresponding to the A Christmas Carol book.

Both pages work for the URL slug and displaying an image from a folder based on that slug, but only the Dracula page (Page A) displays the data from the MySQL DB. The A Christmas Carol (Page B) doesn’t display the data. Below are two screenshots showing the issue I’m having.

Dracula (Page A)

A Christmas Carol (Page B)

Here’s the code for index.php in the title folder:

<?php

include $_SERVER['DOCUMENT_ROOT'] . '/test.com/includes/dbconnection.inc.php';

if (isset($_GET['slug']))
{
	$slug = $_GET['slug'];
}
else
{
	header('Location: .');
	exit();
}

include $_SERVER['DOCUMENT_ROOT'] . '/test.com/includes/dbconnection.inc.php';

try
{
	$sql = "SELECT bookTitle, authorName, publisherName, genreSubjectName
	FROM books, authors, publishers, genresubject
	WHERE books.bookID = authors.authorInfoID
	AND books.bookID = publishers.publisherID
	AND books.bookID = genresubject.genreSubjectID
	AND bookSlugName = '$slug'";
	$result = $pdo->query($sql);
}
catch (PDOException $e)
{
	$error = 'Error fetching title records: ' . $e->getMessage();
	include 'error.html.php';
	exit();
}

while ($row = $result->fetch())
{
	$title = $row['bookTitle'];
	$author = $row['authorName'];
	$publisher = $row['publisherName'];
	$genresubject = $row['genreSubjectName'];
}

include 'title.html.php';
?>

And here’s the basic code from the title.html.php file in the title folder:

<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/test.com/includes/helpers.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="utf-8">
		<title><?php htmlout($title); ?> &ndash; Huffier</title>
	</head>
	
	<body>
		<article>
			<header>
				<h1><?php htmlout($title); ?></h1>
			</header>

			<div id="content">
				<h2>Title Information</h2>

				<div id="title-info-details">
					<img src="../../images/covers/<?php htmlout($slug); ?>.png" alt="<?php htmlout($title); ?>" id="title-cover"/>

					<ul id="title-info">
						<li><strong>Author:</strong> <?php htmlout($author); ?></li>
						<li><strong>Publisher:</strong> <?php htmlout($publisher); ?></li>
						<li><strong>Pages:</strong> </li>
						<li><strong>Genre / Subject:</strong> <?php htmlout($genresubject); ?></li>
						<li><strong>Rating:</strong> </li>
						<li><strong>ISBN:</strong> </li>
						<li><strong>Price:</strong> &pound;</li>
					</ul>
				</div>

				<div id="title-info-ctas">
					<div class="title-cta">
						<p class="top"><a href="../read/?slug=<?php htmlout($slug); ?>" class="title-cta-link">Read On-Demand</a></p>
					</div>

					<div class="title-cta">
						<p><a href="../buy/?slug=<?php htmlout($slug); ?>" class="title-cta-link">Buy eBook Bundle</a></p>
					</div>
				</div>

				<div id="title-desc">
					<h2>Title Description</h2>

					<p>eBook description.</p>
				</div>
			</div>
		</article>
	</body>
</html>

I’m really stumped, honestly, as to why it works perfectly fine for the Dracula page but not for the Christmas Carol page. I’m sure it’s a problem with the PHP code I’ve used. I initially had it displaying as a foreach loop with an array and that didn’t work so I changed it to a while loop. Still doesn’t work properly.

Could anyone point me in the right direction of what I should be searching for on Google? Or at best provide a solution for me with an explanation as to why what I’ve currently got doesn’t work in this situation? Very much appreciated. Been stumped on it for about a week now trying to figure it out…I know it’s going to be something easy to fix…For somebody who knows PHP, unlike me. This is really my first proper PHP & MySQL project.

2

In case of page B the problem is that there is no slug ‘a-christmas-carol’ in the database and 0 records are returned from your query. As a result the assignments in the while loop never execute so the variables $title, $author, $publisher and $genresubject are undefined, hence the notice errors. This is a standard example of a “page not found” error and you should prepare your code to handle that because you can receive all kinds of weird data in the slug parameter when your page is live. Simply make some code that checks if the db result is empty and if it is do something about it, for example display an error page.

The other question is why doesn’t the slug exist? Maybe a typo somewhere? Wrong link? This one you have to investigate yourself :slight_smile:

3

Hey,

Thanks for your quick reply Lemon Juice!

The strange thing is…The slug ‘a-christmas-carol’ does exist in the database. If it didn’t then surely the eBook cover wouldn’t show up as it does? The slug is definitely correct in both the database and the page / URL.

And of course you’re right regarding the notice errors as a standard example of a “page not found”. I’ll have to add in code for that later - Thanks for the heads-up! I likely wouldn’t have noticed such a glaring error in the functionality of it!

Could you suggest another reason why it doesn’t work?

4

If the book exists then the problem might be lack of related records in the other tables you are joining in your query. You are doing an INNER JOIN on tables authors, publishers, genresubject - if there is no corresponding row for the given bookID in any of those tables you will get an empty result.

BTW, while the query is technically correct it looks suspicious because of the names of the IDs - for example you are joining bookID with publisherID, if publisherID is a separate ID for the publishers (which normally I would think it is) then this will never work because you are comparing two unrelated IDs. The book table should have publisherID and then you would join it like this:

books.publisherID = publishers.publisherID

The same applies to the other tables.

5

There is a corresponding row - bookID is the primary key in the books table and publisherID is the primary key in the publishers table. publishersID is a foreign key in the books table, as are a number of others which you can see in the screenshot I posted in my previous post.

The funny thing is…That tiny bit of code you just posted, along with having the same for the others in the code statement…Fixed the issue I was having!

Why didn’t you just post that snippet earlier on? :stuck_out_tongue: Thanks for the help Lemon Juice! I knew it was going to be something as small and easy to fix as simply changing a word or two. I just didn’t know which and where!

Problem solved!

6

Okay, glad it works for you but if you left the other JOIN comparisons without changing then this may still be not right and the fact it works now is just a pure coinsidence of unrelated IDs having the same value. So you SQL should look like this:


    $sql = "SELECT bookTitle, authorName, publisherName, genreSubjectName 
    FROM books, authors, publishers, genresubject 
    WHERE books.authorInfoID = authors.authorInfoID
    AND books.publisherID = publishers.publisherID
    AND books.genreSubjectID = genresubject.genreSubjectID
    AND bookSlugName = '$slug'";

And also - I don’t know if it’s just a simplification in your code - if you insert $slug like this without escaping it you leave a security hole in your script! :slight_smile:

7

@AndrewCooper;

Whenever I get SQL errors if it is not obvious then I add these lines to see what is happening:



    $sql = "SELECT bookTitle, authorName, publisherName, genreSubjectName  
    FROM books, authors, publishers, genresubject  
    WHERE books.bookID = authors.authorInfoID 
    AND books.bookID = publishers.publisherID 
    AND books.bookID = genresubject.genreSubjectID 
    AND bookSlugName = '$slug'"  ; 

    $result = $pdo->query($sql); 

// added these lines:
    echo $sql;

    echo '<pre>'; // formats and makes easier debugging
       var_dump( $result );    
    echo '</pre>'; 
    die;