visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Ranking (Top 10, Top 100 and the rank) VERY URGENT!!!!
By: kaeonthibe U.S.A.  Date: 01/06/2003 00:00:00  English French  Points: 500 Status: Answered
Quality : Excellent
Hi,
I got a script and with the script I can put in MySQL database some values like for voting. I got a picture rating system on my website and I need a code (complete code on the page) to get all of this information: score of the member, number of votes for the member, rank of the member on all member of his gender, number of visits on his page, top 10 rank of guys, top 10 rank of girls, top 100 rank of guys and top 100 rank of girls.
I can have the score of the member, the number of votes and the number of visits on his page. But for ranking I don't know what to do. There's the table...
Table - rl_member_ratings with:
MEMBER_ID (the member)
MEMBER_RATING_ID
MEMBER_RATING_VALUE (the value of the rating for each time someone vote for the member)
GENDER_ID (the gender of the member for who the person vote)
And you can call the handle of the member from the table rl_member with the MEMBER_ID
check this, that's how the php script look:
$intMemberId = get_session("MEMBER_ID");
//fire the sql query to get value to be shown on this page
$strSql = "SELECT " .
"MEMBER_ID," .
"MEMBER_RATING_ID ," .
"MEMBER_RATING_VALUE ," .
"GENDER_ID " .
"FROM " .
"rl_member_ratings " .
"WHERE " .
"MEMBER_ID = " .tosql($intMemberId,"Number");

$db->query($strSql);

if($db->num_rows()>0)
{
while($db->next_record())
{
$score= dlookup("rl_member_ratings","FORMAT(avg(MEMBER_RATING_VALUE),2)","MEMBER_ID=".tosql($intMemberId,"Number"));
$numvote= dlookup("rl_member_ratings","COUNT(MEMBER_RATING_VALUE)","MEMBER_ID=".tosql($intMemberId,"Number"));
$memid=$db->f("MEMBER_ID");

$strSql = "SELECT " .
"* " .
"FROM " .
"rl_member_ratings " .
"WHERE " .
"GENDER_ID = " .tosql($db->f("GENDER_ID","Number"));

$strSql = $strSql . "ORDER BY MEMBER_RATING_VALUE DESC";

$db2->query($strSql);

while($db2->next_record())
{
$rank=$db2->f("MEMBER_ID");
}

$strSql1 = "SELECT " .
"VIEWED_COUNT " .
"FROM " .
"rl_viewedmember " .
"WHERE " .
"MEMBER_ID = " .tosql($memid,"Number");
$db1->query($strSql1);

while($db1->next_record())
{
$visits=$db1->f("VIEWED_COUNT");
}

//*******************************************************************

$objtpl->set_var("score",$score);
$objtpl->set_var("numvote",$numvote);
$objtpl->set_var("rank",$rank);
$objtpl->set_var("visits",$visits);
$objtpl->parse("InfoShow",true);
$objtpl->set_var("NoRecords","");
}
$objtpl->set_var("total",$total);
$objtpl->parse("GrandTotal",false);
}
else
{
$objtpl->parse("NoRecords",false);
$objtpl->set_var("GrandTotal","");
$objtpl->set_var("InfoShow","");
}
$objtpl->parse("FormShow",false);

}
?>

I want to get the ranking of the member and everything that I ask : the rank on all the member of his sex, top 10 of guys, top 10 of girls, top 100 of guys and top 100 of girls.

Thanks,
Thierry
By: VGR Date: 01/06/2003 23:17:00 English  Type : Comment
use the same technique but with :
-yop10guys : SELECT * FROM rl_member_ratings WHERE GENDER_ID =malevalue ORDER BY MEMBER_RATING_VALUE DESC LIMIT 10;
(malevalue is 0 or 1, or 'Y' or 'N', or TRUE/FALSE, I don't know for your data)
-yop100guys : SELECT * FROM rl_member_ratings WHERE GENDER_ID =malevalue ORDER BY MEMBER_RATING_VALUE DESC LIMIT 100;

-yop10girls : SELECT * FROM rl_member_ratings WHERE GENDER_ID =femalevalue ORDER BY MEMBER_RATING_VALUE DESC LIMIT 10;
-yop100girls : SELECT * FROM rl_member_ratings WHERE GENDER_ID =femalevalue ORDER BY MEMBER_RATING_VALUE DESC LIMIT 100;

-ranking : I didn't understand the requirement and the link with MEMBER_RATING_ID and MEMBER_RATING_VALUE.
By: kaeonthibe Date: 01/06/2003 23:45:00 English  Type : Comment
Because I want a top of the average of MEMBER_RATING_VALUE...
The score of a user appears like that = dlookup("rl_member", "avg(MEMBER_RATING_VALUE)", "MEMBER_ID = " themembervalue");
And I want to get ORDER BY (average of member rating) DESC LIMIT 10... because If I do like you said I will get the rank of all the user who got 10.

And for the ranking, I know that you didn't understand the requirement but I don't know how to put eg. you are the 345th on 34567 men with all member_rating_value. I don't know if it possible to COUNT all the value of (MEMBER_RATING_VALUE with GENDER_ID) that less than the MEMBER_RATING_VALUE of the member.

Thanks
Thierry
By: kaeonthibe Date: 02/06/2003 00:06:00 English  Type : Comment
Or suggest me something (add tables, add variables, value I don't know) I have a picture rating system... And I just want to have ranking system with it.
THanks
By: VGR Date: 02/06/2003 00:29:00 English French  Type : Comment
on va faire de notre mieux 8-)

I'll think about it.

I know what is a ranking&voting system, but I don't understand your table layout. Could you explain it with basic operations like B+A=BA ?

Your users post images an dpeople vote for them ? Well, what else ?

MEMBER_ID (the member)
MEMBER_RATING_ID ## does this link to an other table ?
MEMBER_RATING_VALUE (the value of the rating for each time someone vote for the member) ## not understood
GENDER_ID (the gender of the member for who the person vote) ## not understood, say i in French if you prefer
By: kaeonthibe Date: 02/06/2003 01:04:00 French  Type : Comment
Ok je vais l'expliquer en français...
Chaque fois que quelqu'un vote, voici les valeurs qui sont entrées dans la table rl_member_ratings
MEMBER_RATING_ID (Le id du vote)
MEMBER_RATING_VALUE (La valeur du vote sur 10)
MEMBER_ID (Le id du membre pour qui on vote)
GENDER_ID (Le sexe de la personne pour qui on vote, 0=fille 1=gars)
RATEFROM_ID (Le id de la personne qui vote)
COMMENTS (Son commentaire)
IP (Son ip par mesure de sécurité)
DATE (La date du vote)

Je veux faire un top avec les moyennes de vote pour chaque id de membre pour chaque sexe, je sais pas comment y penser...
Et j'aimerais montrer au membre son rang sur le top pour son sexe.
Genre 345e sur 3456 gars.
Dites-moi quoi faire, quoi ajouter, quoi mettre... Je suis perdu et desespéré... je peux ajouter des points à la question si nécessaire et merci de converser en français
avec moi.
Thierry

P.S. Comment avez-vous su que je parlais français?

By: VGR Date: 02/06/2003 01:17:00 English French  Type : Comment
je suis intelligent, puisque je suis Français :D
huh huh huh

ok, I hope the other Experts won't be offended by our conversation in French ;-) After all, it's your request :D
"le client est roi" (customer is king)

Je regarde ça, y a pas de quoi désespérer ;-)
By: kaeonthibe Date: 02/06/2003 01:47:00 English French  Type : Comment
Sorry for the others Experts who don't understand this conversation, read the first thread and if you can answer this question, don't hesitate.

For VGR - - -
Parfait et merci beaucoup...
SI vous avez quelconque questions ou si vous voulez avoir d'autres renseignements sur la table ou la programmation du script, n'hésitez pas. J'ai vraiment besoin d'aide puisque j'ai décidé de programmer mon site moi-même alors que j'avais demandé à au moins trois programmeurs de le faire pour moi, tous des cas desespérés, ils n'ont pas été capable de faire ce que je voulais. Là je l'ai programmé et il ne me manque que cette partie.
Oui ceux qui causent en français sont plus intelligents!! Moi je suis Canadien, du Québec à Montréal. :-D
Merci encore une fois,
Thierry
By: VGR Date: 02/06/2003 02:05:00 English  Type : Comment
ok, you get the average score for all votes like this :
$score= dlookup("rl_member_ratings","FORMAT(avg(MEMBER_RATING_VALUE),2)","MEMBER_ID=".tosql($intMemberId,"Number"));

and you get the number of votes like that :
$numvote= dlookup("rl_member_ratings","COUNT(MEMBER_RATING_VALUE)","MEMBER_ID=".tosql($intMemberId,"Number"));

so I suggest that you add a field to the votes : the gender of the person who votes. Let's call it VOTER_GENDER

1) to get the average score for votes being male/female, you would use :
$score= dlookup("rl_member_ratings","FORMAT(avg(MEMBER_RATING_VALUE),2)","MEMBER_ID=".tosql($intMemberId,"Number").' AND VOTER_GENDER=0'); // for girls' votes, =1 for boys' votes

2) to get the top-10 of boys & girls (change for GENDER_ID=0) , or the top-100 (change the LIMIT=10 )

select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10;

NB : this could be a "SELECT * FROM tempotop LIMIT 10;" see below

3) to get the rank of a boy in the top votes given to boys :
DROP TABLE IF EXISTS tempotop;
CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;
SELECT * FROM tempotop;
// fetch results (incrementing a counter) until MEMBER_ID is the current user's ID [or not found -> "you're not in the top 10"]

hope this helps,
By: kaeonthibe Date: 02/06/2003 02:15:00 French  Type : Comment
Juste une petite question avant de vérifier si cela fonctionne... pourquoi avoir besoin du the gender of the person who votes?
Peu importe qui vote, un vote reste un vote... J'aimerais comprendre...
Merci et je vérifie si le tout si fonctionne...
By: VGR Date: 02/06/2003 02:24:00 French  Type : Comment
parce que cela simplifie le traitement du "top sexuel". C'ets pareil pour le GENDER_ID, sexe de la personne pour qui l'on vote. Il est complètement inutile puisqu'on a déjà le MEMBER_ID pour qui l'on vote. Il n'est donc utile qu'en cas de traitement ultérieur, pour éviter un JOIN... Pareil pour moi. Je ne fais que compléter ta solution en restant dans le même esprit.
By: kaeonthibe Date: 02/06/2003 03:16:00 French  Type : Comment
OK, je pense que cela peut fonctionner... mais avec ce que vous m'avez envoyé quelle serait la valeur que je devrais mettre pour par exemple le rang du member... $rank = " quoi? "
By: kaeonthibe Date: 02/06/2003 03:35:00 French  Type : Comment
Voici de quoi à l'air la fin de ma page... Juste me dire quoi changer, ajouter... J'ai beaucoup de difficulté avec la mise en page, comment attribuer les fonctions MySQL aux bonnes variables.
Désolé et merci beaucoup pour votre aide.
Thierry

$intMemberId = get_session("MEMBER_ID");
//fire the sql query to get value to be shown on this page
$strSql = "SELECT " .
"* " .
"FROM " .
"rl_member_ratings " .
"WHERE " .
"MEMBER_ID = " .tosql($intMemberId,"Number");

// fetch results (incrementing a counter) until MEMBER_ID is the current user's ID [or not found -> "you're not in the top 10"]

$db->query($strSql);

if($db->num_rows()>0)
{
while($db->next_record())
{
$score= dlookup("rl_member_ratings","FORMAT(avg(MEMBER_RATING_VALUE),2)","MEMBER_ID=".tosql($intMemberId,"Number"));
$numvote= dlookup("rl_member_ratings","COUNT(MEMBER_RATING_VALUE)","MEMBER_ID=".tosql($intMemberId,"Number"));
$memid=$db->f("MEMBER_ID");
$genderid= dlookup("rl_member", "GENDER", "MEMBER_ID", "MEMBER_ID=".tosql($intMemberId,"Number"));

$strSql = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ";

if($genderid==1)
{
$strSql = $strSql . "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
}
else
{
$strSql = $strSql . "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
}

$db2->query($strSql);

while($db2->next_record())
{
$rank= $db2->f("MEMBER_ID");
}

$strSql1 = "SELECT " .
"VIEWED_COUNT " .
"FROM " .
"rl_viewedmember " .
"WHERE " .
"MEMBER_ID = " .tosql($memid,"Number");

$db1->query($strSql1);

while($db1->next_record())
{
$visits=$db1->f("VIEWED_COUNT");
}

$strSql2 = "DROP TABLE IF EXISTS tempotop ";
"CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne ";
"MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC ";
"SELECT * FROM tempotop";

$db2->query($strSql1);
$db2->next_record();
By: VGR Date: 02/06/2003 03:49:00 French  Type : Comment
c'est normal, car le codage utilisé est tout sauf standard !

tous ces ->next_record(), ces dlookup(), ces ->f() ne sont jamais utilisés en général.

je pense aussi que vous ne pouvez pas passer les 3 phrases SQL à la fin. Il faudra faire trois appels à $db2->query()

en fait, à la fin, c'est ceci :

$strSql2 = "DROP TABLE IF EXISTS tempotop ";
$db2->query($strSql1);
$strSql2 = "CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC ";
$db2->query($strSql1);
$strSql2 = "SELECT * FROM tempotop";
$db2->query($strSql1);
$compteur=0;
$pastrouve=TRUE;
while ((pastrouve)AND($db2->next_record())) {
pastrouve=($db2->f("MEMBER_ID") == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (! pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
// not found in ranking, theoretically impossible !?!
die('Aberration : user not found in top rankings.');
} // if found or not
By: kaeonthibe Date: 02/06/2003 04:21:00 French  Type : Comment
J'ai essayé ce que vous m'avez dit mais cela retourne toujours le chiffre 0- $compteur est spécifié à un endroit et c'est comme si rien ne lui affectait.

Voici ce que j'ai fait... Désolé si j'ai des la difficulté à comprendre... Et si je prends de votre temps...

$strSql2 = "DROP TABLE IF EXISTS tempotop ";
$db2->query($strSql1);
$strSql2 = "CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC ";
$db2->query($strSql1);
$strSql2 = "SELECT * FROM tempotop";
$db2->query($strSql);
$compteur=0;
$pastrouve=TRUE;
while (($pastrouve)AND($db2->next_record())) {
$pastrouve=($db2->f("MEMBER_ID") == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
// not found in ranking, theoretically impossible !?!
die('Aberration : user not found in top rankings.');
} // if found or not

$strSql = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ";

if($genderid==1)
{
$strSql = $strSql . "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
}
else
{
$strSql = $strSql . "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
}

$db->query($strSql);

while($db->next_record())
{
$rank= $compteur;
}





J'ai mit cela directement après:
$strSql1 = "SELECT " .
"VIEWED_COUNT " .
"FROM " .
"rl_viewedmember " .
"WHERE " .
"MEMBER_ID = " .tosql($memid,"Number");

$db1->query($strSql1);

while($db1->next_record())
{
$visits=$db1->f("VIEWED_COUNT");
}


Et j'ai retiré cela pour le mettre plus bas:


$strSql = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ";

if($genderid==1)
{
$strSql = $strSql . "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
}
else
{
$strSql = $strSql . "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
}

$db2->query($strSql);

while($db2->next_record())
{
$rank= $db2->f("MEMBER_ID");
}
By: VGR Date: 02/06/2003 04:44:00 English  Type : Comment
$strSql2 = "CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC ";
$db2->query($strSql1); // ERROR
STRSQL2 !!!

$strSql2 = "SELECT * FROM tempotop";
$db2->query($strSql); // ERROR
STRSQL2 !!!
By: kaeonthibe Date: 02/06/2003 04:52:00 French  Type : Comment
Maintenant il indique Aberration : user not found in top rankings. pourtant, les données sont bien présentes dans la base...

By: VGR Date: 02/06/2003 05:31:00 French  Type : Comment
au point du commentaire // the rank found is $compteur
$compteur contient le rang recherché.

Malheureusement, deux blocs plus bas il y a

while($db->next_record())
{
$rank= $compteur;


que je ne comprends pas très bien...


vous devriez repenser la logique du script, algorithmiquement :
-opération 1
-opération 2
...
-opération N

et ensuite insérer les blocs idnépendants à la bonne place, en faisant attention à ne pas écraser les variable sutiles avec d'autres, à fair ele minimum d'opérations etc

par exemple construire le "top" pour avoir le rang permet ensuite d'avoir le "top10" ou "top100" sans autre opération qu'un SELECT ... FROM tempotop LIMIT ......
By: psadac Date: 02/06/2003 05:37:00 English  Type : Comment
just a little advice kaeonthibe and VGR, why don't you use mysql user variables to set ranks ?

SELECT @i := 0;

CREATE TEMPORARY TABLE tempotop
select @i := @i + 1 as rank, FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings
WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;

i'm french too, so i'm not offended :-)
By: kaeonthibe Date: 02/06/2003 06:36:00 English French  Type : Comment
I tried everything but it doesn't work...
J'ai trop de la difficulté à repenser à la logique du script puisque même si je tente de vider les choses inutiles, il reste qu'en appliquant ce que vous m'avez dit cela ne fonctionne pas. Pour "psadac", j'ai essayé et ça me retourne rien non plus. C'est comme si les choses que vous me proposer n'était pas affecté - vous vous y connaissez un peu en programmation, juste me dire quoi et où ajouter ce qu'il faut, corriger les erreurs. Merci beaucoup d'avance.
Thierry

Voici ce que j'ai tenté sans succès:

$intMemberId = get_session("MEMBER_ID");
//fire the sql query to get value to be shown on this page
$strSql = "SELECT " .
"* " .
"FROM " .
"rl_member_ratings " .
"WHERE " .
"MEMBER_ID = " .tosql($intMemberId,"Number");

$db->query($strSql);

if($db->num_rows()>0)
{
$score= dlookup("rl_member_ratings","FORMAT(avg(MEMBER_RATING_VALUE),2)","MEMBER_ID=".tosql($intMemberId,"Number"));
$numvote= dlookup("rl_member_ratings","COUNT(MEMBER_RATING_VALUE)","MEMBER_ID=".tosql($intMemberId,"Number"));
$memid=$db->f("MEMBER_ID");
$genderid= dlookup("rl_member", "GENDER", "MEMBER_ID", "MEMBER_ID=".tosql($intMemberId,"Number"));

$strSql1 = "SELECT " .
"VIEWED_COUNT " .
"FROM " .
"rl_viewedmember " .
"WHERE " .
"MEMBER_ID = " .tosql($memid,"Number");

$db1->query($strSql1);
$db1->next_record();
$visits=$db1->f("VIEWED_COUNT");

$strSql2 = "DROP TABLE IF EXISTS tempotop ";
$db2->query($strSql2);
$strSql2 = "CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC ";
$db2->query($strSql2);
$strSql2 = "SELECT * FROM tempotop ";
$db2->query($strSql2);
$compteur=0;
$pastrouve=TRUE;
while (($pastrouve)AND($db2->next_record())) {
$pastrouve=($db2->f("MEMBER_ID") == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
// not found in ranking, theoretically impossible !?!
die('Aberration : user not found in top rankings.');
}
By: VGR Date: 02/06/2003 07:04:00 English  Type : Answer
@psadac : perhaps because I don't trust them in 3.23.56 when called from a PHP script ? ;-)

My way would be :

<?php
// this script supposes that $db is compatible with the link resource ID returned by a mysql_connect('host','user','password); call
$intMemberId = $_SESSION['MEMBER_ID'];
//fire the sql query to get value to be shown on this page
$strSql = "SELECT * FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId;";
$result=mysql_query($strSql,$db) or die(mysql_error());
if (mysql_numrows($result)>0) {
$strSql = "SELECT FORMAT(avg(MEMBER_RATING_VALUE),2) as a, COUNT(MEMBER_RATING_VALUE) as b, GENDER FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId;"
$result=mysql_query($strSql,$db) or die(mysql_error());
$res=mysql_fetch_array($result);
$score=$res['a'];
$numvote=$res['b'];
$genderid=$res['GENDER'];
$strSql = "SELECT VIEWED_COUNT FROM rl_viewedmember WHERE MEMBER_ID = $memid;";
$result=mysql_query($strSql,$db) or die(mysql_error());
$res=mysql_fetch_array($result);
$visits=$res['VIEWED_COUNT'];
$strSql = 'DROP TABLE IF EXISTS tempotop;';
$result=mysql_query($strSql,$db) or die(mysql_error());
$strSql = 'CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;';
$result=mysql_query($strSql,$db) or die(mysql_error());
$strSql = 'SELECT * FROM tempotop;';
$result=mysql_query($strSql,$db) or die(mysql_error());
$compteur=0;
$pastrouve=TRUE;
while (($pastrouve)AND($res=mysql_fetch_array($result))) {
$pastrouve=($res['MEMBER_ID'] == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
// not found in ranking, theoretically impossible !?!
die('Aberration : user not found in top rankings.');
} // if found
} else { // user not found
die("user $intMemberId not found !");
}
// here treat the user having rank=$compteur
?>
By: kaeonthibe Date: 02/06/2003 07:13:00 English  Type : Comment
I've tried your way but I got a message error - "Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause"...
By: VGR Date: 02/06/2003 07:34:00 English  Type : Comment
there IS a group by and I tested my query. You must have done something wrong. Or I made a typo. Or...

In fact, the second $strSql = above is wrong, it contains an average AVG() and no group by.

Add at the en : GROUP BY MEMBER_ID so that it reads :
SELECT FORMAT(avg(MEMBER_RATING_VALUE),2) as a, COUNT(MEMBER_RATING_VALUE) as b, GENDER_ID FROM rl_member_ratings WHERE MEMBER_ID =$intMemberId group by MEMBER_ID;

You were also selecting GENDER while there is only GENDER_ID in that table, I fixed this also.
By: kaeonthibe Date: 02/06/2003 07:38:00 English  Type : Comment
Thanks,
It's work now but users can have same ranking if they've got the same average... and that's not good.
Merci beaucoup
Thierry
By: kaeonthibe Date: 02/06/2003 07:39:00 English  Type : Comment
And you can visit the website at <A HREF="http://www.contactados.com/ContactAdos">http://www.contactados.com/ContactAdos</a> if you want
C'est un site de rencontre pour 25 ans et moins :-)
By: kaeonthibe Date: 02/06/2003 08:34:00 French  Type : Comment
Et juste une petite question, deux petits bugs:
1-> Lorsque j'insère une variable dans un tableau (en html), il n'apparaît qu'une variable... Est-il possible de faire drop down de toutes les données, créer de nouveaux champs dans le tableau pour le top 10

2-> Il y a un petit bug avec le order by parce que j'ai fait un test et il me montre qu'un membre qui 9.00 et 1 vote est en première position tandis qu'un membre qui a 9.50 et 2 votes est en deuxième position.

Merci de répondre encore à mes questions...
Thierry
By: VGR Date: 02/06/2003 08:37:00 French English  Type : Comment
amusant, je connaissais déjà adostreet pour les mêmes raisons :D

this said, "users can have same ranking if they've got the same average" : not really. Should they ? Actually, the way I propsoed the queries, the people are taken in any order past the AVG(MEMBER_RATING) : alphabetical or Ids

you could change "ORDER BY moyenne DESC" into "ORDER BY moyenne DESC, MEMBER_ID ASC"

anyway, it's true that the rank has little sense amongst the people having the same average of votes ;-) How could it be different ?
By: VGR Date: 02/06/2003 08:46:00 French  Type : Comment
1-> je peux voir le code et le résultat escompté ou obtenu ?

2-> je peux voir les données ? La requète ets pourtant simple.
By: kaeonthibe Date: 02/06/2003 08:54:00 French  Type : Comment
Ok, voici le code au complet pour le fichier en php:
$db = mysql_connect('host','user','password');
// this script supposes that $db is compatible with the link resource ID returned by a mysql_connect('host','user','password); call
$intMemberId = $_SESSION['MEMBER_ID'];
//fire the sql query to get value to be shown on this page
$strSql = "SELECT * FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId;";
$result=mysql_query($strSql,$db);
if (mysql_numrows($result)>0) {
$strSql = "SELECT FORMAT(avg(MEMBER_RATING_VALUE),2) as a, COUNT(MEMBER_RATING_VALUE) as b, GENDER_ID FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId GROUP BY MEMBER_ID;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$memid = $intMemberId;
$score=$res['a'];
$numvote=$res['b'];
$genderid=$res['GENDER_ID'];
$strSql = "SELECT VIEWED_COUNT FROM rl_viewedmember WHERE MEMBER_ID = $memid;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$visits=$res['VIEWED_COUNT'];
$strSql = 'DROP TABLE IF EXISTS tempotop;';
$result=mysql_query($strSql,$db);
$strSql = 'CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ';
if($genderid==1)
{
$strSql = $strSql . "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
}
else
{
$strSql = $strSql . "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
}
$result=mysql_query($strSql,$db);
$strSql = 'SELECT * FROM tempotop;';
$result=mysql_query($strSql,$db);
$compteur=1;
$pastrouve=TRUE;
while (($pastrouve)AND($res=mysql_fetch_array($result))) {
$pastrouve=($res['MEMBER_ID'] == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
} // if found

$strSql2 = "SELECT COUNT(GENDER) as c FROM rl_member WHERE GENDER= $genderid";
$strSql2 = $strSql2 . " AND DISPLAYPHOTONUMBER != 0;";
$db1->query($strSql2);
$db1->next_record();
if($genderid==0)
{
$allgender="filles";
}
else
{
$allgender="gars";
}
$handle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $intMemberId);

$strSql3 = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
$db2->query($strSql3);

$strSql4 = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
$db3->query($strSql4);

while($db2->next_record())
{
$gtophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db2->f("MEMBER_ID"));
$gtopscore = $db2->f("moyenne");
}
while($db3->next_record())
{
$ftophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db3->f("MEMBER_ID"));
$ftopscore = $db3->f("moyenne");
}
// here treat the user having rank=$compteur

//*******************************************************************


$objtpl->set_var("score",$score);
$objtpl->set_var("gtopscore",$gtopscore);
$objtpl->set_var("ftophandle",$ftophandle);
$objtpl->set_var("ftopscore",$ftopscore);
$objtpl->set_var("gtophandle",$gtophandle);
$objtpl->set_var("allmemberrank",$db1->f("c"));
$objtpl->set_var("handle",$handle);
$objtpl->set_var("allgender",$allgender);
$objtpl->set_var("numvote",$numvote);
$objtpl->set_var("rank",$compteur);
$objtpl->set_var("visits",$visits);
$objtpl->parse("InfoShow",true);
$objtpl->parse("InfoShow2",true);
$objtpl->set_var("NoRecords","");

$intCounter++;

$objtpl->set_var("total",$total);
$objtpl->parse("GrandTotal",false);
}
else
{
$objtpl->parse("NoRecords",false);
$objtpl->set_var("GrandTotal","");
$objtpl->set_var("InfoShow","");
$objtpl->set_var("InfoShow2",true);
}
$objtpl->parse("FormShow",false);

}
?>


Et voici le code au complet pour la page html:

<html>
<head>
<meta name="title" content="ContactAdos.com site de rencontres pour 25 ans et mois, l'Unique déclic des jeunes">
<meta name="description" content="Le seul site de rencontres pour ados, plus précisément pour 25 ans et moins; gratuit il offre de nombreux avantages que peu de sites de rencontre offrent.">
<meta name="keywords" content="ados jeunes rencontres rencontre amour amitié chat aventure drague concours beauté romance relations seuls fille gars photo babillard forums">
<meta name="classification" content="célibataires chat amour amitié ados jeunes adolescents famille société romance filles fille gars">
<meta name="author" content="Beltnet">

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

<title>.:: Concours de Beaut&eacute; ::. ContactAdos.com :: L'Unique D&eacute;clic des Jeunes!</title>
<base href="{base_href}">
<link rel=STYLESHEET type="text/css" href="template/stsheet1.css">
<script language="JavaScript" src="jsfunctions/jsfunctions.js">


</script>

</head>

<body class="bodygoldenrod">

<table align="center" width="760" valign="middle" class="beigebody" cellpadding=0 cellspacing=0>
<tr>
<td>
<table border=0cellpadding=0 cellspacing=0 width="100%" align="center" valign="middle">
{header}
<tr>
<!--BeginFormShow-->

<td class="bodydarker" align="left" colspan=2> <font class="bigboldtext" size=2>
<font color="#0066FF"> <a href ="profile/myaccount.php"><font color="#0066FF">MonCompte</font></a>
> Concours de Beaut&eacute;</font> </font> </td>
</tr>
<tr><td><table width="509" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="24" valign="top">
<div align="right"><img src="images/coinpub3.gif" width="20" height="65"></div></td>
<td width="468"><IFRAME height=60 width=468 Marginwidth=0 marginheight=0 hspace=0 vspace=0 frameborder=0 scrolling=no bordercolor="#000000" SRC="<A HREF="http://fl01.ct2.comclick.com/aff_frame.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1">http://fl01.ct2.comclick.com/aff_frame.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1</a>">
<SCRIPT type="text/JavaScript" src="<A HREF="http://fl01.ct2.comclick.com/aff_js_src.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1">http://fl01.ct2.comclick.com/aff_js_src.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1</a>">
</SCRIPT>
<NOSCRIPT>
<A HREF="<A HREF="http://fl01.ct2.comclick.com/click_url.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1">http://fl01.ct2.comclick.com/click_url.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1</a>" TARGET="_blank">
<IMG BORDER=0 SRC="<A HREF="http://fl01.ct2.comclick.com/aff_url.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1">http://fl01.ct2.comclick.com/aff_url.ct2?id_regie=1&num_editeur=6572&num_site=1&num_emplacement=1</a>"></A>
</NOSCRIPT>
</IFRAME><img src="images/sousbanniere.gif" width="468" height="7"></td>
<td width="108" valign="top"><img src="images/coinpub3a.gif" width="20" height="65"></td>
</tr>
</table></td></tr>
<tr>

<td> <div align="center">Informations personnelles pour {handle} </div></td>
</tr>
<tr>
<td align="center" valign="middle">
<table width="90%" border=1 cellspacing=0>
<tr>

<td class="bodydarker" align="center" width="19%"> <font face="verdana" color="#0066FF" size=2>
<b>Ton score sur 10</b> </font> </td>

<td class="bodydarker" align="center" width="29%"> <font face="verdana" color="#0066FF" size=2>
<b>Ton rang sur tous les </b></font><font color="#0066FF" size=2 face="Verdana, Arial, Helvetica, sans-serif" class="biglighttext"><strong>{allgender}</strong>
</font> </td>
<td class="bodydarker" align="center" width="17%"> <font face="verdana" color="#0066FF" size=2>
<b>Bas&eacute; sur</b> </font> </td>


<td class="bodydarker" align="center" width="35%"> <font face="verdana" color="#0066FF" size=2>
<b>Nombre de visites &agrave; ta page</b> </font> </td>

</tr>



<!--BeginInfoShow-->
<tr>

<td align="center"> <font class="biglighttext" size=2> {score}
</font> </td>

<td align="center"> <font class="biglighttext" size=2> {rank}e
sur {allmemberrank} {allgender}</font> </td>


<td align="center"> <font class="biglighttext" size=2> {numvote}
votes </font> </td>


<td align="center"> <font class="biglighttext" size=2> {visits}
visites </font> </td>
</tr>

<!--EndInfoShow-->

<!--BeginNoRecords-->
<tr>

<td align="center" colspan=5> <font face="Verdana" color="red" size="2">
Personne n'a vot&eacute; pour toi encore!</font></td>
</tr>
<!--EndNoRecords-->

<!--BeginGrandTotal-->
<tr>
</tr>
<!--EndGrandTotal-->
</table>


<table width="100%" border="1" cellpadding="0" cellspacing="0" bordercolor="#CCCCFF">
<tr>
<td colspan=4><div align="center"><font color="#9999FF" size="3" face="Verdana, Arial, Helvetica, sans-serif"><strong>TOP
10</strong></font></div></td>
</tr>
<tr>
<td colspan=2><div align="center"><font color="#3366FF" size="3" face="Verdana, Arial, Helvetica, sans-serif">TOP
10 des gars</font></div></td>
<td colspan=2><div align="center"><font color="#FF6699" size="3" face="Verdana, Arial, Helvetica, sans-serif">TOP
10 des filles</font></div></td>
</tr>
<tr>
<td><div align="center"><font color="#3366FF" size="2" face="Verdana, Arial, Helvetica, sans-serif">Pseudo</font></div></td>
<td><div align="center"><font color="#3366FF" size="2" face="Verdana, Arial, Helvetica, sans-serif">Score</font></div></td>
<td><div align="center"><font color="#FF6699" size="2" face="Verdana, Arial, Helvetica, sans-serif">Pseudo</font></div></td>
<td><div align="center"><font color="#FF6699" size="2" face="Verdana, Arial, Helvetica, sans-serif">Score</font></div></td>
</tr>
<!--BeginInfoShow2-->
<tr>
<td><div align="center"><font class="biglighttext" size=2>{gtophandle}</font>
</div></td>
<td><div align="center"><font class="biglighttext" size=2>{gtopscore}</font></div></td>
<td><div align="center"><font class="biglighttext" size=2>{ftophandle}</font></div></td>
<td><div align="center"><font class="biglighttext" size=2>{ftopscore}</font></div></td>
</tr>
<!--EndInfoShow2-->
</table>


</td>
<!--EndFormShow-->
{footer}
</tr>
</table>
</td>
</tr>
</table>
</body>

</html>

Pour le 2-> Ça donne exactement ce que je t'ai dit comme 9.00 avec 1 vote est en première place et 9.50 avec deux votes est en deuxième... erreur de order by alors...
Merci
Thierry
By: VGR Date: 02/06/2003 09:01:00 French  Type : Comment
bin d'abord il manque (il me semble) le :
mysql_select_db('databasename',$db);
après le connect()

ensuite (près cet ajout) j'obtiens :
Parse error: parse error, unexpected '}' in i:\www\extra\testado.php on line 111

à quel endroit se trouverait la "dropdown" dans le tableau ? et quelles seraient les données ?

2) non, en théorie l'ORDER BY est correct et je l'ai testé. Je re-teste
By: VGR Date: 02/06/2003 09:11:00 French  Type : Comment
la page HTML s'appelle comment ?
By: kaeonthibe Date: 02/06/2003 09:13:00 French  Type : Comment
la page HTML s'appelle concoursdebeaute.html
By: kaeonthibe Date: 02/06/2003 09:14:00 French  Type : Comment
car je fais un call disons de la page php... le html et la page php ne sont pas les mêmes fichiers
By: VGR Date: 02/06/2003 09:17:00 English  Type : Comment
what's the trick or tool you do use in the HTML page so that {rank} expands to the member of the $tpl object ?!?
By: kaeonthibe Date: 02/06/2003 09:21:00 English  Type : Comment
I don't know but I have others HTML files that can expands value of row in a table. But I can't do it with this file, I don't know why...
By: VGR Date: 02/06/2003 18:04:00 English  Type : Comment
what do you use ? This doesn't look like ColdFusion. What's the stuff besiding PHP on your server ?

Anyway, I came up with this which seems fine to me. For the other problem, i van't help because I always generate the HTML myself (we could do it from your file) and thus builds the SELECT (dropdown list) myself from in-memory top10 and top100 arrays...

<?php
$db = mysql_connect('localhost','root','rootpassword');
mysql_select_db('test',$db) or die('could not select DB');
// this script supposes that $db is compatible with the link resource ID returned by a mysql_connect('host','user','password); call
$intMemberId = $_SESSION['MEMBER_ID'];
//fire the sql query to get value to be shown on this page
$strSql = "SELECT * FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId;";
$result=mysql_query($strSql,$db);
if ($result)
if (mysql_numrows($result)>0) {
$strSql = "SELECT FORMAT(avg(MEMBER_RATING_VALUE),2) as a, COUNT(MEMBER_RATING_VALUE) as b, GENDER_ID FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId GROUP BY MEMBER_ID;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$memid = $intMemberId;
$score=$res['a'];
$numvote=$res['b'];
$genderid=$res['GENDER_ID'];
$strSql = "SELECT VIEWED_COUNT FROM rl_viewedmember WHERE MEMBER_ID = $memid;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$visits=$res['VIEWED_COUNT'];
$strSql = 'DROP TABLE IF EXISTS tempotop;';
$result=mysql_query($strSql,$db);
$strSql = 'CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ';
if($genderid==1) $strSql .= "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
else $strSql .= "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
$result=mysql_query($strSql,$db);
$strSql = 'SELECT * FROM tempotop;';
$result=mysql_query($strSql,$db);
$compteur=0; // sorry, can't start at 1 (it'll be the value when results set is empty !)
$pastrouve=TRUE;
while (($pastrouve)AND($res=mysql_fetch_array($result))) {
$pastrouve=($res['MEMBER_ID'] == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
// the rank found is $compteur (from 1 to N)
} else {
// not found, $compteur is still zero
} // if found

$strSql2 = "SELECT COUNT(GENDER) as c FROM rl_member WHERE GENDER= $genderid";
$strSql2 = $strSql2 . " AND DISPLAYPHOTONUMBER != 0;";
$db1->query($strSql2);
$db1->next_record();
if($genderid==0) {
$allgender="filles";
} else {
$allgender="gars";
} // if gender
$handle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $intMemberId);
// modification : on a déjà le TOP N ! Il suffit d'en prendre les 10 ou 100 premières lignes pour le Top 10 ou Top 100 !
$strSql3 = "select * FROM tempotop LIMIT 100;";
$db2->query($strSql3);

$strSql4 = "select * FROM tempotop LIMIT 10;";
$db3->query($strSql4);

while($db2->next_record()) {
$gtophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db2->f("MEMBER_ID"));
$gtopscore = $db2->f("moyenne");
} // while resultats du top100
while($db3->next_record()) {
$ftophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db3->f("MEMBER_ID"));
$ftopscore = $db3->f("moyenne");
} // while résultats du top 10
// here treat the user having rank=$compteur

//*******************************************************************


$objtpl->set_var("score",$score);
$objtpl->set_var("gtopscore",$gtopscore);
$objtpl->set_var("ftophandle",$ftophandle);
$objtpl->set_var("ftopscore",$ftopscore);
$objtpl->set_var("gtophandle",$gtophandle);
$objtpl->set_var("allmemberrank",$db1->f("c"));
$objtpl->set_var("handle",$handle);
$objtpl->set_var("allgender",$allgender);
$objtpl->set_var("numvote",$numvote);
$objtpl->set_var("rank",$compteur);
$objtpl->set_var("visits",$visits);
$objtpl->parse("InfoShow",true);
$objtpl->parse("InfoShow2",true);
$objtpl->set_var("NoRecords","");

$intCounter++;

$objtpl->set_var("total",$total);
$objtpl->parse("GrandTotal",false);

} else { // user not found in votes
$objtpl->parse("NoRecords",false);
$objtpl->set_var("GrandTotal","");
$objtpl->set_var("InfoShow","");
$objtpl->set_var("InfoShow2",true);
} // if user found or not
} // if $result
else { // user not found in votes
$objtpl->parse("NoRecords",false);
$objtpl->set_var("GrandTotal","");
$objtpl->set_var("InfoShow","");
$objtpl->set_var("InfoShow2",true);
} // if not $result

$objtpl->parse("FormShow",false);

?>
By: kaeonthibe Date: 02/06/2003 20:15:00 English  Type : Comment
You said that you can generate the HTML file for me to get all what I need - That will be very appreciated if you can.
I will start a new question and give you points for this if you want.
Thanks,
Thierry
By: VGR Date: 02/06/2003 20:50:00 English  Type : Comment
it's not that easy. I don't at all understand where does this $objtpl come from, how you can write "InfoShow" code in the HTML page, how {rank} is linked to $objtpl->get_var("rank") etc

???? $objtpl->set_var
???? $objtpl->parse

seems to me it does a parsing-replace for the variables... Does it do anything else ?
By: kaeonthibe Date: 02/06/2003 20:58:00 English  Type : Comment
That's the file concoursdebeaute.php

<?php

$root_path = "../";
include($root_path . "common.php");
include($root_path . "header.php");


//start the session
session_start();

//Check for the last accessed.
i_am_there();


//If session object does not have any value then redirect to "member_login.php"
if(get_session("MEMBER_ID")=="")
{
header("Location:../home.php");
exit();
}

$intMemberId = get_session("MEMBER_ID");

//*****************************************

$strfile_name = $root_path."divertissement/concoursdebeaute.php";
//Create object of template
$objtpl = new Template($app_path);
//Load the template to be displayed
$objtpl->load_file($template_path . "concoursdebeaute.html" ,"main");
$objtpl->load_file($template_path . "header.html", "header");
//Load header
//Load footer
$objtpl->load_file($template_path ."footer.html","footer");
//set base href -
$objtpl->set_var("base_href", $sBaseHref);
//set var the file name in Form tag
$objtpl->set_var("FileName",$strfile_name);
$strErrorMessage = "";
$strAction=get_param("FormAction");
$strForm = get_param("FormName");


header_show(4);
Form_Show();

$objtpl->parse("header", false);
$objtpl->pparse("main", false);

//===============================
// Display Grid Form
//-------------------------------
function Form_Show()
{
//-------------------------------
// Initialize variables
//-------------------------------


global $objtpl;
global $db;
global $db1;
global $db2;
global $db3;
$sSQL = "";
$sFormTitle = "REFERALS";
$bEof = false;
$handle = "";
$gender = "";
$regdate = "";
$mematat = "";
$amt = "";
$memid = "";

$intCount = 1;

$db = mysql_connect('host','user','password');
mysql_select_db('user',$db);
// this script supposes that $db is compatible with the link resource ID returned by a mysql_connect('host','user','password); call
$intMemberId = $_SESSION['MEMBER_ID'];
//fire the sql query to get value to be shown on this page
$strSql = "SELECT * FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId;";
$result=mysql_query($strSql,$db);
if (mysql_numrows($result)>0) {
while(mysql_fetch_row($result))
{
print("</tr><tr>");
$strSql = "SELECT FORMAT(avg(MEMBER_RATING_VALUE),2) as a, COUNT(MEMBER_RATING_VALUE) as b, GENDER_ID FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId GROUP BY MEMBER_ID;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$memid = $intMemberId;
$score=$res['a'];
$numvote=$res['b'];
$genderid=$res['GENDER_ID'];
$strSql = "SELECT VIEWED_COUNT FROM rl_viewedmember WHERE MEMBER_ID = $memid;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$visits=$res['VIEWED_COUNT'];
$strSql = 'DROP TABLE IF EXISTS tempotop;';
$result=mysql_query($strSql,$db);
$strSql = 'CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ';
if($genderid==1)
{
$strSql = $strSql . "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
}
else
{
$strSql = $strSql . "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
}
$result=mysql_query($strSql,$db);
$strSql = 'SELECT * FROM tempotop;';
$result=mysql_query($strSql,$db);
$compteur=1;
$pastrouve=TRUE;
while (($pastrouve)AND($res=mysql_fetch_array($result))) {
$pastrouve=($res['MEMBER_ID'] == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
} // if found

$strSql2 = "SELECT COUNT(GENDER) as c FROM rl_member WHERE GENDER= $genderid";
$strSql2 = $strSql2 . " AND DISPLAYPHOTONUMBER != 0;";
$db1->query($strSql2);
$db1->next_record();
if($genderid==0)
{
$allgender="filles";
}
else
{
$allgender="gars";
}
$handle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $intMemberId);

$strSql3 ="select * FROM tempotop LIMIT 10;";
$db2->query($strSql3);

$strSql4 = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";
$db3->query($strSql4);

while($db2->next_record())
{
$gtophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db2->f("MEMBER_ID"));
$gtopscore = $db2->f("moyenne");
}
while($db3->next_record())
{
$ftophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db3->f("MEMBER_ID"));
$ftopscore = $db3->f("moyenne");
}
// here treat the user having rank=$compteur

//*******************************************************************


$objtpl->set_var("score",$score);
$objtpl->set_var("gtopscore",$gtopscore);
$objtpl->set_var("ftophandle",$ftophandle);
$objtpl->set_var("ftopscore",$ftopscore);
$objtpl->set_var("gtophandle",$gtophandle);
$objtpl->set_var("allmemberrank",$db1->f("c"));
$objtpl->set_var("handle",$handle);
$objtpl->set_var("allgender",$allgender);
$objtpl->set_var("numvote",$numvote);
$objtpl->set_var("rank",$compteur);
$objtpl->set_var("visits",$visits);
$objtpl->parse("InfoShow",true);
$objtpl->parse("InfoShow2",true);
$objtpl->set_var("NoRecords","");
}
}
else
{
$objtpl->parse("NoRecords",false);
$objtpl->set_var("GrandTotal","");
$objtpl->set_var("InfoShow","");
$objtpl->set_var("InfoShow2",true);
}
$objtpl->parse("FormShow",false);

}
?>
By: kaeonthibe Date: 02/06/2003 21:03:00 English  Type : Comment
I made a mistake
forgot the lines;
while(mysql_fetch_row($result))
{
print("</tr><tr>");
Thanks
Thierry
By: VGR Date: 02/06/2003 21:30:00 English  Type : Comment
I didn't apply some modifications done in my comment Date: 06/02/2003 01:04AM PDT
(especially $compteur no longer decremented,so that 0 indicates "not found in top" and 1..N is the rank otherwise)

I could take the HTML file and generate it differently from the way you do it now (with $objtpl etc) but I'm not sure that's what you want... Your code will look weird. It should be better to sort problems one by one.

So just tell me : what's left as wrong ? using the modified code given Date: 06/02/2003 01:04AM PDT ?

for the SELECT/dropdown, I suggest you pass the values array to set_var(), perhaps it'll work when doing $objtpl->parse().

où as-tu dégotté ce code à base d'$objtpl ?
By: kaeonthibe Date: 02/06/2003 22:26:00 French  Type : Comment
Sérieusement, c'est un script que j'ai acheté d'un programmeur... Il travaillait pour une compagnie, l'a laissé et vends des scripts et travaille en tant que FreeLancer maintenant. Puisque je travaillais à l'époque où je l'ai achété de sa part, je lui ai demandé d'y faire quelques modifications mais je comprenais pas pourquoi le programmeur était incapable de m'aider. Chaque fois que je lui demandais une petite question, une petite mod, il était incapable de le faire, je devais venir à chaque fois ici pour trouver solution à mon problème, étrange de la part du programmeur qui m'a vendu le script, alors j'ai décidé de le programmer et de le modifier moi-même... Assez simple mais compliqué lorsqu'il faut tout changer.
Là je ne comprends pas ce que tu me suggères de faire pour les set_var() et les $objtpl->parse().
Merci bien,
Thierry
By: VGR Date: 02/06/2003 22:36:00 French  Type : Comment
et c'est beaucoup de travail pour le transformer en "code normal" ?
By: kaeonthibe Date: 03/06/2003 23:11:00 French  Type : Comment
J'ai réussi à faire en sorte que les résultats défilent dans le tableau... Mais les données ne sont pas en ordre... depuis le début j'avais de la difficulté avec l'ordre des données, deux membres pouvaient avoir le même rang et le dernier était premier mélangeant alors je n'ai besoin d'aide que pour l'ordre... Voici le résultat du fichier concoursdebeaute.php que j'ai fait et qui fonctionne bien sauf un problème d'ordre : P.S. c'est une page qui montre le top du sexe de la personne.

<?php
/******************************************************************************
* Filename: referals.php
* Generated by Nirmal Jog
* build 14/10/2002
*********************************************************************************/

$root_path = "../";
include($root_path . "common.php");
include($root_path . "header.php");


//start the session
session_start();

//Check for the last accessed.
i_am_there();


//If session object does not have any value then redirect to "member_login.php"
if(get_session("MEMBER_ID")=="")
{
header("Location:../home.php");
exit();
}

$intMemberId = get_session("MEMBER_ID");

//*****************************************

$strfile_name = $root_path."divertissement/concoursdebeaute.php";
//Create object of template
$objtpl = new Template($app_path);
//Load the template to be displayed
$objtpl->load_file($template_path . "concoursdebeaute.html" ,"main");
$objtpl->load_file($template_path . "header.html", "header");
//Load header
//Load footer
$objtpl->load_file($template_path ."footer.html","footer");
//set base href -
$objtpl->set_var("base_href", $sBaseHref);
//set var the file name in Form tag
$objtpl->set_var("FileName",$strfile_name);
$strErrorMessage = "";
$strAction=get_param("FormAction");
$strForm = get_param("FormName");


header_show(3);
Form_Show();

$objtpl->parse("header", false);
$objtpl->pparse("main", false);

//===============================
// Display Grid Form
//-------------------------------
function Form_Show()
{
//-------------------------------
// Initialize variables
//-------------------------------


global $objtpl;
global $db;
global $db1;
global $db2;
global $db3;
global $db4;
global $db5;
global $db6;
global $db7;
$sSQL = "";
$sFormTitle = "REFERALS";
$bEof = false;
$handle = "";
$gender = "";
$regdate = "";
$mematat = "";
$amt = "";
$memid = "";
$gender=1;

$db = mysql_connect('host','user','password');
// this script supposes that $db is compatible with the link resource ID returned by a mysql_connect('host','user','password); call
$intMemberId = $_SESSION['MEMBER_ID'];
//fire the sql query to get value to be shown on this page
$strSql = "SELECT * FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId";
$result=mysql_query($strSql,$db);
if (mysql_numrows($result)>0) {
$strSql = "SELECT FORMAT(AVG(MEMBER_RATING_VALUE),2) as a, COUNT(MEMBER_RATING_VALUE) as b, GENDER_ID FROM rl_member_ratings WHERE MEMBER_ID = $intMemberId GROUP BY MEMBER_ID;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$memid = $intMemberId;
$score=$res['a'];
$numvote=$res['b'];
$genderid=$res['GENDER_ID'];
$strSql = "SELECT VIEWED_COUNT FROM rl_viewedmember WHERE MEMBER_ID = $memid;";
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$visits=$res['VIEWED_COUNT'];
$strSql = 'DROP TABLE IF EXISTS tempotop;';
$result=mysql_query($strSql,$db);
$strSql = 'CREATE TEMPORARY TABLE tempotop select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings ';
if($genderid==1) $strSql .= "WHERE GENDER_ID=1 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
else $strSql .= "WHERE GENDER_ID=0 GROUP BY MEMBER_ID ORDER BY moyenne DESC;";
$result=mysql_query($strSql,$db);
$strSql = 'SELECT * FROM tempotop;';
$result=mysql_query($strSql,$db);
$res=mysql_fetch_array($result);
$compteur=0; // sorry, can't start at 1 (it'll be the value when results set is empty !)
$pastrouve=TRUE;
while (($pastrouve)AND($res=mysql_fetch_array($result))) {
$pastrouve=($res['MEMBER_ID'] == $memid);
$compteur++;
} // while not found AND not exhausted results set

$strSql2 = "SELECT COUNT(GENDER) as c FROM rl_member WHERE GENDER= $genderid";
$strSql2 = $strSql2 . " AND DISPLAYPHOTONUMBER != 0;";
$db1->query($strSql2);
$db1->next_record();
if($genderid==0)
{
$allgender="filles";
}
else
{
$allgender="gars";
}
$handle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $intMemberId);
$intGender = dlookup("rl_member", "GENDER", "MEMBER_ID = " . $intMemberId);

$objtpl->set_var("score",$score);
$objtpl->set_var("allmemberrank",$db1->f("c"));
$objtpl->set_var("handle",$handle);
$objtpl->set_var("allgender",$allgender);
$objtpl->set_var("numvote",$numvote);
$objtpl->set_var("rank",$compteur);
$objtpl->set_var("visits",$visits);
$objtpl->parse("InfoShow",true);

$strSql7 = "select * FROM rl_member_ratings WHERE GENDER_ID = $intGender";

$db6->query($strSql7);

while($db6->next_record())
{
$strSql7 = "select FORMAT(AVG(MEMBER_RATING_VALUE),2) as moyenne, MEMBER_ID FROM rl_member_ratings WHERE GENDER_ID= $intGender GROUP BY MEMBER_ID ORDER BY moyenne DESC LIMIT 10";

$db5->query($strSql7);

while($db5->next_record())
{
$topscore = $db5->f("moyenne");
}
if($intGender==1)
{
$color = "#3366ff";
$colorborder = "#3366cc";
}
else
{
$color = "#FF6666";
$colorborder = "#ff6666";
}
$tophandle = dlookup("rl_member", "HANDLE", "MEMBER_ID = " . $db6->f("MEMBER_ID"));
$memberid = $db6->f("MEMBER_ID");
$photo = dlookup("rl_manage_photos", "PHOTO_NAME", "MEMBER_ID = " . $db6->f("MEMBER_ID"));
$objtpl->set_var("colorborder", $colorborder);
$objtpl->set_var("color", $color);
$objtpl->set_var("memberid", $memberid);
$objtpl->set_var("photo", $photo);
$objtpl->set_var("topscore",$topscore);
$objtpl->set_var("tophandle",$tophandle);
$objtpl->parse("InfoShow2",true);
// here treat the user having rank=$compteur

//*******************************************************************

$objtpl->set_var("NoRecords","");
}
}
else
{
$objtpl->parse("NoRecords",false);
$objtpl->set_var("GrandTotal","");
$objtpl->set_var("InfoShow","");
$objtpl->parse("InfoShow2",true);
}
$objtpl->parse("FormShow",false);

}
?>
By: VGR Date: 04/06/2003 00:06:00 French  Type : Comment
tempotop contient uniquement le bon GENDER_ID et est ordonné selon "moyenne DESC"

Il suffit d'en prendre les 10 premiers éléments pour avoir le top10, les 100 premiers pour avoir le top100 etc

Deux membres se trouvant dans tempotop ne peuvent pas avoir le même rang si on utilise ce que j'avais suggéré Date: 06/01/2003 02:04PM PDT :

$strSql = 'SELECT * FROM tempotop;';
$result=mysql_query($strSql,$db) or die(mysql_error());
$compteur=0;
$pastrouve=TRUE;
while (($pastrouve)AND($res=mysql_fetch_array($result))) {
$pastrouve=($res['MEMBER_ID'] == $memid);
$compteur++;
} // while not found AND not exhausted results set
if (!$pastrouve) { // ok, found
$compteur--;
// the rank found is $compteur
} else {
// not found in ranking, theoretically impossible !?!
die('Aberration : user not found in top rankings.');
} // if found
} else { // user not found
die("user $intMemberId not found !");
}
// here treat the user having rank=$compteur


il est ***évident*** que deux personnes ne peuvent pas avoir le même rang car elles n'ont pas le même MEMBER_ID. Donc elles sont trouvées avec deux valeurs différentes de $compteur, même si leur "moyenne" est identique.

Cordialement,

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 564.802890 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page