Forum Moderators: open
i have already posted this in the php forum, but they recommended i posted it in here. im so out of my depth on this one :-(
i have no experience with arrays what-so-ever, but i think this is what i might need, but i dont know where to start.
i have a form that needs to input tax into a mysql db.
the form has company 1 to 5 and 3years ago, 2years ago, 1years ago, this year, next year.
so is it possible to just have one field for tax which will hold upto the five companies and the 5 values of tax.
this form is obviously alot bigger than just one entry of tax, so i have to create 45 fields per company(9 types of entires x 5 years x 5 companies) so i dont want to end up with 225 fields in the db just for this tax part - not inc the rest of the form.
any help would be great - remember i have never touch arrarys so any code snippets please explain.
thanks in advance
oh and as a footnote - i will need help retrieving it from the db as well
So you have a company record in a table something like this:
**************************
tblCompany
company_id, name, location. blah.. and so on,
**************************
Then want you want is to have a company_has_tax_info table
**************************
tblCompanyHasTaxInfo
company_id, txt_year, tax_rate_code
**************************
Join them on comapny_id and away you go. Not sure what got you started about an array but this is how you want to store it. Just remeber that 1 company can only have 1 company record but can have multiple tax_info records. Some simple SQL will get you all the tax info for a company as long as you have that companies id.
Good luck and if you neeed more help or this isn't what you needed post back and I will see if I can help.
let's take this one step at a time based on the information you gave and expanding on what Demaestro mentioned.
what information are you storing about the 5 companies?
do you have address and contact info (forget about the tax stuff for this question)?
you could create 1 table with your companies in it with all info you need for the company and leave the tax stuff to another table. This table could have an id that would uniquely identify each company.
we could then have a second table that stores all of the tax data. The 2 most important columns would be the unique id from your company table and tax year. Using these 2 columns we could get all the information we needed about any given company. The rest of this table would probably be your 45 columns of data.
does that make any sense?
that is all straight forward but once i started working through this form i realised this is going to be a massive db - the db doesnt need to be anything special as it only needs to pull the record out at the other end to be printed off by the guy who created the form in the first place so he can analysis the info.
tell me if you are getting lost here.
now i think i know what an array is - it is a variable that can store more than one piece of data. tell me if i am wrong. im sure you would anyway.
ok so lets start off simple. i am creating this form over serveral section (well loads really) so everythime you fill in a section it add it to the person record in the db.
ok - one page requires you to add you corporate tax for the last 5 years. - so can this be added to one field in the db
year 1 though to year 5 ammounts below (just an example)
corp_tax=50,48,51,56,47
how do i take that from 5 fields in a form into one field in the db.
thats the easy bit for you guys to help me with.
============================================
the next bit would be later on it asks for some other tax that you have paid over 5 years - same as above, but this would be with 5 companies.
company1_____years
_________1__2__3__4__5
petrol_tax 45 48 53 56 54
company2_____years
_________1__2__3__4__5
petrol_tax 49 56 50 56 48
etc...
hope you understand the table above
if you can understand that and help me with both - could you also tell me how i would then pull them from the db ready to be emailed/printed out.
thanks for the responses so far
>> 100 page pdf
blech, that just isn't right
The way I see it is you need to sit down and draw it out
figure out what elements will always exist. These can probably all go into a single nasty table, that's fine, it happens.
For all data that won't always be present you may want supplementary tables. This way those tables don't have to store huge amounts of empty cells.
You might even want to store data in sections (1 table per section), it might make it much easier to pull and write that data. Printing it out doesn't really matter, all you have to do is run around and grab all the pieces and stick them into a single page.
I think I may give you more questions than answers on this, sorry. Trouble is I understand the volume of data and it seems that there isn't too much to link it all together.
Mainly you just need to figure out how to have this whole mess make sense and then draw it out and see if you can make it work. Should you put all 5 values in the same row or should they each be individual rows? no clue, it depends what makes sense for you. Grabbing data from a form and taking 5 fields and smacking them together is really easy. The only hard part about it is to remember to intelligently and uniquely name every single field. You also want to group things like
corp_tax=50,48,51,56,47
would end up being in fields called
corp_tax1, corp_tax2, corp_tax3, corp_tax4, corp_tax5
this will make the programming much simpler as you can loop through things that are connected and stuff them together. The other bonus is you know there are 5 of most things. I could slam corp_tax together like so
for ($i=1; $i<=5; $i++) {
$corp_tax_ins .= $_POST['corp_tax' . $i];
if ($i < 5) $corp_tax_ins .= ',';
}
that would put the 5 corp_tax vars together seperated by a comma, though you might want to pick a better seperator than that, maybe a : or even a pipe
As far as pulling them from db, I wouldn't worry about that until you figure out how to store them. You've got to start at the start. ;)
who knows, maybe the best way is just to have a single file for each person who needs to fill it out. 1 value per line, you could even use section seperators in the file so you don't have to constantly loop through the whole thing and rely on line counts.
man, this is a hard question to answer because there is so much involved.
does any of that help or does it make it worse?
from that array loop you posted for me - how do i get it from this form (below) to that loop and then to update the db - really sorry bit of a noob question
<table>
<tr class="main">
<td>3 Years Ago</td>
<td>2 Years Ago</td>
<td>1 Year Ago</td>
<td>This year</td>
<td>Next Year</td>
</tr>
<tr>
<td>£ <input name="min_desired_inc_tax_3" type="text" id="min_desired_inc_tax_3" /></td>
<td>£ <input name="min_desired_inc_tax_2" type="text" id="min_desired_inc_tax_2" /></td>
<td>£ <input name="min_desired_inc_tax_1" type="text" id="min_desired_inc_tax_1" /></td>
<td>£ <input name="min_desired_inc_tax_t" type="text" id="min_desired_inc_tax_t" /></td>
<td>£ <input name="min_desired_inc_tax_n" type="text" id="min_desired_inc_tax_n" /></td>
</tr>
</table>
once i have that in place i can condense the db alot
then once that is sorted i might be able to understand this array within the array better - i normally only ever need to be shown something once then i get the grasp of it - hmmm
thanks for you help so far!
is it worth sending you the pdf to look at?
right - i have created the new user which sets up a record in the db, so when you start it will just keep updating that persons record.
i have already created some of the simple fields in the db, but i am now at a point where i am stuck with creating and adding the arrays to the db, not to mention the array within an array for some of the fields.
most of this form i could prob use this array format, with just a few questions that would require the array within an array and several questions with a basic yes/no answer (would you put that as an "int" in the db using 1 for yes 0 for no?)
if i can cut the db down using arrays that would be great.
man this db is going to be massive!
yes it is but pure accounting db's often are
at this point I would suggest looking through the PHP Library [webmasterworld.com]. There are a number of threads about inserting data into mysql from forms.
Then if have issues with the PHP portion then you can start a new thread in the PHP forum. We can deal with the programming aspects of this behemoth there.
<added>I missed a whole paragraph in your post I noticed
>> would you put that as an "int" in the db using 1 for yes 0 for no?
try this thread
[webmasterworld.com...]
i have took your advice and split the database up into different sections (57 in total).
when i first create an account i have been trying to insert the persons email and id across all the tables, but it only seems to update the frist 3.
is there a way to do this across all the tables without having to do an sql insert for each of the tables.
am i doing this right?
I can tell you for sure that you might be on the wrong track. You shouldn't need to store repetative data. You should only have to store the email in one table once.
The unique id should be the only peice of data on a person or company that you store the value of in more then one table.
I may be misunderstanding but see if the following makes sense. In your example you said that you have companies and you have tax info. So a really basic way to start this would be with 2 tables. 1 table for information about the company and 1 table to store that company's tax info.
You first want to start by creating contact info so your table would look like this:
company_id (unique id primary key, not null),
company_name,
address,
city,
state,
contact_person,
contact_email,
contact_phone,
contact_fax
Your second table for tax information would look like this:
(do not make company_id a primary key because you will want to allow for multiple entries with the same id.)
company_id,
tax_year,
total_income,
tax_paid,
credit_earned,
total_paid,
blah blah blah,
You only want to have each company in the company table once. But you may make an entry for each company in the second table for every tax year.
Once you have this you can ask the database questions like:
-What is the id of the company with the name 'Me Corp'?
-Get me all the tax records for comapnies with an id that matches my above question.
-Get me the tax record for 2005 for the company who's id matches the first question.
Or for reporting you can ask the database stuff like:
-Get me all the company ids from the tax table where total_income greater then 20 000
-Get me the email and contact person from the company table where the comapny id matches the ids from my above question.
******
I hope some of this makes sense, I don't want to get too far ahead of you and I don't want to send you down the wrong road either. I just can't think of a reason that you would need to store the email in more then one table. As long as you have a unique id you can just draw a 'relationship' between tables based on the unique id to find out contact info or what have you.
Keep asking questions and I will keep trying to help you as I am sure others will as well.
I have actually got 51 tables in the db as you will see the reason why below: - is it possible to increment all of these tables without having to write 51 sql inserts?
SECTION 1: INTRODUCTION & PERSONAL DETAILS.4
Section 1.1: Personal Details.5
Section 1.2: Introduction.6
SECTION 2: TAX PAID & PAYABLE.8
Section 2.1: Corporate Taxation Paid & Payable.9
Section 2.2: Personal Taxation Paid & Payable.12
Section 2.3: Total Taxation Paid & Payable.13
SECTION 3: CORPORATE TAXATION.16
Section 3.1: Your Ideal Corporate Taxation Position.17
Section 3.2: Corporate Ownership.19
Section 3.3: Company Valuations.24
Section 3.4: Corporation Tax.26
Section 3.5: Contingent Corporation Tax on Capital Gains.30
SECTION 4: PERSONAL TAXATION.34
Section 4.1: Your Ideal Personal Taxation Position.35
Section 4.2: Tax Free Income & Benefits.37
Section 4.3: Taxable Income.42
Section 4.4: Income Tax Planning – Non-Corporate.44
Section 4.5: Income Tax Planning – Corporate.68
Section 4.6: Capital Gains Tax Planning – Realised Gains.78
Section 4.7: Capital Gains Tax Planning – Non-Corporate.81
Section 4.8: Capital Gains Tax Planning – Corporate.90
Section 4.9: Inheritance Tax Liability – Self.95
Section 4.10: Inheritance Tax Liability – Parents.103
Section 4.11: Inheritance Tax Planning.111
so as you can see we have quite a few sections to go through with each section offering different varied types of questions, some basic ones that are yes and no answers (radio buttons) other questions require you to input you tax details over a 5 year period, pensions, benefits, etc...
once at the end the details will be collated and emailed to the the guy that assess the tax info, so they would be no need for any other queries on the db.
im slowly getting over the hurdles and keep falling over another, but i keep getting back up - lol
Good Luck and if you have any SQL syntax questions I will be all over it to help you.
not really, unless you have access to stored procedures and the like. I am not even sure if the newest mysql has those.
I have to echo what Demaestro said, ambitious undertaking and you are coming along well
>> im slowly getting over the hurdles and keep falling over another, but i keep getting back up
hehe, I'm impressed so far, I love watching people get the old trial by fire for their first project, makes them much stronger programmers in the long run.
if im posting any more questions shall i keep it in this post so people can follow the story so they will understand what is going on? though at the moment i have solved my major worries.
thanks again!
