Need some help calling up info from my dbase...

[Padgoi]

Ok, so I have this table named ratings.

There are 3 fields in this table, pid, uid, and rating.

Pid refers to the id number of the rating given.

Uid refers to the ID number of the user giving or being given the rating.

Rating refers to the rating given in number form.

 

I need a function that will display the Top 10 users that have received any given rating.

 

In other words, I want to display the Top 10 users that have received a certain rating.  So if the rating ID number is 13 (which corresponds to a certain rating, like Intelligent), I want to display the Top 10 users that have received the most ratings of rating ID #13. 

 

Can anyone help me with this?  I'd be very appreciative and my PHP skills are . . . very limited.

 

 

 

[phpSensei]

<?php
### Sensei's Note, please ignore###
// 3 fields
// PId == rating number given
// Uid == user id
// Rating == rating
#########################

$rate_type = 13;
$query = mysql_query("SELECT * FROM table WHERE rating = '$rate_type' LIMIT 10"); // All ratings that are more than 10

     while($row = mysql_fetch_array($query)){

           $user = mysql_fetch_array(mysql_query("SELECT * FROM users WHERE id = '$row[uid']"));
           
             print "username: " . $user[username];
             print "rating: " . $row[rating];
     

     }

?>

 

 

no clue if this is what you want or not.

[kjtocool]

From what I get from it ...

 

Pid specifies a specific type of rating.  He wants to be able to search this rating for the frequency of a specific UID.  Thus, a user can have multiple ratings.  So your database could look like:

 

PID - UID - Rating

_______________

1 - 1 - A

1 - 1 - B

2 - 1 - A

9 - 1 - C

9 - 4 - A

 

 

To do what he asks, it seems most logical to me that we need to know what types of values he is storing as ratings.  Are they star ratings (meaning 4 out of 5 stars), and does he want us to select the 10 highest average ratings?  In essence we need a way to quantify rating, and then select the 10 highest average (or frequency) unique UID.

 

If so, what Sensei posted above will get the job done for the most part with regards to the PHP code, but we need a much more complex SQL query.

[Padgoi]

Here's a few pieces of code from the actual rating script, maybe it can make things easier as to what sql fields are being called up:

 

//-------------------
// this little bit adds the icon next to a users name in every post.
//------------------
if( $member['id'] )
{
$this->ipsclass->load_template('skin_erate');

$this->ipsclass->DB->build_query
(
	array(
		'select' => 'r.*,COUNT(r.rating) as count',
		'from' => array( 'ratings' => 'r' ),
		'where' => "p.author_id = '{$member['id']}' GROUP BY r.rating",
		'add_join' => array(
				0 => array (
					'select' => 'p.author_id',
					'from' => array( 'posts' => 'p' ),
					'where' => 'r.pid = p.pid',
					'type' => 'left'
			)
		),
		'order' => 'count DESC',
		'limit' => '1'
	)
);

$this->ipsclass->DB->exec_query( );

if( $this->ipsclass->DB->get_num_rows( ) )
{
	$row = $this->ipsclass->DB->fetch_row( );

	$member['userrating'] = $this->ipsclass->compiled_templates['skin_erate']->user_rating( str_replace( ' ' , '' , strtolower( $this->ratings[$row["rating"]] ) ) , $member["id"] , $member["members_display_name"] , $this->ratingsSuffix[$row["rating"]] );
}
}

 

And here's another piece of code:

 

//---------------
// this bit is responsible for displaying the ratings at the bottom of every post
//---------------
if( $row['pid'] )
{
$row['ratings'] = "";
$ratingshtml = "";

$this->ipsclass->DB->query
("SELECT `pid` , `rating`, COUNT(`rating`) as ratings
	FROM `ibf_ratings`
	WHERE `pid` = '" . $row['pid'] . "' GROUP BY `rating` ORDER BY rating ASC"
);

if( $this->ipsclass->DB->get_num_rows( ) )
{
	while( $rrow = $this->ipsclass->DB->fetch_row( ) )
	{
		$ratingshtml .= "<div class=\"rate_it_display\" style=\"background-image: url( 'style_images/rate/" . str_replace( ' ' , '' , strtolower( $this->ratings[$rrow['rating']] ) ) . ".png' ) \"> " . $rrow['ratings']. " x " . $this->ratings[$rrow['rating']] . "!</div>";
	}
}

$this->ipsclass->load_template( 'skin_erate' );
$row['ratings'] = $this->ipsclass->compiled_templates['skin_erate']->ratings( $row['pid'] , $row['author_id'] , $ratingshtml );
}

[Padgoi]

Here is the piece of code that lists the actual ratings and again, sorry for my lack of knowledge, I'm trying to give you as much code as possible to help me with my original question:

 

//
//
// Vriables used by this script, edit them to your liking!
// If you change a rating, make sure you change the image aswell, e.g. 'Super Cool', the image MUST be 'supercool.png'
//
// TODO: put this in a DB and make editable within the ACP!
//	 use an images array
//

// Seperate values with a comma. Use UserIDs.
var $ratingsBanned = Array( '' );
var $ratings = Array( 1 => 'Agree' , 'Disagree' ,  'Gold Star' , 'Informative' , 'Thanks' , 'ZING' , 'Useful' , 'Funny' , 'Bad Spelling' , 'Unfriendly' , 'Good Idea' , 'Spam' , 'WTF' , 'Artistic' );
var $ratingsSuffix = Array( 1 => 'is usually Agreed with.' , 'is Disagreed with a lot.' , 'has lots of pretty Gold Stars!' ,
		'is Informative' , 'has been Thanked many times.' , 'ZINGGGGG!' , 'is Useful.' , 'is Funny.' ,
		'has Spelling problems' , 'is Unfriendly' , 'has lots of Good Ideas!' , 'Spams alot' , 'is confusing!' , 'is Artistic!' );

[Padgoi]

anyone please?

[Padgoi]

Can anyone help me with this please? 

[Padgoi]

and phpSensei, there's something wrong with your bit.

 

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']' in rating.php on line 53

 

Line 53 is:

 

           $user = mysql_fetch_array(mysql_query("SELECT * FROM ".SQL_PREFIX."members WHERE id = '$row[uid']"));

[phpSensei]

and phpSensei, there's something wrong with your bit.

 

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']' in rating.php on line 53

 

Line 53 is:

 

           $user = mysql_fetch_array(mysql_query("SELECT * FROM ".SQL_PREFIX."members WHERE id = '$row[uid']"));

 

What i put was

 

           $user = mysql_fetch_array(mysql_query("SELECT * FROM users WHERE id = '$row[uid']")); 

[Padgoi]

Same error after I used your line.

 

Here's the entire code:

 

<?php

if ( ! defined( 'IN_IPB' ) )

{

            print "<h1>Incorrect access</h1>You cannot access this file directly. If you have recently upgraded, make sure you upgraded all the relevant files.";

            exit();
}

class component_public {
var $html = "";
var $output = "";
var $nav;
var $ipsclass;

function run_component(){

$this->ipsclass->load_template('skin_global');
$this->ipsclass->load_language('lang_global');
$this->html = $this->ipsclass->compiled_templates[ 'skin_global' ]; 

// 3 fields
// PId == rating number given
// Uid == user id
// Rating == rating

$rate_type = 13;
$query = mysql_query("SELECT * FROM ibf_ratings WHERE rating = '$rate_type' LIMIT 10"); // All ratings that are more than 10

     while($row = mysql_fetch_array($query)){

           $user = mysql_fetch_array(mysql_query("SELECT * FROM users WHERE id = '$row[uid']")); 
           
             print "username: " . $user[username];
             print "rating: " . $row[rating];
     

     }
}
}

?>

[phpSensei]

or i forgot a single quote

 

try

 

<?php

if ( ! defined( 'IN_IPB' ) )

{

            print "<h1>Incorrect access</h1>You cannot access this file directly. If you have recently upgraded, make sure you upgraded all the relevant files.";

            exit();
}

class component_public {
var $html = "";
var $output = "";
var $nav;
var $ipsclass;

function run_component(){

$this->ipsclass->load_template('skin_global');
$this->ipsclass->load_language('lang_global');
$this->html = $this->ipsclass->compiled_templates[ 'skin_global' ]; 

// 3 fields
// PId == rating number given
// Uid == user id
// Rating == rating

$rate_type = 13;
$query = mysql_query("SELECT * FROM ibf_ratings WHERE rating = '$rate_type' LIMIT 10"); // All ratings that are more than 10

     while($row = mysql_fetch_array($query)){

           $user = mysql_fetch_array(mysql_query("SELECT * FROM users WHERE id = '$row[uid]'")); 
           
             print "username: " . $user[username];
             print "rating: " . $row[rating];
     

     }
}
}

?>

 

 

[Padgoi]

Thanks for the help, but I'm afraid it's a bit more complicated than that.  Your code just resulted in the following display:

 

username: rating: 13username: rating: 13username: rating: 13username: rating: 13username: rating: 13username: rating: 13username: rating: 13username: rating: 13username: rating: 13username: rating: 13