PHP array stuff

[bluemicrobyte]

So what I'm trying to accomplish is this: I have a table in a MySQL database - one of the columns has numbers in it (IDs). Each number (ID) can appear any number of times. I want to find the number (ID) that appears in the most number of entries.

So far, I have

connect_db();
$query = "SELECT * FROM tablename WHERE id != 0";
$result = mysql_query($query) or die ('Could not query database'.mysql_error());
$ids = array();
while ($row = mysql_fetch_row($result)) {
array_push($ids, $row[4]);
}


which should (hasn't been tested) put ALL the numbers (IDs) in an array. How would I go about figuring out which number appears in the array the most number of times? Or, is there a more efficient way to figure out which number appears the most when I query the database?

And for those that want an example of what I'm trying to do, I have the following array:

$array = array(4, 5, 3, 4, 3, 6, 1, 5, 4, 9, 4, 4);

And I want to know how to find which number appears most in the array (in this case the number 4).

[Modred]

Here's an idea:

PHP allows you to manually set the keys for an array. You could use this to set the keys as the values in the array. The first time a number is encountered, it adds an entry to the array with that value as the key and a value of 1. Each additional time the number is encountered, the corresponding counter value in the other array is incremented. At the end, you find the highest value in your counting array and return the key that indexes it.

Just what popped into my head, so there's probably a better way.

[bluemicrobyte]

Here's an idea:

PHP allows you to manually set the keys for an array. You could use this to set the keys as the values in the array. The first time a number is encountered, it adds an entry to the array with that value as the key and a value of 1. Each additional time the number is encountered, the corresponding counter value in the other array is incremented. At the end, you find the highest value in your counting array and return the key that indexes it.

Just what popped into my head, so there's probably a better way.

Alright, I understand basically how keys work:
$array = array('key1' => 'value1', 'key2' => 'value2');
echo $array['key1']; // outputs value1
echo $array['key2']; // outputs value2
But I don't quite get what your saying to do. I think I kind of get it, but could I have an example?

edit: I think I get what you mean, but I'm not sure how to actually do it with PHP code. I don't have THAT much experience with keys =P

[TimP]

You can do it with one SQL query.

SELECT ID, COUNT(ID) AS Qty FROM tablename GROUP BY ID ORDER BY Qty DESC LIMIT 1;

It will return one row and two columns. The first column is the ID, the second column is the number of times it occured.

[bluemicrobyte]

ah, that's exactly what I was looking for, thanks TimP! /karma for you (if it still existed....)

(I'll test it in a bit and post here if I have problems)

edit: how would I go about making it so that it ignores values of 0?

edit2: this seems to work:

SELECT ID, COUNT(ID) AS Qty FROM tablename WHERE winner_id != 0 GROUP BY ID ORDER BY Qty DESC LIMIT 1;

[Modred]

Well, now that TimP has mentioned a simpler way to do it, I don't think this is necessary, but here's something like what I was thinking. With the array_count_values() function, you could transform the first array into something like the second.

$array = array(4, 5, 3, 4, 3, 6, 1, 5, 4, 9, 4, 4);

$reps = array_count_values($array);
//that makes it look like this
//$reps = array(4 => 5 , 5 => 2 , 3 => 2 , 6 => 1 , 1 => 1, 9 => 1)

You would then use a foreach loop to find the highest value in the repetition array.

$highest = 0;

foreach($reps as $key => $val)
{
if($val > $highest)
{
$highest = $val;
$index = $key;
}
}

At the end, $highest will have the number of repetitions and $index will have the value that was repeated. Of course, it could use a little modification, such as initializing $highest to a value in the array instead of 0, but this gives you the idea.

[bluemicrobyte]

Hmmm, alright, I'll hold on to that example for future reference. Thanks =P

[Modred]

For future reference, here's a better way to use that foreach loop.

//set $highest to a value in $reps
$highest = current($reps);

foreach($reps as $key => $val)
{
if($val > $highest)
{
$highest = $val;
$index = $key;
}
}

[Black.Ice]

You can do it with one SQL query.

SELECT ID, COUNT(ID) AS Qty FROM tablename GROUP BY ID ORDER BY Qty DESC LIMIT 1;

It will return one row and two columns. The first column is the ID, the second column is the number of times it occured.

Quick question TimP. Can you please explain how the above statement works? My SQL is a bit rusty, and I'm interested in getting back into it.

I have a general idea of SQL, so a brief explanation would suffice.

Thanks.

[TimP]

COUNT(ID) counts the numbers of IDs and it's returned as a column called Qty, GROUP BY combines all the identical IDs into one row. It's ordered by the ID that had the most number counted and LIMIT 1 returns the row with the highest count.

[Black.Ice]

COUNT(ID) counts the numbers of IDs and it's returned as a column called Qty, GROUP BY combines all the identical IDs into one row. It's ordered by the ID that had the most number counted and LIMIT 1 returns the row with the highest count.


Thanks for the reply. The AS and GROUP were the two things that I was a bit rusty on.