[thelist] Help with sql statements
rudy limeback
r937 at interlog.com
Sun Jul 23 09:55:28 CDT 2000
> 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
More information about the thelist
mailing list