Ca va Etienne,
> well in fact i think i haven t be very clear with the goal i need to get.
> let me detail this to you frm the beginning to the end...
Advertising
=the goal is still not clear to me, and if you want to critique this response, or ask
a further question, it
would help me to know what on earth we are talkinga bout with "naffaire", "ncpt", and
the system being
constructed... I've tried to work with this information on a theoretical basis, but it
makes the job more
difficult, not to know what we're actually working with!
=I do (now) see that you were not wanting to invert the array, but to perform a pivot
table analysis.
> i ve a table as follows:
>
> naffaire | ncpte
> 1 | 3
> 2 | 5
> 7 | 9
> 5 | 6
> 2 | 5
> 6 | 9
> 4 | 2
> 1 | 3
> 4 | 2
> 7 | 9
> 4 | 2
> 9 | 7
> 7 | 9
> 4 | 2
> 5 | 6
> 4 | 2
> ... and 1 million lines like that...
>
> naffaire and ncpte are linked : there can only be one ncpte for a naffaire,
> if you find a 7 as naffaire with 9 as ncpte all the lines containing 7 as
> naffaire will have 9 as ncpte. BUT the opposite of this relation is not true
> : a ncpte can be associated with different naffaire. (as you can see in the
> previous example at the lines 3, 6, 10...
>
> what i do with this table is a count on the number of couple of
> naffaire/ncpte and the only parameter return with my query is the count (not
> the naffaire or the ncpte...), no matter the datas, i juste need to work
> with their occurences...
=if a particular naffair value has a one-to-one relationship with a particular ncpte
value, then naffaire implie
ncpte and there is no need to consider them as 'couples' - only need to deal with
naffaire.
=to count the number of occurences of each naffair value (couple):
SELECT naffair, COUNT(*) AS suite
FROM tbl
# no need for a WHERE clause because we're looking at the entire tbl
GROUP BY naffair # to control the COUNT()
> well, now for the examle detail previously the query gives me
>
> RESULT OF TABLE WHERE
> THE QUERY THE QUERY IS DONE
>
> count(*) naffaire |
> ncpte
> 2 1
> | 3
> 2 2
> | 5
> 3 7
> | 9
> 2 5
> | 6
> 2 2
> | 5
> 1 6
> | 9
> 5 4
> | 2
> 2 1
> | 3
> 5 4
> | 2
> 3 7
> | 9
> 5 4
> | 2
> 1 9
> | 7
> 3 7
> | 9
> 5 4
> | 2
> 2 5
> | 6
> 5 4
> | 2
> ... and 1 million lines like that...
=and I trust that the SQL above yields the same result (I haven't tested it in any
way). The naffair value and
the number of times it appears in the table.
> So i get a 2 dimension array with only one column and 1 million rows...
> the fact is what i need is to count the occurrences for each result in this
> array.
> So for the same example we would get :
> occurrence of 1 : 2
> occurrence of 2 : 6
> occurrence of 3 : 3
> occurrence of 4 : null
> occurrence of 5 : 5
> ...
=so now you want to know how many naffair values appear in the table a specific number
of times/how many naffair
values share the same occurence rate/frequency.
=you could feed the MySQL result set 'suite' column straight into your existing PHP
array and function (as
below)...
> to do this in an array there's an easy and light way : array_count_values.
> the problem is that this function only works on the first dimension of the
> array.
> That s why i have to return my column of result into a line
> I did it with this loop:
>
> while($row = mysql_fetch_array($result)) {
> array_push($suite, $row["count(*)"]);
> }
>
> It works fine... of course and then i make the array count values on $suite
> and i get
> $suite[1] : 2
> $suite[2] : 6
> $suite[3] : 3
> $suite[4] : unset
> $suite[5] : 5
> (max length with a 1 million row table about 25 indexes, so this array
> should be quite small)
=...alternatively, change the SQL (above) to SELECT...INTO a temporary table. Then
perform another
SELECT...COUNT()...GROUP on that resultset to get a result that lists the number of
occurences, and the number
of naffair values/couples that occur with that frequency. This is a situation where
sub-SELECTs would be a
valuable feature!
> Just to go to the very end of my need, even if its not useful:
> then i divide the values by their index (i gonna tell you what for)
> so the final result is :
> $suite[1] : 2 /1 ====> 2
> $suite[2] : 6 /2 ====> 3
> $suite[3] : 3 /3 ====> 1
> $suite[4] : unset /////////////////////
> $suite[5] : 5 /5 ====> 1
> (as the previous array was small this one is too ;o) )
=I think I got lost in this last step...
> Well you know everything : now it will be easier for me to tell you what i
> want to calculate :
> THE NUMBER OF COUPLES of naffaire/ncpte that have an occurence of 1, that
> have an occurrence of 2, of 3, of 4....
=continuing on with the second SQL, to achieve this, use ORDER BY to sort the results.
> so thereare only 2 long processes : the while loop (it won t be much shorter
> if i use a "for" loop)
> and the array_count_values (this one is a single instruction so i trust php
> developpers for the optimization of their code ;o) )
=whereas for processing large volumes of data, I would prefer to rely upon the RDBMS -
and remember, a temporary
table should be constructed entirely in RAM (just like a PHP array) so there's no
disk-speed/time penalty to be
paid!
> You will have understand my prob now : the very very big loss of time in my
> algorythm is the while loop...
> That s why i would like to do the same whithout using any kind of loop
> before the array_count_values, before this instruction, the array is far too
> big.
> I would win a lot of execution time.... help please i m sure there must be a
> solution but i m lost...
=is the db on an Internet-accessible server that I could reach?
=Whats the number of distinct naffaire values? How many different frequencies result?
ie How many records/array
elements do you have returned at each step?
> Hope i managed to be clear.
=see comment above.
=Regards,
=dn
> Thanx for your help
>
> Etienne
>
> ----- Original Message -----
> From: "DL Neil" <[EMAIL PROTECTED]>
> To: "Etienne DURAND" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, January 31, 2002 11:47 PM
> Subject: Re: [PHP-DB] how to reverse a hudge multidimensional array?
>
>
> > Bon soir Etienne,
> >
> > Like I say, "Inverting" an array is a chore, it's far easier to invert the
> pointers...
> > but if you must do it:-
> >
> > For loop to control the first dimension using $i
> > For loop to control the second dimension using $j
> > $temp = $array[ $i ][ $j ];
> > $array[ $i ][ $j ] = $array[ $j ][ $i ];
> > $array[ $j ][ $i ] = $temp;
> >
> > Ok?
> > =dn
> >
> >
> > > thx a lot for your answer,
> > >
> > > in fact i only have to return one of the 15 datas, so it makes one
> column...
> > > anyway, i already coded a solution with a while. My question was just to
> > > know if it was possible to do it without repeating 1000000 times the
> same
> > > processing.
> > >
> > > In fact i begin to believe that it is not possible to reverse the 2
> > > dimensions of an array...
> > >
> > > If you have a solution for me i m interested, even if it takes a lot of
> > > processor ressources because i will have a dedicated server and there
> wont
> > > be more than 30 users, not at the same time (that s for a stat
> intranet)...
> > > just to correct my unprecision again, there will be 1M datas to process
> not
> > > 15 because the array i ve to process is the result of my mysql query and
> > > that this query will only return a single column of datas (count(*) )
> > >
> > > Helllpppp :)
> > >
> > > Etienne
> > >
> > >
> > > ----- Original Message -----
> > > From: "DL Neil" <[EMAIL PROTECTED]>
> > > To: "Etienne Durand" <[EMAIL PROTECTED]>
> > > Cc: <[EMAIL PROTECTED]>
> > > Sent: Thursday, January 31, 2002 10:44 AM
> > > Subject: Re: [PHP-DB] how to reverse a hudge multidimensional array?
> > >
> > >
> > > > Etienne,
> > > >
> > > > > Well i hope someone will be able to give me a solution...
> > > > > Here s my problem:
> > > > > I'm working with a hudge mysql table (about 15 columns and 1000000
> > > rows...)
> > > > >
> > > > > in fact i ve got to count the number of couples
> `ncompte`/`naffaire` in
> > > the
> > > > > table and finaly calculate the number of couples that appear once,
> > > twice....
> > > > >
> > > > > here is the query i do:
> > > > > $result = mysql_query("SELECT count(*) FROM datas GROUP BY
> `ncompte`,
> > > > > `naffaire`");
> > > > >
> > > > > its result like something like this:
> > > > > count(*) naffaire ncompte
> > > > > 4 affaire1 compte1
> > > > > 4 affaire2 compte2
> > > > > 1 affaire3 compte3
> > > > > 2 affaire4 compte4
> > > > > 1 affaire5 compte5
> > > > >
> > > > > (plus many more)
> > > > >
> > > > >
> > > > > my final result should be:
> > > > > $result[1] : 2
> > > > > $result[2] : 1
> > > > > $result[4] : 2
> > > > >
> > > > > I manage to do this quite easily but i use a while instruction...
> > > > >
> > > > > while($row = mysql_fetch_array($result)) {
> > > > > array_push($suite, $row["count(*)"]);
> > > > > }
> > > > >
> > > > > As my table is very long the while takes a lot of time to
> complete...
> > > > > So my question is : Is there a solution to return my array as follow
> :
> > > > >
> > > > > 1 2 3 1 4 7
> > > > > 4 5 6 ===>> 2 5 8 ???
> > > > > 7 8 9 3 6 9
> > > > >
> > > > > It would allow me to not have to use this long long while....
> > > > > So if someone could telle me how to modify my query or what
> instructions
> > > to
> > > > > use to do that...
> > > >
> > > >
> > > > =there are various functions in PHP to 'reverse' arrays, but I must
> > > confess that I have never used them.
> > > > Regardless, if there is one to suit your purpose, it will surely
> consume
> > > CPU time to achieve the swap-over of
> > > > 15M items.
> > > >
> > > > =your example "my final result should be:" talks of enumerated arrays,
> so
> > > I shall assume this is the way you
> > > > always use them.
> > > >
> > > > =you want to somehow achieve:
> > > > array[1][1] = array [1][1];
> > > > array[1][2] = array [2][1];
> > > > array[1][3] = array [3][1];
> > > > etc
> > > > (you know that you can't do the above, right!?)
> > > >
> > > > =thereafter the array would be processed by using a mechanism such as
> two
> > > nested FOR loops to iterate through
> > > > the row/column elements of the array, eg
> > > >
> > > > for ( i=1; i<15; i++ );
> > > > for ( j=1; j<1000000; j++ );
> > > > process( array[i][j] );
> > > > etc
> > > >
> > > > =can you leave the array where it is, and adjust the way the
> iterations
> > > are managed? Instead of proceeding
> > > > methodically by counting 'up', count 'down', eg
> > > >
> > > > for ( i=15; i>0; i-- );
> > > > for ( j=1000000; j>0; j-- );
> > > > process( array[i][j] );
> > > > etc
> > > >
> > > > =Regards,
> > > > =dn
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > PHP Database 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 Database 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]
