Barand

I used your site page to generate the pedigree for Dog#4 then entered that in their calculator chart. There was no dog #20 in their results (only 14 and 17) and then I looked at what results would give their value for #17.

Looks like that site used REF SITE METHOD 3 | 14 | 0.0625 | 1 | 2 | | 17 | 0.0312 | 2 | 2 | | 17 | 0.0625 | 2 | 1 | Dog 4 COI : 0.1562 Dog #20 ignored, and only two of the three occurrences of #17 were used. So why?

So, which is correct for Dog #4? METHOD 1 | 14 | 0.0625 | 1 | 2 | | 20 | 0.0156 | 2 | 3 | | 17 | 0.0156 | 2 | 3 | | 17 | 0.0312 | 2 | 2 | | 17 | 0.0625 | 2 | 1 | Dog 4 COI : 0.1875 METHOD 2 | 14 | 0.0625 | 1 | 2 | | 20 | 0.0156 | 2 | 3 | | 17 | 0.0156 | 2 | 3 | | 17 | 0.1250 | 0 | 2 | | 17 | 0.2500 | 0 | 1 | Dog 4 COI : 0.4688

For dog 4, you had this for common ancestor 17 sire dam | 17 | Dog R | 2| 3,2,1| so you processed it as 0.015625 | 2 | 3 | 0.03125 | 2 | 2 | 0.0625 | 2 | 1 | Shouldn't it have been 0.015625 | 2 | 3 | 0.125 | 0 | 2 | 0.25 | 0 | 1 |

Does this version give the results you expect? <?php $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id][] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } function COI($id, &$dogs) { if ($id==0) return 0; $sires = $dams = []; getAncestors($dogs[$id][0], $dogs, $sires, 1); getAncestors($dogs[$id][1], $dogs, $dams, 1); $result=0; foreach ($sires as $did=>$dists) { if (isset($dams[$did])) { if (!is_null($dogs[$did][3])) { return $dogs[$did][3]; } else { $sumd = array_sum($dists) + array_sum($dams[$did]); $result += pow(0.5, 1+$sumd) * (1 + COI($did, $dogs)); $dogs[$did][3] = $result; } } } return $result; } $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm,null]; } $dogid = 9; printf("Dog %d COI : %0.3f", $dogid, COI($dogid, $dogs)); ?>

The problem is the function was only storing one occurrence per ancestor. Try this $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id][] = $dist; // <-- changed to store more than 1 distance if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } $dogid = 1; getAncestors($dogs[$dogid][0], $dogs, $sires, 1); getAncestors($dogs[$dogid][1], $dogs, $dams, 1); ksort($sires); ksort($dams); echo '<pre>sires ',print_r($sires, true),'</pre>'; echo '<pre>dams ',print_r($dams, true),'</pre>'; $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s |%6s|%6s|\n", $id, $dogs[$id][2], join(',', $sires[$id]), // changed to show the join(',', $dams[$id]) ); // multiple distances } Now gives results like | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 3| 2| | 10 | dog K | 4,3| 3| | 16 | dog Q | 4| 3| | 17 | dog R | 4| 3| | 20 | dog U | 5,4| 4| | 21 | dog V | 5,4| 4|

Add another element to the $dogs array to store the COI (Fa) for that dog. It will require a recursive function along these lines function COI(ancester_id) { if (COI is in the table already) { return COI from the table } else { calculate COI of ancester // will require calls to COI(ancester) the recursive bit store in table return calculated value; } }

try this version $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } $dogid = 1; $sires = $dams = []; getAncestors($dogs[$dogid][0], $dogs, $sires, 1); getAncestors($dogs[$dogid][1], $dogs, $dams, 1); ksort($sires); ksort($dams); #echo '<pre>',print_r($dogs, true),'</pre>'; echo '<pre>sires ',print_r($sires, true),'</pre>'; echo '<pre>dams ',print_r($dams, true),'</pre>'; $common = array_intersect_key($sires,$dams); #$common = array_merge($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); }

Can you post/attach a dump of your data?

Here's one to start with http://forums.phpfreaks.com/topic/293179-reseting-of-passwords/?do=findComment&comment=1500065

The first half of the UNION finds (for each trainer) free time before first booking free time between bookings all trainers time if no bookings The second part finds trainers' free time after last booking of the day. SELECT trainer , from_time , to_time , timeslot FROM ( SELECT a.day , TIMEDIFF(IFNULL(start_time,close_time), IF(a.trainer=@prevt,@prevend,open_time )) as timeslot , CAST(IF(a.trainer=@prevt,@prevend,open_time ) as TIME) as from_time , IFNULL(start_time,close_time) as to_time , @prevend := end_time as prevend , @prevt := a.trainer as trainer FROM bookingavailability a JOIN (SELECT @prevend:=null,@prevt:=null) as init LEFT JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day AND c.bookingdate = '2014-08-18' WHERE a.day = WEEKDAY('2014-08-18') ORDER BY a.trainer, c.start_time ) gaps UNION SELECT a.trainer , MAX(end_time) as from_time , a.close_time as to_time , TIMEDIFF(MAX(end_time),close_time) as timeslot FROM bookingavailability a INNER JOIN ( SELECT trainer , MAX(end_time) as end_time FROM bookingscalendar WHERE bookingdate = '2014-08-18' GROUP BY trainer ) eod WHERE a.day = WEEKDAY('2014-08-18') ORDER BY trainer,from_time;

If you do that you won't know about the clients that are double-booked to inform them and change their bookings, you just hide the problem. This query will pull any booking where a room is booked at the same time as another booking. SELECT id ,bookingdate ,room ,start_time ,end_time ,trainer ,customer_id FROM bookingscalendar WHERE id IN ( SELECT b1.id FROM bookingscalendar b1 INNER JOIN bookingscalendar b2 ON b1.bookingdate = b2.bookingdate AND b1.room = b2.room AND b1.start_time < b2.end_time AND b1.end_time > b2.start_time AND b1.id <> b2.id ) ORDER BY bookingdate,room,start_time; Ensure at the time of booking that only free rooms can be allocated.

Your availability is for trainer 2, your bookings for trainer 1. Dates will be null when no matching bookings for a trainer

I notice you have added another level of complexity with the inclusion of the "room" in the bookings. So as well as checking for trainer availability you also have to check for room availability.

These are my table definitions CREATE TABLE `bookingscalendar` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bookingdate` date DEFAULT NULL, `trainer` int(11) DEFAULT NULL, `start_time` time DEFAULT NULL, `end_time` time DEFAULT NULL, `customer_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `bookingavailability` ( `availability_id` int(11) NOT NULL AUTO_INCREMENT, `trainer` int(11) DEFAULT NULL, `dayofweek` int(11) DEFAULT NULL, `open_time` time DEFAULT NULL, `close_time` time DEFAULT NULL, PRIMARY KEY (`availability_id`) )

Booking date looks OK when I run it. I had to comment out the trainername column, that should be in a trainer table, not in availability table) mysql> SELECT trainer -> -- , trainername -> , dayofweek -> , bookingdate -> , CONCAT(from_time,'') as from_time -> , to_time, timeslot -> FROM -> ( -> SELECT a.trainer -> -- , a.trainername -> , dayofweek -> , bookingdate -> , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot -> , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time -> , start_time as to_time -> , @prevend := end_time as prevend -> , @prevdate := bookingdate as prevdate -> FROM bookingavailability a -> JOIN (SELECT @prevend:=null,@prevdate:=null) as init -> INNER JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.dayofweek -> -> UNION -> -> SELECT a.trainer -> -- , a.trainername -> , dayofweek -> , bookingdate -> , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot -> , IFNULL(MAX(end_time),open_time) as from_time -> , close_time as to_time -> , null as prevend -> , null as prevdate -> FROM bookingavailability a -> LEFT JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.dayofweek -> GROUP BY a.trainer,dayofweek,bookingdate -> ) as gaps -> WHERE timeslot > '00:00:00' -> ORDER BY trainer, dayofweek, bookingdate, from_time; +---------+-----------+-------------+-----------+----------+----------+ | trainer | dayofweek | bookingdate | from_time | to_time | timeslot | +---------+-----------+-------------+-----------+----------+----------+ | 1 | 0 | 2014-08-18 | 09:00:00 | 10:00:00 | 01:00:00 | | 1 | 0 | 2014-08-18 | 11:00:00 | 13:00:00 | 02:00:00 | | 1 | 0 | 2014-08-18 | 14:30:00 | 16:00:00 | 01:30:00 | | 1 | 0 | 2014-08-18 | 17:30:00 | 20:00:00 | 02:30:00 | | 1 | 1 | 2014-08-19 | 10:30:00 | 17:00:00 | 06:30:00 | | 1 | 2 | 2014-08-20 | 11:00:00 | 12:00:00 | 01:00:00 | | 1 | 2 | 2014-08-20 | 13:00:00 | 15:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 08:00:00 | 10:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 11:00:00 | 13:00:00 | 02:00:00 | +---------+-----------+-------------+-----------+----------+----------+ Check your data

this one

There is no "probably" about it, it's a certainty.

Corrected version $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], $dogs, $sires, 1); getAncestors($dogs[$dogid][1], $dogs, $dams, 1); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 3 | 2 | | 16 | dog Q | 4 | 3 | | 17 | dog R | 4 | 3 |

PS - apologies for the bisexual dog in the data, but you get the idea

A couple of years back I was working on dog pedigree charts so still had some test data. The chart of the data is attached and, as you can see, there are common ancestors. To find them you need to use a recursive function so I would load your data into an array and use that for the recursive search rather bombard your server with dozens of queries. This code will give the common ancestors and the generational distance on the sire and dam sides. You would then need to calc the IC value for each of these ancestors. $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, $key, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][$key], 0, $dogs, $ancests, $dist+1); getAncestors($dogs[$id][$key], 1, $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], 0, $dogs, $sires, 0); getAncestors($dogs[$dogid][1], 1, $dogs, $dams, 0); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } Outputs | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 2 | 1 | | 16 | dog Q | 3 | 2 | | 17 | dog R | 3 | 2 |

Here is an example using the bookingscalendar table <?php include("db_inc.php"); $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT bookingdate , trainer , start_time , end_time , customer_id FROM bookingscalendar ORDER BY bookingdate,start_time"; $data = array(); $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $data[$row['bookingdate']][] = array_slice($row,1,4,true); } $json = json_encode($data); // // write to file // file_put_contents('mybookings.json', $json); // // show results (for demo purposes only) // echo '<pre>',print_r($data, true),'</pre>'; echo $json; /**** RESULTS *************************************************************** The array: Array ( [2014-08-18] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 10:00:00 [end_time] => 11:00:00 [customer_id] => 101 ) [1] => Array ( [trainer] => 1 [start_time] => 13:00:00 [end_time] => 14:30:00 [customer_id] => 102 ) [2] => Array ( [trainer] => 1 [start_time] => 16:00:00 [end_time] => 17:30:00 [customer_id] => 103 ) ) [2014-08-19] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 09:00:00 [end_time] => 10:30:00 [customer_id] => 106 ) ) [2014-08-20] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 09:00:00 [end_time] => 11:00:00 [customer_id] => 101 ) [1] => Array ( [trainer] => 1 [start_time] => 12:00:00 [end_time] => 13:00:00 [customer_id] => 105 ) [2] => Array ( [trainer] => 1 [start_time] => 15:00:00 [end_time] => 17:00:00 [customer_id] => 106 ) ) [2014-08-21] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 10:00:00 [end_time] => 11:00:00 [customer_id] => 102 ) ) ) The JSON-encoded array data: {"2014-08-18":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"101"}, {"trainer":"1","start_time":"13:00:00","end_time":"14:30:00","customer_id":"102"}, {"trainer":"1","start_time":"16:00:00","end_time":"17:30:00","customer_id":"103"}], "2014-08-19":[{"trainer":"1","start_time":"09:00:00","end_time":"10:30:00","customer_id":"106"}], "2014-08-20":[{"trainer":"1","start_time":"09:00:00","end_time":"11:00:00","customer_id":"101"}, {"trainer":"1","start_time":"12:00:00","end_time":"13:00:00","customer_id":"105"}, {"trainer":"1","start_time":"15:00:00","end_time":"17:00:00","customer_id":"106"}], "2014-08-21":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"102"}]} *****************************************************************************/ ?>

When JOINing table it is not uncommon for the same column name to be used in both tables. In my tables (above) the "trainer" column is used in bookingscalendar and bookingavailabilty tables. When referencing trainer, therefore, it is necessary to define which one otherwise you will get an "ambiguous column name" error. This is done by prefixing the column name with the table name SELECT bookingavailability.trainer, bookingavailability.day ... FROM .... Also, if you wanted to reference another database in your query then you would need to specify the database name too SELECT myotherdatabasename.tablename.columnname , ... FROM ... The writing and reading of the query is improved if you use table aliases Thus SELECT bookingavailability.trainer, bookingavailability.day, myotherdatabasename.tablename.columnname FROM bookingavailability INNER JOIN myotherdatabasename.tablename ON bookingavailability.trainer = myotherdatabasename.tablename.trainer WHERE myotherdatabasename.tablename.trainer > 1 ORDER BY myotherdatabasename.tablename.trainer becomes this if you use table aliases SELECT a.trainer, a.day, t.columnname FROM bookingavailability a INNER JOIN myotherdatabasename.tablename t ON a.trainer = t.trainer WHERE t.trainer > 1 ORDER BY t.trainer http://en.wikipedia.org/wiki/Alias_%28SQL%29