[SOLVED] group and count numbers returned from database

tryingtolearn · January 20, 2008

Hi all

Not sure what to use to go about doing this,

I have a database field that has a set of numbers like this

ID number

1 02.07.11.23.34

2 11.16.23.52.73

3 03.11.52.73.99

etc...

I need to count and group the results of the entire database listed like this

02 -1

03 -1

07 -1

11 -3

16 -1

23 -2

and so on...

So I can see how many times the number is present

Any ideas?

Thanks

trq · January 20, 2008

You've made it pretty hard on yourself. Any reason these numbers can't be stored within there own row?

ratcateme · January 20, 2008

you could create a sql query then explode each row and add the numbers to an array then run count commands on that array

Scott.

tryingtolearn · January 20, 2008

That is how the DB is populated

But I dont have control over that portion.

Just the results.

I guess I could take that data and then make a new DB???

tryingtolearn · January 20, 2008

Thanks Scott - I will look into that

tryingtolearn · January 20, 2008

Stuck again,

I have it at a point where I have the new array with all the numbers in it

How do I get the count of the individual occurances of a number?

// Make the query.
$query = "SELECT num FROM bon";		
$result = @mysql_query ($query); // Run the query.
if ($result) {
while ($row = mysql_fetch_assoc($result)){

$listnum=$row['num'];
$numarray = explode('.',$listnum);

foreach($numarray as $key=>$value){
$myarray=array($value);

}//end foreach

}//end while

trq · January 20, 2008

<?php

  $totals = array();
  $query = "SELECT num FROM bon";
  if ($result = mysql_query ($query)) {
    if (mysql_num_rows(4result)) {
      while ($row = mysql_fetch_assoc($result)){
        $listnum = $row['num'];
        $numarray = explode('.',$listnum);
        foreach ($numarray as $key) {
          if (array_key_exists($key,$totals)) {
            $totals[$key] = $totals[$key]++;
          } else {
            $totals[$key] = 1;
          }
        }
      }
    }
  }

?>

This should give you an array ($totals), where each $value will be the number of times this number appears. eg, Show how many times the number 3 appeared...

<?php

  echo "the number 3 appears {$totals[3]} times";

?>

tryingtolearn · January 20, 2008

Thanks Thorpe,

I tried that and get an

undefined offset: 3

For the line

the number 3 appears {$totals[3]} times

Any ideas?

trq · January 20, 2008

Does the number 3 exist within the array? Try...

<?php print_r($totals); ?>

tryingtolearn · January 20, 2008

No - it isnt in the array (But it should be)

And when I do print_r it is only picking up about 30 numbers out of thousands??

trq · January 20, 2008

Are there literally thousands of different numbers or just thousands of numbers?

Can I see the output of print_f()?

tryingtolearn · January 20, 2008

Yeah, Thousands is a bit extreme

Here are the results of print_r

Array ( [09] => 1 [14] => 1 [16] => 1 [18] => 1 [20] => 1 [02] => 1 [22] => 1 [25] => 1 [28] => 1 [04] => 1 [13] => 1 [05] => 1 [15] => 1 [29] => 1 [38] => 1 [11] => 1 [27] => 1 [31] => 1 [21] => 1 [33] => 1 [34] => 1 [10] => 1 [12] => 1 [24] => 1 [19] => 1 [30] => 1 [32] => 1 [03] => 1 [08] => 1 [23] => 1 [26] => 1 [07] => 1 [37] => 1 [39] => 1 [35] => 1 [36] => 1 [17] => 1 [01] => 1 [06] => 1 )

and here are all the # sets from the database

Im only working with the middle set separated by the .

VALUES (18, '04.13.21.26.32', '5'), (17, '05.13.21.23.37', '18'), (16, '04.11.19.23.37', '26'), (15, '02.03.04.05.30', '14'), (14, '03.05.11.15.21', '17'), (13, '20.24.29.37.38', '32'), (19, '02.08.10.21.32', '36'), (20, '05.11.26.36.39', '33'), (21, '04.05.20.26.38', '7'), (22, '14.28.30.33.34', '1'), (23, '04.06.27.33.37', '2'), (24, '06.18.23.28.36', '22'), (25, '15.16.27.35.39', '22'), (26, '04.06.10.12.38', '25'), (27, '06.09.10.23.32', '12'), (28, '04.10.18.25.27', '38'), (29, '03.15.25.28.32', '4'), (30, '14.21.32.37.38', '31'), (31, '01.08.15.18.24', '2'), (32, '02.06.11.14.19', '8'), (33, '06.08.13.28.29', '5'), (34, '19.20.24.27.30', '14'), (35, '04.11.29.30.34', '7'), (36, '14.16.20.30.38', '34'), (37, '05.17.20.27.33', '15'), (38, '04.11.23.34.37', '5'), (39, '01.12.18.26.33', '21'), (40, '06.10.29.37.39', '11'), (41, '01.04.14.16.38', '21'), (42, '01.06.12.23.38', '7'), (43, '03.08.22.25.30', '1'), (44, '16.21.23.30.33', '3'), (45, '16.30.31.35.38', '17'), (46, '13.25.26.32.34', '2'), (47, '01.16.21.28.33', '30'), (48, '08.20.24.27.34', '12'), (49, '20.23.24.25.29', '17'), (50, '09.21.25.31.34', '13'), (51, '02.15.21.28.39', '37'), (52, '02.09.27.29.38', '16'), (53, '06.14.21.29.38', '34'), (54, '10.13.19.20.28', '3'), (55, '03.09.13.22.34', '37'), (56, '15.17.21.22.28', '35'), (57, '29.30.33.34.36', '3'), (58, '02.08.17.29.36', '27'), (59, '11.20.26.33.38', '24'), (60, '07.09.16.25.30', '35'), (61, '01.16.19.33.36', '14'), (62, '11.22.24.26.30', '7'), (63, '02.09.22.30.39', '19'), (64, '14.16.18.22.39', '10'), (65, '05.10.15.18.38', '24'), (66, '01.05.09.18.20', '32'), (67, '11.17.18.22.33', '20'), (68, '01.06.24.26.33', '5'), (69, '09.13.16.19.22', '2'), (70, '20.27.28.30.31', '37'), (71, '04.14.28.31.34', '1'), (72, '10.15.16.34.38', '32'), (73, '11.13.15.19.38', '35'), (74, '08.22.30.31.39', '38'), (75, '06.11.12.19.38', '28'), (76, '13.14.20.28.30', '1'), (77, '02.17.26.38.39', '28'), (78, '01.03.16.18.31', '37'), (79, '02.04.29.30.38', '3'), (80, '10.12.13.19.31', '16'), (81, '08.11.19.23.34', '21'), (82, '04.07.11.27.36', '3'), (83, '09.11.24.33.37', '7'), (84, '01.02.09.38.39', '12'), (85, '07.15.23.27.36', '22'), (86, '10.16.25.26.37', '35'), (87, '01.10.11.13.22', '37'), (88, '01.04.14.22.39', '38'), (89, '07.09.18.22.32', '5'), (90, '03.04.08.21.23', '29'), (91, '13.18.19.23.33', '14'), (92, '05.06.27.28.37', '19'), (93, '20.21.24.33.35', '37'), (94, '10.12.17.35.36', '4'), (95, '17.27.28.38.39', '12'), (96, '04.05.16.20.38', '19'), (97, '08.11.18.31.35', '33'), (98, '17.30.34.36.38', '5'), (99, '11.17.28.30.34', '19'), (100, '05.12.16.28.38', '6'), (101, '01.13.19.32.34', '14'), (102, '02.21.28.33.39', '14'), (103, '01.03.14.27.33', '30'), (104, '03.13.14.22.38', '30'), (105, '10.23.27.34.38', '5'), (106, '09.15.35.37.38', '24'), (107, '05.08.19.25.32', '20'), (108, '01.10.25.27.35', '2'), (109, '02.08.09.30.32', '20'), (110, '05.15.21.29.31', '32'), (111, '06.08.13.26.29', '30'), (112, '02.19.24.26.33', '27'), (113, '06.19.24.34.39', '25'), (114, '10.18.25.26.38', '33'), (115, '03.13.27.28.32', '7'), (116, '01.14.17.20.27', '36'), (117, '02.15.16.19.30', '14'), (118, '01.16.17.26.34', '6'), (119, '01.07.23.27.28', '31'), (120, '01.11.20.23.27', '3'), (121, '01.02.11.20.28', '7'), (122, '08.10.13.17.21', '37'), (123, '03.07.12.15.21', '24'), (124, '25.26.33.35.39', '11'), (125, '07.13.14.24.35', '21'), (126, '04.18.21.30.37', '5'), (127, '01.08.13.20.25', '12'), (128, '04.10.23.27.34', '5'), (129, '03.08.22.28.33', '39'), (130, '04.11.23.26.39', '13'), (131, '02.07.11.14.19', '17'), (132, '10.11.13.26.39', '35'), (133, '02.11.22.27.32', '24'), (134, '09.14.16.31.37', '30'), (135, '09.10.20.31.39', '11'), (136, '13.14.15.34.39', '19'), (137, '01.02.08.15.34', '30'), (138, '02.17.22.24.32', '8'), (139, '01.02.16.17.22', '10'), (140, '02.05.11.13.35', '20'), (141, '19.23.25.36.37', '8'), (142, '06.10.16.20.32', '30'), (143, '09.22.23.27.28', '6'), (144, '01.17.30.37.39', '4'), (145, '04.05.12.27.37', '15'), (146, '06.13.19.24.38', '7'), (147, '12.28.30.32.33', '39'), (148, '08.09.13.16.19', '11'), (149, '17.19.27.28.38', '22'), (150, '04.07.10.13.37', '23'), (151, '11.13.17.26.31', '29'), (152, '03.11.16.19.24', '12'), (153, '12.20.26.31.37', '11'), (154, '02.06.20.23.39', '5'), (155, '08.10.20.23.35', '32'), (156, '10.18.34.37.39', '13'), (157, '03.08.25.28.39', '32'), (158, '02.06.11.29.34', '14'), (159, '13.23.33.34.37', '32'), (160, '01.04.16.18.35', '22'), (161, '02.03.13.19.21', '27'), (162, '03.07.21.24.34', '36'), (163, '04.12.14.16.32', '34'), (164, '05.07.12.17.23', '16'), (165, '04.10.28.33.35', '12'), (166, '06.11.15.19.20', '18'), (167, '01.02.11.33.39', '23'), (168, '01.04.12.24.25', '31'), (169, '02.05.12.32.33', '15'), (170, '11.14.20.36.37', '10'), (171, '01.19.24.37.39', '20'), (172, '01.05.06.12.23', '20'), (173, '01.24.28.37.39', '20'), (174, '05.14.16.20.23', '1'), (175, '09.11.16.19.33', '15'), (176, '02.06.16.19.33', '27'), (177, '08.15.20.26.34', '32'), (178, '03.16.21.27.33', '15'), (179, '02.10.14.22.28', '6'), (180, '09.24.25.26.30', '20'), (181, '17.20.27.30.38', '5'), (182, '04.21.23.27.31', '9'), (183, '16.17.21.23.33', '7'), (184, '09.19.28.32.36', '12'), (185, '01.02.14.17.27', '19'), (186, '05.11.15.18.23', '34'), (187, '03.10.20.25.26', '16'), (188, '19.23.29.33.37', '13'), (189, '06.11.14.27.28', '22'), (190, '01.02.05.07.30', '38'), (191, '01.04.15.19.25', '24'), (192, '08.20.25.29.39', '9'), (193, '11.16.19.29.34', '6'), (194, '03.10.11.25.38', '32'), (195, '02.09.10.15.28', '26'), (196, '09.11.22.23.34', '3'), (197, '02.09.24.34.36', '32'), (198, '01.19.21.30.36', '12'), (199, '09.11.12.13.39', '24'), (200, '09.11.13.30.33', '39'), (201, '20.21.23.33.38', '11'), (202, '02.03.08.11.36', '27'), (203, '18.23.32.38.39', '1'), (204, '01.03.11.30.31', '4'), (205, '07.16.20.24.32', '13'), (206, '02.18.24.26.32', '31'), (207, '03.08.10.18.21', '36'), (208, '06.15.32.37.39', '35'), (209, '14.20.28.30.31', '19'), (210, '03.08.15.24.33', '25'), (211, '05.11.14.19.36', '15'), (212, '03.07.08.17.27', '25'), (213, '06.08.20.26.34', '16'), (214, '08.10.15.21.35', '6'), (215, '02.09.16.20.36', '22'), (216, '08.09.18.22.28', '36'), (217, '06.10.11.14.23', '34'), (218, '03.14.25.35.37', '39'), (219, '26.28.29.30.32', '13'), (220, '23.24.28.30.31', '9'), (221, '07.08.30.34.37', '33'), (222, '02.04.10.21.30', '5'), (223, '15.28.31.33.34', '5'), (224, '09.11.19.25.28', '37'), (225, '04.05.17.21.24', '32'), (226, '17.23.30.33.39', '24'), (227, '10.14.15.29.37', '20'), (228, '09.13.17.21.29', '1'), (229, '04.07.09.20.28', '2'), (230, '01.04.10.15.31', '29'), (231, '06.10.13.23.38', '18'), (232, '06.08.30.34.39', '23'), (233, '06.17.24.28.37', '29'), (234, '06.13.20.26.28', '23'), (235, '01.10.23.27.33', '29'), (236, '01.10.19.21.25', '17'), (237, '02.12.20.26.33', '17'), (238, '01.02.21.36.38', '12'), (239, '02.03.07.20.26', '32'), (240, '03.23.29.32.35', '9'), (241, '04.06.20.28.34', '10'), (242, '07.15.26.31.32', '18'), (243, '13.15.24.30.32', '34'), (244, '02.03.18.19.28', '33'), (245, '06.13.15.27.32', '37'), (246, '11.12.26.36.39', '16'), (247, '10.11.14.24.38', '27'), (248, '06.08.26.33.34', '19'), (249, '16.20.36.38.39', '18'), (250, '02.13.18.21.26', '31'), (251, '04.09.12.24.28', '16'), (252, '15.16.31.32.36', '1'), (253, '12.15.17.29.34', '33'), (254, '20.21.26.27.39', '13'), (255, '15.20.24.26.30', '16'), (256, '18.27.30.34.36', '33'), (257, '01.11.25.31.38', '34'), (258, '04.09.33.35.38', '17'), (259, '05.10.11.18.29', '32'), (260, '06.07.10.19.37', '18'), (261, '03.04.05.10.28', '35'), (262, '15.20.21.29.37', '11'), (263, '04.14.16.30.39', '31'), (264, '02.17.19.25.28', '15'), (265, '05.06.17.24.25', '34'), (266, '10.15.16.23.31', '35'), (267, '02.04.09.11.15', '38'), (268, '06.11.20.22.26', '39'), (269, '03.04.11.25.35', '38'), (270, '03.08.16.22.33', '14'), (271, '03.18.23.24.34', '37'), (272, '08.18.22.33.34', '36'), (273, '11.20.27.29.32', '19'), (274, '01.07.14.27.36', '12'), (275, '02.07.15.25.35', '32'), (276, '10.15.17.20.38', '22'), (277, '07.16.29.30.34', '2'), (278, '13.29.30.36.39', '21'), (279, '24.26.29.34.39', '11'), (280, '03.20.24.25.39', '38'), (281, '06.19.22.37.39', '14'), (282, '06.07.09.11.21', '17'), (283, '03.24.33.37.39', '30'), (284, '05.07.26.29.31', '1'), (285, '02.13.21.27.32', '8'), (286, '04.05.12.16.35', '31'), (287, '01.03.16.17.21', '2'), (288, '08.09.11.18.28', '30'), (289, '17.19.23.24.28', '7'), (290, '02.03.08.35.38', '20'), (291, '02.14.15.22.35', '20'), (292, '03.05.11.23.32', '1'), (293, '06.19.27.33.39', '34'), (294, '08.12.15.22.32', '14'), (295, '10.14.21.23.33', '8'), (296, '02.17.28.32.34', '3'), (297, '07.08.12.35.38', '27'), (298, '05.10.14.20.34', '29'), (299, '16.20.21.24.35', '38'), (300, '13.15.18.26.36', '20'), (301, '03.04.12.14.24', '29'), (302, '09.13.19.29.32', '5'), (303, '01.11.16.22.34', '12'), (304, '01.03.09.27.33', '26'), (305, '17.32.33.34.37', '10'), (306, '09.10.22.24.36', '30'), (307, '09.11.27.29.31', '5'), (308, '07.14.16.19.20', '2'), (309, '07.10.12.14.20', '38'), (310, '05.17.25.31.32', '4'), (311, '08.14.19.26.39', '15'), (312, '09.18.20.31.35', '14'), (313, '05.08.12.16.24', '18'), (314, '01.13.15.22.23', '8'), (315, '13.15.16.28.30', '5'), (316, '05.09.11.22.23', '10'), (317, '02.08.12.17.25', '37'), (318, '06.10.19.21.37', '39'), (319, '09.13.14.21.37', '12'), (320, '18.24.27.28.37', '2'), (321, '01.12.20.22.39', '27'), (322, '10.20.28.38.39', '31'), (323, '12.14.17.19.28', '33'), (324, '02.04.15.19.33', '17'), (325, '20.31.34.35.38', '27'), (326, '03.08.15.19.21', '22'), (327, '02.04.21.24.38', '14'), (328, '01.22.27.30.35', '8'), (329, '02.09.11.32.39', '10'), (330, '01.09.25.30.38', '19'), (331, '05.10.25.28.30', '12'), (332, '05.09.12.17.19', '11'), (333, '05.08.22.23.29', '38'), (334, '13.21.24.29.35', '8'), (335, '11.12.28.32.38', '3'), (336, '04.09.10.34.37', '14'), (337, '01.07.11.18.22', '6'), (338, '13.18.27.32.38', '31'), (339, '03.13.20.22.37', '17'), (340, '03.13.25.30.37', '11'), (341, '01.08.20.21.27', '3'), (342, '13.15.31.33.37', '6'), (343, '02.07.09.17.29', '20'), (344, '06.18.24.28.39', '30'), (345, '14.15.24.26.39', '18'), (346, '13.16.17.20.38', '8'), (347, '01.17.22.29.38', '31'), (348, '07.14.23.29.37', '8'), (349, '07.19.25.27.33', '12'), (350, '03.23.25.27.35', '39'), (351, '01.13.21.22.37', '24'), (352, '07.11.17.34.38', '24'), (353, '11.12.16.28.32', '26'), (354, '02.05.11.26.29', '19'), (355, '15.16.17.18.39', '13'), (356, '08.09.24.26.32', '30'), (357, '08.21.24.29.31', '10'), (358, '04.08.11.21.27', '13'), (359, '13.22.29.36.37', '5'), (360, '03.15.25.31.33', '6'), (361, '13.18.19.24.31', '34'), (362, '04.09.13.21.35', '14'), (363, '04.09.32.34.39', '19'), (364, '08.19.27.31.34', '20'), (365, '09.12.18.29.37', '27'), (366, '16.27.29.37.39', '34'), (367, '07.10.15.19.23', '3'), (368, '18.22.23.26.31', '20'), (369, '02.03.04.05.08', '30'), (370, '14.15.19.30.32', '16'), (371, '04.10.12.13.24', '6'), (372, '11.13.21.33.34', '4'), (373, '02.11.18.27.31', '10'), (374, '05.13.15.29.38', '3'), (375, '04.13.14.18.25', '2'), (376, '02.09.22.25.28', '19'), (377, '09.14.16.18.20', '27');

trq · January 20, 2008

You might try chnging this line...

$totals[$key] = $totals[$key]++;

to...

$totals[$key] = $totals[$key]+1;

Otherwise, I'll need to take a look when I get home and can do some testing.

tryingtolearn · January 20, 2008

Well Thanks Thorpe but that didnt change anything.

I appreciate your time.

trq · January 20, 2008

Tested, this works.

$totals = array();
$query = "SELECT num FROM bon";
if ($result = mysql_query ($query)) {
    if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_assoc($result)){
            $listnum = $row['num'];
            $numarray = explode('.',$listnum);
            foreach ($numarray as $key) {
                $totals[$key] += 1;
            }   
        }   
    }   
}

echo '<pre>';
print_r($totals);
echo '</pre>';

?>

tryingtolearn · January 20, 2008

Thanks Thorpe but it still isnt working for me

Still getting the undefined index errors.

Im lost!

trq · January 20, 2008

Because the index is not 3 but 03 (if its 3 your trying). Code works fine for me.

trq · January 20, 2008

Youy actually need to use $totals['03'];

tryingtolearn · January 20, 2008

Dont get why it doesnt work for me

Oh well,

If you have to know the number beforehand that wont work for this anyway since I wont know what the #s are

Back to the drawing board

tryingtolearn · January 20, 2008

This is giving me each individual #

without any errors

So is there anything to count them from this?

$query = "SELECT num FROM bon";		
$result = @mysql_query ($query); // Run the query.
if ($result) {
   while ($row = mysql_fetch_assoc($result)){
   $listnum=$row['num'];
   $numarray = explode('.',$listnum);
   			 foreach($numarray as $key=>$value){
		 echo"$value<br />";
		}
	}
}

tryingtolearn · January 21, 2008

Easiest solution for anyone following

$array = array();
while ($row = mysql_fetch_assoc($result)) {
    $array = array_merge($array, explode('.', $row['num']));
}
print_r(array_count_values($array));

Sign In

[SOLVED] group and count numbers returned from database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information