contare e sommare dopo JOIN

giancarlo75

Nuovo Utente
10 Ago 2007
10
0
1
Ho due tabelle, una contiene un elenco di cd, l'altra i voti dati ai cd. Per ogni cd io vorrei contare il numero dei voti e sapere la loro somma. In base a questi risultati voglio, per esempio, selezionare solo i cd che hanno un numero di voti non superiore a 100; oppure selezionare solo i cd la cui media dei voti (somma totale/numero) sia superiore a 5.
Ho abbozzato una query così:

SELECT DISTINCT * FROM elenco_cd el_cd
INNER JOIN voti_cd vo_cd
ON vo_cd.id_cd = el_cd.id

dove per ogni cd vengono collegati i relativi voti, in base all'id del cd, ma non so come contarli e sommarli per scrivere la WHERE.
 

borgo italia

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
4 Feb 2008
16.046
150
63
PR
www.borgo-italia.it
ciao, non so se potrò aituarti, ma ritengo che per risolvere il tuo problema debba indicare come sono strutturate/organizzate le due tabelle
 

giancarlo75

Nuovo Utente
10 Ago 2007
10
0
1
Ecco la struttura delle tabelle, non sono tutte ma solo le tre che interessano quello che voglio fare:
Tabella cd
Codice:
CREATE TABLE IF NOT EXISTS `cd` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `titolo` varchar(255) NOT NULL,
  `anno` year(4) NOT NULL,
  `note` tinytext,
  `commento` mediumtext,
  `supporto` enum('cd','vinile','musicassetta','dat','mp3','cd masterizzato','dvd','vhs','altro') NOT NULL DEFAULT 'cd',
  `tip_inc` enum('DDD','ADD','AAD','33 giri','45 giri','78 giri','altro') NOT NULL DEFAULT 'altro',
  `qualita` enum('non specificata','scadente','discreta','buona','ottima') NOT NULL DEFAULT 'non specificata',
  `giudizio` enum('vuoto','insufficiente','sufficiente','discreto','buono','ottimo','eccellente') NOT NULL DEFAULT 'vuoto',
  `durata_cd` time DEFAULT NULL,
  `data_registrazione` date NOT NULL,
  `prestito` varchar(255) DEFAULT NULL,
  `in_prestito` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=121 ;

Tabella users
Codice:
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `vis_mail` enum('0','1') COLLATE utf8_bin NOT NULL DEFAULT '1',
  `passwd` varchar(32) COLLATE utf8_bin NOT NULL,
  `nickname` varchar(255) COLLATE utf8_bin NOT NULL,
  `grado` enum('1','2','3') COLLATE utf8_bin NOT NULL DEFAULT '1',
  `info` text COLLATE utf8_bin NOT NULL,
  `codice_attivazione` varchar(32) COLLATE utf8_bin NOT NULL,
  `attivazione` enum('true','false') COLLATE utf8_bin NOT NULL DEFAULT 'false',
  `data_creazione` datetime NOT NULL,
  `ip` varchar(255) COLLATE utf8_bin NOT NULL,
  `host` varchar(255) COLLATE utf8_bin NOT NULL,
  `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_email` (`email`),
  UNIQUE KEY `nickname` (`nickname`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=40 ;

Tabella giudizio_users
Codice:
CREATE TABLE IF NOT EXISTS `giudizio_users` (
  `id_cd` int(11) unsigned NOT NULL,
  `id_users` int(11) unsigned NOT NULL,
  `giudizio_utente` enum('vuoto','insufficiente','sufficiente','discreto','buono','ottimo','eccellente') COLLATE utf8_bin NOT NULL,
  `data_giudizio` datetime NOT NULL,
  UNIQUE KEY `id_cd` (`id_cd`,`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Sono abbastanza lunghe ma quello che importa sono i campi id.
Praticamente tramite sessione controllo l'id dell'utente, che risiede nella tabella users, e così lo metto in condizioni di votare il cd che vuole. Quando vota, si inserisce un record nella tabella giudizio_users che mi indica l'id del cd, l'id di chi ha votato, il suo giudizio e quando ha votato.

Ora io voglio creare un motore di ricerca che permetta di filtrare i risultati sia a seconda di come l'utente ha votato, sia secondo la media dei voti. Devo quindi scorrere tutti i cd e per ogni cd calcolare la media dei voti per selezionare solo quelli con una certa media.

Per esempio dopo
Codice:
SELECT DISTINCT * FROM cd c
INNER JOIN giudizio_users giu_us
ON giu_us.id_cd = c.id
dovrei calcolarmi il numero delle righe di giudizio_users che corrispondono al cd in esame, calcolarmi la somma totale dei campi giudizio_utente e poi fare la media. Questo per ragionare sul totale dei giudizi, mentre ragionando sul giudizio individuale dell'utente, dopo:
Codice:
SELECT DISTINCT * FROM cd c
INNER JOIN giudizio_users giu_us
ON giu_us.id_cd = c.id AND giu_us.id_users = ."$_SESSION[id_user]".
vorrei vedere se l'utente ha votato o meno, ovvero se esiste una riga della tabella giudizio_users che soddisfa sia l'id del cd, sia l'id dell'utente.
Per tutte queste cose non ho la più pallida idea di come fare.
 
Ultima modifica:

borgo italia

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
4 Feb 2008
16.046
150
63
PR
www.borgo-italia.it
ciao
prima di mettere le mani al codiche o alle select vorrei fare una piccola osservazione

...secondo la media dei voti...

puoi contare il totele di voto dati al cd (e/o al cd da un utente), ma la media è un po difficele da ottenere perchè i valori di giudizio che dai (vuoto, insufficiente...) non sono numeri e quindi di brutto non puoi fare

es. se il totale dei voti è 2
(insufficiente + eccellente)/(2) = discreta
ma assegnare un numero ad ogni giudizio
vuoto = 0
insufficiente = 1
....
eccellente= 6

al che la media di prima diventa

(1+6)/(2) = 7/2 = 3.5
 

giancarlo75

Nuovo Utente
10 Ago 2007
10
0
1
Quello della media in realtà è il problema minore, perchè siccome riesco ad ottenerla con PHP, utilizzando una switch che attribuisce un numero ad ogni valore di enum, potrei creare un campo nella tabella cd che contiene già la media dei voti di quel cd; campo che ovviamente viene aggiornato ogni volta che un utente da un nuovo voto al cd.
Stessa cosa vale per il totale dei voti dati al cd, che potrei memorizzare in un campo della tabella cd, per semplificare le cose.
Per esempio, al fine di visualizzare la media ed il totale dei voti, ho già scritto codice php:
Codice:
// GIUDIZIO DEGLI UTENTI	
	
	$query_giudizi = "SELECT * FROM giudizio_users WHERE id_cd = '".$id."'";
	$results_giudizi = mysql_query($query_giudizi) or die (mysql_error());
	$numero_giudizi=0;
	$somma_giudizi=0;
	while ($rows_giudizi=mysql_fetch_array($results_giudizi)) {
		extract($rows_giudizi);
		switch ($giudizio_utente) {
		case "insufficiente":
			$giudizio_utente=-2;
		break;
		case "sufficiente":
			$giudizio_utente=1;
		break;
		case "discreto":
			$giudizio_utente=2;
		break;
		case "buono":
			$giudizio_utente=3;
		break;
		case "ottimo":
			$giudizio_utente=4;
		break;
		case "eccellente":
			$giudizio_utente=5;
		break;
		}
		if($giudizio_utente!="vuoto") $numero_giudizi++;
		if($giudizio_utente!="vuoto") $somma_giudizi = $somma_giudizi + $giudizio_utente;
	}
	if ($numero_giudizi!=0) {
		$giudizio_utenti = floor($somma_giudizi / $numero_giudizi);
		if ($giudizio_utenti==0) {
			$giudizio_utenti=100;
		}
	} else {
		$giudizio_utenti = "vuoto";
	}
	if($giudizio_utenti<0) {
		echo "<tr><td>giudizio degli utenti: <img src='negativo.gif'></td></tr>";
	}
	if($giudizio_utenti!=vuoto&&$giudizio_utenti>0&&$giudizio_utenti!=100) {
	echo "<tr><td>giudizio degli utenti: ";
		for ($i=1;$i<=$giudizio_utenti;$i++) {
		echo "<img src='positivo.gif'>";
		}
	echo "</td></tr>";
	}
	if($giudizio_utenti!=vuoto&&$giudizio_utenti==100) {
		echo "<tr><td>giudizio degli utenti: <img src='positivo.gif'></td></tr>";
	}
	
// FINE GIUDIZIO DEGLI UTENTI
Basterebbe memorizzare le variabili $numero_giudizi e $giudizio_utenti nel database, nella tabella cd, ogni volta che un nuovo giudizio viene espresso.

Il problema principale è invece verificare se l'utente ha votato o no, ovvero selezionare solo i cd dove non ha votato, ovvero dove non c'è una riga della tabella giudizio_users dove coincidono sia l'id del cd, sia l'id dell'utente.
 
Ultima modifica:

borgo italia

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
4 Feb 2008
16.046
150
63
PR
www.borgo-italia.it
ciao
intanto ti invio una traccia per relizzare il filtro, spero di aver capito quello che intendi fare,
considera che è solo una traccia, anche perche al posto degli id, per una migliore comprensione dell'uotput,
è meglio il nik dell'utente ed il nome del cd, che comunque puoi ricavare da apposite select.
ho presupposto che per effettuare la ricerca tu invii i dati da un form

PHP:
<?php
$id_utente=$_POST['id_utente'];
$id_cd=$_POST['id_cd'];
if($id_utente=="" && $id_cd==""){
	echo "non possono essere entrambi vuoti";
//reindirizzo al form
}elseif($id_utente!="" && $id_cd==""){//voti di un utente ai vari cd
	$c=1
	$condizione=" WHERE id_users='".$id_utente."' ";
	$messaggio0="l'utente $id_user non ha espresso alcun voto";
	$messaggio1="l'utente $id_user ha espresso i seguenti voti:<br>";
	$messaggio2="l'utente ha giudicato i CD: ";
}elseif($id_utente=="" && $id_cd!=""){//voto di un cd da vari utenti
	$c=2;
	$condizione=" WHERE id_cd='".$id_cd."' ";
	$messaggio0="il CD $id_user non ha ricevuto alcun voto";
	$messaggio1="il CD $id_user ha ricevuto i seguenti voti:<br>"
	$messaggio2="il CD è stato giudicato dagli utenti: ";
	
}else{//voto di un cd dato da un utente
	$c=3;
	$condizione=" WHERE id_users='".$id_utente."' AND id_cd='".$id_cd."' ";
	$messaggio0="l'utente $id_user non ha dato alcun voto al CD $id_cd";
	$messaggio1="l'utente $id_user ha dato il seguente voto al CD $id_cd :";
}

$query1="SELECT * FROM giudizio_users ".$condizione;
$ha_votato=mysql_num_rows(mysql_query($query1));
if($ha_votato==0){
	echo $messaggio0;
}else{
	echo $messaggio1;
	$voto_somma=0;
	while($riga=mysql=fetch_array($query1)){
		$cd=$riga['id_cd'];
		$user=$riga['id_users']:
		$voto=$riga['giudizio_utente'];
		switch($c){
			case 1:
				echo " per CD $cd : $voto<br>";
				$voto_somma=$voto_somma+giudizio_numero($voto);
			break;
			case 2:
				echo " $voto dall'utente $user<br>";
				$voto_somma=$voto_somma+giudizio_numero($voto);
			break;
			case 3:
				echo $messaggio1.$voto;
				//trattandosi di un voto di un utente non ha senzo il voto medio
			break;
		}//fine switch
	}//fine while
	if($c !=3){echo $messaggio2.giudizio_medio($voto_somma, $ha_votato);}
}// fine if else


//possibili funzioni comunqua da mettere prima di richiamarle
function giudizio_numero($v){
	switch($v){
		case "vuoto":
			return 0;
		break;
		//tutti gli altri casi
		case "eccellente":
			return 6;
		break;
	}
}
function giudizio_medio($sv, $nv){//sv somma voti, nv numero di voti
	if($nv==0){
		return "media non calcolabile";
	}else{
		$m=round($sv/$n, 1);//arrotondo alla prima cifra decimale
		if($m <1){
			return "vuoto (".$m.")";
		}elseif($m >=1 && $m <2){
			return "insufficiente (".$m.")";
			//salto tutti gli altri casi intemedi
		}elseif($m>6){
			return "eccellente (".$m.")";//salto tutti gli intemedi
		}
	}
}
?>

dagli un occhio, se non ti piace butta via tutto;)

ora provo a guaradere questo

Il problema principale è invece verificare se l'utente ha votato o no, ovvero selezionare solo i cd dove non ha votato, ovvero dove non c'è una riga della tabella giudizio_users dove coincidono sia l'id del cd, sia l'id dell'utente.
se ha votato o no lo vedi dallo script sopra, anzi vedi se un utente ha votato e/o se un cd è stato o no votato
 

giancarlo75

Nuovo Utente
10 Ago 2007
10
0
1
Be' intanto ti ringrazio ed apprezzo l'impegno.
Veniamo al codice.
per una migliore comprensione dell'uotput,
è meglio il nik dell'utente ed il nome del cd
Non utilizzo il nome del cd ma il suo id, perchè possono esistere due cd con titolo uguale ma sono due cose diverse e si vede solo dall'id.
Il problema della media e di tutti i risultati che riguardano la totalità dei voti l'ho risolto come avevo scritto sopra, ovvero dedicando due campi nella tabella dei cd, i quali contengono il numero totale dei voti e ma media dei voti. Entrambi questi campi vengono aggiornati ogni volta che un utente vota.
Invece in problema di trovare i cd che l'utente non ha votato mi rimane.

Riguardo al tuo codice, è da escludere la possibilità che uno dei due valori - id_utente e id_cd - sia nullo. Quindi sicuramente mi ritrovo in questa condizione:
Codice:
else{//voto di un cd dato da un utente
    $c=3;
    $condizione=" WHERE id_users='".$id_utente."' AND id_cd='".$id_cd."' ";
    $messaggio0="l'utente $id_user non ha dato alcun voto al CD $id_cd";
    $messaggio1="l'utente $id_user ha dato il seguente voto al CD $id_cd :";
}
Tuttavia la query, siccome serve a selezionare un elenco di cd in base a diverse opzioni, parte sempre con una SELECT alla tabella dei cd, così:
Codice:
SELECT DISTINCT * FROM cd c
e non come è scritta da te, che verrebbe:
Codice:
SELECT * FROM giudizio_users
WHERE id_users='".$id_utente."' AND id_cd='".$id_cd."'
La query deve selezionare i dati della tabella cd, ma solo le righe dei cd che non sono stati votati dall'utente, quindi avrei bisogno di una query tipo:
Codice:
SELECT DISTINCT * FROM cd c
INNER JOIN giudizio_users giu_u
ON giu_u.id_cd = c.id AND giu_u.id_users = '".$id_utente."'
dove la WHERE verifichi che effettivamente esista o no una riga della tabella giudizio_users che soddisfi queste condizioni, ed in caso affermativo scartare il cd.
Al limite la query scritta da te potrei inserirla come subquery all'interno di quella principale, ma non so come scriverla.
 

borgo italia

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
4 Feb 2008
16.046
150
63
PR
www.borgo-italia.it
ciao
da quello che ho capito vuoi estrrae quanto non votatao dall'utente, quindi le righe che non hanno l'utente con l'id selezionato, dovrebbe bastare così

SELECT DISTINCT * FROM cd c
INNER JOIN giudizio_users giu_u
ON giu_u.id_cd = c.id AND giu_u.id_users != '".$id_utente."'

oppure
SELECT DISTINCT * FROM cd c
INNER JOIN giudizio_users giu_u
ON giu_u.id_cd = c.id AND giu_u.id_users <> '".$id_utente."'
 

giancarlo75

Nuovo Utente
10 Ago 2007
10
0
1
Grazie mi hai aperto gli occhi, ora ho risolto così:
Codice:
SELECT distinct(c.id), c.* FROM cd c  
WHERE  ((SELECT COUNT(giudizio_utente) FROM giudizio_users WHERE id_cd = c.id AND id_users = '."$id_utente".')!=1)
Praticamente non funzionava perchè l'avevo scritta così:
Codice:
SELECT DISTINCT * FROM cd c
INNER JOIN giudizio_users giu_u
ON giu_u.id_cd = c.id AND giu_u.id_users = '".$id_utente."'
WHERE id_cd = c.id AND id_users = '."$id_utente".')!=1)
Non sapendo che quando effettuo la giunzione con ON già equivale a soddisfare una condizione. Praticamente come è scritta sopra è come chiamare tra una serie di oggetti rossi e verdi, prima solo quelli rossi, subito dopo solo i verdi... per forza dava risultato nullo.
Grazie mille, mi sei stato di grande aiuto, ciao.
 
Ultima modifica:
Discussioni simili
Autore Titolo Forum Risposte Data
M Contare totale risultati tabella in un periodo di tempo PHP 5
A Contare gli elementi di un array PHP 13
maxnegri Contare sessioni aperte e creare condizione PHP 1
L contare record uguali in file csv PHP 4
B [MySQL] contare quante un oggetto e' presente MySQL 2
elpirata [RISOLTO][Mysql] Contare le occorrenze in un campo tipo varchar MySQL 2
P [PHP] Contare quante volte uno stesso id si ripete nella tabella PHP 12
P [PHP] Contare i mesi con timestamp. PHP 8
A Contare n° totale oggetti da mysql PHP 5
C Contare numero record uguali Database 3
asevenx [Javascript] [AngularJS] contare opzioni selezionate di una select in un elenco ng-repeat Javascript 0
G [ACCESS2007]: contare li ultimi record con lo stesso valore in un campo, finchè il valore non cambia MS Access 2
xone [PHP] Contare numero dei giorni tra data_in e data_out PHP 11
zorro [PHP] Contare i record di una tabella PHP 6
F Contare le righe con php ad oggetti MySQL 0
asevenx Contare numero di righe in un form dinamico Javascript 5
F Miglior metodo per contare le righe di una query MySQL 2
S Contare valori uguali in una colonna PHP 2
N Contare post MySQL 2
L contare nodi figli di un determinato nodo madre XML 0
H Contare file presenti nella pagina PHP 3
S [RISOLTO]php contare numero delle query PHP 1
L Contare determinati valori che si ripetono in una tabella per un detemrinato ID MySQL 2
Monital Contare il numero di righe con php5 PHP 10
M contare record db con valore specifico PHP 1
Shyson Contare i caratteri user e dominio Javascript 57
D Contare numero div con una determinata classe Javascript 2
criric Contare le settimane di un mese Snippet PHP 3
nim contare record uguali in tabella PHP 10
anthares Contare record in tabella secondo certe condizioni PHP 21
Monital Contare valori uguali di un array e dividerli in una stringa PHP 12
nim contare frasi che si ripetono PHP 10
R Contare il numero di click su un link PHP 2
A contare iniziali dei nomi Java 3
M non contare gli accessi dal tuo pc Supporto Mr.Webmaster 1
S Come contare i Download file .zip su html HTML e CSS 10
M Sommare i punteggi PHP 17
A sommare valori a video per lo stesso giorno jQuery 1
M [PHP] Sommare due campi calcolati PHP 3
maxnegri Sommare i prezzi dei prodotti aggiunti al carrello di diverse aziende con Select sum php mysqli PHP 10
C [PHP] Sommare o sottrarre a ZERO PHP 7
M [PHP] Sommare ore e minuti PHP 22
elpirata [PHP][RISOLTO] Sommare gli importi estratti da un ciclo while PHP 3
ste80 [PHP] sommare le ore PHP 24
L [PHP] Sommare campi e aggiornare tabella PHP 14
F Sommare valori di ogni periodo con SELECT PHP 7
C selezionare distinti id e sommare uguali PHP 1
clodiny come sommare dei record raggruppandoli per anno MySQL 33
A sommare valori in un array multidimensionale PHP 0
N Sommare due date PHP 4

Discussioni simili