Help with query

cmccomas

Is this possible:

select * from mytable Where blog_id=date

where date is a field in the table, mytable.

i'm trying to setup a blog. keep all of the info, including comments in the same table, is this a good idea or not?

also, would i need to use javascript or php, to check that the forms are valid.

thanks in advance!

cmccomas

One more thing.

How do I get the count the number of comments?

like below each days post have:

comments(#)

Thanks!

travelbuff

Hi cmccomas:

Is this possible:
select * from mytable Where blog_id=date

Only if blog_id is a date. Usually id fields are unique record identifiers, so you need to think through you DB design.

i'm trying to setup a blog. keep all of the info, including comments in the same table, is this a good idea or not?

Probably not! Do a google search on mysql database design tutorials and mysql database normalization tutorials. There are some good ones on this site as well.

I am not trying to discourage you, but a blog is a big undertaking for a newbie. I might suggest that you look at some of the open source blog scripts here http://www.hotscripts.com/PHP/Scripts_and_Programs/Blog/index.html and install one, then learn to customize it to meet your needs. Then you can try building one from scratch. That is how I have learned most of my php.

You could also follow a tutorial on writing a blog - http://www.hotscripts.com/PHP/Tips_and_Tutorials/Discussion_Boards/

Have fun.

cmccomas

Thanks,

I do have a good bit of knowledge in PHP. Have a nice news system I use on all the sites I do.

The only reason I thought about putting them together is:

1) I'll post daily, so the date will be the same as far as blog_content and blog_comments.

So, therefore it'd be easy to match up the blog, with the comments.

Any suggestions on database structure.

It's not going to be a complex, blog, just a simple way to add, edit, update and publish, for my personal site.

travelbuff

cmcomas:

you will have to be prepared for a blog entry to have multiple comments. I would suggest something like:

TABLE blog_entries
FIELDS entry_id (pk), entry_title, entry_body, date_added

TABLE blog_comments
FIELDS comment_id (fk), blog_entries_entry_id (fk), date_entered, entered_by, approved

that way you can have unlimited comments for a particular entry.

The way you would get number of comments would be a query like this:

$select_comments = MYSQL_QUERY(SELECT blog_comments.comment_id FROM blog_comments WHERE comments.comment_id = $entry_id) ;
$num_comments = MYSQL_NUM_ROWS($select_comments);
echo "There are $num_comments comments on this entry";

cmccomas

Thanks.

What'd be the best way to make sure the comments for each day match up and what not?

I see you have the fields:
blog_entries_entry_id
entry_id

How can I make sure they are both on the same number, always?

Also, what's the (pk) and (fk) for?

travelbuff

pk and fk means "primary key" and "foriegn key". You might want to read some tutorials on php and mysql for more info on them, but in short a primary key is a unique record identifier for that record. A foriegn key is the primary key for a record from another table that relates to that entry.

The way that you make sure they match up is when you insert the comment into the db you use the entry_id from the current page in the comments table as blog_entries_entry_id. If you are displaying the entry on a page, you will have already had to retrieve that entry from the db, so you will have the entry_id already defined. Did you look at any of those hotscripts tutorials on blogs? I think they would be really helpful for you.

cmccomas

would this be right?

SELECT * FROM blog,comments WHERE blog.blog_id = comments.blog_id;

Then how do I decide what to display? This?

<? 
// includes 
include("config.php");

// open database connection 
$connection = mysql_connect($host, $user, $pass) or die // generate and execute query 
$query = "SELECT * FROM blog_entries,blog_comments WHERE blog_entries.entry_id = blog_comments.blog_entries_entry_id;"; 
$result = mysql_query($query) or die ("Error in query: $query. " . ("Unable to connect!"); 

// select database 
mysql_select_db($db) or die ("Unable to select database!"); 

mysql_error()); 

// # of comments
$num_comments = MYSQL_NUM_ROWS($select_comments); 
echo "There are $num_comments comments on this entry";

// close database connection
mysql_close($connection);
?>

cmccomas

TABLE blog_comments
FIELDS comment_id (fk), blog_entries_entry_id (fk), date_entered, entered_by, approved

Two foreign keys here, or should comment_id be a primary key?

cmccomas

okay, here's what I got:

TABLE blog_entries
blog_entry_id (pk), blog_entry_title, blog_entry_body, blog_entry_date_added

TABLE blog_comments
comment_id (pk), blog_entry_id, comment_date_added, comment_name, comment_email, comment_body

I want the page to be:

Jan 19 (blog_entry_date_added)

My Blog (blog_entry_title)
This is my Blog (blog_entry_body)

comments(# of comments)

I'm having trouble with the query, getting the info. Can I run two queries in one? First one, get the info from blog_entries, the second get the total number of comments?