rvdveen27 Posted May 10, 2015 Share Posted 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` Quote Link to comment Share on other sites More sharing options...
Barand Posted May 10, 2015 Share Posted May 10, 2015 try SELECT driver , COUNT(id) as deliveries , SUM(distance) as totaldistance , SUM(price) as totalprice FROM `drive_routes` GROUP BY driver Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 11, 2015 Author Share Posted May 11, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2015 Share Posted May 11, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 11, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2015 Share Posted 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 ? Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 11, 2015 Author Share Posted May 11, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
fastsol Posted May 11, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2015 Share Posted 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; Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 17, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 17, 2015 Author Share Posted 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'); ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 17, 2015 Share Posted May 17, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 18, 2015 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 18, 2015 Share Posted May 18, 2015 Yes Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 18, 2015 Author Share Posted May 18, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 22, 2015 Author Share Posted 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: Quote Link to comment Share on other sites More sharing options...
Barand Posted May 22, 2015 Share Posted 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! Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 22, 2015 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
CroNiX Posted May 22, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 22, 2015 Author Share Posted May 22, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
CroNiX Posted May 22, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 22, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 23, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted May 23, 2015 Author Share Posted 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: Quote Link to comment Share on other sites More sharing options...
Barand Posted May 24, 2015 Share Posted May 24, 2015 Can we see the whole page. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.