Can multiple entries in a mysql table column cause server to

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Can multiple entries in a mysql table column cause server to

Post by drayarms »

Here is one of those purely conceptual questions, which involves no code. I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range. I have a column in the table where the members' information are stored which records their ages. Which brings us to the problem. On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically. I can't think of any way to automatically update this column on a daily basis. So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range. This leads to the second problem. Imagine there are thousands of users using the website. At any given instance, there could be hundreds of members, trying to search others based on age. This means hundreds of users will be updating a single column (the age column) in one table at the same time. Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can multiple entries in a mysql table column cause serve

Post by mikosiko »

I have a column in the table where the members' information are stored which records their age
That is an incorrect approach precisely for the update issues that you described . Don't store "age" in the table... store just DOB's (date of birth) in a DATE field and calculate the age when necessary using date's functions.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Can multiple entries in a mysql table column cause serve

Post by califdon »

mikosiko wrote:
I have a column in the table where the members' information are stored which records their age
That is an incorrect approach precisely for the update issues that you described . Don't store "age" in the table... store just DOB's (date of birth) in a DATE field and calculate the age when necessary using date's functions.
ABSOLUTELY! This is an example of storing dependent data in a field (dependent on what the current date is). Never store dependent data in a database. Store the raw data, in this case, the date of birth, which is not dependent on when you check its value, nor on anything else in the record, other than the primary key.
emelianenko
Forum Commoner
Posts: 35
Joined: Thu Sep 09, 2010 11:49 am

Re: Can multiple entries in a mysql table column cause serve

Post by emelianenko »

I have tested this myself and it works. I got it partly from elsewhere but modified it slightly as there was a small error in the original code. The "from_days" cares for the fact of those born on days of leap years.

SELECT extract(YEAR FROM from_days(datediff(curdate(), dob))) AS age

FROM students

GROUP BY age;
Post Reply