Barand

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.

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 | +---------------------+-----------+