Hey guys,

I have a login system and I want to add a budgeting system that allows a user to enter number (dollar) values, which will add up at the bottom. Also, it will organized on a monthly basis.

But I'm not sure how to go about doing this using MySQL tables.

My login system just has one table with usernames, passwords, emails, etc. So how would create a new table that corresponds to each user? And more importantly, will I need to create a new table for each user... or is it possible to have one table that includes all users data, hopefully?

One table for all the data would be sufficient. That's essentially the definition of a relational database. You're able to just include the userid into your financial record table, and then use joins to pull information from both tables.

I'm a bit confused as far as what you're asking. If you're just looking to continue to input your users into the single table, you don't need multiple tables. Just continue running your insert statements. Make sure you also create an int field named userid, and auto_increment it. This is what you'll use for the relationships of that user to the rest of the tables in your database.

Let us know if you need more help.

Okay, using an auto incrementing int to create a relationship between tables makes sense to me.

But how about as far as the budgeting system, itself? How would I setup a table for that?

Basically, a user would see a screen, say, June. On that screen, there would be a display table with two columns, and two inputs for each row: expensename and expense.

For example, let's say the user already added 'Food' and '500.00'.

Now, the user chooses the option 'Add New Expense'. In those two fields, they type, say, 'Car Insurance' and "249.94' and click enter.

Then those new values would be included in the display table and at the bottom, the sum would be totaled automatically: $749.94.

So I'm not sure how to setup a MySQL table that would accomplish the function of adding new expensenames and expenses. I guess I'm just not sure what kind of variable to use. I'm only familiar with variables that hold one value. So would I use an array or something like that?

Also, when it's done, past expenses will be separated and archived according to their months (and even years). Using a TIMESTAMP makes sense, but if a user wants to go back to a different month and change something, things would start getting mixed up. Any idea the best to make sure each expense shows up in the correct month?

For that type of thing you want to link the user's id to their budget and expenses so that you can query for everything that is the user currently has in their budget/expenses without having to worry about getting records swapped with other users.

IPs can change but user's account id won't (unless they make a new/different account).

Hope that helps.

How about setting up a MySQL table? What kind of variables would I have to use, so that users could continue adding new expenses? Is it possible to have one row in a table that could hold a list of information for a single user?

I can code basic stuff, but I'm still somewhat a of newbie.

Basically, in every table thatyou have add a field called something like "user_id" that just holds the id (member number) of the user who has added the expense.

So it would look something like so:

(format)
Table 1
- Column 1
- Column 2
- Etc.

Users
- Id
- Username
- password
- whatever else you want

Expenses
- user_id
- amount
- item_name
- whatever else you need

Budget
- user_id
- amount
- imcome_from
- whatever else you need

Where all the red text is the same number (depending on the user), so the user with id = 1 would have all expenses and budgets have user_id = 1 also, making it easy to say something like "SELECT * FROM Expense WHERE user_id='1'" for a query statement (giving back all expenses that the user with id = 1 has.

Okay, I understand the id issue. Thanks for all the feedback.

And how about adding multiple expenses? How would I set up a datebase table for that? Because there will have to be the ability to essentially enter an infinite number of expense values (or practically, at least 50+).

For example, let's say I have food, rent, car insurance. With a dollar value for each one, that would be a total of 6 values entered.

In the expense database table, if I have $amount (an INT) and $item_name (a VARCHAR), wouldn't that only fit one expense item for each user? For example, all that would fit is food at $500.

Hopefully I'm explaining my problem clearly. So how do I create the database table so $amount can hold as many values as the user inputs?

Would I use an enum(value1, value2, etc) or a set? I've never used these before, so I'm not sure.

This post has been edited by Inertia: 6 Jun, 2008 - 08:01 PM

you should make a second table containing the users ID numbers, then select the data from the second table where the id is equal to the one you're looking for.

It's starting to sound like you may be developing a system that's a bit more than what you might be able to at this level. You should start taking a look at some database tutorials and php tutorials. You might also want to consider picking up a good database management book. Start small and then work your way up to this bigger project. Just a friendly suggestion.

You may be right. But I do think I this is just beyond my level, so I all I need is a pointer in the right direction. I think that if I knew what kind of data type I'd need, I'd be well on my way. But, like you suggested, maybe I should see if I can find a tutorial similar to what I'm trying to code.

Hey, so i don't think you picked up what was being said before so ill have a go at explaining. What you will need for sure will be a users table and in that there would be the fields 'user_id', 'username', 'password', and then any other information you want to store about the user.

next would be the budgeting information which would be in there own tables just as BetaWar said...

Expenses
- user_id
- amount
- item_name
- whatever else you need

Budget
- user_id
- amount
- imcome_from
- whatever else you need

The key factor in all of this is the user id as this will be what ties all the imformation to a certain user. so ill do a few examples of the data in the expenses fields.

Expenses
- 1
- $250.00
- TV license

Expenses
- 1
- $624.00
- Car Insurance

Expenses
- 2
- $250.00
- TV license

So to get the right expenses for that user you would have to first find out there user_id. You may have already stored this in the session at login, or you may only have the username in the session. So you will have to do a mysql query to find out the user_id something like this..

"SELECT 'user_id' FROM users WHERE username='$_SESSION['username']'"

next you would call all the expenses that belong to the user_id you previously called from the database. You would do this in a while statement and then echo the results into your layout. something like this...

while (mysql_fetch_assoc(mysql_query("SELECT * FROM expenses WHERE user_id='$user_id'"))) {

put all your information in the right columns in the table and so on

}

so if the user_id was set to 1 it would only bring back the imformation...

Expenses
- 1
- $250.00
- TV license

Expenses
- 1
- $624.00
- Car Insurance

whereas if the id was 2 it would bring...

Expenses
- 2
- $250.00
- TV license

So you would only need one expenses table but there would be a lot of data in the table, but you would sort it all by using the user_id. hopefully this will help or i've just wasted my time as well as yours XD

~ KiNgY ~

Kingy's right on track with how to set up the database. Really look at his schema slowly and make sure you understand everything. DIC is a great resource for tutorial on programming in PHP, but if you're looking for something that's more of a straight track from start to finish, I highly recommend www.tizag.com.

I learned how to write PHP by using their tutorials, and I'm well on my way. Make sure you read up on the PHP sections and the MySQL sections. Also, type your code in. Don't just copy and paste. You won't learn how to code unless you actually try typing yourself. There are a lot of reasons why you should do this. *hey maybe that'll be my first blog post!*

For the record, I'm by no means trying to discourage you from working on this application. On the contrary, I'm encouraging you to read and learn as much as you can, so it can be the best you're able to make. You seem like you have an idea of what's going on, so definitely put a little more work in. You'll find you can do great.