Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    45
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Displaying Result from PDO - GROUP BY query

    i have a PDO query that gives me the SUM price of a prices column using the GROUP BY but when i try to display in PHP i can't get the layout to work, can anyone show me how to correct it?

    it tries to displays 2 rows - even then the 2nd row isnt within the table, but i would i like to match the Screenshot layout.

    Cash Total | Card Total | Final Total

    the query works as it is pulling the correct totals based on the GROUP BY.

    This is the PDO Function:


    PHP Code:
        function expenses(){
     
        
    $query "SELECT
                    method, SUM(price) as total_expense
                FROM
                    " 
    $this->table_name " GROUP BY method";
                     
        
    $stmt $this->conn->prepare$query );
        
    $stmt->execute();
     
        return 
    $stmt;

    This is the Results Table:

    PHP Code:
    echo "<table class='table table-hover'>";
        
            echo 
    "<thead>";
                echo 
    "<th>Cash</th>";
                echo 
    "<th>Card</th>";
                echo 
    "<th>Total</th>";
            echo 
    "</thead>";

    $total 0;

             while (
    $row $stmt->fetch(PDO::FETCH_ASSOC)){
                  
                  
    $total $row['total_expense'];
                        
                echo 
    "<tbody>"
                echo 
    "<tr>";
                    echo 
    "<td>£{$total}</td>";
                    echo 
    "<td>£{$expense_card}Card Total here</td>";
                    echo 
    "<td>Final Total Here</td>";
                echo 
    "</tr>";     
                
                echo 
    "</tbody>"


        echo 
    "</table>";   

    This is the screenshot of results layout:
    -capture-jpg
    Last edited by sjanssen; Oct 11th, 2018 at 06:49 PM.

  2. #2
    Supreme Master coder!
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    10,320
    Thanks
    10
    Thanked 1,185 Times in 1,175 Posts
    Show us more ... show us the PHP scripting you have now that doesn't display correctly.

  3. #3
    New Coder
    Join Date
    May 2012
    Posts
    45
    Thanks
    13
    Thanked 0 Times in 0 Posts
    sorry, i pasted the same code twice, i have updated the 2nd code section

  4. #4
    Regular Coder Vege's Avatar
    Join Date
    Jan 2008
    Posts
    982
    Thanks
    22
    Thanked 132 Times in 131 Posts
    When you build tabular data with php you loop <tr><td></td><td></td></tr>part
    Remove tbody and table tags from the while loop.

  5. #5
    Senior Coder deathshadow's Avatar
    Join Date
    Feb 2016
    Location
    Keene, NH
    Posts
    3,130
    Thanks
    4
    Thanked 455 Times in 444 Posts
    What's the table data? Since you're doing a SUM with method as the only other field, there would only be ONE record per method in your table, making the group-by utterly irrelevant. Meaningless.

    Some advice though, STOP using multiple echo to do one echo's job, STOP using string addition when you don't have to, STOP using 'variables in the strings' processing... and don't magically assume the browser is going to make all your TR for you.

    Also it doesn't make much if any sense to have one TBODY for each and every TR... and I'd probably skip the variables for nothing. Hence for your output code it should likely go something more like this:

    Code:
    echo '
    	<table class="table table-hover">
    		<thead>
    			<tr>
    				<th scope="col">Cash</th>
    				<th scope="col">Card</th>
    				<th scope="col">Total</th>
    			</tr>
    		</thead><tbody>';
    
    $total = 0;
    
    // I have no idea where your $expense_card variable is coming from
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) echo '
    			<tr>
    				<td>', $row['total_expense'], '</td>
    				<td>', $expense_card, '</td>
    				<td>Final Total Here</td>
    			</tr>';
    			
    echo '
    		</tbody>
    	</table>';
    Far, FAR simpler... and gives you nice easy to debug formatted output too. See just one TBODY per table, not per TR.

    Oh, and since you're not applying any variables as values in your query, there's no reason to not just ->query it. No reason to prepare/execute there (as yet)
    “There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies.” – C.A.R. Hoare, The 1980 ACM Turing Award Lecture
    http://www.cutcodedown.com

  6. #6
    Senior Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    1,127
    Thanks
    205
    Thanked 139 Times in 139 Posts
    Another possible way might be to use PHP's alternative syntax.

    If you don't want to echo out your html, you could do something like this for example


    PHP Code:
    <table class="table table-hover"> 
        <thead>
          <th>Cash</th>
          <th>Card</th>
          <th>Total</th>
        </thead>
        <tbody>
    <?php while ($row $stmt->fetch(PDO::FETCH_ASSOC)): ?>                
        <tr>
          <td>£<?= $row['total_expense']; ?></td>
          <td><?= $expense_card?></td>
          <td>Final Total</td>
        </tr>
    <?php endwhile; ?>
      </tbody>
    </table>

    It may be worth knowing about.
    0000


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •