Barand

do it with a query mysql> SELECT * FROM test.refer; +----+------+------------+ | id | name | referer_id | +----+------+------------+ | 1 | A | NULL | | 2 | B | 1 | | 3 | C | 2 | | 4 | D | 3 | | 5 | E | 1 | | 6 | F | 2 | | 7 | G | 1 | | 8 | H | 3 | | 9 | I | 1 | | 10 | J | 3 | | 11 | K | 3 | | 12 | L | 4 | +----+------+------------+ SELECT a.id , a.name , GROUP_CONCAT(b.name ORDER BY b.name SEPARATOR ', ') as referrals , IF(COUNT(b.name) > 2, '5%', '') as commission FROM refer a INNER JOIN refer b ON b.referer_id = a.id GROUP BY a.id; +----+------+------------+------------+ | id | name | referrals | commission | +----+------+------------+------------+ | 1 | A | B, E, G, I | 5% | | 2 | B | C, F | | | 3 | C | D, H, J, K | 5% | | 4 | D | L | | +----+------+------------+------------+

As an alternative you could have +-----------------+ +---------------+ | booking | | course | +-----------------+ +---------------+ | booking_id (PK) | | course_id(PK) | | client_id | | coursename | | course_id | | price | | coursedate | +---------------+ +-----------------+ +-----------------+ | | | price_exception | | | +-----------------+ | +---------------<| prex_id(PK) |>-----------------+ | course_id | | dayofweek | | price | +-----------------+ Then if your query is like this SELECT c.coursename b.coursedate IFNULL(px.price, c.price) as price FROM booking b INNER JOIN course c USING (course_id) LEFT JOIN price_exception px ON b.course_id = px.course_id AND DAYOFWEEK(b.coursedate) = px.dayofweek it will use the exception price if there is one, otherwise it will use the standard price from the course table

You say the current data does not allow for multiple prices for courses, yet the system requires them. The database design, therefore, is flawed and not fit for purpose.

I'd have thought the best place to store the price data is in the database +----------------+ +---------------+ | booking | | course | +----------------+ +---------------+ | booking_id(PK) | | course_id(PK) | | client_id | | coursename | | course_id | +---------------+ | course_date | | +----------------+ | | | | | | +--------------+ | | | course_price | | | +--------------+ | +----------------<| cp_id(PK) |>---------------+ | course_id | | dayofweek | | price | +--------------+ Then join to the price table from booking table SELECT ... FROM booking b INNER JOIN course_price p ON b.course_id = p.course_id AND DAYOFWEEK(.b.course_date) = p.dayofweek

1 ) The "%R" in the format string is causing the + sign to be output. If you don't want it, remove it. 2 ) If you use DateTime object to add 1 month you risk missing a month eg $dt = new DateTime('2015-01-31'); $dt->add(new DateInterval('P1M')); echo $dt->format('Y-m-d'); // 2015-03-03 (jumps from Jan to Mar) If you use SQL mysql> SELECT '2015-01-31' + INTERVAL 1 MONTH as next_pay; +------------+ | next_pay | +------------+ | 2015-02-28 | +------------+

The query would be SELECT day , TIME_FORMAT(open_time, '%k:%i%p') as otime , TIME_FORMAT(close_time, '%k:%i%p') as ctime FROM business_hours WHERE restaurant_id = ? ORDER BY day, open_time

Are you using an old version of php that doesn't support empty(function())? Try changing elseif (empty(array_filter($times))) { to elseif (!array_filter($times)) {

I wouldn't bother storing "closed" days but, if you want to, you could store both times as "00:00:00" so the code would be if ($_SERVER['REQUEST_METHOD']=='POST') { $restaurant_id = $_POST['rid']; $def_times = []; // default $sql = "INSERT INTO business_hours (restaurant_id,day,open_time,close_time) VALUES (?,?,?,?)"; $stmt = $db->prepare($sql); $stmt->bind_param('iiss', $restaurant_id, $dayno, $ot, $ct); foreach ($_POST['openclose'] as $dayno => $times) { if ($times[1]==-1) { // closed $times[1] = $times[2] = '00:00:00'; } elseif (empty(array_filter($times))) { $times = $def_times; // set the times to the stored defaults } else { $def_times = $times; // save the times as the default times } $ot = $times[1]; $ct = $times[2]; $stmt->execute(); if ($times[3]!='') { $ot = $times[3]; $ct = $times[4]; $stmt->execute(); } } } I leave the validation to you.

Your table is OK. When the data is submitted the $_POST[openclose] array will look like this i.e. arrays of four times for each day of the week. Loop through the days using this logic foreach day if "closed" (-1) continue endif if array is empty set day to default values else store times as default endif insert record for times 1 and 2 if times 3 and 4 not empty insert record for times 3 and 4 endif endforeach

Sorting on that formatted value won't work, for the same reason we don't store like that in the first place. Use the formatted version for your output but you need ORDER BY dob

IMHO something like this would give you the flexibility if you interpret the blanks as defaulting to first set of times given. function daysandtimes() { $out = ''; $timeOptions = timeOptions(); $days = [1=>'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']; foreach ($days as $dno => $dname) { $out .= <<<OUT <tr> <td>$dname</td> <td><select name='openclose[$dno][1]'>$timeOptions</select></td> <td><select name='openclose[$dno][2]'>$timeOptions</select></td> <td><select name='openclose[$dno][3]'>$timeOptions</select></td> <td><select name='openclose[$dno][4]'>$timeOptions</select></td> </tr> OUT; } return $out; } function timeOptions() { $opts = "<option value=''> </option>\n <option value='-1'>Closed</option>\n"; $dt1 = new DateTime('06:00'); $di = new DateInterval('PT30M'); $dp = new DatePeriod($dt1, $di, 42); foreach ($dp as $d) { $v = $d->format('H:i'); $t = $d->format('g:i a'); $opts .= "<option value='$v'> $t</option>\n"; } return $opts; } ?> <html> <head> <title>Example</title> <style type='text/css'> body, td, th { font-family: sans-serif; font-size: 10pt; } legend { background-color: black; color: white; padding: 2px; } </style> </head> <body> <form> <fieldset> <legend>Operating Hours</legend> <table> <tr><th>Day</th><th>Open</th><th>Close</th><th>Open</th><th>Close</th></tr> <?=daysandtimes()?> </table> </fieldset> <input type='submit' name='btnSub' value='Submit'> </form> </body> </html>

Your second image above is fine for those opening on the same hours everyday. You may be lucky and get away with it, but Sod's Law dictates that someone will come along with a non-uniform pattern as in the example I gave before. When this happens you need to able to handle it.

This basically boils down to the design of your input form. There will no doubt be a mix of human interpretation of the information given by restauranteurs and coding logic. How much of each will be up to you. However you do it, you need to end up with something like +---------------+------+----------+------------+ | restaurant_id | day | open | close | +---------------+------+----------+------------+ | | | | | Mon closed all day (no record) | 1 | 2 | 11:00:00 | 21:00:00 | | 1 | 3 | 11:00:00 | 21:00:00 | | 1 | 4 | 11:00:00 | 21:00:00 | | 1 | 5 | 17:00:00 | 23:30:00 | | 1 | 6 | 11:00:00 | 14:30:00 | open midday, closed 14:30 - 19:00 | 1 | 6 | 19:00:00 | 25:30:00 | open late Note 25:00 = 1am on next day | 1 | 7 | 19:00:00 | 22:30:00 | +---------------+------+----------+------------+ Note this allows for Days when the restaurant is not open Days when it closes during part of the day then reopens Is open past midnight I used to do a lot of timetable work for the transport industry and all-night buses would begin around 11pm and finish around 7am. For data consistency of always finishing after it started, and maintaining correct arithmetic of duty lengths, we adopted a 32 hour clock, so 7am on a night service was 31:00:00. You may need a similar device for late-closing restaurants.

Using a similar function but making it recursive to search the directory tree. $dt = 0; $latestfile = ''; $latestdir = ''; $startDir = 'c:/inetpub/wwwroot'; // set start of search newestDir($startDir, $dt, $latestfile, $latestdir); // call search function echo "$latestdir/$latestfile : " . date('Y-m-d H:i:s', $dt); // output lastest dir/file function newestDir($dir, &$dt, &$latestfile, &$latestdir) { if ($p = opendir($dir) ) { while (false !== ($file = readdir($p))) { if ($file=='.' || $file=='..') continue; if (!is_dir("$dir/$file")) { if (($ft = filemtime("$dir/$file")) > $dt) { $dt = $ft; $latestdir = $dir; $latestfile = $file; } } else { newestDir("$dir/$file", $dt, $latestfile, $latestdir); } } } closedir($p); }

If you get new user just add them to the end of the original list and rerun

Don't you just love it when you search the internet for the same problem that you have only to find there is only the same problem but no solution?

You have two identical stat history tables (champhorses for this year and champhorses2014 for last year). To get the maximum stat value for each horse you need to combine this data into one table - which is what the subquery with the UNION is doing. Having got the maxima for each horse, this subquery is then JOINED to match with each horse

Or you can do it the SQL query SELECT lastupdate , timestampdiff(MINUTE, lastupdate, NOW()) as min_since FROM updates; +---------------------+-----------+ | lastupdate | min_since | +---------------------+-----------+ | 2015-07-12 13:20:10 | 14 | | 2015-03-12 13:30:10 | 175684 | | 2015-04-12 13:30:10 | 131044 | | 2015-05-12 13:30:10 | 87844 | | 2015-06-12 13:30:10 | 43204 | +---------------------+-----------+

Instead of two history data tables (champhorses and champhorses2014) which have identical structure, combine and extract this year and previous year stats based on the date. However, as you currently have it you need a union of the two table to get the max values Something like this SELECT t1.Horse , t1.Track , t1.Race , t1.Jockey , t1.Trainer , t1.Owner , t1.Date , t2.perStart , t2.rank , t2.speedFigure , t3.perStart2014 , t3.rank2014 , t3.speedFigure2014 , maxps , maxrank , maxsf FROM champsintoday t1 LEFT JOIN champshorses t2 ON t1.HorseRef = t2.referenceNumber LEFT JOIN champshorses2014 t3 ON t1.HorseRef = t3.referenceNumber2014 INNER JOIN ( SELECT referenceNumber , MAX(perStart) as maxps , MAX(rank) as maxrank , MAX(speedFigure) as maxsf FROM ( SELECT referenceNumber , perStart , rank , speedFigure FROM champshorses UNION SELECT referenceNumber2014 , perStart2014 , rank2014 , speedFigure2014 FROM champshorses2014 ) as bothtbls GROUP BY referenceNumber ) as t4 ON t1.HorseRef = t4.referenceNumber WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC

Explanation: The boolean expression "Status='expired'" evaluates to 0 or 1 SELECT variety , expiry , status , status='expired' as demo FROM vegetable ORDER BY status='expired', expiry; +----------+---------------------+---------+------+ | variety | expiry | status | demo | +----------+---------------------+---------+------+ | sprouts | 2015-07-05 21:53:57 | fresh | 0 | | cabbage | 2015-07-10 21:53:57 | rotten | 0 | | lettuce | 2015-07-11 21:53:57 | fresh | 0 | | cucumber | 2015-07-12 21:53:57 | rotten | 0 | | potato | 2015-07-08 21:53:57 | expired | 1 | | totmato | 2015-07-14 21:53:57 | expired | 1 | +----------+---------------------+---------+------+

ORDER BY status='expired' , arrival_time

Congratulations. Mine's a gin'n'tonic

Your problem seems similar to this thread http://forums.phpfreaks.com/topic/296962-looping-is-making-me-loopy/

Sorry about the form/fieldset. I spotted that after posting and moved the form tags. (It worked as it was but I noticed a warning when I viewed the html source in my IDE) <?=$example?> is shortcode for <?php echo $example;?> and is available from 5.4 list() requires a numerically indexed array, not an associative array. And yes, I omitted the error handling as my objective with the example was to show you how to handle the data.

Read this thread http://forums.phpfreaks.com/topic/297236-get-magic-quotes-gpc-function-is-deprecated-some-help-please/