Accessing Joomla DB and retrieving AVG of value column

Hello,
I'm trying to retrieve the average value for ratings within a Joomla DB table. This is not the standard Joomla article rating, but a rating extension for a component (YooTheme Zoo).

Using Joomla's database object I need to retrieve the average rating value, for all records within a table

I have attached the code that I've used so far, without success.
I have also attached a screenshot of the DB table so you can see the structure.

If possible it would be great to see the SQL Query as well as the code used echo/print the result to a page.

Thanks!

function avgRating() {
	$db   =& JFactory::getDBO();

	  $sql = "SELECT AVG(value) FROM #__zoo_core_rating WHERE item_id = 4";
	  $db->setQuery( $sql );
	  if (!$result = $db->query()) {
	    echo $db->stderr();
	    return false;
	  }

	  return $db->getNumRows( $result );

	}

	echo 'The average rating is ' . avgRating();

Open in new window

snapshot--17.png
LVL 8
Jesse MatlockUX EngineerAsked:
Who is Participating?
 
leakim971PluritechnicianCommented:
So call the function and pass the id  :

echo 'The average rating is ' . avgRating($this->item->id);

And the function :

function avgRating($id) {
        $db   =& JFactory::getDBO();
        $sql = "SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id = " . $id;
...

Open in new window

0
 
leakim971PluritechnicianCommented:
Hello cloud9manager,

Do you call avgRating from a non empty (no tags (html, head, body,...)
Do you want avg for all item_id on the same page ?

Regards.
0
 
Jesse MatlockUX EngineerAuthor Commented:
@leakim971,
Hello,

Thanks for the reply.. to clarify, I'm using this in a template file and will use $item_id to select that current items rating values from the DB. Right now, I just hard coded the item_id to get a solid value in there and eliminate a potential error, etc..

So, the sql code should retrieve the avg value of the ratings for the item_id which is assigned when the page loads.. make sense :D ?

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
leakim971PluritechnicianCommented:
try (line 4) :


$sql = "SELECT item_id, AVG(value) FROM #__zoo_core_rating WHERE item_id = 4 GROUP BY item_id";

Open in new window

0
 
Jesse MatlockUX EngineerAuthor Commented:
Excellent..
OK, I tried the code you provided for line 4, and like  this:

$sql = "SELECT item_id, AVG(value) FROM jos_zoo_core_rating WHERE item_id = 4 GROUP BY item_id";

but this returns '1', instead of the average of the 'values' for each row.

I dug a little into the base code that makes the rating extension work and this is the code they use within to call the avg rating..

      $query = 'SELECT AVG(value) AS rating, COUNT(id) AS votes'
                .' FROM #__zoo_core_rating'
                     .' WHERE element = '.$db->Quote($this->name)                  
                .' AND item_id = '.$this->_item->id
                .' GROUP BY item_id';



is there anything from this that can be gleaned and used?
Again, I'm doing my best to learn MySQL and have been reading non-stop about this.. trying to find a solid solution AND learn along the way..

greatly appreciate the help!
0
 
leakim971PluritechnicianCommented:
could you post resultS of :

(run this in phpmyadmin)

SELECT * FROM #__zoo_core_rating WHERE item_id = 4

Open in new window

0
 
Jesse MatlockUX EngineerAuthor Commented:
Of course.. i ran the snippet your provided (although i had to change the #__ to jos_ to use it in PHPMyAdmin) and the attached image was the result.
snapshot--18.png
0
 
leakim971PluritechnicianCommented:
http://docs.joomla.org/How_to_use_the_database_classes_in_your_script

$db->getNumRows( $result ) return number of result, in our case : 1

You need to use : $result = $db->loadResult()

function avgRating() {
   $db   =& JFactory::getDBO();
   $sql = "SELECT AVG(value) FROM #__zoo_core_rating WHERE item_id = 4";
   $db->setQuery( $sql );
   if(!$result = $db->query()) {
      echo $db->stderr();
      return false;
   }
   return $db->loadResult();
}
echo 'The average rating is ' . avgRating();

Open in new window

0
 
Jesse MatlockUX EngineerAuthor Commented:
Hello again,
Ah, I knew I was missing something - thank you.! The code works great and displays the correct rating for this item! :D

One question as to the format... as it returns '5.0000' for a value. I need to format this as a percentage, so this would be 100% (1-5) ... I tried using substr() to no avail.. what is your recommendation on being able to format this to a percentage ?

Thanks again!
0
 
Jesse MatlockUX EngineerAuthor Commented:
Scratch that! Corrected the syntax and we're rockin!

Thanks again for the help..
0
 
Jesse MatlockUX EngineerAuthor Commented:
OK.. one last question.. (honest, last one ;D)
I have tried this:
$sql = "SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id = .$this->item->id";

and this:
$sql = 'SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id = ".$this->item->id"';

Notice the variation on the quote styles, etc.. but I keep getting an error for $this being  undefined, however, when i echo $this->item->id; I get the correct ID for this item.. but I cannot get it to work within the SQL statement..

0
 
leakim971PluritechnicianCommented:
Try :


$myid = $this->item->id;
$sql = "SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id = " . $myid;

Open in new window

0
 
Jesse MatlockUX EngineerAuthor Commented:
I tried to assign $this->item->id to a variable as well and both your code and mine give this error:
Notice: Undefined variable: myid in /../../../../components/com_zoo/templates/editor-review/item.php on line 394
DB function failed with error number 1064
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 '' at line 1 SQL=SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id =

So, not sure why this cannot be used within the SQL statement as it echo's out on the page correctly..
Any ideas here?
0
 
leakim971PluritechnicianCommented:
check for the $

If you can, post your code.

0
 
Jesse MatlockUX EngineerAuthor Commented:
Good idea.. especially since these errors are normally format or syntax related.. but, I believe I checked it thoroughly.. here is the code I'm using,.. let me know what you see (and thanks for the fast reply!) :D


$id = $this->item->id;

function avgRating() {
   	$db   =& JFactory::getDBO();
	$sql = "SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id = " . $id;

   	$db->setQuery( $sql );

   	if(!$result = $db->query()) {
      	echo $db->stderr();
      	return false;
   	}
   	return $db->loadResult();
	}
	$percent = avgRating();
	$percent_user = substr(($percent)*10, 0, 4);
	
	echo $percent_user;

Open in new window

0
 
Jesse MatlockUX EngineerAuthor Commented:
odd thing is, when I change the 'echo' code at the bottom to this:
      echo $percent_user.'<br />';
      echo $id.'<br />';
      echo $this->item->id.'<br />';
I get:
0
5
5
So, I know that the function is NOT working (hence no percentage output, BUT the $id and $this->item->id are in fact outputing the correct data.

0
 
leakim971PluritechnicianCommented:
Put the var inside the function
I will not use $id as var name, I will choose another one (why not $myid ?)
function avgRating() {
        $db   =& JFactory::getDBO();
        $myid = $this->item->id;
        $sql = "SELECT AVG(value) FROM jos_zoo_core_rating WHERE item_id = " . $myid;

Open in new window

0
 
Jesse MatlockUX EngineerAuthor Commented:
ah, ok. in the function. Ok, tried it and get this error:
Fatal error: Using $this when not in object context in /../../....etc. etc..../editor-review/item.php on line 391
Good idea on the variable name, I simply used your code copy/pasted...

So, why is this still not wanting to use this variable?
0
 
Jesse MatlockUX EngineerAuthor Commented:
beautiful ! And, I'm learning as we go.. (I'm the design lead of a small dev team and can handle the front end just fine.. but, as I dive into more php and js, I'm getting to love what I do more and more.. and very excited about learning more)

Thank you again for the help.!
0
 
Jesse MatlockUX EngineerAuthor Commented:
Excellent help and fast to reply.. thanks again!
0
 
leakim971PluritechnicianCommented:
You're welcome! Have fun!
0
 
Jesse MatlockUX EngineerAuthor Commented:
Very cool... will do. thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.