PHP/MySQL - Yearly Versions

PHP
1

I have a pretty elaborate question here, about how to have yearly versions of data retreival from mysql, as a PHP application.

I’m going to have a year drop down on my main application. What I’m wanting to know is what the best approach to display data based on the current year, and then offer old data for previous years.

For example, let’s say, year 2009. At the end of the 2009 year, I decide to go through my customer accounts, and I know know that 10 of my customers are no longer going to be a customer, however I also know that I’ll get new business in the coming year. How would I be able to then make these changes, and then move onto the next year, for instance 2010, without those customer, but be able to go back to that year and edit information (such as if they have an outstanding balance, and I’m just updating information related to that year)?

Another example is (keeping in mind of the previous information), I add a new customer in 2010; I only want that to show for 2010 (and coming years, if that customer decides to stay as a customer). I do not want the “cancelled” customer from the previous year to be in 2010, and I do not want the new 2010 customer to be in the previous year. So, basically, using one database, how can I have multiple “versions” or “years” implemented into one database.

This is more of a technical AND dba design question. I already have the database structure and everything, but I’ll probably need to update the schema after discussing this, as I’m sure that the database structure might need a little bit of changing after this.

Please let me know :slight_smile:

2

I think you’re overcomplicating things. Store the date you created the customer, and the date of any transactions associated with the customer. If you want a report of “new customers in 2009” you select all those records with a create date in 2009. If you want a report of “active customers in 2010”, you select all customers associated with some kind of transaction in 2010, whether they were created that year or not, etc.

3

Reports aren’t really what I’m after though.

Overall, what I’m wanting to do is at the end of each year, I would like to close out the year and start a new year automatically and auto fill the currently active customers for the new year since we will “bring” the new customers over to the new year. I’m going to have a drop down menu on the main page for the user to select difference years. I’m wanting to know how to get the logic inside the mysql database and work from there. Right now, I don’t think i need too much help with the PHP, but more with the mySQL logic of this.

Any other solutions possible?

4

You can close the old year and open a new year but it has nothing to do with a database. In the database you put a column called CreatedDate so you now when the customer first signed up for your business.

5

Correct, however, that’s not my problem. The problem is the fact that I want to be able to select from a drop down menu a certain year, and see everything from that year only, including customer information. So, let’s say in 2009 I had some customers cancel, but they had an outstanding balance. They are cancelled, so when I switch back to year 2010, they shoulnd’;t be in there, however, when i switch back to 2009 they are “active”. Each “year” should basically be uniq.

6

have a cancelled date …

SELECT * FROM table WHERE something AND something AND …

You can have as many where clauses as you desire. What the posters above are telling you is correct.

7

@Dan:

Aha! I finally understand. I had to re-read your response a few times, and I changed my monitor brightness/color settings to something much dimmer than the factory settings and re-read your proposition slower this time and now I understand. Basically, you are saying to use the customer as a static/uniq variable (so to speak), and use the actual data that is created as a factor of how you control the year output. This makes total sense, and now I understand.

I have a little side questions, however, for anyone here, that should be pretty easy to answer. I have a year table, with an ID (auto_increment). I want to optimize the database usage, so I’m wondering if I’m doing this correctly or now. I have a year table, with the ID (as stated in my previous sentence), and this table also has another column called “Year”. So, each year, has an ID, so right now the year table reads as 1, 2009 (1 being the YearID, and 2009 being Year). As soon as I get this all fixed up, there will be another entry in there that basically looks like 2,2010 (YearID is 2, and 2010 is the Year). I was going to add a “YearID” column to the “data” table, and reference the YearID with the Year table. Is this the proper way to do this, or is this harder on the database? I want to make sure I do this right, rather than create a bigger issue down the road.

Also, whichever way is the best way you guys think, could you give me an example how you would select the two tables in one query so this makes more sense to me?

Thanks again for the help. This should be my last question related to this thread.

8

Why not use the year itself as the identifier. There’s no need for any kind of additional ID.

9

Also, do you really need a year table with a single column?

10

There’s also no reason to put the year into any other table as a separate column if you’re going to store dates. You can pull out the year from a date easily to find matching rows.

11

Will do guys. I’ll remove it from the schema and let you know if I have any questions. If not, I can mark this topic as resolved. Gimmie until tomorrow afternoon.

Thanks for all the help! :smiley:

12

Okay, here’s my MySQL date question in relation to this issue.

So, here’s a date that I have stored in mySQL:

2009-08-15 12:47:34

How could I run a mySQL query that would get just the year (xxxx) out of this? I can do the PHP, however, my SQL statement, how would I be able to convert on the fly?

Here’s my SQL statement right now that basically get’s me everything:


select customernotes.CustomerNotesID, customernotes.CustomerID, customernotes.NoteDate, customernotes.NoteDescription, customernotes.NoteInitials FROM customernotes INNER JOIN customers ON customernotes.CustomerID = customers.CustomerID where customers.CustomerID='22' ORDER BY customernotes.NoteDate DESC

Basically this allows me to get customer notes, and I’m wanting to get the notes only for 2009. I’m manually inputing customer ID 22, however, in real-life, this would be a PHP variable that would make it dynamic. NoteDate is the Date in mySQL. I’m wanting to be able to run the query and basically say year 2009 and have mysql be able to know that 2009-08-15 12:47:34 is a 2009 year, or 2006-08-15 12:47:34 is a 2006 year.

Let me know and thanks in advanced.

13 
SELECT
   [columns]
FROM
   customernotes
WHERE
   YEAR(NoteDate) = 2009
   AND ...

or

SELECT
   [columns]
FROM
   customernotes
WHERE
   EXTRACT(YEAR FROM NoteDate) = 2009
   AND ...

MySQL Reference Manual :: Date and Time Functions