armar consulta para mostrar las opiniones mas...

[catpaw]

que tal forer@s

quiero mostrar las opiniones mas debatidas que serian las mas comentadas
y las mas populares que serian las que tienen mas visitas, no se como armar las consultas

necesito su consejo verán tengo las siguientes tablas:

opiniones
----------------
id_opinion
autor_op
fecha_op
hora_op
email_op
id_categoria_op
titulo_op
descripcion_op

la de los comentarios, que tiene de llave foránea el id_opinion:

replicas
----------------
id_replica
id_opinion
fecha_rep
hora_rep
nombre_rep
email_rep
comentario_rep

la del contador de visitas, que tiene tambien de llave foránea el id_opinion:

contador
-----------------
id_visto
ip_visto
num_visto
fecha_visto
id_opinion

se que debe llevar el order by pero como son 2 tablas diferentes no se como seria, la logica que necesito seria asi:

para mostrar las de mas comentarios

SELECT * FROM opiniones ORDER BY $num_comentarios WHERE id_opinion=$id_opinion

donde $num_comentarios seria el resultado de:

$NroComentarios=mysql_num_rows(mysql_query("SELECT id_opinion FROM replicas WHERE id_opinion='$id_opinion'"));

seria correcto???

y para mostrar los mas visitados seria algo parecido??

Gracias

[quimfv]

Código MySQL:

SELECT op.id_opinion,
op.autor_op,
op.fecha_op,
op.hora_op,
op.email_op,
op.id_categoria_op,
op.titulo_op,
op.descripcion_op,
ifnull(numRep.numComentarios,0) comentarios
FROM
opiniones op LEFT JOIN (SELECT id_opinion, 
                                    count(*) numComentarios 
                                    FROM replicas GROUP BY id_opinion) numRep
                ON op.id_opinion=numRep.id_opinion
ORDER BY ifnull(numRep.numComentarios,0) DESC;
 

Para las visitas seria algo similar....

[catpaw]

hola quimfv, gracias por tu respuesta, si funciona bien pero tengo algunas dudas sobre la consulta,

en la consulta entiendo lo que haces aqui:

SELECT op.id_opinion, op.autor_op, op.lugar_op, op.fecha_op, op.hora_op, op.email_op, op.id_categoria_op, op.titulo_op, op.descripcion_op,

estas seleccionando los campos que se mostraran, pero en esta linea ya me perdi:

ifnull(numRep.numComentarios,0)

no entiendo de donde sacas numRep.numComentarios, me imagino que es un tipo alias y despues pones

comentarios FROM opiniones op

no se a que te refieres con comentarios, igual no se si es un alias

Gracias

[catpaw]

otra duda, es que mira para sacar el numero de visitas que tiene cada opinion no lo hago con un count, lo hago sumando:

Código PHP:

  <?php
$fila_pop=mysql_fetch_array(mysql_query("SELECT SUM(num_visto) FROM contador WHERE id_opinion='$id_opinion_pop'")); 
$NroVisitas_pop=$fila_pop[0];
echo $NroVisitas_pop;
?>   

y entonces así cambiaría la consulta que me funciona bien para los comentarios pero en este caso seria diferente, acá te dejo la tabla con los valores que tiene actualmente, deberían salir las opiniones en este orden:

opinion id 1 -> 3 visitas
opinion id 2 -> 2 visitas
opinion id 4 -> 1 visita
opinion id 6 -> 1 visita

id_visto__ ip_visto__ num_visto____ fecha_visto__________ id_opinion
1_______ 127.0.0.1_______ 2_______ 2012-02-03 13:30:23_______ 2
2_______ 127.0.0.2_______ 1_______ 2012-02-02 18:29:57_______ 1
3_______ 127.0.0.1_______ 2_______ 2012-02-03 13:26:11_______ 1
4_______ 127.0.0.1_______ 1_______ 2012-02-03 13:17:35_______ 6
5_______ 127.0.0.1_______ 1_______ 2012-02-03 13:30:45_______ 4

Gracias

[catpaw]

Hola mira intente adaptar la consulta que me dejaste para los comentarios y saque esto:

Código PHP:

  $sql_query_pop="SELECT op.id_opinion, op.autor_op, op.lugar_op, op.fecha_op, op.hora_op, op.email_op, op.id_categoria_op, op.titulo_op, op.descripcion_op FROM opiniones op LEFT JOIN (SELECT SUM(ct.num_visto) FROM contador ct) ON op.id_opinion=ct.id_opinion ORDER BY ct.num_visto DESC LIMIT 0,4";  
   

pero me sale un error y es que ademas yo se que no es correcto, no se como se tenga que hacer, necesito:

que muestre todos los campos de la tabla opiniones donde sume num_visto de la tabla contador donde el id_opinion (tabla opiniones) sea igual al id_opinion (tabla replicas) ordenado por el resultado de la suma de num_visto descendiente limite 0,4

yo se que esta complicado espero me puedan apoyar

Gracias

[quimfv]

SELECT op.id_opinion,
op.autor_op,
op.fecha_op,
op.hora_op,
op.email_op,
op.id_categoria_op,
op.titulo_op,
op.descripcion_op,

/// Efectivamente es la lista de campos

ifnull(numRep.numComentarios,0) comentarios

/// Numero de replicas (si, numRep.numComentarios es un alias)
/// La funcion Ifnull pasa los nulos a cero
/// al resultado se le da el alias "comentarios"


FROM
opiniones op LEFT JOIN

/// Por el left join consigo que me muestre todas las opiniones tengan o no replicas

(SELECT id_opinion,
count(*) numComentarios
FROM replicas
GROUP BY id_opinion) numRep

///Subconsulta, con alias numRep, que me da el numero de replicas por cada opinion, fijate que retorna dos campos el id_opinion para poder relacionar con la otra tabla y el count(*) con alias numComentarios


ON op.id_opinion=numRep.id_opinion

//Clausula ON que relaciona la tabla opiniones (alias op) con la subconsulta con alias numRep por el campo id_opinion

ORDER BY ifnull(numRep.numComentarios,0) DESC;

//// Ordeno en orden inverso (DESC, mayor a menor) por el numero de replicas.

Otras dudas...

No se como consigues ese num_visto

1_______ 127.0.0.1_______ 2_______ 2012-02-03 13:30:23_______ 2

esto significa que la opinion 2 se ha visitado 2 veces en el segundo 23 del minuto 30.... muy fino el contador...

No seria mas logico guardar un registro por visita?...

Tu adaptación de la consulta

SELECT
op.id_opinion,
op.autor_op,
op.lugar_op,
op.fecha_op,
op.hora_op,
op.email_op,
op.id_categoria_op,
op.titulo_op,
op.descripcion_op,

Te falta el numero de visitas... no?

sbcVisitas.visitas


FROM opiniones op LEFT JOIN


(SELECT SUM(ct.num_visto)
FROM contador ct)

Esta subconsulta retorna la suma de todos los vum_visto, y ademas no esta el campo id_opinion por lo que no se puede relacionar con la tabla opiniones

Toda subconsulta debe tener su alias

sunbstituyela por

(SELECT ct.id_opinion,SUM(ct.num_visto) visitas
FROM contador ct
GROUP BY ct.id_opinion) sbcVisitas

ON op.id_opinion=ct.id_opinion

La relacion es entre la tabla y la subconsulta luego debes cambiar los alias

ON op.id_opinion=sbcVisitas.id_opinion

ORDER BY ct.num_visto DESC LIMIT 0,4";

idem

ORDER BY sbcVisitas.visitas DESC LIMIT 0,4;

[catpaw]

que tal quimfv, gracias por tu explicacion, ya me quedo mucho mas claro el asunto

No se como consigues ese num_visto

mira normalmente ya conozco el id de cada opinión que muestro de acuerdo a la consulta simple

SELECT * FROM opiniones LIMIT 0,4 donde mediante un:

while($row_pop=mysql_fetch_array($res_pop, MYSQL_ASSOC)){

voy recogiendo los valores de cada opinion,

$id_opinion_pop=$row_pop['id_opinion'];

para cada opinion muestro cuantas visitas tiene, con la siguiente consulta:

SELECT SUM(num_visto) FROM contador WHERE id_opinion='$id_opinion_pop'

las tablas que interaccionan aqui son:

opiniones
----------------
id_opinion
autor_op
fecha_op
hora_op
email_op
id_categoria_op
titulo_op
descripcion_op


contador
-----------------
id_visto
ip_visto
num_visto
fecha_visto
id_opinion

No seria mas logico guardar un registro por visita?...

según yo así lo hago, mira acá en la tabla contador se guarda la id de la opinion visitada y los datos de la visita que son un id, la ip, el numero de visitas y la fecha/hora (si coincide la ip y han pasado mas de 5 horas, le suma uno al valor que tenga el campo num_visto y actualiza la fecha y hora, de lo contrario no cuenta la visita)

por ejemplo te ponia los valores que tengo de prueba en la tabla contador:

id_visto__ ip_visto__ num_visto____ fecha_visto__________ id_opinion
1_______ 127.0.0.1_______ 2_______ 2012-02-03 13:30:23_______ 2
2_______ 127.0.0.2_______ 1_______ 2012-02-02 18:29:57_______ 1
3_______ 127.0.0.1_______ 2_______ 2012-02-03 13:26:11_______ 1
4_______ 127.0.0.1_______ 1_______ 2012-02-03 13:17:35_______ 6
5_______ 127.0.0.1_______ 1_______ 2012-02-03 13:30:45_______ 4

como podras darte cuenta la id de la opinion 1 se repite, lo que significaria que se vio 1 una vez por la ip 127.0.0.2 y dos veces por la ip 127.0.0.1, entonces para obtener el numero de visitas de la opinion 1 se deben sumar el 1 y el 2 del campo num_visto donde las ip sean iguales, en este caso a 1.



entonces lo que necesito es en una sola consulta tener los resultados de:

-> SELECT * FROM opiniones
-> SELECT SUM(num_visto) FROM contador WHERE id_opinion='$id_opinion_pop'

***mostrados de acuerdo al numero de visitas DESC***
perooooo teniendo en cuenta que ya no sabre de antemano la $id_opinion_pop



de verdad muchas gracias por la ayuda

[quimfv]

Se ha duplicado el mensaje

[quimfv]

Código MySQL:

SELECT ...lista de todos los campos de opinion...
ifnull(numVisitas.num,0) visitas
FROM
opiniones op LEFT JOIN (SELECT id_opinion, 
                                    SUM(num_visto) num
                                    FROM contador
                                    GROUP BY id_opinion) numVisitas
                ON op.id_opinion=numRep.id_opinion
ORDER BY ifnull(numVisitas.num,0) DESC;
 

No te hace falta saber de antemano que opinion buscas porque por el group by suma todas las visitas agrupadas por opiniones....

[catpaw]

quimfv de verdad muchas gracias por la ayuda!!

ya quedo la consulta, solo marcaba un error aca pero lo demas todo bien

ON op.id_opinion=numRep.id_opinion

lo remplace por numVisitas y todo listo!!

Gracias