posts.inc.php

<?php

//checks if the given post id is in the table
function valid_pid($pid){
    $pid = (int)$pid;

    $total = mysql_query("SELECT COUNT(`post_id`) FROM `posts` WHERE `post_id` = '{$pid}'");
    $total = mysql_result($total, 0);

    echo mysql_error();

    if ($total != 1){
        return false;
    }else{
        return true;
    }
}

//fetches a summary of all the posts
function get_posts(){
    $sql = "SELECT
                `posts`.`post_id` AS `id`,
                `posts`.`post_body` AS `preview`,
                `posts`.`post_user` AS `user`,
                DATE_FORMAT(`posts`.`post_date`, '%d/%m/%Y %H:%i') AS `date`,
                `comments`.`total_comments`,
                DATE_FORMAT(`comments`.`last_comment`, '%d/%m/%Y %H:%i:%s') AS `last_comment`
            FROM `posts`
            LEFT JOIN(
                SELECT
                    `post_id`,
                    COUNT(`comment_id`) AS `total_comments`,
                    MAX(`comment_date`) AS `last_comment`
                FROM `comments`
                GROUP BY `post_id`
            ) AS `comments`
            ON `posts`.`post_id` = `comments`.`post_id`
            ORDER BY `posts`.`post_date` DESC";

    $posts = mysql_query($sql);

    $rows = array();
    while (($row = mysql_fetch_assoc($posts)) !== false){
        $rows[] = array(
            'id'                => $row['id'],
            'preview'           => $row['preview'],
            'user'              => $row['user'],
            'date'              => $row['date'],
            'total_comments'    => ($row['total_comments'] === null) ? 0 : $row['total_comments'],
            'last_comment'      => ($row['last_comment'] === null) ? 'No recent comments. Be the first to comment!' : $row['last_comment']
        );
    }

    return $rows;
}

//fetches a single post from the table
function get_post($pid){
    $pid = (int)$pid;

    $sql = "SELECT
                `post_body` AS `body`,
                `post_user` AS `user`,
                `post_date` AS `date`
            FROM `posts`
            WHERE `post_id` = {$pid}";

    $post = mysql_query($sql);
    $post = mysql_fetch_assoc($post);

    $post['comments'] = get_comments($pid);

    return $post;
}

// adds a new post entry
function add_post($name, $body){
    $name = mysql_real_escape_string(htmlentities($name));
    $body = mysql_real_escape_string(nl2br(htmlentities($body)));

    mysql_query("INSERT INTO `posts` (`post_user`, `post_body`, `post_date`) VALUES ('{$name}', '{$body}', NOW())");
}

?>