Trying to get a several rows and colums with data.

rvdveen27 · May 10, 2015

Hello all,

I'm trying to get some information out of the database and want to put them together. Basically an user submits a delivery and the correct information gets stored into the database. Now I want to know per user (driver), what their total amount of deliveries are, what their total profit is (price) and what their total distance traveled is(price).

Now I've tried the query's below, but these only give me several columns with the same numbers everywhere, the ID of the driver. Anyone knows how I could show above requested information properly?

SELECT id, driver, distance, price 
FROM `drive_routes`
GROUP BY driver


SELECT count(id), count(driver), count(distance), count(price)
FROM `drive_routes`

Barand · May 10, 2015

try

SELECT 
      driver
    , COUNT(id) as deliveries
    , SUM(distance) as totaldistance
    , SUM(price) as totalprice
FROM `drive_routes`
GROUP BY driver

rvdveen27 · May 11, 2015

try

SELECT 
      driver
    , COUNT(id) as deliveries
    , SUM(distance) as totaldistance
    , SUM(price) as totalprice
FROM `drive_routes`
GROUP BY driver

Yup, that worked like a charm.

Although I'm running into a few more problems. I currently have this code:

	$query = " 
        SELECT DISTINCT 
			driver, 
			COUNT(driver) as 'deliveries',
            SUM(distance) as 'distance'
			FROM drive_routes
			WHERE status = '2'
			GROUP BY driver
			ORDER BY distance 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();

		// Row9 profit
		$query = " 
        SELECT
			id,
            SUM(price) as 'price',
			SUM(costs) as 'costs',
			SUM(cargodamage) as 'cargodamage',
			price - costs - cargodamage as profit
			FROM drive_routes
				";
			$query_params = array( 
            ':id' => $row['id'] 
        ); 
        $query .= " 
            WHERE 
                id = :id 
		";
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
        $row9 = $stmt->fetch();  
		
		?>
	  <tbody>
		<tr>
		  <td><?php echo $row['id']; ?></td>
		  <td><?php echo $driverid['username']; ?></td>
		  <td>€<?php echo htmlentities($row9['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>

Which results in the following:

Now I need to calculate the correct profit, which it seems not to do for some reason. Important part here is that the profit calculation should limit to only jobs of the same driver and do this for all entries in the deliveries table(drive_routes).

Next to that, I would like the "#" on the current rankings page to be auto increment from 1, 2, 3, 4, etc.. But I have no idea how to do this.

Very much appreciated if you can help me with this.

Edited May 11, 2015 by rvdveen27

Barand · May 11, 2015

Where is $row['id'] supposed to come from?

There are two separate aggregation queries on same table - what does the "id" relate to (identify) in the second?

What is your table structure?

Edited May 11, 2015 by Barand

rvdveen27 · May 11, 2015

This is that my table structure looks like:

Where is $row['id'] supposed to come from?

There are two separate aggregation queries on same table - what does the "id" relate to (identify) in the second?

I'm not even sure if the id should be in the second query, I tried it to mess arround with it a bit to see what it would do, but it didn't manage to solve my problem, so I might need to take that out again.

Barand · May 11, 2015

Your output sample shows a driver name but in your table the driver column is INT. Where is the name coming from?

Also, why are the value columns (price, cargodamage,costs) that you are adding defined as VARCHAR ?

rvdveen27 · May 11, 2015

The name is coming from the other table with the users in it, see below.

The value columns (price, cargodamage, costs) are limited to only digits in the code, see below.

				Cargo damage (€):<br />
				<input type="number" name="cargodamage" class="form-control" placeholder="Cargo damage (€)" required><br />
				Delivery rating:<br />
				<select name="rating" class="form-control">
					<?php foreach($results5 as $row): ?> 
						<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>
					<?php endforeach; ?>
				</select><br />
				Distance (Kilometers):<br />
				<input type="number" name="distance" class="form-control" placeholder="Distance (Kilometers)" required><br />
				Price (€):<br />
				<input type="number" name="price" class="form-control" placeholder="Price (€)" required><br />
				Expenses (€):<br />
				<input type="number" name="costs" class="form-control" placeholder="Gas, Repairs, Ferries/Trains, etc." required><br />

Edited May 11, 2015 by rvdveen27

fastsol · May 11, 2015

Relying on the html to only allow numbers is a very bad thing to do. The html side can certainly be used to help the user experience but it is NO substitute for real server side validation. You MUST validate EVERY input on the server side, even if it's coming from a secured area. The reason he asked about the column types is cause when you have things like numbers or dates in a varchar type, it's impossible to accurately compare and gather correct data based on the value in the column. Anything that is a number format must be stored in it's according type of column.

So prices, if they can have decimal places should be stored as Float, ID's would be INT, dates or time can have a few different types depending on the end usage needed, but I find it easiest to just use DATETIME so that you have an entire date time to compare to no matter what the circumstance. The main point is that you need to store the info in the proper way to be able to retrieve it in the proper way.

Barand · May 11, 2015

So do I take it that your second query (profits) was being run within a loop of query for each user, hence the "WHERE id = :id" ?

If so , that is a really inefficient way to do it. You should use JOINS rather than running queries inside loops.

I am assuming you want to rank by profit. To get the rank, just maintain a counter variable as you process the results

SELECT
    u.username
    ,SUM(price) as price
    ,SUM(costs) as costs
    ,SUM(cargodamage) as cargodamage
    ,SUM(price - costs - cargodamage) as profit
FROM drive_routes dr
    INNER JOIN
    users u ON u.id = dr.driver
GROUP BY driver
ORDER BY profit DESC;

rvdveen27 · May 17, 2015

So do I take it that your second query (profits) was being run within a loop of query for each user, hence the "WHERE id = :id" ?

If so , that is a really inefficient way to do it. You should use JOINS rather than running queries inside loops.

I am assuming you want to rank by profit. To get the rank, just maintain a counter variable as you process the results
SELECT
    u.username
    ,SUM(price) as price
    ,SUM(costs) as costs
    ,SUM(cargodamage) as cargodamage
    ,SUM(price - costs - cargodamage) as profit
FROM drive_routes dr
    INNER JOIN
    users u ON u.id = dr.driver
GROUP BY driver
ORDER BY profit DESC;

Could you explain this to me? I'm not entirely sure how "u.username" and "users u ON u.id = dr.driver" fits in my code.

rvdveen27 · May 17, 2015

Wanted to edit but couldn't, so sorry for double post.

This is my current code of the whole rankings page, I assume this will make things clearer a bit. The "//row9 profit" is still empty below it since I can't figure out the right code to get that profit shown correctly. I tried your query but that gives a same profit result for everyone on the rankings page.

<?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 DISTINCT 
			driver, 
			COUNT(driver) as 'deliveries',
            SUM(distance) as 'distance'
			FROM drive_routes
			WHERE status = '2'
			GROUP BY driver
			ORDER BY distance 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();
?>


<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 if($count == 0)
	{?>
		<tr>
			<td></td>
			<td></td>
			<td></td>
			<td>None</td> 
			<td></td>
		</tr> 
	<?php }	else foreach($rows as $row):
		
		// get driver id.
		$query = " 
        SELECT 
            *
			FROM users
			WHERE id = ". $row['driver'] ."
		"; 
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute(); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
        $driverid = $stmt->fetch(); 
		
		
		// Row9 profit
		
		?>
	  <tbody>
		<tr>
		  <td><?php echo $row['id']; ?></td>
		  <td><?php echo $driverid['username']; ?></td>
		  <td>€<?php echo htmlentities($row9['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'); ?>

Barand · May 17, 2015

As I stated, you are looping through your first query results, and, for each result, you are querying the users table. Running queries inside a loop is not the way to go.

My query JOINs matching user records with the driver_route records so the whole thing is done with a single query.

I set up a couple of test files with the relevant columns

mysql> SELECT * FROM drive_routes;
+----+--------+--------+-------+-------------+
| id | driver | price  | costs | cargodamage |
+----+--------+--------+-------+-------------+
|  1 |      1 |  25.00 |  5.00 |        2.00 |
|  2 |      2 | 125.00 |  5.00 |       20.00 |
|  3 |      1 |  75.00 |  5.00 |        2.00 |
|  4 |      2 |  25.00 |  5.00 |        2.00 |
|  5 |      1 | 250.00 | 35.00 |       20.00 |
|  6 |      1 |  25.00 |  5.00 |        2.00 |
|  7 |      2 | 125.00 | 15.00 |       12.00 |
|  8 |      3 |  25.00 |  5.00 |        2.00 |
|  9 |      1 | 225.00 | 50.00 |        2.00 |
| 10 |      3 | 225.00 |  5.00 |        2.00 |
+----+--------+--------+-------+-------------+

mysql> SELECT * FROM users;
+-------+----------+
| id    | username |
+-------+----------+
|     1 | User 111 |
|     2 | User 222 |
|     3 | User 333 |
+-------+----------+

Then ran my query

SELECT
    u.username
    ,SUM(price) as price
    ,SUM(costs) as costs
    ,SUM(cargodamage) as cargodamage
    ,SUM(price - costs - cargodamage) as profit
FROM drive_routes dr
    INNER JOIN
    users u ON u.id = dr.driver
GROUP BY driver
ORDER BY profit DESC;

giving

+----------+--------+--------+-------------+--------+
| username | price  | costs  | cargodamage | profit |
+----------+--------+--------+-------------+--------+
| User 111 | 600.00 | 100.00 |       28.00 | 472.00 |
| User 333 | 250.00 |  10.00 |        4.00 | 236.00 |
| User 222 | 275.00 |  25.00 |       34.00 | 216.00 |
+----------+--------+--------+-------------+--------+

Edited May 17, 2015 by Barand

rvdveen27 · May 18, 2015

So in theory I should replace the query at line 52 with your query and remove the other queries and that should make it work?

However your query is then missing "distance" and the amount of deliveries made, but I assume I can just add that to your query by adding:

COUNT(driver) as 'deliveries',

and:

SUM(distance) as 'distance'

Correct?

Barand · May 18, 2015

Yes

rvdveen27 · May 18, 2015

Okay the query seems to be working fine, except I can't seem to get the results posted.

This is the current code:

<?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 
			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();
?>


<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>
	  <tbody>
		<tr>
		  <td><?php echo $rows['id']; ?></td>
		  <td><?php echo $rows['username']; ?></td>
		  <td>€<?php echo htmlentities($rows['profit'], ENT_QUOTES, 'UTF-8'); ?></td>
		  <td><?php echo $rows['deliveries']; ?></td>
		  <td><?php echo htmlentities($rows['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td>
		</tr>
	</tbody>
	</table>
  </div>
</div>

This gives me messages of unidentified indexes.

However, as soon as I add:

	  <?php foreach($rows)
	  ?>

after line 96

and

	  <?php endforeach; ?>

after line 105, the page breaks completely and all I get is a blank page.

Edited May 18, 2015 by rvdveen27

rvdveen27 · May 22, 2015

Please disregard the above, I managed to get it to work.

There's only one more problem. I'd like the query to auto number the results it's getting. Starting from 1.

Currently I'm getting the following:

Barand · May 22, 2015

As I said in reply #9 ^

To get the rank, just maintain a counter variable as you process the results

$rank = 1;

while (....) {
    // output row with $rank
   
    ++$rank;  // increment it
}

Simples!

rvdveen27 · May 22, 2015

I added that like you said as following:

	$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()); 
        }
        
		$rank = 1;

		while ($rows) {
		// output row with $rank
   
		++$rank;  // increment it
}
    $rows = $stmt->fetchAll(); 
	$count = $stmt->rowcount();

?>

Which results in:

I just have no idea what to fill in for the while?

CroNiX · May 22, 2015

Because your

while($rows)

is ABOVE where you define

$rows = $stmt->fetchAll();

, so $rows doesn't exist at the point where you're trying to use it in the while() loop.

rvdveen27 · May 22, 2015

True, but if I put it as done below, the page breaks and I get a white page with nothing.

	$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
		}

?>

Edited May 22, 2015 by rvdveen27

CroNiX · May 22, 2015

white page with nothing usually indicates a fatal error, but you have error reporting turned off or display_errors turned off. Check your php error log or enable those settings so you SEE the error occurring.

rvdveen27 · May 22, 2015

white page with nothing usually indicates a fatal error, but you have error reporting turned off or display_errors turned off. Check your php error log or enable those settings so you SEE the error occurring.

To be honest, I have no idea how or where to do that.

mac_gyver · May 23, 2015

to get php to show fatal parse errors in your main file, you must set the error_reporting and display_errors settings in the php.ini on your development system.

you would find the php.ini that php is using (see the Loaded Configuration File value in the output from a phpinfo() statement), then find and change the error_reporting and display_errors lines in it, to be error_reporting = E_ALL and display_errors = On

stop and start your web server to get any changes made to the master php.ini to take effect. once you put these settings in the php.ini, you don't need to put them into your .php files.

rvdveen27 · May 23, 2015

Okay I managed to fix the problem, it seems that there was an "}" too many in the code that wasn't attached to anything.

Now with the following code:

	$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
		}
?>

I get the following error:

Barand · May 24, 2015

Can we see the whole page.

Sign In

Trying to get a several rows and colums with data.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information