ojef1 Posted June 6, 2011 Share Posted June 6, 2011 For example, if I had 7 columns for the amount of sales made for each day of the week, could I have another column that sums the total sales of the whole week (if the figures change frequently) rather than doing the calculations manually? Is there a way to do this within the mysql database rather than through php code? thanks Quote Link to comment Share on other sites More sharing options...
ojef1 Posted June 6, 2011 Author Share Posted June 6, 2011 I don't have a problem doing it within the php code, I just want some fields within my tables to update automatically without relying on the user to click on their account for all the calculations and updates to occur (which is how I have it now). If there is a way to include a file or something that ran automatically, that would work too. I just don't know how to run any sort of commands on the databases without waiting for a page to load and process the information. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted June 6, 2011 Share Posted June 6, 2011 A relational database is not supposed to be treated as a spreadsheet. If you describe what you currently have, and what you want to end up with, maybe someone can make some suggestions. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 6, 2011 Share Posted June 6, 2011 You wouldn't have a separate column for each day of a week (that's referred to as spreadsheet design and makes using a database extremely difficult.) You would use a separate row for each date and then simply execute a query that sums the values for any range of dates that you specify. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 6, 2011 Share Posted June 6, 2011 Let me rephrase what I posted above - you would have a separate row for each piece of data (you could have more than one for any particulate date or none for any particular date) and then simply execute a query that sums the values for any range of dates that you specify Quote Link to comment Share on other sites More sharing options...
ojef1 Posted June 6, 2011 Author Share Posted June 6, 2011 I thought Mysql was meant to be treated like a database or spreadsheet. It sounds like you're saying I should have a lot more smaller tables rather than a few big tables. I'm not having any problem running the query's that update the tables, I just want those query's to be executed automatically, rather than only when a user loads the particular page that executes these query's and performs the operations to the data in order to display information. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 6, 2011 Share Posted June 6, 2011 sounds like you're saying I should have a lot more smaller tables No one here stated anything like that, either directly or implied. Quote Link to comment Share on other sites More sharing options...
ojef1 Posted June 7, 2011 Author Share Posted June 7, 2011 Maybe this will make my question more clear If i had a table of this structure... Week Monday Tuesday Wednesday Thursday Friday 1 $400 $350 $200 $500 $120 2 $350 $450 $500 $800 $1400 and wanted to add an additional column for "Total Sales for Week", is there any way I could make that column update automatically like you can in excel (SUM(...))? Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 7, 2011 Share Posted June 7, 2011 and wanted to add an additional column for "Total Sales for Week", is there any way I could make that column update automatically like you can in excel (SUM(...))? short answer for your specific question... NO Maybe this will make my question more clear If i had a table of this structure... Week Monday Tuesday Wednesday Thursday Friday 1 $400 $350 $200 $500 $120 2 $350 $450 $500 $800 $1400 your question was clear from the beginning, and you got 2 very good answers... Pikachu was very clear answering you this: I thought Mysql was meant to be treated like a database or spreadsheet and there is your big conceptual design mistake.... a relational DB is not a spreadsheet.. PFMAbisMad gave you a suggestion regarding which could be a better way to design your table. Storing calculated fields in a table (also knows as dependent fields) is not a good practice/modeling technic ... you can always use any aggregate function to produce those values at display time (assuming that your table has been designed correctly). Quote Link to comment Share on other sites More sharing options...
ojef1 Posted June 9, 2011 Author Share Posted June 9, 2011 Let me rephrase what I posted above - you would have a separate row for each piece of data (you could have more than one for any particulate date or none for any particular date) and then simply execute a query that sums the values for any range of dates that you specify So what would be the best way to structure something like this then? I understand the points you all have made but I don't exactly know how to put them into practice.... How many columns should I have and how many tables should I have??? Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 9, 2011 Share Posted June 9, 2011 in the most simplest design... 1 table 2 columns thedate DATE, amount DECIMAL week and day can be obtained from the "thedate" column Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.