phpesp-devel

[phpesp-dev] meaning of MERGE RESULTS?

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

hello all!

maybe there is a miss understanding in the Merge feature!?

i have a survey
after ending it i start a copy of this survey again.
then i want to merge them to get the total results of both (or more) =
surveys!? am i=B4m right?
the archived and stopped surveys should be included in the list where i =
can merge then ..?!


kind regards flobee

[phpesp-dev] little help -> off topic

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

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

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

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

hello! thanks for your answer! sorry for answering late :-)
i tried and tried! i think this or a similar querys will not work!
 i always get timeouts and on mysql.com i found a good note for JOIN:
 >>" This assumes that table2.id is declared NOT NULL"<<  -> null is
returned
and the cols are "null" or "UNSIGNED"

same situations if you want to find out which survey of a group(realm) or
generally  has no (0) response and/or want to sort the results as "ASC" to
see them first (i have so many survey that i´m starting to loose the
overview)
i think there is only a chance to get these information when pushing all
values in an array and sorting in it self (but take more power to load the
hole response table :-( (

kind regards
flobee

----- Original Message ----- 
From: "Luis Fagundes" <lfa...@fu...>
To: "flobee" <fl...@gm...>
Sent: Sunday, August 10, 2003 12:49 AM
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
> >
> >
> >
>
>

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
> >
> >
> >
>
>