database design help: creating subject categories

[ultigma]

Hello, I'm new to php and i'm trying to figure out the best way to structure a database about animals.

 

Basically i have a blog database called 'posts' with

 int   |   text     |     text        |       int

-------------------------------------------------

' id ' |  ' title '   | ' content '  |  ' category '

 

and what I want to happen is that depending on the category show only them posts. For example would this be the right way to do it;

 

1 - Dogs

2 - Cats

3 - Birds

4 - Fish

 

"SELECT * FROM posts WHERE category = 1" to display everything for dogs

 

OR

 

Should I change category to varchar and explicitly call them ' Dog ' - ' Cat ' - ' Bird '

 

I would prefer the latter as having it explicitly called makes more sense to me.

 

Thanks for anyone that can help =)

[requinix]

The first one with numbers is correct. You should actually have another table for the categories too

id | category
---+---------
 1 | Dogs
 2 | Cats
 3 | Birds
 4 | Fish

so if you really wanted to search by name it would be

SELECT p.* FROM posts p JOIN categories c ON p.category = c.id WHERE c.category = "Dogs"

But most of this should be happening automatically: you show all the categories in a list, their links go to some page using the category ID, and that page shows whatever posts are in that category. You don't specify "Dogs" or "Birds" anywhere but have the code output it.

 

Example:

echo "<ul>";
foreach (/* SELECT id, category FROM categories ORDER BY category */ as $row) {
	echo "<li><a href='/posts.php?category=", $row["id"], "'>", $row["category"], "</a></li>\n";
}
echo "</ul>";

// links to the posts under the category
$category = (int)$_GET["category"];

echo "<ul>";
foreach (/* SELECT id, title FROM posts WHERE category = $category */ as $row) {
	echo "<li><a href='/post.php?id=", $row["id"], "'>", $row["title"], "</a></li>\n";
}
echo "</ul>";

[ultigma]

Ah ok, in that case i'll create a category table and reference that in the 'posts' > 'category' table.

 

I think I need a bit of training on relational databases and get my head round how they are best used

 

Thanks again =)