I need help with this PHP issues.

[Freddieyanc2022]

Hi there,

am building a project, I have this page where users can buy a token from the site. When buying the token you will have to select the token name from the drop down option that fetched from the database, after selecting a name the price of the selected option will display in the text field, user will input their qualities and the total will auto calculate and display in the input field. I have userbuy, usersell, Token, as my table names In the database, the table name 'Token' is where users can buy the tokens from and the table has, Id, NameofToken, Price. In the userbuy table I have Id, TokenName, Amount, Quantity, Totalbuy, Image, Interest. After the user clicks on buy button the system will calculate a 3 percent interest from the Totalbuy for the user and insert it into the Interest column in the table, the order will go through if user has enough fund in his account else displays error of 'not enough fund'. This is how I want the page backend to work.

 

Please if you understand my problem, I just need an example working code to follow and do mine. Please any solution?

[ginerjm]

It's not so much that we are trying to figure out your problem (you didn't tell us what it is) but more that we are trying to picture what your app is doing.  Your need for a table to represent purchases and sales confuses me.  I would simply have a user transaction table that saves a person's actions which would be tied to a users table where the user's account value may be stored.  The tokens are your inventory that people buy and sell I assume.  You do have multiple coluns named 'Id' which will be very confusing.  Better that the name was a bit more creatively assigned.

Maybe you might re-write your project's plans to make it clearer and then you could also tell us what your perceived problem.

[Freddieyanc2022]

Thank you for your response, now let's say this is my database with 3 tables.

token - id, Name, Price,. 

users - Id, AcctNo, Balance, Username, Fullname, Email, Password, Role.

usertoken - id, AcctNo, TokenName, Amount, Quantity, Totalbuy, Interest.

The table named 'token' is where products are stored and registered users can buy. On the html form I have a drop-down option, a text field labelled 'Price', 'Quantity' and 'Total' with the button 'BUY', the drop-down option is where the token's names are fetched from the database, when a user selects a particular token he wants to buy and the price will display in the Price text field then the user will input the quantity in the Quantity text field and it will auto calculate in the Total text field, now, after the user clicks on buy button the system will calculate a 3 percent interest as a discount from the Totalbuy for the user this value will be stored in the 'Interest' column on the usertoken table.

When user submits form, system will check if user has enough fund in his 'Balance' else displays error of 'not enough fund'.

 

 

 

 

 

 

[mac_gyver]

3 hours ago, Freddieyanc2022 said:

token - id, Name, Price,. 

if you were doing this for real, where the price can change over time, the pricing would be stored in a related table, associated back to the token rows through the token's id, with a token_id, price, and effective start and effective end datetime columns.

the point of the id column in this table is to assign a token id (auto-increment integer primary index), that would be stored in any related data table. this is the value that the form would submit for the selected token and would be stored in the usertoken table, not the token name.

3 hours ago, Freddieyanc2022 said:

users - Id, AcctNo, Balance, Username, Fullname, Email, Password, Role.

the balance is a derived value. you would not maintain the balance as a single value in a column, but would instead calculate it when needed. to do so, you need an account(ing) table, with a separate row inserted for each deposit/withdrawal that affects the account balance. also, the full name should be stored, using two columns, e.g. first_name, and last_name, so that you can uniquely distinguish between and search for people by name, e.g. is someone Ross Martian or Martian Ross?

the account(ing) and usertoken table would use the id value from the users table, to related any stored data back to the correct user.

3 hours ago, Freddieyanc2022 said:

usertoken - id, AcctNo, TokenName, Amount, Quantity, Totalbuy, Interest.

this table should contain all the who, what, when, where, and why information about the purchase. the who would be the user_id of the buyer. the what would be the token_id, quantity, and purchase price (if you have a separate table for the pricing, you don't need this value). the when would be the datetime of the purchase. the where would only apply if there's a location associated with the purchase. the why would be a status/type value or memo field describing the reason for the purchase.

this table doesn't need the account number, as that is defined in the users table and is known based on the user id. also, the totalbuy is a derived value that isn't stored. it is calculated when needed using the quantity and the purchase price (or the separately stored price at the datetime of the purchase.)

the discount amount should be calculated and inserted as a separate row, in a related discount/interest accounting table, related back to the usertoken through the id in the usertoken table.

3 hours ago, Freddieyanc2022 said:

When user submits form, system will check if user has enough fund in his 'Balance' else displays error of 'not enough fund'.

you must do this as a single 'atomic' operation, in one query. the way to do this is use the query technique shown in this thread -  https://forums.phpfreaks.com/topic/315532-avoid-appointment-conflict where you have an INSERT ... SELECT query that will only insert a new row in the usertoken table if the where clause calculates if the user's current balance - by summing the user's accounting rows, discount/interest rows, minus the user's existing token purchase amounts, is greater then or equal to the total amount of the submitted purchase.

and just to clarify, the only two values submitted from the form should be the selected token id and the quantity. everything else should use values that only exist on the server.

 

Edited November 29, 2022 by mac_gyver