How to store multiple data in mysql?

Posted on 2009-05-22
Medium Priority
Last Modified: 2013-12-12

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!

Question by:20one
  • 6
  • 5
  • 2
LVL 19

Expert Comment

ID: 24454024
Can you be a bit more clear.  Do you need to re-save the new amount or just overwrite the current amount?
LVL 81

Expert Comment

ID: 24454098
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.

Author Comment

ID: 24454215
Thank´s for answering!

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.

OK, i can follow a liitle bit your idea. But i´m not a professionel and don´t know how realize this.
define( "DATABASE_SERVER", "xxx" );
define( "DATABASE_USERNAME", "xxx" );
define( "DATABASE_PASSWORD", "xxx" );
define( "DATABASE_NAME", "xxx" );
mysql_select_db( DATABASE_NAME );
if($_POST["del"]) {
     // delete
      $Query = "delete from muster where userid = '".$_POST[userid]."';";
      mysql_query( $Query );
if($_POST["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"]) {
	$Query = "INSERT INTO muster (member_id,name, date, amount) VALUES ('".$_POST[member_id]."', '".$_POST[name]."', '".$_POST[date]."', '".$_POST[amount]."')";
	$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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 81

Expert Comment

ID: 24454297
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.

You can arrange the data as you see fit.


Author Comment

ID: 24454680
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.
LVL 19

Accepted Solution

Jones911 earned 750 total points
ID: 24455204
update tableName
set amount = amount - $_PurchaseAmount
and date = $_someDate
where memberId = $_memberId

Unless you need to track each transation?

Author Comment

ID: 24455268

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?
LVL 81

Expert Comment

ID: 24455629
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


Author Comment

ID: 24456883
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.
LVL 81

Expert Comment

ID: 24457693

As Jones911 pointed out, if you want to keep track of the transactions, you need a separate table transaction table,
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.


Author Comment

ID: 24467044
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!
LVL 81

Assisted Solution

arnold earned 750 total points
ID: 24467158
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.

Author Comment

ID: 24467171
Thank´s arnold,
i will try it again later, when i come home. I´m now at work.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question