Jump to content

Trying to get a several rows and colums with data.


rvdveen27

Recommended Posts

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`
 
Link to comment
Share on other sites

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:

 

ZHJAPXY.jpg

 

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 by rvdveen27
Link to comment
Share on other sites

This is that my table structure looks like: 
 
2zOmCML.jpg
 

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.

Link to comment
Share on other sites

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

9s53JpW.jpg


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 by rvdveen27
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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;
Link to comment
Share on other sites

 

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. 

Link to comment
Share on other sites

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'); ?>
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by rvdveen27
Link to comment
Share on other sites

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: 

tkjYKHz.jpg

 

 

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

Link to comment
Share on other sites

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 by rvdveen27
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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: 

 

9TfT8G9.jpg

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.