Associated Tables + Converting Date Format

[Pennimus]

In looking to code a blog with comments, I've been told that the best way is to use associated tables, with one for the actual blog and one for the comments.

Firstly, is this in fact the best way, and if so can anybody point me to a good summary/discussion/tutorial on associated tables?

EDIT: Another question. I'm using this query to pull the blog entry out of the database, which is working fine:

$blog = mysql_query ("SELECT title, content, date FROM `blog` ORDER BY `date` DESC LIMIT 10") or die(mysql_error());

However the resulting date isn't particularly user friendly, reading 2006-02-02 or similar. I'd like it instead to say 'February 2nd 2006'

I attempted to use this tutorial (http://www.wsworkshop.com/php/php-mysql-weblog.html) which advised using this format to convert the date on the fly -

$query ="SELECT entrytitle, entrytext,";
$query.=" DATE_FORMAT(entrydate, '%M %d, %Y') AS date";
$query.=" FROM weblog ORDER BY entrydate DESC LIMIT 10";

However that resulted in an error - unexpected ';' on line 9 (which is the first semi colon in the above code) so evidently that's not a good way of approaching this.

I understand the actual date formats just not how to convert them as they come out of the database. Anyone got any ideas?

[raf]

about your db-design --> we need to have more info on your application (functionalitys, expected number of simultanious users etc) before we can realy say anything about that.

about the error for your dateconversion: if you get a php-error like that, the ';' will probably be on the previous line (line 8) + you can also write that code as

$query ="SELECT entrytitle, entrytext,
DATE_FORMAT(entrydate, '%M %d, %Y') AS date
FROM weblog ORDER BY entrydate DESC LIMIT 10";

[Pennimus]

Thanks, it will function like a standard blog. ie, each entry will be called into the home page and also have a seperate page where the entry will permanently reside along with any user comments.

I don't expect the user load to be high at all - even the most popular blogs that I read rarely get more than a few comments, and the website will be in a market not particular reknowned for a verbose consumer base either.

So, while I don't think it's possible to predict if those few users of the comments feature will ever post simultaneously I don't think it's very likely. Although I would like it to scale reasonably well.

[Pennimus]

I have attempted to use the code provided to format the date, but this simply results in a blank page.


$blog ="SELECT title, content,
DATE_FORMAT(date, '%M %d, %Y') AS date
FROM blog ORDER BY date DESC LIMIT 10";


I also tried...

$blog = mysql_query ("SELECT title, content
DATE_FORMAT(date, '%M %d, %Y') AS date
FROM 'blog' ORDER BY 'date' DESC LIMIT 10") or die(mysql_error());

Which gives me this error -

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_FORMAT(date, '%M %d, %Y') AS date FROM 'blog'

At this rate, I can see myself just writing the date manually as a VARCHAR field and ordering by ID instead :rolleyes:

On another note, what's the difference between specifying that it's a mysql_query and not?