Most entries in array

birwin

I have a database with entries as follows:

emp#, store#, class, sales

the list is sorted by employee with multiple entries for each employee

1234, 505, 2021, 1234.56
1234, 878, 2031, 1789.11,
1234, 878, 2041, 987.65
1234, 878, 2051, 4567.89
1234, 123, 2061, -58.78
1234, 878, 2061, -22.88
1234, 878, 2051, 567.99

I need an elegant way to determine that, in the above example, store 878 is the predominant store. I want the equation or process to ignore entries with a negative sales values.

I would prefer to do this as a mysql equation. Failing that I assume that there must be some way to do this within an array within PHP.

Any ideas?

Brian

Weedpacket

By predominant you mean the one with the maximum number of entries (I'll call it the "predominance")

select store_number, count(*) as predominance
from the_table
group by store_number
order by predominance desc
limit 1

Note that this does handle ties for first place, however - if there is a tie it could pick either one.

Oh, just thought of a way to fix that.

select store_number, count(*) as predominance
from the_table
where predominance = max(predominance)
group by store_number

I think that would work ... looks like it should ... haven't got a dbms handy to try it on...

birwin

Thank you for your reply.

I went back to my code and realize that I need to get the predominant store from an array of the above information. The way I have written the legacy code I can't easily use mysql to do the work.

Can anyone help me with a way to sort an array of the above information to give me the predominant store, ignoring any entries that have negative sales?

Thanks.

Brian

Weedpacket

Well, I don't know how your array is structured, so I gan't give any specifics; but PHP has a variety of array-manipulation functions which could be used to massage it. array_count_values, array_sum, usort,

birwin

The data is just a one dimensional array

The set is:

1234, 505, 2021, 1234.56
1234, 878, 2031, 1789.11,
1234, 878, 2041, 987.65
1234, 878, 2051, 4567.89
1234, 123, 2061, -58.78
1234, 878, 2061, -22.88
1234, 878, 2051, 567.99

It goes into arrays:

$emp[], $store[], $class[], $sales[]

The above would create the array $store[] as:

$store[0]=505
$store[1]=878
$store[2]=878
$store[3]=878
$store[4]=123
$store[5]=878
$store[6]=878

I want to calculate a variable to contain the value 878 which is the most frequent entry. The store number could be any value so sorting it by value does no good. The most frequent store could be 1 as easily as 878.

I went through all of the array functions on the PHP site and couldn't find anything to do this.

Brian

Weedpacket

Not with a single function. You're going to have to do some programming. Eewww...

Use array_count_values() on $store, look through the result for the maximum value, then look at the corresponding key (the store number).

Then go through $store again, getting the array indices that correspond to that store number.

Then go through the $sales array, looking at those store numbers. Keep a running total (I suggest multiplying by 100 first, and then dividing the sum by 100 at the end, to avoid roundoff errors) of the positive sales.

Some of this would've been simplified if all the information were in a single table. E.g.

$data = array(
array('emp'=>1234, 'store'=>505, ...),
...
);

'cos then you wouldn't need so many temporary arrays of array indices to keep everything linked together.