sleepyw Posted July 10, 2009 Share Posted July 10, 2009 I'm working on something that entails searching for project milestone dates and amounts into a single search. Let's say the fields for each project include: milestone1, cost1, milestone2, cost2, and milestone3, cost3 (cost 1 relates to milestone1). What I'm trying to do is a single query to identify which milestone dates fall between, say 2009-07-01 and 2009-08-01 and list that milestone date and cost associated with that specific milestone. There may be cases where more than 1 project's milestone falls within those dates. Each milestone that falls within the date should show as a separate entry. After those are listed, I then need to total up all of the costs that fit those dates and display that total. This seems like it would be easy, but I'm drawing a blank. I can do a search by milestone1, a separate search by mielstone2, etc., but can't figure out how to have the query search for any of the 3 dates that fit and ONLY show one milestone item per line in the results. Does that make sense? Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/ Share on other sites More sharing options...
CincoPistolero Posted July 10, 2009 Share Posted July 10, 2009 is all this data stored in a database. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873173 Share on other sites More sharing options...
sleepyw Posted July 10, 2009 Author Share Posted July 10, 2009 Yes. but there are some cases where the milestone dates are empty (0000-00-00). Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873205 Share on other sites More sharing options...
CincoPistolero Posted July 10, 2009 Share Posted July 10, 2009 you'd just do a query that outputs data into an array by using a while statement, query where date >7.1.2009 AND date < 8.1.2009, then do some math to add up the cost column. You'll have to fix your date field that don't have real dates in them. Upload your current code and we can take a stab at it. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873231 Share on other sites More sharing options...
Zane Posted July 10, 2009 Share Posted July 10, 2009 Let's say the fields for each project include: milestone1, cost1, milestone2, cost2, and milestone3, cost3 (cost 1 relates to milestone1). Why would you have all those fields like that...that's extremely redundant. you could just as easily have 2 columns (m_id and cost) ------------------- m_id | cost 1 3.99 2 3.99 3 88.99 4 3.99 5 4.98 6 30.99 Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873239 Share on other sites More sharing options...
Zane Posted July 10, 2009 Share Posted July 10, 2009 actually after re-reading your question you would add more columns as well like date and order. ------------------- m_id | cost | date | order 1 3.99 m/d/y 3 2 3.99 m/d/y 2 3 88.99 m/d/y 5 4 3.99 m/d/y 1 5 4.98 m/d/y 4 6 30.99 m/d/y 6 Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873240 Share on other sites More sharing options...
sleepyw Posted July 11, 2009 Author Share Posted July 11, 2009 Let's say the fields for each project include: milestone1, cost1, milestone2, cost2, and milestone3, cost3 (cost 1 relates to milestone1). Why would you have all those fields like that...that's extremely redundant. It's not redundant because each milestone represents a different amount for a different task of a project. But they are all related to one project. For example, a project has a contract and that contract carries milestones for completing certain tasks. Each milestone has a separate date and amount, but they're all part of the same project/contract. For example, let's say I have 3 projects with data displayed below: Project # milestone 1 m1cost milestone 2 m2cost milestone 3 m3cost ------------------------------------------------------------------------------------------ 1 2009-06-15 500 2009-07-15 625 2009-08-19 550 2 2009-07-03 580 2009-07-29 400 2009-09-19 750 3 2009-05-21 100 2009-06-11 125 2009-07-19 150 So I want to be able to query this data and list the specific milestones and amounts that fit the dates between x and y (in this case, we'll say 7/1/09 and 8/1/09) and total up everything in the costs column. I want the output (HTML table) to look like this: Project # Milestone date Milestone cost ----------------------------------------------------- 1 2009-07-15 $625 2 2009-07-03 $580 2 2009-07-29 $400 3 2009-07-19 $150 ------------------------------------------------------- TOTAL $1755 I'm stuck because some rows have more than 1 possible result that fits the query. I don't know how to separate the ones that fit and display them as separate lines in the final table. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873259 Share on other sites More sharing options...
Zane Posted July 11, 2009 Share Posted July 11, 2009 you could have a projects table and a milestones table and link the milestones to a projects query. m_id project milestone mcost -------------------------------------- 1 2 2009-06-15 500 2 2 2009-07-03 580 3 1 2009-05-21 100 unless of course you are only going to have a maximum of 3 milestones per project...then it would be logical to have the schema you do. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873284 Share on other sites More sharing options...
sleepyw Posted July 11, 2009 Author Share Posted July 11, 2009 The data already exists, and there are 5 milestones/costs per "project." I'm beyond the point of creating new tables, though. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873297 Share on other sites More sharing options...
Andy-H Posted July 11, 2009 Share Posted July 11, 2009 http://www.horsforth.leeds.sch.uk/subjects/comp/alevel/module5/unit59/unit59g.asp I found this while working on a college assignment a while back, pretty good resource if you need to learn database normalisation. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873304 Share on other sites More sharing options...
sleepyw Posted July 11, 2009 Author Share Posted July 11, 2009 What I'm trying to do is something out of the scope of the original db plan, so I'm trying to make do with what is already in place. I'm starting to think it's more complicated than just a simple query will handle. Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873331 Share on other sites More sharing options...
sleepyw Posted July 11, 2009 Author Share Posted July 11, 2009 So back to my original question with how I thought I'd have to do this - what if I run separate queries - 1 to check each milestone? Something like: $query1 = "SELECT * FROM table WHERE milestone1 BETWEEN '$date1' AND '$date2'" $query2 = "SELECT * FROM table WHERE milestone2 BETWEEN '$date1' AND '$date2'" $query3 = "SELECT * FROM table WHERE milestone3 BETWEEN '$date1' AND '$date2'" Is it possible to combine the results into a single HTML table? Quote Link to comment https://forums.phpfreaks.com/topic/165534-combining-query-for-multiple-items-into-a-single-result-plus-sum-of-amounts/#findComment-873524 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.