PHP & MySQL - Calculation before inserting data into table

[rvdveen27]

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

 

 

 

[Psycho]

Ideally, you would store those values  (price, damages & cost) in the database. Then you would dynamically calculate the profit, as needed, in your SELECT queries. Only storing the calculated value results in less fidelity. Plus, what if you're asked to provide data such as profit as a percentage of costs or average damage per trip?

[rvdveen27]

Ideally, you would store those values  (price, damages & cost) in the database. Then you would dynamically calculate the profit, as needed, in your SELECT queries. Only storing the calculated value results in less fidelity.

 

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. 

 

 

Plus, what if you're asked to provide data such as profit as a percentage of costs or average damage per trip?

 

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. 

[ginerjm]

A basic tenet of good db design is never to store a data value in a table where the value can be determined easily from other pieces of data in that same table. A simple straight-forward calc such as yours relying on fields stored side-by-side with this calculated field is wasteful, unnecessary and causes undo repeat calcs whenever one of the inputs to the calculation is modified. As pointed out already, the profit can ALWAYS be calc'ed as part of a query and this can always be counted on to be correct. What happens when a value is edited and the recently-written update job that does it fails to re-calc the profit using the new value?

[Barand]

I agree with ginerjm's basic tenet except I wouldn't restrict the advice to same table only. For example you wouldn't store a total when the total can be got by SUMming a related table. Don't store data that is derived from other data.

[rvdveen27]

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.

[rvdveen27]

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>

[Barand]

Don't put the quotes around column names - SQL will treat them as strings.

SELECT
            price,
            costs,
            cargodamage,
            price - costs - cargodamage as profit
            FROM drive_routes
        

[rvdveen27]

 

Don't put the quotes around column names - SQL will treat them as strings.

SELECT
            price,
            costs,
            cargodamage,
            price - costs - cargodamage as profit
            FROM drive_routes
        

 

 

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>";

[rvdveen27]

Sorry to bump this but anyone has any idea?