optimize query: get al votes from user's item

Posted by Toni Michel Caubet on Stack Overflow See other posts from Stack Overflow or by Toni Michel Caubet
Published on 2010-12-23T17:19:07Z Indexed on 2010/12/23 18:54 UTC
Read the original article Hit count: 464

hi there! i did it my way because i'm very bad getting results from two tables...

Basically, first i get all the id items that correspond to the user, and then i calculate the ratings of each item.

But, there is two different types of object item, so i do this 2 times: show you:

function votos_usuario($id){

        $previa = "SELECT id FROM preguntas WHERE id_usuario = '$id'";
        $r_previo = mysql_query($previa);
        $ids_p = '0, ';
        while($items_previos = mysql_fetch_array($r_previo)){
            $ids_p .= $items_previos['id'].", ";
            //echo "ids pregunta usuario: ".$items_previos['id']."<br>";
        }
        $ids = substr($ids_p,0,-2);
        //echo $ids;

        $consulta = "SELECT valor FROM votos_pregunta WHERE id_pregunta IN ( $ids )";
        //echo $consulta;

       $resultado = mysql_query($consulta);
       $votos_preguntas = 0;
       while($voto = mysql_fetch_array($resultado)){
         $votos_preguntas = $votos_preguntas + $voto['valor'];
       }




       $previa_r = "SELECT id FROM recetas WHERE id_usuario = '$id'";
        $r_previo_r = mysql_query($previa_r);
        $ids_r = '0, ';
        while($items_previos_r = mysql_fetch_array($r_previo_r)){
            $ids_r .= $items_previos_r['id'].", ";
            //echo "ids pregunta usuario: ".$items_previos['id']."<br>";
        }
        $ids = substr($ids_r,0,-2);
        $consulta_b = "SELECT valor FROM votos_receta WHERE id_receta IN ( $ids )";
        //echo $consulta;

       $resultado_b = mysql_query($consulta_b);
       $votos_recetas = 0;
       while($voto_r = mysql_fetch_array($resultado_b)){
         $votos_recetas = $votos_recetas + $voto_r['valor'];
       }





        $total = $votos_preguntas + $votos_recetas;
        return $total;

    }

As you can si this is two much.. O(n^2)

Feel like thinking?

thanks!

© Stack Overflow or respective owner

Related posts about php

Related posts about algorithm