[thelist] Help with sql statements

[CDitty]

Thanks Rudy.  Look like it might work.  I'll play with this tomorrow.

Thanks again.

At 09:36 AM 07/23/2000 , you wrote:
> > Is there anyone here that can assist me in re-writing the
> > sql statements below to give me better memory usage?
> > I recently rewrote the program that holds them, and now it is using
> > too much memory.   I am guessing that JOINs are needed
>
>hi chris
>
>not sure if it will help with the memory problem, but i can do the joins
>
>
> >$lastnumbers = "7, 36, 37, 47, 55, 56";
> >
> >$sql = "select count(pick) as cnt, ticket
> > from drawpicks
> > where pick IN ($last_numbers)
> > group by ticket
> > having count(pick) = '$n'";
>
>count() is a numeric function, remove the quotes from '$n'
>
>
> >$sql2 = "select pick from drawpicks
> > where ticket = $ticket";
>
>the way you would normally combine these two queries is like this --
>    select pick from drawpicks
>    where ticket IN
>       ( select ticket
>         from drawpicks
>         where pick IN ($last_numbers)
>         group by ticket
>         having count(pick) = '$n' )
>
>notice i did not select count(pick) -- didn't need to, as it will be equal
>to $n
>
>it gets really messy if count(pick) is *not* $n, i mean, if you need to
>find all winning tickets, i.e. with *at least* $n numbers correct
>
>luckily for me, your first query was testing for *exactly* $n, so
>presumably you're going to run the program for $n=6, then $n=5, etc.
>
>anyhow, mysql doesn't allow subqueries, and while it is always possible to
>write a subquery as a join, let's not do it on this one yet (you'll see why
>in a minute)
>
>
> >$picksresult = mysql_query($sql2) or die(mysql_error());
> >$i = 0;
> >while($myrow2 = mysql_fetch_array($picksresult)) {
> >    $pick[$i] = $myrow2["pick"];
> >    $i = $i + 1;
> >}
> >mysql_free_result(picksresult);
> >
> >// Combine the user's picks into one variable.
> >$user_picks = $pick[0] . ", " . $pick[1] . ", " . $pick[2] . ", " .
> >$pick[3] . ", " . $pick[4] . ", " . $pick[5];
>
>this part was really interesting but you're not using it anywhere!!
>
>the idea of pulling out 6 rows and combining them into one is called
>denormalization
>
>in ms access you would use a crosstab query for this
>
>i'll come back to this later, but for the moment let's discard this query
>
>
> >$usersql = "select id, emailaddr, userid, DATE_FORMAT(date, 'b d, Y') as
> >enter_date from drawusers
> > where id = $ticket";
>
>this is where you pull out the winning users
>
>if this weren't mysql, you would combine this with the first query like
>this --
>
>    select id, emailaddr, userid
>         , DATE_FORMAT(date, 'b d, Y') as enter_date
>      from drawusers
>     where id  IN
>       ( select ticket
>         from drawpicks
>         where pick IN ($last_numbers)
>         group by ticket
>         having count(pick) = $n )
>
>rewriting this as a join, you get
>
>    select id, emailaddr, userid
>         , DATE_FORMAT(date, 'b d, Y') as enter_date
>      from drawusers
>         , drawpicks
>     where id = ticket
>       AND pick IN ($last_numbers)
>    group by id, emailaddr, userid, enter_date
>    having count(pick) = $n
>
>notice that the columns in the group by list must be the same columns as
>in the select list
>
>i haven't used mysql before, but i'm pretty sure you have to use the alias
>name "enter_date" in the group by
>(http://mysql.com/Manual_chapter/manual_Reference.html#Grouping_functions)
>
>(in any case i would never call one of my columns "date" -- that's too much
>like a reserved word and can only lead to trouble)
>
>now the only thing that's missing, of course, is the query we discarded
>partway through --  the 6 numbers that the user picked
>
>this is where you could do your loop
>
>grab each winning ticket number, go back and get the picks, string them
>together, and output the result along with the user's emailaddr, etc.
>
>
>
>having said all that, i think you may not be out of the woods yet
>
>perhaps your memory problems are due simply to the overhead associated with
>looping through a query while processing the result set of another query
>
>if you do need to combine the user's picks query into the join, be aware
>that you will end up with a rather cumbersome result set
>
>    select id, emailaddr, userid
>         , DATE_FORMAT(date, 'b d, Y') as enter_date
>         , BBB.pick
>      from drawusers
>         , drawpicks AAA
>         , drawpicks BBB
>     where id = AAA.ticket
>       AND id = BBB.ticket
>       AND AAA.pick IN ($last_numbers)
>    group by id, emailaddr, userid, date, BBB.pick
>    having count(AAA.pick) = $n
>
>you will get id, emailaddr, userid, and enter_date on *each of six rows* --
>i.e. for each BBB.pick
>
>
>hope that helps, and i hope i didn't screw up the sql -- i did this all
>without coffee, and now i gotta get some breakfast...  ;o)
>
>
>rudy.limeback
>r937.com
>evolt.org
>
>
>
>
>
>
>
>
>
>
>
>
>---------------------------------------
>For unsubscribe and other options, including
>the Tip Harvester and archive of TheList go to:
>http://lists.evolt.org Workers of the Web, evolt !