How to store multiple data in mysql?

Hello,

please can somebody help me?
I save data from Flex over PHP into a MYSQL database. (id, member_id, name, amount, date)
Save and read the data works great. Now i have to modify the application with a new function where i can subtract any amounts from the global amount. And the new amount (old amount - amounts = new amount) has to be show in a Textfield.

I have 2 new textfields (date, minus_amount) where i can write and save a new minus from the global amount.

And here i reach the end of knowing ... How can i realise this? Do i need a second table in the MYSQL db?

Thank´s in advance!

Regards,
Mario
20oneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jones911Commented:
Can you be a bit more clear.  Do you need to re-save the new amount or just overwrite the current amount?
0
arnoldCommented:
One option is to add the additional columns to your database.
Second add a table that will have these columns:
I.e. discount table which might include the item to which the discount applies.
Al your existing function have to do is check whether the discount table as a row that applies to the item and the date and then alter the displayed value in the php page. i.e. value_from_table - minus_amount.
This way you have a record of the original amount.
0
20oneAuthor Commented:
Thank´s for answering!

@Jones911
I will try to explain it liitle bit more ...
I have a database called myDatabase, in this i have 1 table called muster. muster includes (id, member_id, name, amount, date).

Now i will save some amounts to database, which have to be subtracted from the amount (table muster).
But it´s not only a minus amount, it´s (amount, date).
The new amount has than to be displayed in my Flex application in a textfield.

@arnold
OK, i can follow a liitle bit your idea. But i´m not a professionel and don´t know how realize this.
<?php
 
define( "DATABASE_SERVER", "xxx" );
define( "DATABASE_USERNAME", "xxx" );
define( "DATABASE_PASSWORD", "xxx" );
define( "DATABASE_NAME", "xxx" );
$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);
mysql_select_db( DATABASE_NAME );
 
if($_POST["del"]) {
     // delete
      $Query = "delete from muster where userid = '".$_POST[userid]."';";
      mysql_query( $Query );
}
 
if($_POST["update"]) {
	// UPDATE
	mysql_query("UPDATE muster SET member_id='".$_POST["member_id"]."',name='".$_POST["name"]."', date='".$_POST["date"]."', amount='".$_POST["amount"]."' WHERE userid=".$_POST["userid"]);
}
 
if($_POST["insert"]) {
	// INSERT
	$Query = "INSERT INTO muster (member_id,name, date, amount) VALUES ('".$_POST[member_id]."', '".$_POST[name]."', '".$_POST[date]."', '".$_POST[amount]."')";
 	mysql_query($Query);
	$result = mysql_query("SELECT * FROM muster WHERE userid=(SELECT MAX(userid) FROM muster)");
	$User = mysql_fetch_object( $result );
	
	$Return = "<users>";
	$Return .= "<user><userid>".$User->userid."</userid><member_id>".$User->member_id."</member_id><name>".$User->name."</name><date>".$User->date."</date><amount>".$User->amount."</amount></user>";
	$Return .= "</users>";
	print ($Return);
}
 
if($_POST["getUsers"]) {
	$Query = "SELECT * from muster";
	$Result = mysql_query( $Query );
 
	$Return = "<users>";
 
	while ( $User = mysql_fetch_object( $Result ) )
	{
	$Return .= "<user><userid>".$User->userid."</userid><member_id>".$User->member_id."</member_id><name>".$User->name."</name><date>".$User->date."</date><amount>".$User->amount."</amount></user>";
	}
	$Return .= "</users>";
	mysql_free_result( $Result );
	print ($Return);
}
?>

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

arnoldCommented:
Before you get to implementing the code, you have to work out the logic.
I.e. is the date/minus_amount mean that after date the total amount has to be adjusted by the minus_amount.

What is it that you are trying to workout?
issuing an : update table set globalamount=globalamount-$variable_that_has_minus_amount where id=$id_where_change_needs_to_occur.

The test for date has to be within the PHP processing and not within mysql.
There are many things you can do within the php code prior to issuing commands to the mysql server.
$x=$x+somedata;
$row_id=$USER->id;

You can arrange the data as you see fit.

0
20oneAuthor Commented:
Thank´s arnold!

The logic behind this is the following ...
My client gives out vouchers (cards in cc format). Every Voucher haves a number on it. When he gives out a voucher, he will write some data into flex (member_id, name, amount, ...) Now the one who has get the voucher will come to my client, to pay with this voucher. And every time he pays with it, my client will write/save this data to the Flex application.

An example:
You get a voucher from me, with an start amount of ¬ 100,-
I will write the needed data into Flex (member_id, name, amount, date of buy)

1 month later:
You come into my shop and buy something with an amount of ¬ 30,-
I will write the date of this day and the amount of you buy into Flex (date, amount)
And now have the result in Flex to be ¬ 70,-

Again a few months later:
You come again ... ;) i hope so ...
and you buy something with an amount of ¬ 50,-
I will write again the date of buy and the amount ....
Now have the result to be ¬ 20,-

So long as the amount is reached ¬ 0,-

I don´t know how to handle that.
0
Jones911Commented:
update tableName
set amount = amount - $_PurchaseAmount
and date = $_someDate
where memberId = $_memberId

Unless you need to track each transation?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
20oneAuthor Commented:
Thank´s!

Sorry, Jones911 but how to implement this into my code ...
Do i need a second table, where the negativ records are stored with an id that belongs to the id of the username from table muster?

Please, is it posible that you show me in my code?
0
arnoldCommented:
Not sure what the update you have:
Lets call the transaction purchase:
The problem is that you have to add logic either on the front end where the client can determine how much is left.
passing data directly from forms to mysql queries is not a good Idea.
if($_POST["purchase"]) {
        // UPDATE
        mysql_query("UPDATE muster SET amount=amount - '".$_POST["amount"]."' WHERE userid=".$_POST["userid"]);
}
 

Open in new window

0
20oneAuthor Commented:
Thank´s for answering!

Do i have to save the new records into a another table? I need to display the start amount.

Example (The issue haves to look like):
Start amount: 100,-
- 30,- (22.05.2009)
- 50,- (23.05.2009)
-------------------------
New amount: 20,-

If i follow your example, the start amount will be overwritten everytime.
0
arnoldCommented:
Yes

As Jones911 pointed out, if you want to keep track of the transactions, you need a separate table transaction table,
transaction_Id,discount_id,user_id,date,amount.
You would then have to calculate every time.

Instead of altering the criteria, you need to outline what and how you want the information managed, and then implement the logic.
Another option is to use one table for the initial card issuance information.
Table two will be the transaction records.
You can create a view that will always reflect the current value.

0
20oneAuthor Commented:
OK, thank´s guys for helping. But i have still problems. PLEASE is it possible to show me a code example. I understand what you mean, but i don´t know how to code it and set up the database.

Thank´s again!
0
arnoldCommented:
You should start from setting up the database first to handle/manage the information.
What coding? Do you mean the coding to pull the current amount and then display the purchases at the conclusion of which you will display the remaining total?

You can use the same queryusers with modification to query purchases history for user from the new table you create.
1st query, get the original amount for usera. assign the initial amount to a variable.
2nd query (similar to queryusers) to get all the records from the purchases history table for usera.  While outputing the data, you should subtract the value of the purchase from the variable to which you assigned the initial value.

You have all the query examples you need.  You just need to copy some of the existing queries modify them to meet your additional needs.
0
20oneAuthor Commented:
Thank´s arnold,
i will try it again later, when i come home. I´m now at work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.