Updating the number of comment in a DB

Question

I'm trying to update the comments column in one table (Entries), when a new row is added to another table (blog_comments). This code is inside of the PHP function that adds a comment to the DB:

    $count = mysql_query("SELECT COUNT(*) FROM blog_comments WHERE entry_title='$entry_title'");
$update = mysql_query("UPDATE Entries SET comments='$count' WHERE title='$entry_title'");

$entry_title is the name of the page. This code does nothing as of now; it doesn't change anything. I'm not sure what I'm doing wrong. Any ideas?

Answer 1

mysql_query() doesn't return the result directly. It returns a resource which you can use to get the result:

$result = mysql_query("SELECT COUNT(*) FROM blog_comments WHERE entry_title='$entry_title'");
$row = mysql_fetch_array($result);
$count = $row[0];
$update = mysql_query("UPDATE Entries SET comments='$count' WHERE title='$entry_title'");

Two things worth mentioning:

1. Using a title as a foreign ke isn't generally advisable. You should be using a blog post ID or something (imho); and

2. This represents a denormalization, which can be useful if you have a performance issue. Otherwise it can create problems if the numbers somehow get out of sync. You can write one query that in one round trip pulls back the blog posts and the number of comments (as a subquery).