By Anarchtica on

I have a review site. Let's pretend it's a "toy" review site. A toy gets its review score from comments left by users. What I want to do is display the average review score for each individual toy.

I have a table called "content_type_review." Every review score for all the individual toys gets dumped into this table.

I have a query that averages every review in this table:

SELECT (SUM(field_review_value)/COUNT(field_review_value)) FROM content_type_review

The result given by this query is the average for EVERY review score for ALL of the toys.

How can I change this SQL query to only show the average review score for each individual toy? For example, if I'm on the "Toy Truck" page, only show the average review score for that "Toy Truck."

-Aaron-

⋅ Categories: Drupal 6.x

Comments

cog.rusty’s picture

cog.rusty commented

The query could use an additional "WHERE nid = something", if you have the nids in the table. The next question is how to give the current page's nid to the query. Add a test like:

if ( arg(0)=='node' && ctype_digit(arg(1)) ) {  // if we are on a node/{nid} page
  run_the_query_with( arg(1) );
}

The arg() function is unaffected by path aliases and can be used always.

Anarchtica’s picture

Anarchtica commented

How do I pass the current node id to my query?

My query is currently this:

$sql = "SELECT (SUM(field_review_value)/COUNT(field_review_value)) AS review_value FROM {content_type_review}";
$result = db_fetch_object(db_query($sql));
$review_value = $result->review_value;

$node_field[0]['value'] = $review_value;

I need the current node id passed to the query because I only want to average reviews associated with that particular node.

cog.rusty’s picture

cog.rusty commented

You want "to only show the average review score for each individual toy". As I said,
- If you have the toy node ID in your table, check if it is a full node page as I described and then add a "WHERE nid = arg(1)" to your query.
- If you don't have the toy node ID in your table, you can't.