<?php
/********
Plugin Name: CS_Posts by Category
Plugin URI: http://kinrowan.net/development/
Author: cori schlegel
Author URI: http://kinrowan.net/
Version: 0.1
Description: A couple of functions to use in generating lists of posts within categories, suitable for recursive category->post tree generation
Date: 2005-10-18
WP Version: 1.5-Strayhorn
************/
/* --------------------------------------------------------------------

Usage notes, revision info, etc.: forthcoming
ToDo:	add recursion to deal with deeper levels of heirarchy

-------------------------------------------------------------------- */

function cs_get_categories($category = "") {
//get the categories from the WP database as a mysql result resource - accepts an optional cat_ID

	global $wpdb;

	$category_sql = "SELECT c.cat_ID, c.cat_name, c.category_nicename, c.category_description, c.category_parent ";
	$category_sql .= "FROM $wpdb->categories c ";
/*	if ("" == $category) {
		$category_sql .= "INNER join $wpdb->post2cat p ON c.cat_ID = p.category_id ";
	}*/
	if ("" != $category) {
		$category_sql .= "WHERE cat_ID = $category ";
	}
	$category_sql .= "ORDER BY cat_name ";

	$categories = mysql_query($category_sql);
	return $categories;

}

function cs_get_category_posts($cat) {
//get the posts of a given category from the WP database as a mysql result resource

	global $wpdb;

	$get_cat_posts_sql = "SELECT p.ID, p.post_author, p.post_date, p.post_content, p.post_title, p.post_category, p.post_status, ";
	$get_cat_posts_sql .= "c.post_id, c.category_id ";
	$get_cat_posts_sql .= "FROM $wpdb->posts p INNER JOIN $wpdb->post2cat c ";
	$get_cat_posts_sql .= "WHERE p.ID = c.post_ID AND c.category_id = '$cat' ";
	$get_cat_posts_sql .= "AND p.post_status = 'publish' ";
	$get_cat_posts_sql .= "ORDER BY p.post_date DESC";

	$get_cat_posts_result = mysql_query($get_cat_posts_sql);
	return $get_cat_posts_result;

}

function cs_get_cat_children($cat) {
//get the child categories of a given category as a mysql result resource

	global $wpdb;

	$get_cat_children_sql = "SELECT cat_ID, cat_name from $wpdb->categories WHERE category_parent = $cat ORDER BY cat_name";

	$cat_children = mysql_query($get_cat_children_sql);
	return $cat_children;

}

function cs_get_admin_name() {
//get the name of the person with the admin login

	global $wpdb;

	$admin_name_result = mysql_query("SELECT user_firstname, user_lastname FROM $wpdb->users WHERE user_login = 'admin'");

	while ($admin_name = mysql_fetch_assoc($admin_name_result)) {
		$fname = $admin_name['user_firstname'];
		$lname = $admin_name['user_lastname'];
		}

	return $fname . ' ' . $lname;

}

function cs_cat_has_posts($category) {
//checks to see if the passed-in category id has any posts associated with it

	global $wpdb;
	$bool_return = false;

	$rows = mysql_query("SELECT * FROM $wpdb->post2cat WHERE category_id = $category");
	$row_qty += mysql_num_rows($rows);

	$children_result = cs_get_cat_children($category);
	while ($child = mysql_fetch_assoc($children_result)) {
		$child_ID = $child['cat_ID'];
		$child_rows = mysql_query("SELECT * FROM $wpdb->post2cat WHERE category_id = $child_ID");
		$row_qty += mysql_num_rows($child_rows);
		}

	if (0 < $row_qty) {
		$bool_return = true;
	}

	return $bool_return;

}

function cs_list_child_categories() {
//gets a list of all the categories with parents

	global $wpdb;

	$cats_with_parents = mysql_query("SELECT * FROM $wpdb->categories WHERE 0 != category_parent");
	while ($child_cat = mysql_fetch_assoc($cats_with_parents)) {
		$child_cat_ids .= $child_cat['cat_ID'] . ', ';
	}

	return $child_cat_ids;

}

?>