I can count, but my code can't

[robj]

I have a field that contains about 300 2-digit numbers. I want to count how many times each number appears. I've tried using 'count', but can't for the life of me get it to work correctly(ehem... at all). I honestly don't know how to program this.

 

Can someone provide me with a script that would accomplish the following:

  1. count how many time a number appears in the field, starting with 00 as the base number

  2. auto increment the base number up to 99

  3. echo as such: NUMBER## appears ## times

 

Thanks in advance.

 

rob

[Jeremysr]

You could keep a count of each number in a 100-element array, loop through the 300 numbers, and increment the array element corresponding to the current number.

 

// Initialize array
for ($i = 0; $i < 100; $i++) {
    $numbers[$i] = 0;
}

// Count the 300 numbers, I'm assuming they're in a database
$sql = "SELECT two_digit_number FROM a_table";
$result = mysql_query($sql);
while ($current_number = mysql_fetch_assoc($result)) {
    $the_number = intval($current_number['two_digit_number']);
    $numbers[$the_number]++;
}

// Output the number counts
foreach ($numbers as $number => $count) {
    echo "$number appears $count times\n";
}

[Orio]

From your post I can't really understand how your numbers are stored. Database? An array? Something else?

If it's in an array, you could use array_count_values() and then just go over them using a foreach (If you want to sort it, use ksort() first):

 

<?php

$numbers = array(1,5,2,1);

$count = array_count_values($numbers);
ksort($count);
foreach($count as $key => $val)
echo "Number {$key} appears {$val} times";

?>

 

 

Orio.

[PFMaBiSmAd]

I'm going to guess a properly normalized database table -

 

SELECT your_column, COUNT(*) FROM your_table GROUP BY your_column

[Prismatic]

I'm going to guess a properly normalized database table -

 

SELECT your_column, COUNT(*) FROM your_table GROUP BY your_column

 

You really only need to return the data you need,

 

$query = "SELECT disctinct(your_column), COUNT(*) FROM your_table GROUP BY your_column"  

 

[PFMaBiSmAd]

GROUP BY does that when it makes the groups.

[Prismatic]

GROUP BY does that when it makes the groups.

 

Whatever!

[robj]

// Initialize array
for ($i = 0; $i < 100; $i++) {
    $numbers[$i] = 0;
}
// Count the 300 numbers, I'm assuming they're in a database
while ($current_number = mysql_fetch_assoc($result)) {
    $the_number = intval($current_number['two_digit_number']);
    $numbers[$the_number]++;
}

// Output the number counts
foreach ($numbers as $number => $count) {
    echo "$number appears $count times<br />";
}

The above doesn't exactly work. It's outputs as follows: 0 appears 0 times      1 appears 0 times    2 appears 0 times      3 appears 0 times      4 appears 0 times

 

The code below is excellent in it's current form. Add as many numbers in the array as you'd like and it does the job great.

$numbers = array(1,5,2,1);

$count = array_count_values($numbers);
ksort($count);
foreach($count as $key => $val)
   echo "Number {$key} appears {$val} times";

 

However, I cannot get the data from the database into either of these scripts. I can only assume the way I'm connecting my DB is causing the issue. So here it is:

// MySQL Connection
$connection = mysql_connect("localhost", "root", "");
if (!$connection) {
die('Counld Not Connect, Dummy! ' . mysql_error());
}

mysql_select_db("tasks", $connection);

$result = mysql_query("SELECT userID FROM marketing");

 

Keep in mind, that I'm use XAMPP locally to develop this project (might also be issue). So Orio, if you have any suggestions, I'd appreciate it.

 

rob

[PFMaBiSmAd]

If you just want the numbers that are present (tested) -

$query = "SELECT your_column, COUNT(*) as cnt FROM your_table GROUP BY your_column";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
while($row = mysql_fetch_assoc($result)){
echo "NUMBER {$row['your_column']} appears {$row['cnt']} times<br />";
}

 

If you want all the numbers, including zeros (tested) -

 

$query = "SELECT your_column, COUNT(*) as cnt FROM your_table GROUP BY your_column";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
$numbers = array();
while($row = mysql_fetch_assoc($result)){
$numbers[$row['your_column']] = $row['cnt'];
}
for($i=0;$i < 100;$i++){
$cnt = isset($numbers[$i]) ? $numbers[$i] : 0 ;
echo "NUMBER {$i} appears {$cnt} times<br />";
}

[robj]

Works perfect PFMaBiSmAd,

 

Thanks.

[robj]

So how would i accomplish multiple columns?

I'm able to get the data from 2 columns, but I'm having issues with GROUP BY:

$query = "SELECT your_column1, your_column2, COUNT(*) as cnt FROM your_table GROUP BY ?????";

while($row = mysql_fetch_assoc($result)){
   echo "NUMBER {$row['?????']} appears {$row['cnt']} times<br />";
}

 

GROUP BY your_column1,your_column2 does not work

 

[PFMaBiSmAd]

You would need to describe or show an example of what the data is and what you want the results to be.

[robj]

Certainly. Keeping the long story short. I created an app to assign and manage tasks. I'm very comfortable with the mysql query basics, but not the 'advanced' queries.

 

The table in question is made up of 6 fields (id, date, department, task, assign_to, assign_by). assign_to and assign_by are made up of a 2-digit ID assign that's related to a user/employee/client via another table. I want to generate a 'top assigned to', and 'top assigned by' report, which the above has accomplished. However (and really just for learning purposes), I want to know how many times an ID/user has been referenced.

 

Explained:

SELECT assign_to,assign_by, COUNT (*) as cnt FROM table_name GROUP BY id_within_added_column ORDER BY cnt DESC LIMIT 5

 

I'm not entirely sure where the breakdown lies. The query is definitely reading both columns and counting within them, but not adding the ID from both as 1 column. So when I GROUP BY assign_to, the output is of results is correct, but not when I GROUP BY assign_to,assign_by I get a low count and no ID.

[robj]

I guess the other option is to merge both columns then COUNT and ORDER BY.

 

Anyone know the best way to accomplish this task?