PHP and mysql

PHP
1

Howdy. I’m a total newbie to mysql and really don’t expect to use it much as I’m a designer with some developer experience (php, javascript). Anyway I have two tables I’m trying to get a count of records from based on a field in one table that doesn’t exist in another table. Here is the layout.
comments table has a comment_post_id field
posts table has an ID field
The data in each of these fields are the same in that they are related to posts a user submits. There will be one record in the posts table and possibly many records in the comments table.
In the posts table there is an post_author field that has no related data in the comments table. It is this field that I want to base my count of records on. Here is the query I’m using but it returns nothing. The syntax is based on WordPress databases.

$commnum = $wpdb->get_var("
SELECT COUNT (*)
FROM $wpdb->posts
JOIN $wpdb->comments
ON $wpdb->comments.comment_post_ID = posts.ID
WHERE = post.post_author = $user_ID");

Also I would have no idea how to echo results to the page
Any help would be greatly appreciated

2

Hi Tsalagi,

Give this a try:

$user_ID = 'xxxx'; //	This variable should be initialized with user id
$sql = "
SELECT 
	COUNT(*) AS cnt
FROM 
	$wpdb->posts p
	$wpdb->comments c
WHERE
	c.comment_post_ID = p.ID AND
	p.post_author='$user_ID'
GROUP BY
	p.ID
";
$cnt = $wpdb->get_var($wpdb->prepare($sql));

echo "The count is: ".$cnt;