PHP MYSQL need to group rows that have same value in certain column

94
February 22, 2019, at 01:50 AM

I have a table named 'entries' with the columns: originator, collaborator, engagement_partner, niche, and fee_potential. Some of the rows in the table have the same originator. I have a form that someone fills out, submits it to the database. I have tried using GROUP BY, but need some assistance on this.

My WordPress SQL statement: This will be used in the WordPress function file.

global $wpdb;
$rows = $wpdb->get_results( "SELECT * FROM entries group by originator");

I need to display the data in a table format below. Basically, I need to group each entry based on Originator that are the same.

<table class="table"> 
	<thead> 
	  <tr> 
	    <th>Originator</th> 
	    <th>Collaborator</th> 
	    <th>engagement_partner</th> 
	    <th>Niche</th> 
	    <th>Fee Potential</th> 
	</tr> 
	</thead> 
 
	<tbody> 
	    <tr> 
	        <td><b>First Originator</b></td> 
	        <td>Company Name</td> 
	        <td>Engagement Partner</td> 
	        <td>Web</td> 
	        <td>$40000</td> 
	    </tr> 
	    <tr> 
	        <td><b>First Originator</b></td> 
	        <td>Company Name</td> 
	        <td>Engagement Partner</td> 
	        <td>Web</td> 
	        <td>$40000</td> 
	    </tr> 
	    <tr> 
	        <td><b>First Originator</b></td> 
	        <td>Company Name</td> 
	        <td>Engagement Partner</td> 
	        <td>Web</td> 
	        <td>$40000</td> 
	    </tr> 
	</tbody> 
</table> 
 
<br> 
<br> 
 
<table class="table"> 
	<thead> 
	  <tr> 
	    <th>Originator</th> 
	    <th>Collaborator</th> 
	    <th>engagement_partner</th> 
	    <th>Niche</th> 
	    <th>Fee Potential</th> 
	</tr> 
	</thead> 
 
	<tbody> 
	    <tr> 
	        <td><b>Second Originator</b></td> 
	        <td>Company Name</td> 
	        <td>Engagement Partner</td> 
	        <td>Web</td> 
	        <td>$40000</td> 
	    </tr> 
	    <tr> 
	        <td><b>Second Originator</b></td> 
	        <td>Company Name</td> 
	        <td>Engagement Partner</td> 
	        <td>Web</td> 
	        <td>$40000</td> 
	    </tr> 
	</tbody> 
</table>

Answer 1

I suggest you fetch all the entries and group them with php like so;

global $wpdb;
$rows = $wpdb->get_results( "SELECT * FROM entries");
$originators = [];
foreach($rows as $row)
{ 
    $originators[$row->originator][] = $row;
}

then loop through the originators to fill the table, something like this:

<?php
foreach($originators as $originator => $entries){
?>
   <table class="table">
    <thead>
      <tr>
        <th>Originator</th>
        <th>Collaborator</th>
        <th>engagement_partner</th>
        <th>Niche</th>
        <th>Fee Potential</th>
    </tr>
    </thead>
    <tbody>
    <?php foreach($entries as $entry){ ?>
        <tr>
            <td><b> <?php echo $entry->originator; ?></b></td>
            <td><?php echo $entry->collaborator; ?></td>
            <td><?php echo $entry->engagement_partner; ?></td>
            <td><?php echo $entry->niche; ?></td>
            <td><?php echo $entry->fee_potential;?></td>
        </tr>
    <?php } ?>
    </tbody>
</table>
<?php}?>
Answer 2

It doesn't looks like a GROUP BY statement is what you are looking for.

Seeing your example HTML structure, the thing you are looking for is an ordered list of rows. You can then use php to sort the results and then use them

global $wpdb;
$rows = $wpdb->get_results( "SELECT * FROM `entries`");
$sortedRows = [];
foreach($rows as $row) {
    if (!isset($sortedRows[$row->originator])) {
        $sortedRows[$row->originator] = [];
    }
    $sortedRows[$row->originator][] = $row;
}

a different approach is using ORDER BY in the query, which i won't fully work out since i dont know what your approach is on creating the HTML structure:

global $wpdb;
$rows = $wpdb->get_results( "SELECT * FROM `entries` ORDER BY `originator` ASC");
$currentOriginator = NULL;
foreach ($rows as $row) {
   if ($row->originator !== $currentOriginator) {
       //create table
       $currentOriginator = $row->originator;
   }
   //add the row to the table
}
Rent Charter Buses Company
READ ALSO
Multiple photos and videos in same post on facebook

Multiple photos and videos in same post on facebook

I try to create post with multiple photos and videos on facebookI do it in this way:

229
Ajax rejecting JSON created by PHP with &ldquo;SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data&rdquo;

Ajax rejecting JSON created by PHP with “SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data”

I have a page that makes an Ajax call, which retrieves, JSON encodes and returns data from a databaseThe page was working, but in the midst of making some changes, it's now failing

118
Updating same id with different quantity (only updating the id that comes first)

Updating same id with different quantity (only updating the id that comes first)

I am new into a database, I am developing an inventory management, I have four tables, two for purchase and purchase_detailAnd two other for sale and sale_detail

126
What is Difference Between Policy Authorize vs Can Function In Laravel?

What is Difference Between Policy Authorize vs Can Function In Laravel?

I'm using laravel basic policy system to protect unauthorized user from update postFor Example User have Id 1 and In posts Table User_id is also 1

153