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
- [EMAIL PROTECTED]
----- Original Message -----
From: "Michael Roark" <[EMAIL PROTECTED]>
To: "Gyozo Papp" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
> To: "Michael Roark" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> 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" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
