Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>The best compromise, IMHO, would be to select the date range in MySQL and then supply any missing info with PHP.</p> <p>Pardon me for being possibly over-fussy, but one time or another I got bitten by all the problems below, so I'll give an apparently complicated solution to an apparently simple problem :-)</p> <p>One problem may possibly arise from date selection algorithm, since "the next day" is not so clear-cut an expression as one might think - some days are 23 hours or 25 hours long, when switching daylight saving time, and some days have one extra second, so that $ts += 86400 takes you from D:M:Y 00:00:00 to D:M:Y 23:59:59 and not to D+1:M:Y 00:00:00.</p> <p>That can seem nit-picking until you lose fifteen hotel room reservations in July (or was it June?) because of a <a href="http://en.wikipedia.org/wiki/Leap_second" rel="nofollow">leap second</a>. It wasn't my code, but I was the one that had to do the mop-up.</p> <p>Another problem would be that if you have a CAR_FROM of "1st January" and a CAR_TO of "December 31st", and you wanted prices between 21 and 28 of March, then that row would be relevant, even if no row specifies dates in March. So you need to take care of overlaps.</p> <p>Finally, you might have an entry from 1-1-2013 to 31-12-2013, and later on you might have an entry for 1st May 2013 to 17th May 2013, and you would want the second one to take precedence for dates in the 1-17 May range. Actually you'd want a way of quickly selecting from one precedence system to another if the customer changes its mind.</p> <p>So you might do it like this:</p> <pre><code>&lt;?php $start = mktime(12, 0, 0, $month1, $day1, $year1); $stop = mktime(12, 0, 0, $month2, $day2, $year2); $DATE_FORMAT = 'd-m-Y'; // This might be a define, actually // Use these dates in MySQL query. This takes care // of most date problems. $SQL_From = date('Ymd000000', $start); $SQL_To = date('Ymd235959', $stop); // We create a date array initialized with N/A, and use // a date format as a key $empty = array(); for ($ts = $start; $ts &lt;= $stop; $ts += 86400) { $date = date($DATE_FORMAT, $ts); $empty[$date] = 'N/A'; } /* To select dates overlapping [FROM-TO], consider that we do NOT want a date when its TO is less than our FROM of interest: [from -- car -- to] [FROM TO] and neither when its from is more than our TO of interest: [FROM TO] [from -- car -- to] So we want all dates except those, and this means WHERE NOT ( car_to &lt; SQL_From OR car_from &gt; SQL_To) But since NOT (A OR B) is logically equal to A AND B, we get this not very intuitive, but very efficient query $query = ... SELECT price.car_number1, car_from, car_to, car_fares FROM price JOIN car ON car.car_number1 = price.car_number1 WHERE car_from &lt; $SQL_To AND car_to &gt; $SQL_From ORDER BY car_number1, car_from; The precedence is given by car_from: later dates win over earlier dates. But you might have a PRIORITY field that overrides all, so that PRIORITY=0 means normal and PRIORITY 99 means "THESE are the prices, damn your eyes!"; to achieve that, you can now simply "ORDER BY car_number1,priority,car_from." */ $cars = array(); // Now we retrieve all car data, with PDO or whatever while($tuple = SQLFetchRow(...)) { // If this is a new car, we initialize its array to all N/A if (!isset($cars[$tuple['car_number1'])) $prices = $empty; else $prices = $cars[$tuple['car_number1']; // Now we check our tuple and see what prices have changed // MySQL retrieves date in Y-M-D format list($y,$m,$d) = explode('-', $tuple['car_from']); $from = mktime(12, 0, 0, $m, $d, $y); list($y,$m,$d) = explode('-', $tuple['car_to']); $to = mktime(12, 0, 0, $m, $d, $y); // This will overwrite any existing data with fresher data for ($ts = $from; $ts &lt;= $to; $ts += 86400) { $date = date($DATE_FORMAT, $ts); $prices[$date] = $tuple['car_fare']; } // Now we save the modified array into $cars. $cars[$tuple['car_number1'] = $prices; } // And finally we print the results into a variable, // so it will be easier to wrap this into a helper function. $row = '&lt;tr&gt;'; $row .= '&lt;th&gt;-&lt;/th&gt;'; foreach($empty as $date =&gt; $dummy) $row = '&lt;th&gt;'.$date.'&lt;/th&gt;'; $row .= '&lt;/tr&gt;'; $table = '&lt;table&gt;' . $row; foreach($cars as $plate =&gt; $prices) { $row = '&lt;tr&gt;&lt;td&gt;'.$plate.'&lt;/td&gt;'; foreach($prices as $dummy_date =&gt; $price) $row .= '&lt;td&gt;'.$price.'&lt;/td&gt;'; $row .= '&lt;/tr&gt;'; $table .= $row; } $table .= '&lt;/table&gt;'; print $table; ?&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload