Trying to get a several rows and colums with data.

[rvdveen27]

Here you go:

<?php 
	ob_start();

	require('extra/header.php');
	
	if(empty($_SESSION['user'])) 
    { 
        header("Location: login.php"); 
         
        exit;
	} 
		if($_SESSION['verifypend'] == 1) 
    { 
        header("Location: verifypend.php"); 
         
        exit;
	}
	
	ini_set('display_errors', 1);
	error_reporting(E_ALL); 
	
	$query = " 
		SELECT 
			u.username 
				,SUM(price) as price 
				,SUM(costs) as costs 
				,SUM(cargodamage) as cargodamage 
				,SUM(price - costs - cargodamage) as profit
				,COUNT(driver) as 'deliveries'
				,SUM(distance) as 'distance'
			FROM drive_routes dr 
			INNER JOIN 
				users u ON u.id = dr.driver 
			WHERE status = 2
			GROUP BY driver 
			ORDER BY profit DESC
		"; 
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute(); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
    $rows = $stmt->fetchAll(); 
	$count = $stmt->rowcount();
	
	$rank = 1;

		while ($rows) 
		{
		// output row with $rank
   
		++$rank;  // increment it
		}
?>


<center><img src="http://pro-quest.co.uk/ITRecruitmentAgencyImages/Under_Construction-section.jpg" width="15%"></center><br>

<div class="container">
	<h1>Current rankings</h1> 
	<div class="table-responsive">
	<table class="table table-striped">
	  <thead>
		<tr>
		  <th>#</th>
		  <th>Driver</th>
		  <th>Profit</th>
		  <th>Deliveries</th>
		  <th>Distance</th>
		</tr>
	  </thead>
  <?php foreach($rows as $row): ?>
  
	  <tbody>
		<tr>
		  <td><?php echo $rank['rank']; ?></td>
		  <td><?php echo $row['username']; ?></td>
		  <td>€<?php echo htmlentities($row['profit'], ENT_QUOTES, 'UTF-8'); ?></td>
		  <td><?php echo $row['deliveries']; ?></td>
		  <td><?php echo htmlentities($row['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td>
		</tr>
	</tbody>
	<?php endforeach; ?> 
	</table>
  </div>
</div>
	  
<?php require('extra/footer.php'); ?> 

Edited May 24, 2015 by rvdveen27

[Barand]

I wondered if you were still running queries inside a loop but apparently not. However, your were looping twice through the results.

 

I have put the code I gave you for the rank in its correct place and removed the superfluous loop. (See comments)

    <?php 
        // ob_start();                                       REMOVE, not required
     
        require('extra/header.php');
        
        if(empty($_SESSION['user'])) 
        { 
            header("Location: login.php"); 
             
            exit;
        } 
            if($_SESSION['verifypend'] == 1) 
        { 
            header("Location: verifypend.php"); 
             
            exit;
        }
        
        ini_set('display_errors', 1);
        error_reporting(E_ALL); 
        
        $query = " 
            SELECT 
                u.username 
                    ,SUM(price) as price 
                    ,SUM(costs) as costs 
                    ,SUM(cargodamage) as cargodamage 
                    ,SUM(price - costs - cargodamage) as profit
                    ,COUNT(driver) as 'deliveries'
                    ,SUM(distance) as 'distance'
                FROM drive_routes dr 
                INNER JOIN 
                    users u ON u.id = dr.driver 
                WHERE status = 2
                GROUP BY driver 
                ORDER BY profit DESC
            "; 
            try 
            { 
                $stmt = $db->prepare($query); 
                $result = $stmt->execute(); 
            } 
            catch(PDOException $ex) 
            { 
                die("Failed to run query: " . $ex->getMessage()); 
            }
        $rows = $stmt->fetchAll(); 
        $count = $stmt->rowcount();
        
        //$rank = 1;                                REMOVE
     
        //    while ($rows)                         REMOVE
        //    {
            // output row with $rank                REMOVE
       
        //    ++$rank;  // increment it             REMOVE
        //    }                                     REMOVE
    ?>
     
     
    <center><img src="http://pro-quest.co.uk/ITRecruitmentAgencyImages/Under_Construction-section.jpg" width="15%"></center><br>
     
    <div class="container">
        <h1>Current rankings</h1> 
        <div class="table-responsive">
        <table class="table table-striped">
          <thead>
            <tr>
              <th>#</th>
              <th>Driver</th>
              <th>Profit</th>
              <th>Deliveries</th>
              <th>Distance</th>
            </tr>
          </thead>
          
        
      <?php 
      
      // THIS SECTION BELOW IS WHERE THE REMOVED CODE SHOULD BE
      
      foreach($rows as $row): 
      $rank = 1;
      ?>
      
          <tbody>
            <tr>
              <td><?php echo $rank; ?></td>                                          // output rank
              <td><?php echo htmlentities($row['username'], ENT_QUOTES, 'UTF-8'); ?></td>
              <td>€<?php echo htmlentities($row['profit'], ENT_QUOTES, 'UTF-8'); ?></td>
              <td><?php echo $row['deliveries']; ?></td>
              <td><?php echo htmlentities($row['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td>
            </tr>
        </tbody>
        
        <?php 
        ++$rank;                            // increment rank
        endforeach; 
        ?> 
        </table>
      </div>
    </div>
          
    <?php require('extra/footer.php'); ?>

[rvdveen27]

Taking the code exactly as you said it, results in the following:

 

It gives the rows a rank, but it's all the same rank and there's no increasing it. 

 

 

EDIT: I did a little swap arround in your code, seems if I do it the following way, it does it correctly: 

 

  <?php 
  $rank = 0;
  foreach($rows as $row): 
	++$rank;
  ?>
  
	  <tbody>
		<tr>
		  <td><?php echo $rank; ?></td>
		  <td><?php echo $row['username']; ?></td>
		  <td>€<?php echo htmlentities($row['profit'], ENT_QUOTES, 'UTF-8'); ?></td>
		  <td><?php echo $row['deliveries']; ?></td>
		  <td><?php echo htmlentities($row['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td>
		</tr>
	</tbody>
	<?php 
		
	endforeach; ?> 

Edited May 24, 2015 by rvdveen27

[Barand]

Have you included the line that increments the rank inside the foreach loop?

++$rank;                   // increment rank

[rvdveen27]

 

Have you included the line that increments the rank inside the foreach loop?

++$rank;                   // increment rank

 

See the EDIT in my post  

 

 

Now.. I'm coming to the next bit... I would like to have the results filtered by the current month. Every entry into the drive_routes database recieves CURRENT_TIMESTAMP. Now I did some looking into this and it seems it would be possible with something called "Epoch Unix Time". But I have no idea how I could do this and if this is even applicable on any database?

Edited May 24, 2015 by rvdveen27

[Barand]

Change the WHERE clause to

WHERE status = 2 AND DATE_FORMAT(timestamp, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')

[rvdveen27]

 

Change the WHERE clause to

WHERE status = 2 AND DATE_FORMAT(timestamp, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')

 

 

Can't believe it was THAT simple! That's the rankings page working completly as I wanted it to. Thanks so much for all the help!