Query impossibile :S

[lucif3r]

Ciao a tutti, rieccomi con qualche strano quesito

Se c'era il forum "Logica" postavo li' invece che in php...
Cmq...sono due ore che penso e ripenso :berto: a come fare una query ma proprio non ne vengo a capo...

In pratica mi sono creato uno script che prende le variabili dei giochi flash e mi salva i record sul database, da li' li prendo, li ordino anche in base ad altre tabelle e mi creo la classifica dei punteggi per ogni singolo gioco.
La tabella che salva i punteggi è cosi' composta:
id | gameid | userid |score
tramite il campo gameid metto in relazione i dati della tabella games(che contiene i dati dei giochi) e tramite userid mi ricavo i dati dell'utente per ogni singolo punteggio record.
Il campo score,ovviamente è il punteggio.
Quindi con la tabella dei punteggi relazionata alle altre due mi ricavo le classifiche,e ovviamente in base al gameid mostro la classifica relativa al gioco della pagina corrente,del tipo:

1. Pippo 2300punti
2. Pluto 1680punti
3. etc.etc.

Ora vorrei fare una classifica generale...e qui IL PANICOOOO! VVoVe:

Vorrei assegnare,per ogni gioco,dei punti agli utenti in base alla posizione in classifica.
Esempio:

Gioco A
1. classificato 10 punti
2. " 9 punti
e via dicendo fino al 10° che becca un punto.

Il tutto in tempo reale,quindi non è che assegno i punti nel momento che l'utente fa' il record,ma in base alla posizione in classifica nel momento che viene generata la classifica generale.
Quindi al volo dovrei controllare tutti i giochi dove l'utente è in classifica,assegnargli dei punti in base alla posizione di ogni singolo gioco,fare la somma dei punti guadagnati in ogni singolo gioco e in base a quella piazzarlo al giusto posto nella classifica generale.
Questo ovviamente per tutti gli utenti...non so' se esploderà il server...ma al momento mi stà per esplodere la testa!

Ho pensato a qualche mega query incasinatissima ma non mi viene in mente proprio niente....ho pensato a 400 cicli while nidificati...ma quasi svengo...e più che altro il problema è che non mi viene in mente come fare proprio a livello logico...ho provato anche a farmi un disegno ma niente.

Se qualcuno mi illuminasse anche solamente a parole poi il codice non è un problema...
...
...
...un aiutino?

[daniele_dll]

ehm, usare un bel group by? con magari delle join?

per fare la classifica generale, quindi globale per tutti i giochi, puoi usare il group by e come campo utilizzi l'id utente

a questo punto tra i campi che estrai usi un bel SUM sulla colonna del punteggio e ti viene fuori il punteggio complessivo per quell'utente specifico ... poi li ordini sempre in base al valore estratto (nun zo se lo puoi fare, cmq prova ^^) e quindi con la query hai il tutto ^^

Qui trovi come usare le group by
http://dev.mysql.com/doc/refman/5.0/en/select.html

Qui invece ulteriori dettagli
http://dev.mysql.com/doc/refman/5.0/...imization.html
http://dev.mysql.com/doc/refman/5.0/...functions.html

Alternativamente qui fa con una subquery
http://dev.mysql.com/doc/refman/5.0/...group-row.html

(il procedimento è come quello che serve a te)

comunque, se risolvi con la group by è meglio

[lucif3r]

eeeehhhh fosse così facile
Il problema è che io non devo sommare i punteggi già esistenti nella tabella dei punteggi,ma assegnare ad esempio 10 punti per ogni gioco nel quale l'utente è primo in classifica,e poooii sommare questi di punti.

Io avevo pensato ad un ragionamento del genere:

Cerco tutti i punteggi del singolo utente e da qui ricavo i giochi nei quali è presente in classifica

creo una variabile X=0 e ciclo i giochi ottenuti estraendone le prime 10 righe senza limitare i campi in base all'utente

creo una variabile Y con valore 10
e Una variabile Z=0
ciclo le 10 righe ordinate dal primo al decimo post
diminuisco la Y di 1 ad ogni ciclo e faccio in modo con una condizione,che se la riga che stà controllando appartiene all'utente aggiunge il valore di Y a Z
man mano che cicla i giochi aggiungo il valore di Z a X

e in questo modo ottengo il punteggio globale......ma è troppo bello per essere vero...perchè in questa maniera si trovo il punteggio che mi serve per ogni singolo utente....ma poi come cavolo faccio ad ordinarli? è impossibile...quindi devo fare tutta questa storia con una megaquery

[ciro78]

sicuramente per estrarre le singole classifiche utilizzerai una condizione where sull'id del gioco.

quando effettui il ciclo puoi fare una cosa del genere

$indice = 10
//inizio ciclo
if($indice >0)
{
$genarle[nomeutente] += $indice;
$indice = $indice - 1;
}

//fine ciclo 

questo per ogni ciclo

[lucif3r]

Si è quello che avevo pensato di fare con la serie di cicli di cui sopra,
infatti in questo modo,ciclando tutti i giochi nei quali è presente l'utente incrementando l'indice e ad ogni ciclo e incrementando di volta in volta un indice superiore con i risultati del primo indice riuscirei ad avere i punteggi per la classifica generale ma il problema è sempre lo stesso,come faccio ad ordinarli? I punteggi della classifica generale non vanno inseriti in un ulteriore tabella,ma generati on the fly di volta in volta,perchè esempio 1000 utenti stanno giocando, la classifica generale viene sconvolta ogni istante....certo potrei creare una sorta di tabella per la classifica generale che viene aggiornata di volta in volta...ma sinceramente mi sembra proprio poco professionale

[daniele_dll]

quindi se l'utente è primo in classifica devi assegnare 10 punti

allora, scusa, ancora più semplice ... fai una query dove imposti l'ordinamento prima sul game id e poi sull'user id cosi da ottenere un elenco ordinato dei valori per gioco e per giocatore, dopo di che assegni i punti ai primi 10 risultati scartando gli altri fin quando il gameid non cambia

altrimenti dovresti scrivere una stored procedure per fare le operazioni, ma alla fin fine penso che anche in questo modo non dovresti avere problemi

$scoreboard = array();
previousGameId = 0;
$pointsToAssign = 10;

while($row = mysql_fetch_object($query))
{
    // Confronti il valore di controllo con il game id della riga
    if ($previousGameId != $row->game_id)
    {
        // Aggiorni il valore di controllo
        $previousGameId = $row->game_id;
        
        // Reimposti i punti da assegnare
        $pointsToAssign = 10;
    }
    else if ($pointsToAssign == 0)
    {
        // Passi all'elemento successivo
        continue;
    }
    
    // Assegni il punteggio
    $scoreboard[$row->user_id] += $pointsToAssign;
    
    // Decrementi il punteggio da assegnare
    $pointsToAssign--;
}

Probabilmente c'è una soluzione molto più efficente usando le subquery però sinceramente non ho la testa di sbatterci contro per adesso

PS: il valore, invece di generarlo a ogni accesso, cosa che potrebbe diventare onerosa se sono presenti troppi risultati, lo generi, per esempio, ogni 5 minuti e ogni volta che lo generi serializzi la scoreboard e la metti sul db insieme al timestamp dell'ultimo aggiornamento

[lucif3r]

Allora, alla fine sono stato costretto ad utilizzare un ulteriore tabella per contenere i dati della classfica generale.
Per ricavarmi questi dai ho fatto così:

<?
 $users = mysql_query("SELECT * FROM scores GROUP BY userid");
 while($usersresult = mysql_fetch_array($users)){ 
 $query = mysql_query("SELECT * FROM scores GROUP BY gameid");
 $points = 0;
  while($result = mysql_fetch_array($query)){
   $primo = mysql_query("SELECT * FROM scores WHERE gameid = '".$result['gameid']."' ORDER BY score DESC LIMIT 5");
   $total = 0;
   $single = 5;
   while($rprimo = mysql_fetch_array($primo)){
    echo "Gioco:" . $rprimo['game_name'] . " Utente:" . $rprimo['userid'] . " Punteggio:" . $rprimo['score'] . "
";
    if ($rprimo['userid'] == $usersresult['userid']){
    $total = ($total + $single);
    }
    $single = ($single - 1);
   }
   echo $total . "
";
   $points = ($points + $total);
 }
 echo $points . "
";
 $check = mysql_query("SELECT * FROM totalscores WHERE userid = '" . $usersresult['userid'] . "'");
 if (mysql_num_rows($check) == 1){
 $checkresult = mysql_fetch_array($check);
 mysql_query("UPDATE totalscores SET points ='" . $points . "' WHERE userid ='" . $usersresult['userid'] . "'");
 echo "vecchio";
 }else{
 mysql_query("INSERT INTO totalscores (userid, points) VALUES ('".$usersresult['userid']."','".$points."')");
 echo "nuovo";
 }
 }
 ?>

Ma in questo modo sono costretto a generare la classifica ogni 15 o 30 minuti con un cronjob, o altrimenti ipotizzando 5000 utenti registrati e 3000 giochi inseriti,il server espode se la genero in tempo reale...

Nessuno avrebbe una soluzioncina migliore che mi permetta di generarla in tempo reale?

P.S. non fate caso ai vari echo,mi servivano solo per capire cosa stava succedendo mentre scriptavo

[daniele_dll]

ma, hai letto la mia risposta?

penso faccia tutto quello che serve e sia infinitamente più leggera della tua soluzione e dovresti poterla usare in tempo reale (il server non esplode, ma comunque è una bad praticies fare una cosa del genere in tempo reale non riuscendo ad ottimizzarla al meglio)

[lucif3r]

Originariamente inviato da daniele_dll
quindi se l'utente è primo in classifica devi assegnare 10 punti

allora, scusa, ancora più semplice ... fai una query dove imposti l'ordinamento prima sul game id e poi sull'user id cosi da ottenere un elenco ordinato dei valori per gioco e per giocatore, dopo di che assegni i punti ai primi 10 risultati scartando gli altri fin quando il gameid non cambia

Sinceramente non mi è molto chiara la tua procedura...se ordino i dati in questa maniera come faccio a sapere in che posizione sta' un user in un gioco?
Credo che per forza di cose devo ordinare prima per gameid e poi per score per avere un elenco ordinato:

Gioco 1 ->1 Posto
->2 Posto
->3 Posto

Gioco 2 ->1 Posto
->2 Posto
->3 Posto

e così via...altrimenti come determino i punti da assegnare?

Scusa eh...solitamente sono molto sveglio...ma su questa cosa "so' de coccio"

[daniele_dll]

si, hai ragione, va fatto l'ordinamento per lo score non per l'user ^^

scusami, e che ho scritto di fretta (come sempre, asd) e non ho ricontrollato più di tanto

cmq, la procedura, a parte l'ordinamento, dovrebbe essere quella

considera che con questo sistema invece di fare delle ipotetiche 1 + 5000 + 15000 + 5000 + 5000 = 30001 query ne lanceresti 1 + (5000/500) = 11 query

le ultime 5000/500 rappresentano le query per l'aggiornamento della scoreboard che puoi raggruppare usando una INSERT ... ON DUPLICATES! Ovvero qualcosa tipo ...

INSERT INTO
    totalscores

(
    user_id,
    points
)

VALUES
(
    1,
    100
),
(
    2,
    100
)
.
.
.

ON DUPLICATE KEY UPDATE
    points = VALUES(points)

ovviamente il campo user_id lo fai una primary key NON autoincrementante

i vari values li costruisci con un secondo ciclo, qualcosa tipo

array_push($scoreboard, false);
$executeQuery = false;
$valuesToInsert = array();
$valuesToInsertCounter = 0;

foreach($scoreboard as $userId => $points)
{
    if ($points === false)
    {
        $executeQuery = true;
    }
    else
    {
        $valuesToInsert[] = sprintf('(%d, %d)', $userId, $points);
        $valuesToInsertCounter++;
        
        if ($valuesToInsertCounter >= 500)
        {
            $executeQuery = true;
            $valuesToInsertCounter = 0;
        }
    }
    
    if ($executeQuery === true)
    {
        $executeQuery = false;
        
        mysql_query(sprintf('
            INSERT INTO
                totalscores
            
            (
                userid,
                points
            )
            
            VALUES
            %s
            
            ON DUPLICATE KEY UPDATE
                points = VALUES(points)',
            
            implode(', ', $valuesToInsert));
            
            $valuesToInsert = array();
    }
}

unset($valuesToInsert);

il codice provalo, non dovrebbero esserci errori gravi ... l'ho scritto qui stesso senza testarlo ... comunque a livello logico dovrebbe essere ok

cambiando il valore da 500 a valori più piccoli non conviene e a valori troppo alti nemmeno! considera che tutto quello che puoi recuperare, con 500 inserimenti/aggiornamenti per volta, già l'ho recuperi, però se lo aumenti mysql deve fare meno lavoro

in aggiunta potresti fare una transazione, non tanto per eventuali problemi con i valori d'uscita, ma perché mysql effettua le operazioni di scrittura alla fine ottimizzando ancora di più il tutto!