Counting array items equal to a certain value

[Bilbozilla]

Hello,

I have a database query that returns an array (color_id, item, sku). I do a search for a number of items, and I would like to count the number of occurrences of a particular color_id so that when I display the data, I can have a heading that aggregates and displays the color and its associated items. For example,

Red (color_id=1)

[INDENT] apple[/INDENT][INDENT]fire hydrant[/INDENT][INDENT]cherry[/INDENT]
(3 items)

Yellow (color_id=2)

[INDENT]sun[/INDENT][INDENT]strange snow[/INDENT][INDENT]pineapple[/INDENT][INDENT]corvette[/INDENT]
(4 items)

As colors can increase, is there a function that you add before to count unique elements of query to assist with this? I am very new to this and quickly "outsmarting" myself, but I'm learning.

The query is fine, I am trying to write the routine that counts the occurrences of each color_id, pulls that out, then displays the subsequent items.

[Bilbozilla]

So here is the basis of the function that builds the checkboxes.

 /* =====String to print the value for the color should go here          ======*/
/* ===== Some sort of while or counting mechanism should go here ====== */

foreach ($arr as $ele) {
      $str .= "<td><p><input type=\"checkbox\" name=\"$name\" value=\"$ele->id\">";
      $str .= "$ele->value";

  if ($i % $num == 0) {
    $str .= "</tr>\n<tr>";
  } else {
    $str .= "</td>\n";
  }
  $i++;
}

I'm really not sure where to begin.

[laserlight]

Bilbozilla wrote:

I have a database query that returns an array (color_id, item, sku). I do a search for a number of items, and I would like to count the number of occurrences of a particular color_id so that when I display the data, I can have a heading that aggregates and displays the color and its associated items.

What is the query? You could just sort it and then count while displaying, or you could run another query to group by colour and count the number of rows per group.

[Bilbozilla]

$query = "SELECT color_id, item_name, sku
FROM lu_colors, tbl_items
WHERE tbl_items.color_id=color_id.sku
AND lu_colors.sku='sku' 
ORDER BY color_id, item_name";

The query outputs (color_id, item_name, sku) in each row.

I'd like the color_id values counted, but the reference of color displayed only once as shown in the original post. I can get it grouped and alphabetized, but cannot figure out the counting method, nor the logic for displaying it once while the rest of the grouped data gets displayed.

tbl_colors - This table just lists colors. Can be expanded. No input from user

(id, color_name)

lu_colors - This table is populated by user entries selecting colors associated with items displayed in a checkbox form

(id, sku, color_id)

tbl_items

(id, color_id, item_name)

The purpose of this design is so that it scales. I can always add items and colors, and the webpages will appropriately populate it without having to re-enter any html code.

[Bilbozilla]

Maybe someone can point me in the right direction with this example:

(id, color, item)
(1, red, car)
(2, red, apple)
(3, red, barn)
(4, red, crayon)
(5, yellow, sun)
(6, yellow, butter)
(7, brown, bag)
(8, red, fingernail)

This is the result (wrong format, of course) of a

SELECT * from table

I'd like it to display the following:

RED

car
apple
barn
crayon
fingernail

YELLOW

sun
butter

BROWN

bag

I don't know how to just pull 1 instance of the color out, display it independent of the rest of the data, and then grab the details. There may be additional colors added, or there may be colors in the database without items of that color.

Can someone post a generic solution?

I am thinking first, grab all the items, and then put a single instance of each color into an array. Following this, do a while loop that grabs the records that have the appropriate color and display those.

The problem is, I am new to php and cannot grasp the methodology or the language to do this. I can see the logic, but don't know the syntax. For all I know, there is an even better solution.

If I had any hair left to pull out, I would.

[Bilbozilla]

This question seems to have a lot of views, but little insight. Is this possible to do? As I mentioned, I can kind of see the logic, but I don't get the syntax.

[Weedpacket]

Bilbozilla wrote:

This question seems to have a lot of views, but little insight.

Some of the views are by bots, some of the insight is in other threads that cover the same topic.

for each record in the result set
$records_grouped_by_colour[$record['colour']][] = $record;

Now $records_grouped_by_colour is an array:
$records_grouped_by_colour['red']
(1, red, car)
(2, red, apple)
(3, red, barn)
(4, red, crayon)
$records_grouped_by_colour['yellow']
(5, yellow, sun)
(6, yellow, butter)
$records_grouped_by_colour['brown']
(5, yellow, sun)
(6, yellow, butter)

[Weedpacket]

Bilbozilla wrote:

This question seems to have a lot of views, but little insight.

Some of the views are by bots, some of the insight is in other threads that cover the same topic.

for each record in the result set
$records_grouped_by_colour[$record['colour']][] = $record;

Now $records_grouped_by_colour is an array:
$records_grouped_by_colour['red']
(1, red, car)
(2, red, apple)
(3, red, barn)
(4, red, crayon)
$records_grouped_by_colour['yellow']
(5, yellow, sun)
(6, yellow, butter)
$records_grouped_by_colour['brown']
(5, yellow, sun)
(6, yellow, butter)