Re: [phpesp-dev] little help -> off topic

[flobee <fl...@gm...>]

Long time ago! now the solution!
the timeout came from a missing index of the "username" in response !  now:
its running too fast  :-)

with this you can find users without a survey done (to clean users from the
db, because my is huge!)

ALTER TABLE `response` ADD INDEX ( `username` )

                          $sql = ("SELECT t1.*, COUNT(t2.username) AS cnt
                                FROM respondent AS t1 LEFT JOIN response AS
t2
                                     ON t1.username = t2.username
                                WHERE
                                      t1.realm = '$my_realm'
                                      AND (t2.username is NULL OR
t1.username = t2.username)

                                GROUP BY t1.username
                                ORDER BY cnt ASC
                                 ");

 $match = mysql_query( $sql ) or die ("$sql<br>". mysql_error());
 while (list(..,...,.... .... ,$counts) = mysql_fetch_row($match)) {
// do something
}

flobee

----- Original Message ----- 
From: "Luis Fagundes" <lfa...@fu...>
To: "flobee" <fl...@gm...>
Sent: Saturday, August 09, 2003 11:49 PM
Subject: Re: [phpesp-dev] little help -> off topic


> Hi!
> try this:
>
> SELECT t1.*, max(t2.submitted) as mysort FROM respondent as t1 LEFT JOIN
> response as t2 ON t1.username = t2.username WHERE t1.realm='$realm'
> GROUP BY t1.username
>
> with LEFT JOIN if a row is not found in t2, null is returned.
>
> []s
> Luis
>
> flobee wrote:
>
> >hello all!
> >i´m working on a little tool to get some extra statistic informations
about
> >the respondents and the usage...
> >i build a page with shows me the TOP lists of surverys, and respondents
> >
> >now i want the find out which respondent has not made or finished a
survey
> >to see it or to delete unused accounts (huge list!) but i really have
> >troubles to find a right and simple mysql query to check this. i already
ask
> >in some mysql-communitys but havn´t got any resonse yet :-(
> >
> >the query to show all respondents with the last survey (but missing users
> >with "no survey"):
> >
> >                  $sql = ("SELECT t1.* , max(t2.submitted) as mysort
> >                           FROM respondent AS t1 , response AS t2
> >                           WHERE t1.realm='$realm' AND t1.username =
> >t2.username
> >                           GROUP BY t1.username
> >                           ORDER BY mysort DSEC
> >                           LIMIT 0, 50
> >                         ");
> >
> >i dont´find an working query to include all respondents even thouse who
have
> >not submitted any survey!
> >mysql.com give an example but on my localhost i only get timeouts so that
i
> >can not find a good result/solution:
> >mysql> SELECT table1.* FROM table1
> >    ->        LEFT JOIN table2 ON table1.id=table2.id
> >    ->        WHERE table2.id IS NULL;
> >This example finds all rows in table1 with an id value that is not
present
> >in table2
> >
> >have you any idea to have a query which handle this!?
> >
> >kind regards
> >flobee
> >
> >
> >
> >
> >-------------------------------------------------------
> >This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> >Data Reports, E-commerce, Portals, and Forums are available now.
> >Download today and enter to win an XBOX or Visual Studio .NET.
>
>http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> >_______________________________________________
> >phpESP-devel mailing list
> >php...@li...
> >https://lists.sourceforge.net/lists/listinfo/phpesp-devel
> >
> >
> >
>
>