rvdveen27

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.

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?

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

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.

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 />

This is that my table structure looks like: 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.

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.

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`

Sorry to bump this but anyone has any idea?

Oh woah, that simple. It worked, many thanks! One more question. Back to the submitjob.php page (& code in first post). Users can get a verified status in the database, now I would like to make it so that users that aren't verified cannot submit jobs. For this, I think it's necessary that before the query puts the data into the database, it checks if the user is verified. If not verified it should give a message that users needs to be verified before being able to submit jobs. Below is a part of the code from the profile.php page, where it checks if the user is verified, if so it puts a verified logo on the profile. $query = " SELECT username, email, verified, suspended, regdate, admin FROM users WHERE id = :id "; $query_params = array( ':id' => $userid ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $row = $stmt->fetch(); if($row['verified'] == 0 && $row['suspended'] == 0) { $verified = "<span class=\"label label-warning\">Not Verified</span>";

Okay so I'm now having this, which results in the outputted value being €0, which is incorrect but the query seems to work? // Row8 profit $query = " SELECT price, costs, cargodamage, 'price' - 'costs' - 'cargodamage' as 'profit' FROM drive_routes "; try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $row8 = $stmt->fetch(); ?> <tbody> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $driverid['username']; ?></td> <td><?php echo htmlentities($row2['name'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row3['name'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row4['name'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row5['name'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row['cargoweight'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row6['name'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row7['name'], ENT_QUOTES, 'UTF-8'); ?></td> <td>€<?php echo htmlentities($row['cargodamage'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?php echo htmlentities($row['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td> <td>€<?php echo htmlentities($row['price'], ENT_QUOTES, 'UTF-8'); ?></td> <td>€<?php echo htmlentities($row['costs'], ENT_QUOTES, 'UTF-8'); ?></td> <td>€<?php echo htmlentities($row8['profit'], ENT_QUOTES, 'UTF-8'); ?></td> <td><a href="acceptjob.php?id=<?php echo $row['id']; ?>">ACCEPT</a> - <a href="denyjob.php?id=<?php echo $row['id']; ?>">DENY</a></td> </tr> </tbody>

Okay you guys have me convinced. I assume the calculation has to be made on the page where profit would be shown then. The question is, according to my code, what would be the way to calculate this? (Like I have no idea how to create the code for that). Like I said, I did see some ways to do it by searching via google, but I'm not sure what way would apply to and go well with my code/database. Would be greatly appreciated if someone could explain how best to do it to me.

Data such as Prices, Damages & Cost are already being stored into the database. I want Profit to be a separate column in the same table (drive_routes), which gets calculated and when the other data (Price, Damages & Costs) are submitted and then all data is inserted to the database (Price, Damages, Costs & Profit). This due to the fact that the main page (index.php) shows a table with the total profit amount. For now there's no need for that. There will be a rankings page in the future with several stats being sorted, however this will be a completely separate page and won't affect the code of this page. It will use data that's already in the database.

Hello all, First of all, I'm fairly new to this. I had some (very) basic experiences with HTML/CSS/PHP/MySQL. I've recently bought a CMS package called TruckNet which was created for Euro Truck Simulator 2 Multiplayer, to simulate VTC's (Virtual Trucking Companies). The CMS package was created by StuartD and after buying it I'm free to change it to my own wishes. I saw this a nice to chance to create something for what I like to do and as well learning some new things. Secondly, I'm not sure if this is posted in the right section, if not, my apologies, I was unsure where exactly to post it. I want to insert a calculation into my code. The calculation should go as follow: price before pickup - damages - costs = profit. I assumed that the calculation should happen when the forum is being submitted but before the data is put into the table, so I assumed that it would be somewhere in between that, which would be on the submitjobs page. The thing is, I have no idea where exactly to put that in between the existing code and what exactly to put there. I did google on how to calculate things in PHP & MySQL but there seem to be quite a lot of different options to do so and I'm confused as what to use. I'm hoping someone could explain me where the best location for the calculation would be and how I could create that calculation. Below is the code where I believe the calculation should be made in between. $query = " INSERT INTO drive_routes ( driver, start, startcompany, end, endcompany, cargoweight, cargo, cargotype, cargodamage, distance, price, costs ) VALUES ( :driver, :start, :startcompany, :end, :endcompany, :cargoweight, :cargo, :cargotype, :cargodamage, :distance, :price, :costs ) "; $query_params = array( ':driver' => $_SESSION['userid'], ':start' => $_POST['start'], ':startcompany' => $_POST['startcompany'], ':end' => $_POST['end'], ':endcompany' => $_POST['endcompany'], ':cargoweight' => $_POST['cargoweight'], ':cargo' => $_POST['cargo'], ':cargotype' => $_POST['cargotype'], ':cargodamage' => $_POST['cargodamage'], ':distance' => $_POST['distance'], ':price' => $_POST['price'], ':costs' => $_POST['costs'], ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } header("Location: myjobs.php"); exit; } ?>