PHP SQL Loop results based on field

Question

I am creating a dynamic recordset where I populate a couple values to the query used to select all inked records.

Next I need loop and group the results but am unsure how to group by the values used in the query.

Reference query:

SELECT fldItem, fldDescription, fldRank,fldType FROM tableName
WHERE fldType IN (33, 34, 36) ORDER BY fldRank

The values 33,34 and 36 are going to be passed dynamically to the query. My goal is to then have a loop/repeat region that allows display in groups with all the results that share the same fldType

Results should look like this:

item1, description1, rank1, type33
item2, description2, rank2, type33
item3, description3, rank5, type33
item4, description4, rank6, type33
item5, description5, rank8, type33

--------

item6, descriptionv, rank1, type34
item7, descriptionw, rank2, type34
item8, descriptionx, rank3, type34
item9, descriptiony, rank4, type34
item10, descriptionz, rank5, type34

--------

item11, descriptionA, rank2, type36
item12, descriptionB, rank3, type36

using a do / while loop does displays all the items to display but not with any grouping. Is a ForEach loop the solution?

Have you tried different SQL statements with a GROUP BY? That would return the results in groups. You could then use a loop and when the Type changes, have it drop out a line break, or whatever you want to distinguish the different types. — kevingreen
– kevingreen, Commented Mar 29, 2012 at 15:41
GROUP BY does not return the results in groups, it returns a single result for a group. I.e., using GROUP BY here would result in getting just three rows back. — Alex Howansky
– Alex Howansky, Commented Mar 29, 2012 at 15:52
Yes initially I was trying GROUP BY but found it not working when used with the selection IN () — Burndog
– Burndog, Commented Mar 29, 2012 at 15:56

Alex Howansky · Accepted Answer · 2012-03-29 15:43:31Z

3

If you're talking about a visual grouping in your report output, then you'll need to track that value for each loop iteration, and then issue your visual break when you detect a change. Something like this:

$last_type = null;
foreach ($records as $record) {
    if ($last_type !== null && $last_type != $record->fldType) {
        echo '<whatever your visual cue is>';
        $last_type = $record->fldType;
    }
}

Also, make sure you do:

ORDER BY fldType, fldRank

answered Mar 29, 2012 at 15:43

Alex Howansky

54.1k8 gold badges85 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Burndog Over a year ago

Yes, I believe this is what I am looking for. Thanks!

Bas · Accepted Answer · 2012-03-29 15:40:20Z

1

SELECT fldItem, fldDescription, fldRank,fldType FROM tableName
WHERE fldType IN (33, 34, 36) ORDER BY fldType, fldRank

This will group by fldType first, then by fldRank. In your PHP code, just remember the last fldType, and add a new group whenever it changes. All fldTypes will be grouped together now, so you can just loop through the results in order.

answered Mar 29, 2012 at 15:40

Bas

3,1363 gold badges24 silver badges18 bronze badges

1 Comment

Burndog Over a year ago

Yes, this returns the results in the manner referenced above but I am trying to get groups such as displayed by the "--------" breaks. In actual conditions this is because the results (each group) are to be set to a formatted table.

Collectives™ on Stack Overflow

PHP SQL Loop results based on field

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related