PHP MySQL - adding a row in the middle of a loop

Question

I am wondering if it's possible to add a row in a middle of a loop in PHP without having to run the query multiple times using WHERE 'team' = 'leader_name' . Let's saying I am using while ($row = mysqli_fetch_array($results)) to display my results. In the query, I am pulling every team in 1 query. So I have 5 teams and I want to have a separator between each team. Is it possible?

Current results:

Team Leader    |    Name
---------------+---------------------
 Team1         |     Name1
 Team1         |     Name2
 Team2         |     Name3
 Team3         |     Name4
 Team3         |     Name5
 Team3         |     Name6
 Team4         |     Name7
 Team4         |     Name8
 Team5         |     Name9
 Team5         |     Name10
 Team5         |     Name11
 Team5         |     Name12

Expected results:

Team Leader    |    Name
======================================
 Team1         |     Name1
 Team1         |     Name2
-------------------------------------
 Totals:       |     2
-------------------------------------
 Team2         |     Name3
-------------------------------------
 Totals:       |     1
-------------------------------------
 Team3         |     Name4
 Team3         |     Name5
 Team3         |     Name6
-------------------------------------
 Totals:       |     3
-------------------------------------
 Team4         |     Name7
 Team4         |     Name8
-------------------------------------
 Totals:       |     2
-------------------------------------
 Team5         |     Name9
 Team5         |     Name10
 Team5         |     Name11
 Team5         |     Name12
-------------------------------------
 Totals:       |     4
-------------------------------------

Edit

Let's say I use

$data = mysqli_query ("Select * from tbl_name");
while ($row = mysqli_fetch_array($data)) {
    echo "<tr>";
        echo "<td>".$row['Team']."</td>";
        echo "<td>".$row['Name']."</td>";
    echo "</tr>";
}

How would you implement the example below

writeheader();
$team = $data[0]['team'];
$count = 0;
foreach($data as $row){
 $count += 1;
 //if the current row's team is different than previous than add seperator
 if($row['team'] != $team){
   $team = $row['team'];
   writeseperator();
   writetotal($count);
   $count = 0;
 }
 writerow();
}

Where should I put it? I am kinda confused

Can you please elaborate? :)

John Guan
– John Guan

2013-09-16 12:47:04 +00:00
Commented Sep 16, 2013 at 12:47 — John Guan
– John Guan, Commented Sep 16, 2013 at 12:47

Jakub Kania · Accepted Answer · 2013-09-16 12:59:46Z

3

Of course it is possible. You didn't provide your code though so I'll give you pseudocode.

writeheader();
$team = $data[0]['team'];
$count = 0;
foreach($data as $row){
 $count += 1;
 //if the current row's team is different than previous than add seperator
 if($row['team'] != $team){
   $team = $row['team'];
   writeseperator();
   writetotal($count);
   $count = 0;
 }
 writerow();
}

edited Sep 16, 2013 at 12:59

answered Sep 16, 2013 at 12:47

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

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

2 Comments

Bere Over a year ago

what about the count?

John Guan Over a year ago

Sorry, I am not too sure how to use the code. I'll put an example of my code in the post.

eggyal · Accepted Answer · 2013-09-16 12:48:50Z

1

You could get something similar directly from MySQL using the WITH ROLLUP modifier to its GROUP BY clause:

SELECT   Team_Leader, Name, COUNT(*)
FROM     my_table
GROUP BY Team_Leader, Name WITH ROLLUP

See it on sqlfiddle.

Rendering the results in the desired format would then simply be a case of switching on whether the Name column is NULL.

answered Sep 16, 2013 at 12:48

eggyal

127k18 gold badges220 silver badges244 bronze badges

7 Comments

Alma Do Over a year ago

That not what OP wants, it seems.

eggyal Over a year ago

@AlmaDoMundo: Care to elaborate?

Alma Do Over a year ago

Quick view: it will produce 3 columns while OP needs 2.

eggyal Over a year ago

@AlmaDoMundo: Presentation is usually left to the application layer (in this case PHP), not the database.

Alma Do Over a year ago

May be. As I've understood - OP wants SQL. May be I'm wrong. But in any case such things - I agree - better to do in application.

|

Ruben Serrate · Accepted Answer · 2013-09-16 12:49:47Z

0

I'm not sure if I understand what you want but this could work:

$team="";
while ($row = mysqli_fetch_array($results)) {
    if($team!=$row["leader_name"]) {
        if(!empty($team)) {
            //echo your separator or whatever here
        }
        $team=$row["leader_name"];  
        $team_count=1;
    } else {
        $team_count++;
    }
}

answered Sep 16, 2013 at 12:49

Ruben Serrate

2,8031 gold badge21 silver badges22 bronze badges

Comments

Bere · Accepted Answer · 2013-09-16 13:16:34Z

$a= array(array("Team1","Name1"),array("Team1","Name2"),array("Team2","Name3"),array("Team3","Name4"),array("Team3","Name5"),array("Team3","Name6"),array("Team4","Name7"),array("Team4","Name8"),array("Team5","Name9"),array("Team5","Name10"),array("Team5","Name11"),array("Team5","Name12"));

$columns = array_unique(array_column($a, 0)); //get the 'TeamX' part and select unique ones

$count = 0;
foreach($columns as $column){ //for each unique team
   foreach($a as $row){
       if($row[0] == $column){ //search for a matching column and display it.
           //echo team and name
           $count++;  //keep track of items quantity
         }
       }      
   echo "-------------------------------------<br/>";
   echo "Totals: $count <br/>";
   echo "-------------------------------------";
   $count = 0; //reset count for the next team
}

or Based on @eggyal's query make a this modification and

$query = "SELECT   a.Team_Leader, a.Name, cnt
FROM     my_table a 
left join (select Team_Leader, count(Name) as cnt from my_table group by Team_Leader)
b on a.Team_Leader = b.Team_Leader";

Then you can find total number of names in a team in each row.

Collectives™ on Stack Overflow

PHP MySQL - adding a row in the middle of a loop

Edit

4 Answers 4

2 Comments

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

4 Answers 4

2 Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related