Learning PHP and MySQL

Wednesday, September 27, 2006

Example 16-1 The config.php script defines settings that are used throughout the site

<?php
//  put  full  path  to  Smarty.class.php 
require('/usr/share/php/Smarty/Smarty.class.php');
$smarty  =  new  Smarty();

$smarty->template_dir  =  '/home/www/htmlkb/smarty/templates';
$smarty->compile_dir  =  '/home/www/htmlkb/smarty/templates_c';
$smarty->cache_dir  =  '/home/www/htmlkb/smarty/cache';
$smarty->config_dir  =  '/home/www/htmlkb/smarty/configs';

$blog_title="Coffee  Talk  Blog";
?>

Posted by krautgrrl on 09/27 at 11:30 AM
Chapter 16 Code • (0) Comments • (3) Trackbacks • Permalink

Example 16-10 The posts.php script displays a listing of posts and their subjects

<?php
session_start();
require_once('config.php');
require_once('db_login.php');
require_once("DB.php");
//  Display  the  page  header
$smarty->assign('blog_title',$blog_title);
$smarty->display('header.tpl');
//  Check  for  valid  login
if  (!isset($_SESSION['username']))  {
echo  'Please  <a  href="login.php">login</a>.';
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:  <br  />".  DB::errorMessage($connection));
}
//  Query  the  posts  with  catagories  and  user  information
$query  =  "SELECT  *  FROM  `users`  NATURAL  JOIN  `posts`  NATURAL  JOIN  `categories`
ORDER  BY  `posted`  DESC";
//  Execute  the  database  query
$result  =  $connection->query($query);
if  (DB::isError($result)){
die("Could  not  query  the  database:  <br  />".$query."  ".DB::errorMessage($result));
}
//  Place  the  query  results  into  an  array
while  ($result_row  =  $result->fetchRow(DB_FETCHMODE_ASSOC))  {
$test[]  =  $result_row;
}
//  Send  the  data  to  the  template
$smarty->assign('posts',  $test);
//  Display  the  template  with  the  data  plugged  in
$smarty->display('posts.tpl');
//  Close  the  database  connection
$connection->disconnect();
//  Display  the  page  footer
$smarty->display('footer.tpl');
?>

Posted by krautgrrl on 09/27 at 11:43 AM
Chapter 16 Code • (27) Comments • (94) Trackbacks • Permalink

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

{section  name=mysec  loop=$posts}
<a  href="view_post.php?post_id={$posts[mysec].post_id}">{$posts[mysec].title}</a>
by  <b>{$posts[mysec].first_name}  {$posts[mysec].last_name}</b>
from  the  <b>{$posts[mysec].category}</b>  category  at  <b>{$posts[mysec].posted}</b>.
<br  />
{/section}
<br  />
Click to <a  href="modify_post.php?action=add">add</a> a posting.<br  />

Posted by krautgrrl on 09/27 at 11:45 AM
Chapter 16 Code • (0) Comments • (126) Trackbacks • Permalink

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

<?php 

session_start();

require_once('config.php');
require_once('db_login.php');
require_once("DB.php");

//  Display  the  header
$smarty->assign('blog_title',$blog_title);
$smarty->display('header.tpl');

//  Check  for  valid  login
if  (!isset($_SESSION["username"]))  {
echo  'Please  <a  href="login.php">login</a>.';
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:  <br  />".  DB::errorMessage($connection));
}

$post_id  =  $_GET["post_id"];

$query  =  "SELECT * FROM `users` NATURAL JOIN `posts` NATURAL JOIN `categories`
WHERE  `post_id`=$post_id";
$result  =  $connection->query($query);

if  (DB::isError($result)){
die("Could not query the database: <br  />".$query."  ".DB::errorMessage($result));
}

while  ($result_row  =  $result->fetchRow(DB_FETCHMODE_ASSOC))  {
$test[]=$result_row;
}

$smarty->assign('posts',$test);
$smarty->assign('owner_id',$_SESSION["user_id"]);
$query  =  "SELECT * FROM `users` NATURAL JOIN `comments` WHERE `post_id`=$post_id";
$result  =  $connection->query($query);

if  (DB::isError($result)){
die("Could not query the database:  <br  />".$query."  ".DB::errorMessage($result));
}

$comment_count  =  $result->numRows();

while  ($result_row  =  $result->fetchRow(DB_FETCHMODE_ASSOC))  {
$comments[]  =  $result_row;
}

$smarty->assign('posts',$test);
$smarty->assign('comments',$comments);
$smarty->assign('comment_count',$comment_count);

$smarty->display('view_post.tpl');

$connection->disconnect();

//  Display the footer
$smarty->display('footer.tpl');

?>

Posted by krautgrrl on 09/27 at 11:46 AM
Chapter 16 Code • (13) Comments • (65) Trackbacks • Permalink

Example 16-13 view_post.tpl

{section  name=mysec  loop=$posts}
<h2>{$posts[mysec].title}</h2>
{$posts[mysec].body}
<br  />
Posted  by  <b>{$posts[mysec].first_name}  {$posts[mysec].last_name}</b>
from  the  <b>{$posts[mysec].category}</b>  category  at
<b>{$posts[mysec].posted}</b>.<br  />
{if  $posts[mysec].user_id  ==  $owner_id}
<a  href="modify_post.php?post_id={$posts[mysec].post_id}&action=edit">Edit</a>  ||
<a  href="modify_post.php?post_id={$posts[mysec].post_id}&action=delete">Delete</a>  ||
<a  href="modify_comment.php?post_id={$posts[mysec].post_id}&action=add"
>Add  a  comment</a>
<br  />
{/if}
{/section}
{if  $comment_count  !=  "0"}
<h3>Comments</h3>
{section  name=mysec2  loop=$comments}
<hr  />
<b>{$comments[mysec2].title}</b>
<br  />
{$comments[mysec2].body}
<br  />
Posted  by  <b>{$comments[mysec2].first_name}  {$comments[mysec2].last_name}</b>
at  <b>{$comments[mysec2].posted}</b>.<br  />
{if  $comments[mysec2].user_id  ==  $owner_id}
<a  href="modify_comment.php?comment_id={$comments[mysec2].comment_id}&action=edit"
>Edit</a>  ||
<a  href="modify_comment.php?comment_id={$comments[mysec2].comment_id}&action=delete"
>Delete</a>
<br  />
{/if}
{/section}
{/if}

Posted by krautgrrl on 09/27 at 11:51 AM
Chapter 16 Code • (7) Comments • (226) Trackbacks • Permalink

Example 16-14 modify_posts.php

<?php
    include('db_login.php');
    require_once(  'DB.php'  );
    require_once(  'config.php'  );

    //check  for  valid  login
    session_start();

    //display  the  header
    $smarty->assign('blog_title',$blog_title);
    $smarty->display('header.tpl');

    if    (!isset($_SESSION['username']))  {
    echo  ("Please  <a  href='login.php'>login</a>.");
    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: <br>". DB::errorMessage());
    };
    if  ($_GET['action']=="delete"  and  !$stop)
    {
    $post_id=$_GET[post_id];
    $query  =  "delete from posts where post_id='".$post_id."' and
user_id='".$user_id."'";
    $result  =  $connection->query($query);
    if (DB::isError($result))
    {
    die ("Could not  query the database: <br>".  $query.  "  ".
DB::errorMessage($result));
    };
    echo  ("Deleted  successfully.<br>");
    $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 categories
where  post_id  =  $_GET[post_id]";
    $result  =  $connection->query($query);
    if  (DB::isError($result))
    {
    die  ("Could not query the database: <br>". $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.<br>");
    $found_error=TRUE;
    $stop="TRUE";
    }
    if  ($body  ==  ""){
    echo  ("Body must not be null.<br>");
    $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: <br>". $query.  "  ".
DB::errorMessage($result));
    };
    echo  ("Posted successfully.<br>");
    $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: <br>". $query.  "  ".
DB::errorMessage($result));
    };
    echo ("Updated successfully.<br>");
    $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');

?>

Posted by krautgrrl on 09/27 at 11:55 AM
Chapter 16 Code • (6) Comments • (12) Trackbacks • Permalink

Example 16-15 post_form.tpl

<form action="modify_post.php" method="POST">
<label>
Title:  <input type="text" name="title" value="{$posts[mysec].title}">
</label>
<br  /><br  />
<label>
Body:  <textarea name="body" cols="40" rows="4">{$posts[mysec].body}</textarea>
</label>
<input  type="hidden" name="action"  value="{$action}">
<input  type="hidden" name="post_id"  value="{$posts[mysec].post_id}"><br>
<label> Category:
{html_options  name="category_id" options=$categories selected=$posts[mysec].category_id}
</label>
<br  />
<input  type="submit" name="submit" value="Post"  />
</form>
{/section}

Posted by krautgrrl on 09/27 at 12:19 PM
Chapter 16 Code • (0) Comments • (1200) Trackbacks • Permalink

Example 16-16 modify_comment.php

<?php 

session_start();

require_once('config.php'); 
require_once('db_login.php'); 
require_once("DB.php");

//  Display  the  header
$smarty->assign('blog_title',$blog_title);
$smarty->display('header.tpl');

//  Check for valid login
if  (!isset($_SESSION["username"]))  {
echo 'Please <a  href="login.php">login</a>.';
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: <br  />". 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:  <br  />".$query."  ".DB::errorMessage($result));
}
echo "Deleted successfully.<br  />";
$stop = true;
}

// We're editing an entry, explicitly grab the id from the URL
if ($_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: <br  />".$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.<br  />';
$found_error  =  true;
$stop  =  true;
}
if  ($body  ==  ""){
echo  "Body must not be null.<br  />";
$found_error = true;
$stop = true;
}
// Validated OK lets hit the database
if  ($_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: <br  />".$query."  ".DB::errorMessage($result));
}
echo "Posted successfully.<br  />";
$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: <br  />".$query." ".DB::errorMessage($result));
}
echo 'Updated successfully.<br  />';
$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');

?>

Posted by krautgrrl on 09/27 at 12:25 PM
Chapter 16 Code • (3) Comments • (12) Trackbacks • Permalink

Example 16-17 comment_form.tpl

{section  name=mysec  loop=$comments}
<form  action="modify_comment.php"  method="post">
<label> Title:
<input  type="text"  name="title"  value="{$comments[mysec].title}"  />
</label>
<br  />
<br  />
<label> Body:
<textarea  name="body"  cols="40"  rows="4">{$comments[mysec].body}</textarea>
</label>
<input  type="hidden"  name="action"  value="{$action}"  />
<input  type="hidden"  name="post_id"  value="{$post_id}"  />
<input  type="hidden"  name="comment_id"  value="{$comments[mysec].comment_id}"  />
<br  /><br  />
<input  type="submit"  name="submit"  value="Post"  />
</form>
{/section}

Posted by krautgrrl on 09/27 at 12:36 PM
Chapter 16 Code • (9) Comments • (212) Trackbacks • Permalink

Example 16-2 The header.tpl file

<html>
<head>
<title>{$blog_title}</title>
</head>
<body>
<h1>Welcome  to  the  {$blog_title}</h1>

Posted by krautgrrl on 09/27 at 11:31 AM
Chapter 16 Code • (0) Comments • (0) Trackbacks • Permalink

Example 16-3 The footer.tpl file

<hr>
<a  href='posts.php'>Home</a>  ||  <a  href='logout.php'>Logout</a>
</head>
</body>
</html>

Posted by krautgrrl on 09/27 at 11:32 AM
Chapter 16 Code • (1) Comments • (8) Trackbacks • Permalink

Example 16-4 The login script, called login.php

<?php
//  Example  of  Auth_HTTP  the  also  returns  additional  information  about  the  user
require_once('config.php');
require_once('db_login.php');
require_once("Auth/HTTP.php");
//  We  use  the  same  connection  string  as  the  pear  DB  functions
$AuthOptions  =  array(
'dsn'=>"mysql://$db_username:$db_password@$db_host/$db_database",
'table'=>"users",  //  your  table  name
'usernamecol'=>"username",  //  the  table  username  column
'passwordcol'=>"password",  //  the  table  password  column
'cryptType'=>"md5",  //  password  encryption  type  in  your  db
'db_fields'=>"*"  //  enabling  fetch  for  other  db  columns
);
$authenticate  =  new  Auth_HTTP("DB",  $AuthOptions);
//  set  the  realm  name
$authenticate->setRealm('Member  Area');
//  authentication  failed  error  message
$authenticate->setCancelText('<h2>Access  Denied</h2>');
//  request  authentication
$authenticate->start();
//  compare  username  and  password  to  stored  values
if  ($authenticate->getAuth())  {
session_start();
$smarty->assign('blog_title',$blog_title);
$smarty->display('header.tpl');
//setup  session  variable
$SESSION['username']  =  $authenticate->username;
$SESSION['first_name']  =  $authenticate->getAuthData('first_name');
$SESSION['last_name']  =  $authenticate->getAuthData('last_name');
$SESSION['user_id']  =  $authenticate->getAuthData('user_id');
echo  "Login  successful.  Great  to  see  you  back  ";
echo  $authenticate->getAuthData('first_name');
echo  "  ";
echo  $authenticate->getAuthData('last_name').".<br  />";
$smarty->display('footer.tpl');
}
?>

Posted by krautgrrl on 09/27 at 11:33 AM
Chapter 16 Code • (0) Comments • (0) Trackbacks • Permalink

Example 16-5 SQL to create the posts table

CREATE  TABLE  `posts`  (
   `post_id`  int(11)  NOT  NULL  auto_increment,
   `category_id`  int(11)  NOT  NULL,
   `user_id`  int(11)  NOT  NULL,
   `title`  varchar(150)  NOT  NULL,
   `body`  text  NOT  NULL,
   `posted`  timestamp, PRIMARY  KEY    (`post_id`)
);

Posted by krautgrrl on 09/27 at 11:35 AM
Chapter 16 Code • (2) Comments • (6) Trackbacks • Permalink

Example 16-6 SQL to create the categories table

CREATE  TABLE  `categories`  (
    `category_id`  int(11)  NOT  NULL  auto_increment,
    `category`  varchar(150)  NOT  NULL, PRIMARY  KEY    (`category_id`)
);

Example 16-6 returns:


Query OK,  0 rows affected  (0.01  sec)

Posted by krautgrrl on 09/27 at 11:36 AM
Chapter 16 Code • (5) Comments • (5) Trackbacks • Permalink

Example 16-7 SQL to create the comments table

CREATE  TABLE  `comments`  (
    `comment_id`  int(11)  NOT  NULL  auto_increment,
    `user_id`  int(11)  NOT  NULL,
    `post_id`  int(11)  NOT  NULL,
    `title`  varchar(150)  NOT  NULL,
    `body`  text  NOT  NULL,
    `posted`  timestamp,
PRIMARY  KEY    (`comment_id`)
);

Posted by krautgrrl on 09/27 at 11:38 AM
Chapter 16 Code • (0) Comments • (1) Trackbacks • Permalink

Page 14 of 15 pages « First < 12 13 14 15 >

Statistics

This page has been viewed 181832 times
Page rendered in 0.5439 seconds
Total Entries: 224
Total Comments: 16
Total Trackbacks: 307338
Most Recent Entry: 09/27/2006 12:39 pm
Most Recent Comment on: 10/26/2007 10:00 am
Total Members: 2
Total Logged in members: 0
Total guests: 6
Total anonymous users: 0
Most Recent Visitor on: 11/20/2008 08:48 pm
The most visitors ever was 1103 on 11/20/2007 12:50 pm