i was wondering how can i add these information in one field for each, example adding a field for prices, and a field for capacity in the database.
My first thought is to use a valid mysql date instead of just a year value. What if the company's "fiscal year" does not start and end at the same time as other companies? Or they start their first year in June, so the statistics only have 6 months of data? This would give you a much more accurate view of the company's statistics for a given year.
It's easy to select just the year out of a valid mysql date:
(valid mysql date format: 2010-06-06)
select * from table where year(date_field) = '2010';
year() [dev.mysql.com] Using a standard mysql date format will give you all sorts of other options inherent in mySQL without resorting to programming workarounds, saving you tons of time (sorting, selecting records, extracting date formats.)
Second, it's very likely you will want to store multiple records for each company so you can graph data over time. What good is it otherwise? :-) Knowing this, it's also very likely you
already have data for companies that is single-record data, that is, they only have one company name, one address, one phone (well, these may be multiple, but my point is, you probably already have a main table with row data in place.)
So your solution lies in creating a second table in which to store the many-to-one records, a relational table, and you store it so it joins on the main table's unique id. This would allow you to add these records without disturbing your current table structure as well. If you have something like the below, where "id" is an unique id for each record,
id|company_name|address|contact_fname|contact_lname|address|city|state|postcode|country
etc. you create a second table:
id|
company_id|year_start|year_end|price|capacity
etc., any other data relating to this year And the company id field joins on the id field in the original record. To get all the data for a company, sorted by year from the lowest to highest, with the mySQL date (YYYY-MM-DD) date formatted as MM/DD/YYYY,
select company.company_name,
date_format(year_data.year_start,"%c/%d/%Y"),
date_format(year_data.year_end,"%c/%d/%Y"), year_data.price,year_data.capacity from company,year_data where
company.id=year_data.company_id sort by year_data.start_year asc;
date_format() [dev.mysql.com] To get all the data for a specific year,
select company.company_name, date_format(year_data.year_start,"%c/%d/%Y"), date_format(year_data.year_end,"%c/%d/%Y"), year_data.price,year_data.capacity from company, year_data where company.id=year_data.company_id
and year(year_data.year_start)='2010';
How can i use php, to add to the existing data the prices and capacities for each year, and how can i later on edit them or even remove a part of it?
I won't expand a lot on this because "there are lots of ways." If you've been working with the basics of connecting to a database and displaying records via the select statements, adding or updating records is very similar except that you'd use update, insert, and delete statements.
You would have to create an administrative interface, accessed via a login you would write, that would incorporate the management features you need. The point of my post, really, is to let the database functions to as much work for you as you can to minimize your programming tasks.
