[thelist] Help with sql statements

[rudy limeback]

> 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