Learning PHP and MySQL

Welcome to the O’Reilly Learning PHP & MySQL blog

2006-07-06T17:58:00-06:00

Here you will find code directly from the book, error listings, code fixes, and trivia about the computing world. Welcome. The authors of Learning PHP & MySQL Michele Davis and Jon Phillips will also be happy to answer any questions. So please use your real name when signing in! We look forward to a lively blog with lots of comments! Source code for each chapter is posted under the catagories on the right side bar.

Example 17-2 Function comments

2006-09-27T18:39:00-06:00

/** furniture stores locator.* Locate furniture stores in Minnesota, Wisconsion, Iowa and* Illinois based on their zip code.** @author michele davis mdavis@example.com* @param zipcode the zipcode to search for stores near* @return store the store id of the nearest store* @date 2005-12-21**/

Example 17-1 File comments

2006-09-27T18:36:01-06:00

/*** this file is about furniture stores.* this file is about furniture stores in Minnesota, Wisconsion, Iowa and Illinois.** Portions Copyright 2005-2006 (c) O’Reilly & Associates* The rest Copyright 2005 (c) from their respective authors** @version $Id: coding_standards.html,v 1.2 2005/12/19 24:49:50**/

Example 16-17 comment_form.tpl

2006-09-27T18:36:00-06:00

{section name=mysec loop=$comments} Title: Body:{$comments[mysec].body}{/section}

Example 16-16 modify_comment.php

2006-09-27T18:25:00-06:00

assign('blog_title',$blog_title);$smarty->display('header.tpl');// Check for valid loginif (!isset($_SESSION["username"])) {echo 'Please login.';exit;}// Connect to the database$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");if (DB::isError($connection)){die ("Could not connect to the database: ". DB::errorMessage($connection));}$stop = false;$post_id = $_REQUEST["post_id"];$title = htmlentities($_POST['title']);$body = htmlentities($_POST['body']);$action = htmlentities($_POST['action']);$category_id = htmlentities($_POST['category_id']);$user_id = $_SESSION["user_id"];$comment_id = htmlentities($_POST['comment_id']);if ($_GET['action'] == "delete" and !$stop) {$comment_id = $_GET["comment_id"];$query = "DELETE FROM `comments` WHERE `comment_id`='".$comment_id."' AND `user_id`='".$user_id."'";$result = $connection->query($query);if (DB::isError($result)){die("Could not query the database: ".$query." ".DB::errorMessage($result));}echo "Deleted successfully.";$stop = true;}// We're editing an entry, explicitly grab the id from the URLif ($_GET["comment_id"] and !$stop) {$query = "SELECT * FROM `comments` NATURAL JOIN `users`WHERE `comment_id`=".$_GET["comment_id"];$result = $connection->query($query);if (DB::isError($result)){die("Could not query the database: ".$query." ".DB::errorMessage($result));}while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {$comments[] = $result_row;}$post_id = $_GET["post_id"];$smarty->assign('action','edit');$smarty->assign('comments',$comments);$smarty->assign('post_id',$post_id);$smarty->display('comment_form.tpl');// Display the footer$smarty->display('footer.tpl');exit;}//The form was submitted, was itan add or an update?if ($_POST['submit'] and !$stop) {// Validate fields if ($title == ""){echo 'Title must not be null.';$found_error = true;$stop = true;}if ($body == ""){echo "Body must not be null.";$found_error = true;$stop = true;}// Validated OK lets hit the databaseif ($_POST['action'] == "add" AND !$stop) {$query = "INSERT INTO `comments` VALUES (NULL,'".$user_id."','".$post_id."','".$title."','".$body."', NULL)";$result = $connection->query($query);if (DB::isError($result)){die("Could not query the database: ".$query." ".DB::errorMessage($result));}echo "Posted successfully.";$stop = true;}if ($_POST['action']=="edit" and !$stop){$query = "UPDATE `comments` SET`title`='".$title."',`body`='".$body."'WHERE `comment_id`='".$comment_id."' AND `user_id`='".$user_id."'";$result = $connection->query($query);if (DB::isError($result)){die("Could not query the database: ".$query." ".DB::errorMessage($result));}echo 'Updated successfully.';$stop = true;}}if (!$stop){// Display blank form// Create an empty entry$post_id = $_GET["post_id"];$result_row = array('title'=>NULL,'body'=>NULL,'comment_id'=>NULL);$comments[] = $result_row;// Get the categories$smarty->assign('post_id',$post_id);$smarty->assign('comments',$comments);$smarty->assign('action','add');$smarty->display('comment_form.tpl');}if ($found_error) {// Assign old vals// Redisplay form$post_id = $_POST["post_id"];$result_row = array('title'=>"$title",'body'=>"$body",'comment_id'=>"$comment_id");$comments[] = $result_row;$smarty->assign('action',$action);$smarty->assign('post_id',$post_id);$smarty->assign('comments',$comments);$smarty->display('comment_form.tpl');}// Display the footer$smarty->display('footer.tpl');?>

Example 16-15 post_form.tpl

2006-09-27T18:19:00-06:00

Title: Body: {$posts[mysec].body}
Category:{html_options name="category_id" options=$categories selected=$posts[mysec].category_id}{/section}

Example 16-14 modify_posts.php

2006-09-27T17:55:00-06:00

assign('blog_title',$blog_title);    $smarty->display('header.tpl');    if    (!isset($_SESSION['username']))  {    echo  ("Please  login.");    exit();    }    //grab  submission  variables    $post_id=$_POST[post_id];    $title=htmlentities($_POST['title']);    $body=htmlentities($_POST['body']);    $action=htmlentities($_POST['action']);    $category_id=htmlentities($_POST['category_id']);    $user_id=$_SESSION["user_id"];    //conected to database    $connection  =  DB::connect(  "mysql://$db_username:$db_password@$db_host/$db_database"  );    if  (!$connection)    {    die  ("Could not connect to the database:
". DB::errorMessage());    };    if  ($_GET['action']=="delete"  and  !$stop)    {    $post_id=$_GET[post_id];    $query  =  "delete from posts where post_id='".$post_id."' anduser_id='".$user_id."'";    $result  =  $connection->query($query);    if (DB::isError($result))    {    die ("Could not  query the database:
".  $query.  "  ".DB::errorMessage($result));    };    echo  ("Deleted  successfully.
");    $stop="TRUE";    }    //we're editing an entry, explicitly grab the id from the URL    if  ($_GET[post_id]  AND  !$stop)  {    $query  =  "SELECT * FROM users NATURAL JOIN posts NATURAL JOIN categorieswhere  post_id  =  $_GET[post_id]";    $result  =  $connection->query($query);    if  (DB::isError($result))    {    die  ("Could not query the database:
". $query.  "  ".DB::errorMessage($result));    };    while  ($result_row  =  $result->fetchRow(DB_FETCHMODE_ASSOC))  {    $posts[]=$result_row;    }    $smarty->assign('action','edit');    $smarty->assign('posts',$posts);    //get  those  categories    $query  =  "SELECT  category_id, category FROM categories";    $smarty->assign('categories',$connection->getAssoc($query));    $smarty->display('post_form.tpl');    $stop="TRUE";    }    //The form was submitted, was it an add or an edit?    if  ($_POST['submit']  AND  !$stop)    {    //validate fields    if  ($title  ==  ""){    echo  ("Title must not be null.
");    $found_error=TRUE;    $stop="TRUE";    }    if  ($body  ==  ""){    echo  ("Body must not be null.
");    $found_error=TRUE;    $stop="TRUE";    }    //validated OK lets hit the databae    if  (  $_POST['action']=="add" AND !$stop)    {    $query = "insert into posts values  (NULL,"."'".$category_id."','".$user_id."','".$title."','".$body."',  NULL)";    $result  =  $connection->query($query);    if  (DB::isError($result))    {    die  ("Could not query the database:
". $query.  "  ".DB::errorMessage($result));    };    echo  ("Posted successfully.
");    $stop="TRUE";    }    if  ($_POST['action']=="edit" and !$stop)    {    //do nothing    $query  =  "update posts set category_id  ='".$category_id."',title  ='".$title."',body='".$body."' where post_id='".$post_id."'and  user_id='".$user_id."'";    //echo $query;    $result = $connection->query($query);    if  (DB::isError($result))    {    die  ("Could not query the database:
". $query.  "  ".DB::errorMessage($result));    };    echo ("Updated successfully.
");    $stop="TRUE";    }    }    if  (!$stop)    {    //display blank form    //create an empty entry    $result_row=array('title'=>NULL,'body'=>NULL);    $posts[]=$result_row;    //get the categories    $query = "SELECT category_id, category FROM categories";    $smarty->assign('categories',$connection->getAssoc($query));    $smarty->assign('posts',$posts);    $smarty->assign('action','add');    $smarty->display('post_form.tpl');    }    if  ($found_error)  {    //assign old vals    //redisplay form    $result_row=array('title'=>"$title",'body'=>"$body",'post_id'=>"$post_id");    $posts[]=$result_row;    $smarty->assign('action',$action);    $smarty->assign('posts',$posts);    $smarty->display('post_form.tpl');    }    //display the footer    $smarty->display('footer.tpl');?>

Example 16-13 view_post.tpl

2006-09-27T17:51:00-06:00

{section name=mysec loop=$posts}

{$posts[mysec].title}

{$posts[mysec].body}Posted by {$posts[mysec].first_name} {$posts[mysec].last_name}from the {$posts[mysec].category} category at{$posts[mysec].posted}.{if $posts[mysec].user_id == $owner_id}Edit ||Delete ||Add a comment{/if}{/section}{if $comment_count != "0"}

Comments

{section name=mysec2 loop=$comments}{$comments[mysec2].title}{$comments[mysec2].body}Posted by {$comments[mysec2].first_name} {$comments[mysec2].last_name}at {$comments[mysec2].posted}.{if $comments[mysec2].user_id == $owner_id}Edit ||Delete{/if}{/section}{/if}

Example 16-12 The view_post.php script displays and a summary of its comments

2006-09-27T17:46:00-06:00

Example 16-11 The posts.tpl template file defines how the postings appear on the page

2006-09-27T17:45:00-06:00

{section name=mysec loop=$posts}{$posts[mysec].title}by {$posts[mysec].first_name} {$posts[mysec].last_name}from the {$posts[mysec].category} category at {$posts[mysec].posted}.{/section}Click to add a posting.