Making a blog, question about categories

PHP
1

WARNING! PHP NOOB HERE.

So i have almost finished making a blog from scratch. I can login and logout from admin panel. And from the adminpanel I can add/edit/delete posts and categories.

I have tre mysql tables which are posts, users, categories.

My question is regarding echoing out the categories and posts.

On my right side of the blogpage i have the standard “latest posts” sidebar. The thing I don’t know how do do is how to echo out the posttitles under the correct category, right now i dont even echo out the categories only the titles like this:

function getTitles() {
	$query = mysql_query("SELECT * FROM posts ORDER BY `ID` DESC") or die(mysql_error());
	while($post = mysql_fetch_assoc($query)) {
		echo "<h3><a href=\\"singlePost.php?=" . $post['Title'] . "\\">" . $post['Title'] . "</a></h3>";
	}
}

WHat should i add to this to echo out the categories and get the correct titles under the correct category? I guess it has something to do with the Category_ID row i have in my post table?

2

2 things.

This query using select * when you only actually want the title is very wasteful, imagine you have a blog post with 10,000 chars :wink:


"SELECT * FROM posts ORDER BY `ID` DESC"

prefer:


"SELECT `Title` FROM posts ORDER BY `ID` DESC"

Second, you will have to do a table JOIN if you want to fetch all categories with their titles. [google]mysql table join example[/google]

Get the sql working, then come back if you cannot work out how to have PHP display them as:

Category 1

title 1
title 2
title 3

Category 2

… etc

Else, post the schemas for these two tables.

3

Thanks for the reply! I changed * to title in the query but now i get error:

( ! ) SCREAM: Error suppression ignored for
( ! ) Notice: Undefined index: ID in C:\\wamp\\www\\qssFINAL\\includes\\functions.php on line 14
Call Stack
#	Time	Memory	Function	Location
1	0.0006	671952	{main}( )	..\
ews.php:0
2	1.0102	704344	getTitles( )	..\
ews.php:8

Here are my two tables posts and categories btw:

CREATE TABLE IF NOT EXISTS `categories` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(100) NOT NULL,
  `Description` varchar(250) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

CREATE TABLE IF NOT EXISTS `posts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(150) NOT NULL,
  `Author` varchar(100) NOT NULL,
  `Content` text NOT NULL,
  `Category_ID` int(11) NOT NULL,
  `Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
4

So what is on line 14?

5 
echo "<h3><a href=\\"singlePost.php?ID=" . $post['ID'] . "\\">" . $post['Title'] . "</a></h3>";

And here is the whole function:

function getTitles() {
	$query = mysql_query("SELECT `Title` FROM posts ORDER BY `ID` DESC") or die(mysql_error());
	while($post = mysql_fetch_assoc($query)) {
		echo "&lt;h3&gt;&lt;a href=\\"singlePost.php?ID=" . $post['ID'] . "\\"&gt;" . $post['Title'] . "&lt;/a&gt;&lt;/h3&gt;";
	}
}
6

I suppose this is personal taste, but in my experience, a ‘recent posts’ list would simply list recent posts, while a category list would list categories. Most sidebars I’ve seen look like this.

Recent Articles

Some Post
Another Post
Older Post

Categories

Cat 1
Cat 2
Cat 3

The reason I like this layout is because in most blogs, assigning a category(ies) to a post is optional, and a given post may not have a category. The recent post list would really only need to display the most recent posts, regardless of category. And then, lastly, a short list of categories used to spawn a ‘search results’ style page, with categorically related posts on them. Of course, this is just my two cents. Remember, keep things simple, not just for the users sake, but your own.

7

As the error states; the index you’re using to access an element in the $post array is not set. This is occurring because you aren’t selecting the ID column in the data being fetched; only the Title column.

8

Its something like that i want to create, i was a bit unclear in my threadstart. The thing is I dont understand how to make the correct posts show under(when clicking on) the correct category.

9

Could you just not select all of the posts pertaining to a certain category using the WHERE clause in SQL? It’s hard to give you any sort of definitive answer without seeing more about your database set-up.

10

Well, you altered your getTitles() function from your original posting, you now reference the ID as well as the title.

So now you will need to explicitly select ID, Title from the table.


mysql_query("SELECT `ID`, `Title` FROM posts ORDER BY `ID` DESC");

If you have not understood this fundamental premise then you really need to go and read some tutorials on the basics of SQL, you should do that next. :wink:

Any decent tutorial will also cover how to make a JOIN, where you join data from two tables, and then select what you want from that join.

11

You should test your join by inputting it into your database with whatever db management tool you use, eg PHPMyAdmin is popular.

Here is an untested attempt to grab the categories title with its posts.


SELECT c.Title as category_title, p.Title as post_title, p.ID as post_id
FROM categories as c 
LEFT JOIN posts as p
ON c.ID = p.Category_ID
GROUP BY c.Title

I’ve used aliases in this statement categories as c as a) a means of shorthand and b) because both tables contain fields called ID and Title, so you have to tell your DB which one you want to use - otherwise it will throw an error.

ps what I have not dealt with here is if there is a Category which does not contain any posts yet, but looking at your auto-increment numbers it seems you only have some test data in there. In readiness for your eventual looping through these results, you should make sure that at least 1 of your categories has more that one single post.

12

I don’t mean to make things more complex, but you should probably have a ‘published’ bit in the ‘posts’ table to separate out those posts that are still in draft state. Just add a ‘where p.draft = 0’ to your SQL.