Question about 4th Edition Build your own database driven website using PHP & MYSQL

Hi,

I’m new to the forum and to php and mysql.
I’m currently working through the 4th edition of “Build your own database driven web site using PHP & MYSQL”.

I’ve got to the 7th chapter “A Content Management System” and the index controller code allows you to set the categories for the jokes.
The issue I’m having is that it appears you can set no categories and save the joke, however if you try to edit this joke the code errors, which it doesn’t do if you have set a least one category.

The question is, is this a known error in the code or have i written my code wrong:


<?php 
 	include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/magicquotes.inc.php';
	
	if (isset($_GET['add']))
	{
		$pagetitle = "New Joke" ;
		$action = "addform";
		$text = "";
		$authorid= "";
		$id= "";
		$button= "Add joke";
	
	
	include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
	
	//Build the list of authors
	
	$sql="SELECT id, name FROM author";
	$result= mysqli_query($link, $sql);
	
	if(!$result)
	{
		$error="Error fetching list of authors.";
		include 'error.html.php';
		exit();	
	}
	
	while($row = mysqli_fetch_array($result))
	{
		$authors[] = array('id' => $row['id'], 'name' => $row['name']);
	}
	
	//Build the list of categories
	
	$sql="SELECT id, name FROM category";
	$result= mysqli_query($link, $sql);
	
	if(!$result)
	{
		$error="Error fetching list of categories.";
		include 'error.html.php';
		exit();	
	}
	
	while($row = mysqli_fetch_array($result))
	{
		$categories[] = array('id' => $row['id'], 'name' => $row['name'], 'selected' => FALSE);
	}
	
	include 'form.html.php';
	exit();
	}
		
if (isset($_POST['action'])&& $_POST['action'] == 'Edit')
	{
		include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
		
		$id = mysqli_real_escape_string($link, $_POST['id']);
		$sql = "SELECT id, joketext, authorid FROM joke WHERE id ='$id'";
		$result = mysqli_query($link, $sql);
		
		if(!$result)
		{
			$error="Error fetching joke details.";
			include 'error.html.php';
			exit();	
		}
		
		$row = mysqli_fetch_array($result);
		
		$pagetitle = "Edit Joke" ;
		$action = "editform";
		$text = $row['joketext'];
		$authorid= $row['authorid'];
		$id= $row['id'];;
		$button= "Update joke";
	
		//Build the list of authors
		$sql = "SELECT id, name FROM author";
		$result = mysqli_query($link, $sql);
		
		if(!$result)
		{
			$error="Error fetching list of authors.";
			include 'error.html.php';
			exit();	
		}
		
		while($row = mysqli_fetch_array($result))
		{
			$authors[] = array('id' => $row['id'], 'name' => $row['name']);
		}
		
		//Get List of categories containing this joke
		$sql = "SELECT categoryid FROM jokecategory WHERE jokeid =$id";
		$result = mysqli_query($link, $sql);
		if(!$result)
		{
			$error="Error fetching list of selected categories.";
			include 'error.html.php';
			exit();	
		}
		
		while($row = mysqli_fetch_array($result))
		{
			$selectedCategories[] = $row['categoryid'];
		}
		
		//Build the list of all categories
		$sql = "SELECT id, name FROM category";
		$result = mysqli_query($link, $sql);
		if(!$result)
		{
			$error="Error fetching list of categories.";
			include 'error.html.php';
			exit();	
		}
		
		while($row = mysqli_fetch_array($result))
		{
			$categories[] = array( 'id' => $row['id'], 'name' => $row['name'], 'selected' => in_array($row['id'], $selectedCategories));
		}
		
		include 'form.html.php';
		exit();
	}


	if(isset($_GET['addform']))
	{
		include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
		
		$text = mysqli_real_escape_string($link, $_POST['text']);
		$author = mysqli_real_escape_string($link, $_POST['author']);
		
		if($author == '')
		{
			$error="You must choose an author for this joke. Click &lsquo;back &raquo; and try again.";
			include 'error.html.php';
			exit();	
		}
		
		$sql = "INSERT INTO joke SET joketext = '$text', jokedate = CURDATE(), authorid = '$author'";
		if(!mysqli_query($link, $sql))
		{
			$error="Error adding submitted joke";
			include 'error.html.php';
			exit();	
		}
		
		$jokeid = mysqli_insert_id($link);
		
		if(isset($_POST['categories']))
		{
			foreach($_POST['categories'] as $category)
			{
				$categoryid = mysqli_real_escape_string($link, $category);
				$sql = "INSERT INTO jokecategory SET jokeid ='$jokeid', categoryid = '$categoryid'";
				if(!mysqli_query($link, $sql))
				{
					$error="Error inserting joke into selected category";
					include 'error.html.php';
					exit();	
				}
			}
		}
		
		header('Location: .');
		exit();
	}

	
	
	if(isset($_GET['editform']))
	{
		include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
		$text = mysqli_real_escape_string($link, $_POST['text']);
		$author = mysqli_real_escape_string($link, $_POST['author']);
		$id = mysqli_real_escape_string($link, $_POST['id']);
		
		if($author == '')
		{
			$error="You must choose an author for this joke. Click &lsquo;back &raquo; and try again.";
			include 'error.html.php';
			exit();	
		}
		
		$sql = "UPDATE joke SET joketext = '$text', authorid = '$author' WHERE id = '$id'";
		
		if(!mysqli_query($link, $sql))
		{
			$error="Error updating submitted joke" . mysqli_error();
			include 'error.html.php';
			exit();	
		}
		
		$sql = "DELETE FROM jokecategory WHERE jokeid = '$id'";
		
		if(!mysqli_query($link, $sql))
		{
			$error="Error removing obsolete joke category entries.";
			include 'error.html.php';
			exit();	
		}
		
		if(isset($_POST['categories']))
		{
			foreach($_POST['categories'] as $category)
			{
				$categoryid = mysqli_real_escape_string($link, $category);
				$sql = "INSERT INTO jokecategory SET jokeid ='$id', categoryid = '$categoryid'";
				if(!mysqli_query($link, $sql))
				{
					$error="Error inserting joke into selected category";
					include 'error.html.php';
					exit();	
				}
			}
		}
		
		header('Location: .');
		exit();
		
	}
	
	if(isset($_POST['action']) && $_POST['action'])
	{
		include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
		$id = mysqli_real_escape_string($link, $_POST['id']);
		
		//Delete category assignment for this joke
		$sql = "DELETE FROM jokecategory WHERE jokeid='$id'";
		
		if(!mysqli_query($link, $sql))
		{
			$error="Error removing joke from categories.";
			include 'error.html.php';
			exit();	
		}
		//Delete the joke
		$sql = "DELETE FROM joke WHERE id='$id'";
		
		if(!mysqli_query($link, $sql))
		{
			$error="Error deleting joke.";
			include 'error.html.php';
			exit();	
		}
		header('Location: .');
		exit();
	}



	if(isset($_GET['action']) && $_GET['action'] == 'search')
	{
		include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
		
		//the basic SELECT statement
		$select ='SELECT id, joketext';
		$from =' FROM joke';
		$where =' WHERE TRUE';
	
	
	$authorid = mysqli_real_escape_string($link, $_GET['author']);
	
	if($authorid != '')// An author is selected
	{
		$where .=" AND authorid ='$authorid'";
	}

	$categoryid = mysqli_real_escape_string($link, $_GET['category']);
	
	if($categoryid != '')// An category is selected
	{
		$from .=' INNER JOIN jokecategory ON id = jokeid';
		$where .=" AND categoryid ='$categoryid'";
	}
	
	$text = mysqli_real_escape_string($link, $_GET['text']);
	
	if($text != '')// Some search text was specified
	{
		$where .=" AND joketext LIKE '%$text%'";
	}
	
	
	$result = mysqli_query($link, $select . $from . $where);
	if(!$result)
	{
		$error= 'Error fetching jokes';
		include 'error.html.php';
		exit();	
	}
	
	while($row = mysqli_fetch_array($result))
	{
		$jokes[] = array('id' => $row['id'], 'text' => $row['joketext']);
	}
	
	include 'jokes.html.php';
	exit();	

}

	 //Display search form
	include_once $_SERVER['DOCUMENT_ROOT'] . 'php/chapter7/includes/db.inc.php';
	$result = mysqli_query($link, 'SELECT id, name FROM author');
	
	while($row = mysqli_fetch_array($result))
	{
		$authors[] = array('id' => $row['id'], 'name' => $row['name']);
	}
	
	if(!$result)
	{
		$error= 'Error fetching authors from database!';
		include 'erroor.html.php';
		exit();	
	}
	
	
	$result = mysqli_query($link, 'SELECT id, name FROM category');
	
	while($row = mysqli_fetch_array($result))
	{
		$categories[] = array('id' => $row['id'], 'name' => $row['name']);
	}
	if(!$result)
	{
		$error= 'Error fetching categories from database!';
		include 'erroor.html.php';
		exit();	
	}
	
	include 'searchform.html.php';
	
?>



Also if this is an error in the book, does anyone know the code to remedy this so it doesn’t fail when no category is set for the joke?

Any help would be really appreciated.

Thanks

Dan

Welcome to the forums :slight_smile:

If you FORCE the usage of category in the first instance of a joke, will it help?
I know it will.

Tweak the form itself, add “mandatory field” on < select category > option input field and you will be all set.

Cheers,
Greg

What error are you getting? Can you start with that?

The formatting you used for the sql is like what you would use for UPDATE. For INSERT it would be

$sql = "INSERT INTO joke (joketext,jokedate,authorid)
  VALUES('$text',CURDATE(),'$author')";

I’ve attached the error, as I said, the code was taken directly out of the book. What I haven’t done is download the code archive and ran that and seen if that throws up the error/s.

Add this line just before the bit where you fill the categories array.

$categories=array();

It sets up $categories as an empty array so that any function that expects to be given an array will see an empty array if nothing was returned by a SELECT query

Same with $selectedCategories

In short, you are seeing notices and warnings because you are attempting to use variables that are technically not set yet. They aren’t errors.

Hi Guys

Thanks greg76 - i wanted to do it via the controller code and I was secretly hoping I’d missed something in the books code.
Drummin - The INSERT code is written how the book describes ( although in earlier chapters it does discuss the different way but says its a preference thing.)

SpacePheonix/cpradio - the combination of initializing both the arrays worked a treat. thanks guys!

Dan

Chances are the book was written when notices and warnings were not the default for logging. That has since changed because 1) it makes for better development, and 2) it quickly indicates where bugs may be present.

So don’t be surprised if you see that in future chapters too.