Barand

Try doing var_dump($housedddy); to see if its size and content are as expected

I have rewritten the two functions so that you now pass an array of opening times for the restaurant to daysandtimes() function. function daysandtimes(&$times) { $out = ''; $days = [1=>'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']; foreach ($days as $dno => $dname) { $hours = array_merge($times[$dno], array('','','','')); // ensure > 4 array elements $menu1 = timeOptions($hours[0]); $menu2 = timeOptions($hours[1]); $menu3 = timeOptions($hours[2]); $menu4 = timeOptions($hours[3]); $out .= <<<OUT <tr> <td>$dname</td> <td><select name='openclose[$dno][1]'>$menu1</select></td> <td><select name='openclose[$dno][2]'>$menu2</select></td> <td><select name='openclose[$dno][3]'>$menu3</select></td> <td><select name='openclose[$dno][4]'>$menu4</select></td> </tr> OUT; } return $out; } function timeOptions($current) { $selClosed = $current==-1 ? 'selected="selected"':''; $opts = "<option value=''> </option>\n <option $selClosed 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:00'); $t = $d->format('g:i a'); $sel = ($v==$current) ? 'selected="selected"':''; $opts .= "<option $sel value='$v'> $t</option>\n"; } return $opts; } To use it now, get the times from the database and store in an array, like this $rid = intval($_GET['rid']); // get the restaurant id $sql = "SELECT day , open_time , close_time FROM business_hours WHERE restaurant_id = $rid ORDER BY day, open_time"; // create array of time for each day $times = array(); $res = $db->query($sql); while (list($day, $ot, $ct) = $res->fetch_row()) { if ($ot=='00:00:00' && $ct=='00:00:00') { $times[$day][0] = -1; } else { $times[$day][] = $ot; $times[$day][] = $ct; } } Then pass the array to the function to output the menus <table> <?=daysandtimes($times)?> </table>

You need keep track of the previous score and maintain a count. EG $results = array( array ('name'=>'Peter', 'time'=>'00:20:30'), array ('name'=>'Paul', 'time'=>'00:22:12'), array ('name'=>'Ted', 'time'=>'00:21:42'), array ('name'=>'Ben', 'time'=>'00:23:04'), array ('name'=>'Tom', 'time'=>'00:21:42'), array ('name'=>'Hank', 'time'=>'00:24:36'), array ('name'=>'Fred', 'time'=>'00:21:42') ); // sort the results by time uasort($results, function($a, $b) { return strcmp($a['time'], $b['time']); }); // display results showing the ranking positions $prevtime = '00:00:00'; $count = 0; $pos = 0; foreach ($results as $rider) { ++$count; $pos = ($rider['time']==$prevtime) ? $pos : $count; $prevtime = $rider['time']; echo "$pos : {$rider['name']} ({$rider['time']})<br>"; } /* output ****************** 1 : Peter (00:20:30) 2 : Tom (00:21:42) 2 : Ted (00:21:42) 2 : Fred (00:21:42) 5 : Paul (00:22:12) 6 : Ben (00:23:04) 7 : Hank (00:24:36) */

OK no strategy then, so assuming there are always the same number of each, and given your data looks like this mysql> SELECT sid, roper FROM rrroper; +-----+--------+ | sid | roper | +-----+--------+ | 1 | Alice | | 2 | Bob | | 3 | Cindy | | 4 | Dave | | 5 | Emma | | 6 | Fred | | 7 | Gina | | 8 | Henry | | 9 | Imogen | | 10 | Jack | +-----+--------+ 10 rows in set (0.00 sec) mysql> SELECT sid, heeler FROM rrheeler; +-----+---------+ | sid | heeler | +-----+---------+ | 1 | Martin | | 2 | Nicola | | 3 | Oliver | | 4 | Paula | | 5 | Quentin | | 6 | Rosie | | 7 | Steve | | 8 | Tracey | | 9 | Uri | | 10 | Vanessa | +-----+---------+ 10 rows in set (0.00 sec) CREATE TABLE `result` ( `result_id` int(11) NOT NULL AUTO_INCREMENT, `round_no` int(11) NOT NULL, `roper_id` int(11) NOT NULL, `heeler_id` int(11) NOT NULL, `time` time DEFAULT NULL, PRIMARY KEY (`result_id`) ) this code will generate the rounds for you in a result table for you to record the times (assumes ropers and heelers both have id values 1..N) <?php include("db_inc.php"); // define HOST, USERNAME etc $db = new mysqli(HOST,USERNAME,PASSWORD,'roundrobin'); // // GENERATE THE result TABLE // PAIRING EACH ROPER WITH A // DIFFERENT HEELER FOR EACH ROUND // $res = $db->query("SELECT COUNT(*) FROM rrroper"); list($numRounds) = $res->fetch_row(); for ($r=1; $r<=$numRounds; $r++) { $sql = "INSERT INTO result (round_no, roper_id, heeler_id) SELECT $r as `round` , r.sid as roper , h.sid as heeler FROM rrroper r INNER JOIN rrheeler h ON r.sid = MOD((h.sid + $r),10) + 1"; $db->query($sql); } ?>

What is your strategy if you get entries from, say, 12 ropers and 15 heelers?

$stmt->bind_param('i', intval($_GET['id'])); You need to pass an actual variable (which has an address in memory) and not a function result $id = intval($_GET['id']); $stmt->bind_param('i',$id); Although the intval() is unnecessary with bound params $stmt->bind_param('i', $_GET['id']);

try this <?php include("db_inc.php"); // define HOST, USERNAME etc $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT id , name , r.referer_id , IF(num.referer_id IS NULL, 'NO', 'YES') as flag FROM refer r LEFT JOIN ( SELECT referer_id FROM refer GROUP BY referer_id HAVING COUNT(*) > 2 ) num ON r.id = num.referer_id"; $people = array(); $res = $db->query($sql); while (list($id,$name,$rid,$flag) = $res->fetch_row()) { $people[$id] = array($name,$rid,$flag); } $newId = 15; $srch = hasSponsor3($newId, $people); // call to recursive function if ($srch !== false) { echo $srch; } else { echo "ID \"$newId\" does not exist"; } function hasSponsor3($id, &$people) /** * Search people tree for any with 3+ referrals * * @param $id - id of new person * @param $people - array of $people * @return id of sponsor, 0 if no sponsor found, false if error */ { if (!isset($people[$id]) ) return false; $referrer = $people[$id][1]; if (!$referrer) { return 0; // top of tree } if ($people[$referrer][2] == 'YES') { return $referrer; // found a referrer with 3 or more referrals } else return hasSponsor3($referrer, $people); } ?>

Here's a tip for you when downloading $fp = fopen('php://output', 'w'); // so you can fputcsv to STDOUT Then you can use fputcsv directly to the output without going to a temp file first header(....) // output headers foreach($data as $row){ fputcsv($fp,$row); }

Try ... LEFT JOIN sold ON distribution.pdid = sold.pdid

You don't close the first <?php tag on that line

specify interval based on N days mysql> SELECT '2015-01-31' + INTERVAL 7 DAY as next_pay; +------------+ | next_pay | +------------+ | 2015-02-07 | +------------+ or N weeks mysql> SELECT '2015-01-31' + INTERVAL 2 WEEK as next_pay; +------------+ | next_pay | +------------+ | 2015-02-14 | +------------+

If the position of the password column may be a problem in future then it sounds like your query is using "SELECT * ". Don't. Specify the required columns. If you had, say, "SELECT id, username, password" it would not matter what the order of columns is in the table (so long as you don't actually drop any the used columns)

Why go to the trouble of maintaining that extra table with the YES flag when you can easily determine if a person has 3 referrals by querying the data you already have? Basic rule - do not store derived data. ids with 3 or more: SELECT referer_id FROM refer GROUP BY referer_id HAVING COUNT(*) > 2; Your search for sponsor's sponsor's sponsor will require a recursive function. Read the required data into an array for that otherwise it can get heavy on db server resources.

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.