Extracting MAX values over many records

[Guest]

Hi,

I am really hoping someone can help me. Am a total newbie and my application was left in a semi-finished state and not functioniong correctly so trying to finish it myself...

What I have is a reservations system where people can book rooms etc. The problem I am experiencing is this...

If a client makes a booking request for a specific range of dates, whatever they are, and the period requested straddles multiple rate values .i.e. if there are two rates applicable for the same room, one being a low season rate and the other a high season rate and the booking straddles those two rates I am having a problem with selecting the maximum rate applicable and calculating the costs for each period the client asks for...

My table looks like this (took some columns out as would not fit here)

 

Datefrom 	dateto 	      ratename 	    singlesharing 	single 	child 	double 	infant 	base 	perperson 
         2011-05-22 	2011-05-28 	Room 	          444 	        222  	333          111   	555   	 0      	0 	  	 
2011-05-23 	2011-05-27 	ROOMTEST         444 	        222  	333   	444 	        555  	 0 	        0 	  	 
2011-05-24 	2011-05-26 	NEW 	          555 	        333  	444   	222  	666   	 0 	        0 

 

So far I have code that determines if there is more than one rate period and now I ahve to do a calculation to show the client what their costs are going to be and am stumped completetly... the code I ahve o do this looks like so...

<?php 
                $ratesone = dbGetRows("rates", "venueid = '".$venueid."' AND roomtype = '".$roomtype."' AND datefrom <= '".$datefrom."' AND dateto >= '".$dateto."'"); //booking sits in the middle of the rate period
                if( mysql_num_rows($ratesone) == 1 ) { // only one rate over this period
                  $ratesinfo = mysql_fetch_array($ratesone, MYSQL_ASSOC);
                  echo "Your intended dates fall within a single rate period<br>";
                  echo $ratesinfo['ratename'].": ".date("d M Y", myDateTophp($ratesinfo['datefrom']))." - ".date("d M Y", myDateTophp($ratesinfo['dateto']))."<br><br>";
                  echo "Rates: ".($ratesinfo['single'] > 0 ? "Single R".$ratesinfo['single']." || " : "").($ratesinfo['double'] > 0 ? "Double R".$ratesinfo['double']." || " : "").($ratesinfo['child'] > 0 ? "Child R".$ratesinfo['child']." || " : "").($ratesinfo['infant'] > 0 ? "Infant R".$ratesinfo['infant']." || " : "").($ratesinfo['base'] > 0 ? "Base Rate Per Unit R".$ratesinfo['base']." || " : "")."<br>";
                }
                else { //multiple or no rates over period
                  $ratestwo = dbGetRows("rates", "venueid = '".$venueid."' AND roomtype = '".$roomtype."' AND datefrom <= '".$dateto."' AND dateto >= '".$datefrom."'"); //rate period has begun but hasn't expired
                  if( mysql_num_rows($ratestwo) > 0 ) { //multiple rates
                    echo "<b><font color=\"#AAAA00\">!</font> Your intended dates fall over multiple rate periods</b><br>";
                    while( $ratesinfo = mysql_fetch_array($ratestwo, MYSQL_ASSOC) ) {
                      echo $ratesinfo['ratename'].": ".date("d M Y", myDateTophp($ratesinfo['datefrom']))." - ".date("d M Y", myDateTophp($ratesinfo['dateto']))."<br>";
                      echo "Rates: ".($ratesinfo['single'] > 0 ? "Single R".$ratesinfo['single']." || " : "").($ratesinfo['double'] > 0 ? "Double R".$ratesinfo['double']." || " : "").($ratesinfo['child'] > 0 ? "Child R".$ratesinfo['child']." || " : "").($ratesinfo['infant'] > 0 ? "Infant R".$ratesinfo['infant']." || " : "").($ratesinfo['base'] > 0 ? "Base Rate Per Unit R".$ratesinfo['base']." || " : "")."<br><br>";
                    }
                    echo "<br>Your cost calculation will be spread over these rate periods.<br>Alternatively, you may wish to click BACK and enter new dates.";
                  }
                  else  echo "<font color=\"#AA0000\"><b>X</b></font> There is no rate information available for the period you selected.<br><br>This could indicate that either the room type you selected or the venue itself is not available over this period.";
                }  
              ?>

 

I hope I ahve supplied enough information and that someone can help here....

 

[gizmola]

while( $ratesinfo = mysql_fetch_array($ratestwo, MYSQL_ASSOC) ) {
                      echo $ratesinfo['ratename'].": ".date("d M Y", myDateTophp($ratesinfo['datefrom']))." - ".date("d M Y", myDateTophp($ratesinfo['dateto']))."
";
                      echo "Rates: ".($ratesinfo['single'] > 0 ? "Single R".$ratesinfo['single']." || " : "").($ratesinfo['double'] > 0 ? "Double R".$ratesinfo['double']." || " : "").($ratesinfo['child'] > 0 ? "Child R".$ratesinfo['child']." || " : "").($ratesinfo['infant'] > 0 ? "Infant R".$ratesinfo['infant']." || " : "").($ratesinfo['base'] > 0 ? "Base Rate Per Unit R".$ratesinfo['base']." || " : "")."

";
                    }

 

Your description is not at all clear.  Seems to me that code looks like it will display 2 rate lines. What specifically about what it does now, does not work the way you want it to?  Provide example output and perhaps a mockup of what you expect.  Here is where your code fetches each row in a loop and displays them.  We're not here to do free programming for you, we help advise and mentor people who want to learn about web development. 

 

[Guest]

Hi Gizmola,

Many thanks for taking the time to reply. I am not looking for "free programming", merely guidance on how to do a calculation, not being a programmer I am trying to do this myself and got to a certain point and got stuck is all.......

 

The code you rightfully point out is displaying two rates. I need to calculate the maximum rate applicable over those displayed rates according to the guests booking days, i.e if one of his days falls in one rate period and the other days in another rate period and then to add them in order for the client to see a quoted price for his / her period of stay... I am not sure where to start here or what the prcedures are ......

[gizmola]

I don't see how what you are asking for makes any sense.

 

If I am staying for 10 days, and the first 9 days the rates are offseason and the last day it's in season and the rates go up, what do you want to display.  I asked for specifics, I'm not a mind reader, nor is anyone else here that I know of.

[Guest]

how more explicit can I get..

 

I need to calculate the maximum rate applicable over those displayed rates according to the guests booking days, i.e if one of his days falls in one rate period and the other days in another rate period and then to add them in order for the client to see a quoted price for his / her period of stay..

 

If you can help without being sarcastic then fine.. otherwise I'll seek help elsewhere..

 

thanks

[gizmola]

There is a reason NOBODY else replied to you.  Feel free to look around and notice some of the other threads that receive multiple replies here, and ask yourself why I'm the only one who replied.  I asked you a specific question and for good reason.  Apparently you have absolutely no idea what your existing code does.  You can't read it at all or understand it can you?

 

Your code displays a list of RATES.  These rates vary.  There is no totaling being done when you have a single rate.  So why would anyone assume to understand what sort of totaling you expect when you have multiple different rates per day?  You don't figure that out when you have one day, but now you want to figure it out and display something entirely different simply because there are 2 different rates involved? 

 

But by all means, it is me who has the problem communicating even though I asked you a very specific and reasonable question which you did not answer.  Furthermore your command of written english is not very good.

 

Just to clarify, this is TRIVIAL to me.  The problem exists with you and your inability to express your question in a clear manner either because you are too befuddled or too lazy or some combination of both.  Apparently you are unaware that words have precise meanings. 

 

I need to calculate the maximum rate applicable over those displayed rates according to the guests booking days

 

MAX() as a function returns the largest. 

 

...if one of his days falls in one rate period and the other days in another rate period and then to add them in order for the client to see a quoted price for his / her period of stay

 

And yet you want a "total" ie. a SUM() which takes a range of numbers and adds them together.  Are you unaware that max and sum are two entirely different things? 

 

Here's an example of what you might have said that would actually make sense and be achievable:

 

"I want to calculate the number of days in each range, and multiply days * rate.  Then ADD the sum for each date range and display a total." 

 

The people who answer questions here do so as a public service to people, and you have the unmitigated gall to question someone with 4k answers (nevermind the 3k I answered in prior incarnations of the board), suggest that I'm in someway dense, when I'm a professional developer by trade who has coded literally hundreds of websites visited by tens of millions of people, and then chide me for being sarcastic?

 

Here's an example of sarcasm so that you actually can understand what it is.

 

I am not looking for "free programming", merely guidance on how to do a calculation, not being a programmer I am trying to do this myself and got to a certain point and got stuck is all.......

 

"Let's review--- you can't program and you want someone here to write the code that you can't write, code you already admitted in a roundabout way that someone else wrote, and that you ostensibly paid something for.  Now rather than paying someone to complete, add or fix it for you, you come here asking how to fix what you can't (or in other words, PROGRAM)  but you don't consider that looking for 'free programming.'  I guess when you go down to your mechanic you ask them to fix your car, when they give you the bill you say:  well I popped the hood open and I knew something was wrong with the engine... I mean gee I all you did was fix it."

 

^^^^^^^^^

  Sarcasm

 

If you can help without being sarcastic then fine.. otherwise I'll seek help elsewhere..

 

Trust me, your presence will not be missed.  I would ban you in advance but that would require another minute of my time, and I don't want it to waste that on someone of your ilk.

[Guest]

Yeah well that explains it... not only sarcastic but as arrogant as hell as well.. I suppose you never ever received help when you were starting out... cheers.. ban me.. freak is a good name for you....

[gizmola]

Yeah well that explains it... not only sarcastic but as arrogant as hell as well.. I suppose you never ever received help when you were starting out... cheers.. ban me.. freak is a good name for you....

 

And for the record, we both know that you are not starting out.  You're one of those people who has some business and you need this system and for whatever reason you are in over your head.  Rather than just admit that, you're playing this game where you think that we're stupid, and you can snow us with this "gee shucks I'm just a beginner" act, and we'll all jump in and code what you need, and then when you have what you are looking to get from us, we'll never see you again, nor will you actually be writing code.  I knew this all along, but I thought I'd do you a favor and if it wasn't too big of a deal I'd do your code for you, and you'd go on your merry way. 

 

And since you asked, no I didn't get any help starting out.  I've been programming for a long long time, in a lot of different computer languages for a number of different companies and industries.  The way I started out?  I sat down with a couple of books and read them cover to cover, and then I started writing programs.  And I continued to do this, and even as we speak I continue to study and review, and most of all I try and remain open to learning new things and new ways to do things which is not easy to do at a certain point in life, but it's very much a reality of this business. 

 

People like you come to this site every day.  Even though we have guidelines and a terms of service which people either don't read or just choose to ignore they come in droves looking for "free programming" and in dribs and drabs we often give that out, even though we're very clear that we're here as a community for developers.  So the reality of this situation is... even though I know you are not a programmer, and will never be a programmer, I answered you anyways, because really I'm too nice of a guy. 

 

The saddest thing is that had you just swallowed your pride and showed a small degree of courtesy I would probably have given you something very close to the code you wanted, if not the code you needed exactly.  If you'd answered the question that I asked you multiple times, I probably would have helped you out.  I know a lot about the state of forums on the internet.  Try jumping on irc into the #php channel sometime and try to get some help for what you're doing and you'll find out what php developers think of people with questions like yours.

 

This site is the most visited help site on the web for a reason.  If you can't get help here, you are truly screwed.  And of course you could just spend the $100 or whatever it might cost you to actually hire someone to spend a few hours fixing this code for you, although from the looks of it, it's a complete mess.