Need help with my toplist php query

[secret_life]

Hi all

i have toplist on my website and this is my code

 

<?php
if ( !defined( 'SMARTY_DIR' ) ) {
include_once( 'init.php' );
}

$psize = "10";			// Limit
$minimum_votes = "3";		// Minimum number of votes required

$cpage = $_REQUEST['page'];

if( $cpage == '' ) {
$cpage = 1;
}

$sql = 'SELECT u.country, u.state_province, u.city, u.id, u.username, floor(period_diff(extract(year_month from NOW()),extract(year_month from u.birth_date))/12) as age, u.gender, u.lookgender, count(  *  ) vote_count , round(avg( r.rating ),1) avg_rating FROM ! u INNER JOIN ! r ON u.id=r.profileid GROUP BY 1,2,3,4,5 HAVING COUNT(*)>="'.$minimum_votes.'" ORDER BY avg_rating DESC, vote_count DESC LIMIT 10';

$rs = $osDB->query( $sql, array( USER_TABLE, USER_RATING_TABLE ) );

$rcount = $rs->numRows();

if( $rcount > 0 ) {

$t->assign( 'totalrecs', $rcount );

$pages = ceil( $rcount / $psize );

$start = ( $cpage - 1 ) * $psize;

$t->assign ( 'start', $start );

if( $pages > 1 ) {

	if ( $cpage > 1 ) {

		$prev = $cpage - 1;

		$t->assign( 'prev', $prev );

	}
	$t->assign ( 'cpage', $cpage );

	$t->assign ( 'pages', $pages );

	if ( $cpage < $pages ) {

		$next = $cpage + 1;

		$t->assign ( 'next', $next );
	}
}
$sql .= " limit $start, $psize"	;
}

$data = array();
$number = 1;

while ( $row = $rs->fetchRow() ) {

$countryname = $osDB->getOne('select name from ! where code = ?', array(COUNTRIES_TABLE, $row['country'] ) );

$statename = $osDB->getOne('select name from ! where code = ? and countrycode = ?', array(STATES_TABLE, $row['state_province'], $row['country'] ) );

$row['countryname'] = $countryname;

$row['statename'] = ($statename != '') ? $statename : $row['state_province'];

$row['username'] = $row['username'];

$row['number'] = $number;

$data[] = $row;

$number+=1;
}

hasRight('');

$t->assign ( 'querystring', $querystring );
$t->assign ( 'psize', $psize );

$t->assign ( 'data', $data );
$t->assign ( 'lang', $lang );
$t->assign ( 'rendered_page', $t->fetch('showtoplist.tpl') );

$t->display ( 'index.tpl' );

exit;
?>

 

I want to put user global ranking in user homepage but i can make a query that work in. I want like this '' You ranked : 123 ''

i know i need to use this

count(  *  ) vote_count , round(avg( r.rating ),1) avg_rating

before but im lost.

Please help me for this query if you can

Thanks

[Spudinski]

SELECT
   COUNT(*) AS `vote_count`,
    ROUND((SUM(`r.rating`) / COUNT(*), 1) AS `avg_rating`
FROM
...
JOIN
..
ORDER BY `vote_count`, `avg_rating`

 

?

[secret_life]

thanks for your help. Now i have my table shorted how i can get position for user number 1 exemple

 

$rs=$osDB->query("SELECT COUNT(*) AS vote_count, ROUND((SUM(r.rating) / COUNT(*), 1) AS avg_rating FROM flirt_userrating ORDER BY avg_rating DESC, vote_count DESC") ;

[secret_life]

im there now but i have an error

 

// rating count //

	$total_ratingscnt = 0;
	$ratingcount = $osDB->getOne('SELECT count(id) as ratingcount FROM ! WHERE profileid = ? and rating > ?', array( USER_RATING_TABLE, $_SESSION['UserId'], '0' ) );

	$item["ratingcount"] = $ratingcount;

	$total_ratingscnt += $ratingcount;

	// rating value //

	$rowrate = $osDB->getRow('SELECT count(rating) as tv , sum(rating) as sm FROM ! WHERE profileid = ? and rating > ? ', array( USER_RATING_TABLE, $_SESSION['UserId'], '0' ) );

	$tv = $rowrate['tv'];

	$sm = $rowrate['sm'];

	unset($rowrate);

	if ( $tv == 0 ) {

		$ratingvalue = 0;

	} else {

		$tv = ($tv == 0) ? 1 : $tv;

		$ratingvalue = $sm / $tv;
		$ratingvalue = number_format($ratingvalue,2);

	}

	$item["ratingvalue"] = $ratingvalue;


$rs=mysql_query("SELECT COUNT(*) AS vote_count, round(avg( rating ),1) AS avg_rating FROM flir_userrating WHERE avg_rating > $ratingvalue AND vote_count > $ratingcount") ;
$rankall = mysql_num_rows($rs);

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in ... line 469

that line

 

$rs=mysql_query("SELECT COUNT(*) AS vote_count, round(avg( rating ),1) AS avg_rating FROM flir_userrating WHERE avg_rating > $ratingvalue AND vote_count > $ratingcount") ;
$rankall = mysql_num_rows($rs);

 

IM LOST

[Razor42]

I may have misunderstood what you are trying to do, also the fact I may just be wrong but should it be:

$rs=mysql_query("SELECT COUNT(*) AS vote_count, round(avg( rating ),1) AS avg_rating FROM flir_userrating WHERE avg_rating > $ratingvalue AND vote_count > $ratingcount") ;
$rankall = mysql_fetch_rows($rs);

 

I'm most likely wrong, haha.

[secret_life]

I dont want fetch but count what rank my user are with 2 different condition. avg_rating and vote_count. So if a user have 9.1 / 10 and have 3 vote he must be upper then user who have 9.1 / 10 with 2 vote

[URBANZ]

user order by asc/desc at the end of the query i think thats what ur trying to do