Re: quicker query?

Hello Michael, I'm bit confused now. In your first letter, you wrote this simple code : --- if ($result) { for ($cluster=1;$cluster<200;$cluster++) { for($galaxy=1;$galaxy<26;$galaxy++) { $sql="insert into galaxy select avg(cluster) as gcluster,sum(size) as gsize,sum(score) as gscore,avg(galaxy) as ggalaxy from universe where cluster='$cluster' and galaxy='$galaxy'"; $result=db_query($sql); --- (by the way where you close the braces? after db_query?) So, your script iterates on $cluster and $galaxy. In each iteration you select those rows from the table universe whose $galaxy and $cluster attributes match the respective variables. [ Here comes where I'm messed up. ] Therefore your select statement fetch rows with the same galaxy and cluster value, then why to calculate avg() on these fields? > cluster, galaxy and planet are coordinates. The code I posted loops through > the universe and adds up size and score for each galaxy. adds up size and score is the sum () in select? > There are 742 clusters each with 25 galaxies and each galaxy has 25 planets. I average the > cluster and galaxy sum the score and size then insert the results into a new so please give me a light why to average when these values are the same for each row? Did i miss something? but I suggest to you writing out these $sql queries and attach it to your mail (not the whole just the first 100). Maybe it helps me to understand. Papp Gyozo - pgerzson@freestart.hu ----- Original Message ----- From: "Michael Roark" <rhuian@noxordo.com> To: "Gyozo Papp" <pgerzson@freestart.hu> Cc: <php-general@lists.php.net> Sent: 2001. május 6. 22:53 Subject: Re: [PHP] quicker query? > Its a different problem to explain Let me show you part o the contents from > universe. > > +---------+--------+--------+------------------------+---------------+------ > +--------+--------+ > | cluster | galaxy | planet | planet_name | ruler | size > | score | rank | > +---------+--------+--------+------------------------+---------------+------ > +--------+--------+ > | 1 | 1 | 1 | Stomp | Pomp | 9 > | 46175 | 97055 | > | 1 | 1 | 2 | The Burping World | Burp | 28 > | 125263 | 42971 | > | 1 | 1 | 3 | xion | KLS | 31 > | 97106 | 59469 | > | 1 | 1 | 4 | madlosers | madloser | 55 > | 104154 | 55094 | > | 1 | 1 | 5 | Troy | Lanfeust | 10 > | 25311 | 115558 | > | 1 | 1 | 6 | Infinte Space | The Watcher | 52 > | 131190 | 39903 | > | 1 | 1 | 7 | Kjempefæl planet | EkvALiser | 23 > | 83264 | 68716 | > | 1 | 1 | 8 | Edinazu | Jaypallas | 30 > | 129877 | 40601 | > | 1 | 1 | 9 | Gracen and Gracen inc. | Jim Profit | 33 > | 74665 | 74957 | > | 1 | 1 | 10 | Hades | DarkkAngel | 39 > | 109799 | 51718 | > | 1 | 1 | 11 | ziggy | zigzac | 28 > | 86054 | 66746 | > | 1 | 1 | 12 | Promt L- pop | Kaaleb | 55 > | 156840 | 28239 | > | 1 | 1 | 13 | Wicka | Wreck | 11 > | 52938 | 91364 | > | 1 | 1 | 14 | Karak Azhul | Morglum | 45 > | 155288 | 28907 | > | 1 | 1 | 15 | your fate | decider | 44 > | 153838 | 29491 | > | 1 | 1 | 16 | Poison | BlackWidow | 10 > | 48655 | 94916 | > | 1 | 1 | 17 | Veilside | Sik Silvia | 21 > | 83891 | 68266 | > | 1 | 1 | 18 | Sontis | Bobshi | 45 > | 112186 | 50353 | > | 1 | 1 | 19 | Midkemia | Milamber | 40 > | 130165 | 40444 | > | 1 | 1 | 20 | Fortezza | Saratoga | 39 > | 114699 | 48883 | > | 1 | 1 | 21 | blackdeath | ~~blackwing~~ | 33 > | 107266 | 53248 | > | 1 | 1 | 22 | Lightforge | Darkangel | 36 > | 95566 | 60445 | > | 1 | 1 | 23 | Knor's Planet | Knor | 54 > | 181835 | 19500 | > | 1 | 1 | 24 | Popelplanet | Earthbrain | 1 > | 9008 | 133733 | > | 1 | 1 | 25 | Infected Earth | TeRRoR | 75 > | 187138 | 17817 | > +---------+--------+--------+------------------------+---------------+------ > +--------+--------+ > > > > cluster, galaxy and planet are coordinates. The code I posted loops through > the universe and adds up size and score for each galaxy. There are 742 > clusters each with 25 galaxies and each galaxy has 25 planets. I average the > cluster and galaxy sum the score and size then insert the results into a new > table. The script goes on to the next cluster galaxy and does the same > thing. It does this till it gets to cluster 742 galaxy 25. > > ----- Original Message ----- > From: "Gyozo Papp" <pgerzson@freestart.hu> > To: "Michael Roark" <rhuian@noxordo.com>; <php-general@lists.php.net> > Sent: Sunday, May 06, 2001 6:51 AM > Subject: Re: [PHP] quicker query? > > > hello, > maybe there is another solution that should be quicker, because it uses > fewer DB query. > > I don't have a clear view what you want from this query, so if you can give > me a short description about it > it would be appriciated. I think this one does the same job: > > INSERT INTO galaxy > SELECT > cluster AS gcluster, > sum(size) as gsize, > sum(score) as gscore, > galaxy AS ggalaxy > FROM universe > WHERE cluster BETWEEN 1 AND 200 AND galaxy BETWEEN 1 AND 26 > GROUP BY cluster, galaxy; > > > ----- Original Message ----- > From: "Michael Roark" <rhuian@noxordo.com> > To: <php-general@lists.php.net> > Sent: 2001. május 6. 05:27 > Subject: [PHP] quicker query? > > > Is there a faster (better) way to do the following: > > if ($result) { > for ($cluster=1;$cluster<200;$cluster++) { > for($galaxy=1;$galaxy<26;$galaxy++) { > > $sql="insert into galaxy select avg(cluster) as gcluster,sum(size) as > gsize,sum(score) as gscore,avg(galaxy) as ggalaxy from universe where > cluster='$cluster' and galaxy='$galaxy'"; > $result=db_query($sql); > > Thanks > > Michael > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >