[MySQL] Ordinamento a seguito di sottrazione? (from...order by...)

oslino

[MySQL] Ordinamento a seguito di sottrazione? (from...order by...)

Ciao,
ho una singola query che prende i valori da 3 tabelle. In una di queste tabelle ci sono 2 campi che riportano i voti positivi e quelli negativi. Dovrei estrarne una classifica dei più votati e ovviamente la classifica deve essere fatta sottraendo i voti negativi dal totale di quelli positivi.

Al momento la classifica viene stampata tramite "ORDER BY votipositivi DESC LIMIT 3" ma è ovvio che l'utente con 20 voti positivi e 1 negativo è migliore di quello con 25 positivi e 10 negativi, per cui l'ordinamento attuale non va bene.

Non ho idea però, sempre se sia possibile, di come strutturare una query che mi ordini i risultati DOPO aver fatto le dovute sottrazioni, anche con eventuali valori negativi.

Mi sapete aiutare?

pietror

so che si possono sottrarre delle colonne ma valori di righe diverse non saprei.
Non ti converrebbe estrarre tutti gli elementi con lo stesso ID, fare l'operazione da php e successivamente visualizzare i risultati ordinati?

oslino

@PietroR said:

so che si possono sottrarre delle colonne ma valori di righe diverse non saprei.
Non ti converrebbe estrarre tutti gli elementi con lo stesso ID, fare l'operazione da php e successivamente visualizzare i risultati ordinati?

Eh... potrei... ma come?
Ora il codice è questo

[PHP][...] ORDER BY votipositivi DESC LIMIT 3";

$query_result = mysqli_query($cnt, $query_select);

while( $estrai = mysqli_fetch_assoc($query_result))
{

echo $estrai['nome'] . ' ';
echo $estrai['cognome'] . ' ';
echo $estrai['votipositivi'] . ' ';
echo $estrai['votinegativi'] . ' ';
}
[/PHP]
e ovviamente me li stampa in sequenza esattamente come riportati dalla query. Al momento non ho idea di come potrei riorganizzarli in PHP...

thedarkita

Ciao,
cosi dovrebbe andare bene:

SEELCT *, (votipositivi/(votipositivi+votinegativi)) AS percentuale_votipositivi FROM tabella ORDER BY percentuale_votipositivi DESC LIMIT 3

oslino

@Thedarkita said:

Ciao,
cosi dovrebbe andare bene:

SEELCT *, (votipositivi/(votipositivi+votinegativi)) AS percentuale_votipositivi FROM tabella ORDER BY percentuale_votipositivi DESC LIMIT 3

Con questo metodo mi dà 2 tipi di errore:
[PHP]query = "SELECT nome, cognome, messaggio, destinatario, (votipositivi/(votipositivi+votinegativi)) AS percentuale_votipositivi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY percentuale_votipositivi DESC limit 3";
[...]

$query_result = mysqli_query($cnt, $query_select);

while( $estrai = mysqli_fetch_assoc($query_result))
{

echo $estrai['nome'] . ' ';
echo $estrai['cognome'] . ' ';
echo $estrai['votipositivi'] . ' '; NOTICE: undefined index votipositivi
echo $estrai['percentuale _votipositivi'] . ' '; // risultato 0.8000 anzichè il numero di voti
} [/PHP]

inoltre
persona A voti reali +8 -2
persona B voti reali +3 -0
persona C voti reali +1 -0
perdona D voti reali +4 -1

e dovrebbe restituirmeli in ordine A, B (o D), C mentre l'ordine restituito, con la formula di percentuale, è B - C - A :bho:

thedarkita

Allora una soluzione del tipo votipositivi-votinegativi dovrebbe essere migliore:


SELECT nome, cognome, messaggio, destinatario, (votipositivi-votinegativi) AS percentuale_votipositivi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY percentuale_votipositivi DESC limit 3

oslino

@Thedarkita said:

Allora una soluzione del tipo votipositivi-votinegativi dovrebbe essere migliore:

>
SELECT nome, cognome, messaggio, destinatario, (votipositivi-votinegativi) AS percentuale_votipositivi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY percentuale_votipositivi DESC limit 3

>```
La soluzione è buona per l'ordinamento, ma non per la stampa dei voti reali :gtsad:
Ill codice che segue la query deve mostrare anche i voti totali sia positivi che negativi

while( $estrai = mysqli_fetch_assoc($query_result))
{
echo $estrai['nome'] . ' ' . $estrai['cognome'] . ' voti + ' . $estrai['votipositivi'] . ' -' . $estrai['votinegativi'];
}

Del tipo:
pinco pallo voti +8 -2
Wonder Woman voti  +4 -1
Pippo Pluto voti +3 -0
Super Pippo voti  +1 -0

mente con la tua query me li ordina giusti ma mi stampa 

pinco pallo: +6 -2
Wonder Woman:  +3 -1
Pippo Pluto: +3 -0
Super Pippo:  +1 -0

in pratica mi stampa il risultato della sottrazione anzichè il voto reale. I voti negativi sono ok perchè sono presi direttamente dalla tabella senza manipolazioni... ma il passo successivo sarà quello della lista "dei peggiori" dove ci troveremo davanti allo stesso problema ma a parti invertite...

thedarkita

Con la query che ho scritto io è impossibile, forse cambi tu il nome dopo AS e sovrascrivi il valore.

oslino

@Thedarkita said:

Con la query che ho scritto io è impossibile, forse cambi tu il nome dopo AS e sovrascrivi il valore.

Avevamo mezza ragione entrambi.
Query reale:

[PHP]$query = "SELECT nome, cognome, messaggio, destinatario, votipositivi, votinegativi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY votipositivi DESC limit 3';[/PHP]

la tua query invece

[PHP]$query = 'SELECT nome, cognome, messaggio, destinatario, (votipositivi - votinegativi) AS percentuale_votipositivi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY percentuale_votipositivi DESC limit 3';

while( $estrai = mysqli_fetch_assoc($query)){
echo $estrai['votipositivi']; // ERRORE: l'indice 'votipositivi' non esiste più perchè sostituito da percentuale_votipositivi.
echo $estrai['percentuale_votipositivi']; // OK, ma numero sbagliato dato dal risultato della sottrazione[/PHP]

La soluzione è stata ripetere i due campi

[PHP]$query = 'SELECT nome, cognome, messaggio, destinatario, (votipositivi - votinegativi) AS percentuale_votipositivi, votipositivi, votinegativi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY percentuale_votipositivi DESC limit 3';[/PHP]

che sembra dia il giusto ordinamento e i giusti voti!

oslino

Purtroppo devo ricredermi... non va.

La query è sempra la solita
[PHP]$query = 'SELECT nome, cognome, messaggio, destinatario, (votipositivi - votinegativi) AS percentuale_votipositivi, votipositivi, votinegativi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY percentuale_votipositivi DESC limit 3';

$query_result = mysqli_query($cnt, $query);
[/PHP]
Come si vede è una semplice estrazione di valori. Entrambi i campi riportano valori positivi ma la sottrazione può non esserlo.
Infatti il problema nasce quando i valori negativi sono superiori ai positivi. L'espressione deve fare un calcolo con risultato negativo (es: 5-7 = -2 ) e mi dà questo errore su MySQL 5.5:

Warning: mysqli_query(): (22003/1690): BIGINT UNSIGNED value is out of range in '(mio_db.testo.votipositivi - mio_db.testo.votinegativi)' in F:\xampp1.8.3\htdocs\www\inc\migliori.inc.php on line 4

La riga 4 è quella che materialmente fa la query $query_result = mysqli_query($cnt, $query)

Il secondo problema è con MySQL 5.1: il problema precedente non me lo dà, ma in compenso mi restituisce questo strano ordinamento dei migliori voti (secondo lui)

A voti +0 -1
B voti +0 -3
C voti +8 -2

non riesco proprio a capirne la logica nè nel primo caso ma soprattutto nel secondo... mi sto demoralizzando...

EDIT:
C'è anche un altro errore di cui non mi ero accorto
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in F:\xampp1.8.3\htdocs\www\inc\migliori.inc.php on line 7
che equivale alla riga
[PHP]
while( $estrai = mysqli_fetch_assoc($query_result)){
[/PHP]

oslino

Aggiorno questo post perchè credo di essere arrivato alla giusta soluzione!

L'errore
"Warning: mysqli_query(): (22003/1690): BIGINT UNSIGNED value is out of range in '(mio_db.testo.votipositivi - mio_db.testo.votinegativi)' in F:\xampp1.8.3\htdocs\www\inc\migliori.inc.php on line 4"

l'ho risolto eliminando l'attibuto "unsigned" dal campo della tabella;

Il corretto ordinamento dell'array l'ho ottenuto mediate una semplice modifica della query
[PHP]
$query = "SELECT nome, cognome, messaggio, destinatario, votipositivi, votinegativi FROM mittenti, testo, destinatari WHERE id_mittente.mittenti = id_mittente.testo AND id_destinatario.destinatari = id_messaggio.testo ORDER BY (votipositivi - votinegativi) DESC limit 3';
[/PHP]

Sembra che tutto funzioni correttamente sia su MySQL 5.1 che 5.5