Looking to make a PHP script

[npereira]

Hello all,

I need help on creating a budget php script.
Here is what I need the script to do.

• backended by MySQL DB
• have 2 columns (1st of the month and 15th of the month)
• multi row which I can add title and amount.
• all row's for 1st of the month (column) cumulates and put the total due at the bottom. (same thing for the second column)
• when a row is checked, it must not be counted in the total.

All info, totals, row descriptions and row $, must be logged to the DB.

I have a XLS sheet doing this now, but I would rather have a page on my site that is secured so I can access and update when ever/ where ever.

Thanks.

I'm not very good at php, but I can manage my own. I just need someone to start this for me then I can take over. I just don't know where or how to start this script.

[Stoned_Gecko]

I'd suggest starting with the PHP manual (http://www.php.net/manual/en/). It'll give you plenty of information to start with. It'll be a breeze if you have some basic programming/scripting background.

You should also look over the MySQL manual, although it can be confusing at times.

If you tell me what your script needs to do, I can try to give you a push in the right direction.

[npereira]

The script needs to do basicaly what I mentioned at the begining.

And,

each row should have a check box to indicate this dept has been payed.

Once you check that line, it should refresh the pages with the changes and the amount corrected. Checked row must not be cumulated in the total due.

Basicaly each the table (1 for the left side for 1st of the month and a table on the right side for the 15th of the month) row should have 4 colums:

Title -- Description/Note -- Date payment due -- amount

at the bottom of the table, should be a field that show the sum of all row un-checked.

There should be an empty line (last line) to update (add) more dept line and a refresh button to either update or alter the rows.

That's it!

I've been looking around on how to create my DB tables, and am getting a headack ! This is pretty hard when you no so litle.

Thanks for any help you can provide.

[Stoned_Gecko]

This is one of many headaches you will have while learning. 🙂

I was actually asking for the actual script use. I'm guessing it's something along the lines of a checkbook? Like you enter what you have to pay, and then you check it off as you pay it, and it's done on the 1st and 15th? Am I close? The more details you give me, the bigger picture I can get, the more I can help you 😉

[npereira]

That's right!

Although, when you check off that row, the row should not be deleted because the next month, the same pament will re-occur.

Basicaly it's kinda like a budget. I have an excell sheet but I alway have to update 3 of them since I work with my laptop, home pc and work pc.

By having it on-line, I can simply go to my site and update the budget as to what is paid and what's left to pay.

I'm not so bad when it comes to modify a script, but starting one from scratch, is not something I have been successful at.

[npereira]

I have created the dbconnect.php file and that's about it.

I dont know how to setup the DB table Type, Attributes, Null, Default, Extra, Action should be setup as.

I know I need 5 fields;

title
description
date
amount
paid

the first 2 are easy, basicaly VARCHAR and NOT NULL.
As for the date, amount and paid field, not sure how to set them up.

[Stoned_Gecko]

I see. So you have recurring payments, all of which occur on the 1st and 15th (payroll?), so you want to be able to pull up the sheet, see what's due, mark what you've paid, and see what's left? And you want to see what's due on the 1st if you pull it up after the 15th and what's due on the 15th if you pull it up after the 1st?

[npereira]

Yes,

But.... I dont understand you last sentence. It does not matter, I want to see both tables with the 1st and 15th payments no matter wat date I call the script. That part is irelevant.

I will enter the date the payment is due, and will check it off when it's paid.

[drawmack]

actually you'll need something like this in the database

create table if not exists bills (
  ID BIGINT NOT NULL auto_increment,
  BillName VARCHAR(100) NOT NULL,
  DueWhen ENUM('First','Fifteenth') NOT NULL DEFAULT 'First',
  Amount FLOAT(5,2) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE BillName,
  INDEX DueWhen
);

create table if not exists paid (
  ID BIGINT NOT NULL auto_increment,
  BillID BIGINT NOT NULL,
  Paid  DATE NOT NULL,
  PRIMARY KEY (ID),
  INDEX BillID (BillID),
  INDEX Paid (Paid)
);

that takes care of your bill paying requirement. Now if you want to handle other checking account transactions you'll need this table as well.

create table if not exists transactions (
  ID BIGINT NOT NULL auto_increment,
  Dscrptn VARCHAR(255) NOT NULL,
  DbtCrdt ENUM('Debit','Credit') NOT NULL DEFAULT 'Debit',
  Amount FLOAT(6,2) NOT NULL,
  CheckNo INT NOT NULL,
  TrnsDate DATE NOT NULL,
  PRIMARY KEY (ID),
  INDEX DbtCrdt (DbtCrdt),
  INDEX CheckNo (CheckNo),
  INDEX TrnsDate (TrnsDate)
);

having a look at the database do you understand why this script is not going to be something very simple and easy to write. Additionally if you're storing this kind of stuff on line it'll have to be secure, and I don't mean enter a username and password secure I mean secure enough that a bored hacker can't really screw up your record keeping. You're also going to want archival abilities and such, I mean no need to keep years and years of data in the on line database right?

[npereira]

Security is no problem.
It will secured via [url]https://[/url]

Apart from that, there will not be any banking information what so ever or any real secret information on this sheet.

Archiving would be good also.

P.S.:

Tried to load the SQL structure you gave me, an error orrcured:

Error

SQL-query :

CREATE TABLE IF NOT EXISTS bills(

ID BIGINT NOT NULL AUTO_INCREMENT ,
BillName VARCHAR( 100 ) NOT NULL ,
DueWhen ENUM( 'First', 'Fifteenth' ) NOT NULL DEFAULT 'First',
Amount FLOAT( 5, 2 ) NOT NULL ,
PRIMARY KEY ( ID ) ,
UNIQUE BillName,
INDEX DueWhen
)

MySQL said:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '
INDEX DueWhen
)' at line 7

[drawmack]

ooppss

create table if not exists bills (
  ID BIGINT NOT NULL auto_increment,
  BillName VARCHAR(100) NOT NULL,
  DueWhen ENUM('First','Fifteenth') NOT NULL DEFAULT 'First',
  Amount FLOAT(5,2) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE BillName (BillName),
  INDEX DueWhen (DueWhen)
);

create table if not exists paid (
  ID BIGINT NOT NULL auto_increment,
  BillID BIGINT NOT NULL,
  Paid  DATE NOT NULL,
  PRIMARY KEY (ID),
  INDEX BillID (BillID),
  INDEX Paid (Paid)
);

create table if not exists transactions (
  ID BIGINT NOT NULL auto_increment,
  Dscrptn VARCHAR(255) NOT NULL,
  DbtCrdt ENUM('Debit','Credit') NOT NULL DEFAULT 'Debit',
  Amount FLOAT(6,2) NOT NULL,
  CheckNo INT NOT NULL,
  TrnsDate DATE NOT NULL,
  PRIMARY KEY (ID),
  INDEX DbtCrdt (DbtCrdt),
  INDEX CheckNo (CheckNo),
  INDEX TrnsDate (TrnsDate)
);

[npereira]

OK, the DB is now done, what's next?

Also, I don't think I realy need the transaction part.

[drawmack]

Originally posted by npereira
OK, the DB is now done, what's next?

Also, I don't think I realy need the transaction part.

If you don't need the transaction part then

DROP TABLE transactions

Okay now I'm going to start speaking methodology and letting you work out the code on your own. If you attempt the code and have problems I'll help but I'm not going to write it from the ground up cause people pay me for that kind of thing.

The next step is to figure out the pages you'll need and what you'll need on each page. Below is a start for you

Summary (current outstanding bills, this month's paid bills)
Detail (Bills and status for month, can choose month - defaults to current month)

Once you have that hammered out then it is time to design your interface. Do not worry about php at this point. Just do up an HTML mock up interface with some hard coded data and buttons that just link instead of actually going to a code processor.

After you've done all that it's time to write the code to populate your html with the live data and make your buttons work. Once you get to the point of being ready to write php let me know and post your html (in a zip file attachment please) and I'll give you pointers on making it work.

[npereira]

I basicaly need only one page.

Here is the attached index.html file that contains the interface.

[drawmack]

I see three problems with your datasheet, note that I have not looked at the code yet so more problems may be hiding there. I follow the astectics for methodology so this means that we get it to look right first then we worry about getting the code right.

Problem 1) This will only display the current month, what if you want to look at other months? You should allow for that eventuallity now so you're not kicking yourself later.

Problem 2) It never tells you the month and year you're looking at. In all actuallity it should tell you the month and year you're looking at and the current date.

Problem 3) Even this should be split into three forms. You're going to want to add bills, edit records and view the summary. There are reasons for not doing this all in one form but in the interests of berevity I will say please trust my experiences. What you should really have for this, at an absolute minimum, is add/edit bill screens. View month and pay bills screens. The add and edit bill screens should look almost identical as should the view summary and pay bills screens. Then you also need some method of navigation between the forms.

If you think that I am mistaken on any of these points I'll be more then happy to discuss them with you further. If you agree with my assessment then please make the corrections and post the result when you have it.

EDIT
Come to think of it, why don't you think of the script this way. It has two areas bill management and summary. That should lead you to a better flow of forms.

[npereira]

Not to sure if this is what you mean, but here you go.

Let me know!

[npereira]

Take note,

Most if not all bills are reacurent month over month.

I think the "bill management" and "summary" is better.
What should I do, just 2 html files?

EDIT !

Come to think of it, I don't realy need to see past of future months. I dont see that in my excel sheet, so I don't see a realy reason to have this, since all bills will be the same month over month. Just sometimes, the amount to pay for the month may be less due to income.

[npereira]

Here !

I think this would be better.
Based on 2 files (index.html = summary) and (management.html = obvious)

[drawmack]

I modified your first one while you were working up and posting this second one. Take a look at this and decide how you would like the flow to work. I recommend something close to what I've come up with but you might find something else works better for you. Our next step is to get the html as clean as possible. Then we get the pages looking nice with a concrete layout and design. After that we start php. If you make sure each step is complete before going onto the next one it makes the project take much less time and you don't have nearly as many headaches of something cropping up on you.

[drawmack]

and the files are attached to this post.