Jump to content

PHP & MySQL - Calculation before inserting data into table


rvdveen27

Recommended Posts

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

Edited by rvdveen27
Link to comment
Share on other sites

You wouldn't need to save calculated values like that in the database. You have all of the data to calculate that value, so you can either put a calculation in the select statement to have MySQL calculate the profit when you select data, or you can have PHP calculate it after getting the results.

Link to comment
Share on other sites

Not really, you can calculate the profit any place where you need it. The problem with storing calculations in the database is a possibility for data inconsistency, you could update one of the values but forget to update the calculation and then the data in the database doesn't make sense. If you want to store a calculation in the database then you should use triggers to update that value whenever one of the other values changes.

Link to comment
Share on other sites

Look up the manual for the database you're using, there are all kinds of functions and things you can use. In this case you only need to use subtraction. e.g.:

SELECT price, costs, price - costs AS profit FROM ...
  • Like 1
Link to comment
Share on other sites

Got it working! 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.

Edited by rvdveen27
Link to comment
Share on other sites

The question is: Users need to be verified before they can submit a job and the code needs to check if they are and if not, stop them from being able to submit a job.I understand the logical way of thinking. I thougt it would be enough to just have the query check if they user is verified when they click the submit button, before the query inserts their submitted information into the database. The problem is just, with the little php/mysql knowledge I have, I have no idea how I should put that logic into code. I'm hoping you could show me and explain me.The system only needs to check on one page for now if the user is verified. My idea was that they would be able to view the submitjob page (if not verified) but that the submit button would stop them from inserting data into the database. If however, it is possible to stop them viewing the page and instead give them a page that explains why they can't view the submitjob page, that would be great too, better then my idea actually.

Edited by rvdveen27
Link to comment
Share on other sites

The question is: Users need to be verified before they can submit a job and the code needs to check if they are and if not, stop them from being able to submit a job.

That's not a question, that's a statement of what you want to happen. I don't know which part you need help with.I would suggest looking into using the session in PHP. When a user logs in then store information about their account, like whether they are verified, in the session. On any other page you can look that data up to use it however you want to. If you know they are verified at any point in the code then you can show or hide things on the page, or accept or reject a submission, or redirect, or whatever you want to happen.
Link to comment
Share on other sites

  • 2 weeks later...

Okay so it's clear that I should use this code:

<?phpheader("Location: notverified.php"); /* Redirect browser *//* Make sure that code below does not get executed when we redirect. */exit;?>

Now how do I merge this with the SQL query, where it checks if the logged in user has a verifypend = 1? I understand that I need to include the session somehow, but how?

Link to comment
Share on other sites

Select "verifypend" from the table where the user ID is the same as the user that's currently logged in.

 

If verifypend is 1, then redirect.

Link to comment
Share on other sites

Select "verifypend" from the table where the user ID is the same as the user that's currently logged in.

 

If verifypend is 1, then redirect.

 

Yep got that, the only thing I do not know is how to get the ID of the currently logged in user. How do I check for that?

Link to comment
Share on other sites

 

It looks like this is the line that sets the user ID:

$_SESSION['userid'] = $userid;

 

 

Thank you for your help! I've managed to change the login.php code to restrict and redirect non-verified users upon login, which is a bit different from what I was aiming for earlier in my question (restricting to just a single page). But I will keep these answers as I have a feeling I will still need this in the future! Thanks again! :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...