Barand

If you have 10 cars and 10 drivers there are 100 different combinations, not 10. Table : car (car_id | carname) Table : driver (driver_id | drivername) To get all combinations SELECT carname, drivername FROM car CROSS JOIN driver

Good decision, Admins. A welcome addition to the ranks. Congratulations QOC.

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

First, my apologies - it looks as though I accidentally clipped a couple of the lines of code when I copied from the command line screen. Here is the correct query SELECT trainer, day, bookingdate, from_time, to_time, timeslot FROM ( SELECT a.trainer , a.day , 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.day UNION SELECT a.trainer , day , 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.day GROUP BY a.trainer,day,bookingdate ) as gaps WHERE timeslot > '00:00:00' ORDER BY trainer, day, bookingdate, from_time; @prevdate and @prevend are user variables that used to store values from each row in the resultset so they can be used in the following row. eg in row 1 store the end_time of the booking in @prevend. (line 10) In row2 the available gap between @prevend and the start_time of this booking is calculated (line 7) However, if the second record is not the same date as the first then the available gap is the time between the start of day (open_time) and the start_time of the booking. Therefore the date of each record is stored in @prevdate (line 11). , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time Similarly in the second part (line 23), if there are no bookings for the day the available time at the end of the day is close_time minus open_time instead of the end_time of last booking minus close_time , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot

You can do it with a minimal resource availability table but the difficulty lies with finding the times that are not there (ie the gaps between the bookings) So if you have a bookings table mysql> SELECT * FROM bookingscalendar -> ORDER BY bookingdate, start_time; +----+-------------+---------+------------+----------+-------------+ | id | bookingdate | trainer | start_time | end_time | customer_id | +----+-------------+---------+------------+----------+-------------+ | 1 | 2014-08-18 | 1 | 10:00:00 | 11:00:00 | 101 | | 2 | 2014-08-18 | 1 | 13:00:00 | 14:30:00 | 102 | | 3 | 2014-08-18 | 1 | 16:00:00 | 17:30:00 | 103 | | 8 | 2014-08-19 | 1 | 09:00:00 | 10:30:00 | 106 | | 4 | 2014-08-20 | 1 | 09:00:00 | 11:00:00 | 101 | | 5 | 2014-08-20 | 1 | 12:00:00 | 13:00:00 | 105 | | 6 | 2014-08-20 | 1 | 15:00:00 | 17:00:00 | 106 | | 7 | 2014-08-21 | 1 | 10:00:00 | 11:00:00 | 102 | +----+-------------+---------+------------+----------+-------------+ and a table for the trainer availability by day of the week (0 = Monday) mysql> SELECT * FROM bookingavailability; +-----------------+---------+------+-----------+------------+ | availability_id | trainer | day | open_time | close_time | +-----------------+---------+------+-----------+------------+ | 3 | 1 | 0 | 09:00:00 | 20:00:00 | | 4 | 1 | 1 | 09:00:00 | 17:00:00 | | 5 | 1 | 2 | 09:00:00 | 17:00:00 | | 6 | 1 | 3 | 08:00:00 | 13:00:00 | +-----------------+---------+------+-----------+------------+ you can now find those gaps. The first part of the query finds the time difference between a booking start_time and the end_time of the previous booking (or since the start of day if no previous booking that day). The second part of the query finds any gaps between the end of the last booking and that day's closing time. mysql> SELECT trainer, day, bookingdate, from_time, to_time, timeslo -> FROM -> ( -> SELECT a.trainer -> , a.day -> , bookingdate -> , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend, as timeslot -> , IF(bookingdate=@prevdate,@prevend,open_time ) as from_t -> , 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.day -> -> UNION -> -> SELECT a.trainer -> , day -> , bookingdate -> , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) -> , 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.day -> GROUP BY a.trainer,day,bookingdate -> ) as gaps -> WHERE timeslot > '00:00:00' -> ORDER BY trainer, day, bookingdate, from_time; +---------+------+-------------+-----------+----------+----------+ | trainer | day | 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 | +---------+------+-------------+-----------+----------+----------+

Also Use a while loop and not do..while() otherwise the first record isn't read until the end of the first iteration. Use mysql_real_escape_string() instead of addslashes() (Better still, use mysqli or PDO)

MySQL Admin is fine for editing records. Open a connection under "SQL Development" Right click a table name and select "Edit table data"

it should now look something like this $sql = "SELECT DISTINCT date FROM attendance ORDER BY DATE"; $res = $db->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); $emptyRow['P'] = 0; $emptyRow['A'] = 0; // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table border='1'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th><th>Present</th><th>Absent</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT date, staffname, status FROM attendance ORDER BY staffname"; $res = $db->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; $rowdata[$s]++; } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; $tdata .= "</table\n"; ?> <html> <head> <style type="text/css"> td,th { text-align: center; padding: 5px; } table { border-collapse:collapse; } </style> </head> <body> <?php echo $heads; echo $tdata; ?> </body> </html>

I expected you would remove the lines you added last time since you no longer want the totals by date, despite your earlier post:

You need to add keys "P" and "A" to the $emptyRow array and increment those. $rowdata[$s]++; You'll also need to add the two headings to the table headings row.

Added a few lines <?php $sql = "SELECT DISTINCT date FROM attendance ORDER BY DATE"; $res = $db->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); // create arrays for "absent" and "present" $present = $absent = array_fill_keys($dates, 0); // ADD LINE // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table border='1'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT date, staffname, status FROM attendance ORDER BY staffname"; $res = $db->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; switch ($s) { // ADD THIS SWITCH STATEMENT case 'P' : $present[$d]++; break; default: $absent[$d]++; } } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; // present and absent totals // ADD THESE LINES $tdata .= "<tr><td>PRESENT</td><td>" . join('</td><td>', $present). "</td></tr>\n"; $tdata .= "<tr><td>ABSENT</td><td>" . join('</td><td>', $absent). "</td></tr>\n"; $tdata .= "</table\n"; ?>

Do you mean absent and present on each date?

Here's my method for reports like that $sql = "SELECT DISTINCT date FROM attendance ORDER BY DATE"; $res = $db->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table border='1'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT date, staffname, status FROM attendance ORDER BY staffname"; $res = $db->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; $tdata .= "</table\n"; ?> <html> <head> <style type="text/css"> td { text-align: center; } table { border-collapse:collapse; } </style> </head> <body> <?php echo $heads; echo $tdata; ?> </body> </html> Your dates (dd-mm-yyyy) are unusable in a database. Store as type DATE format YYYY-MM-DD so they can be correctly sorted or compared.

If he did find a solution it would have been nice if he had posted it here.

I've only just noticed that this thread got hijacked. I hope, Steve, that I managed to kill two birds with one stone.

Well done!

I'm guessing because the table and column names provided do not match those in the original posts. And there would definitely be a charge + expenses for the foot rub. It would currently look like this Team1 Team 2 Team 3 Team 4 Team 5 Team 1 X 2-1 3-2 1-2 Apr03 Team 2 2-2 X Apr10 4-0 1-1 Team 3 May01 1-0 X 0-0 2-1 Team 4 2-2 1-2 3-2 X 4-2 Team 5 2-1 1-1 0-1 2-2 X

Not without data.

Had to get a night's sleep first. Here's my offering $db = new mysqli(HOST,USERNAME,PASSWORD,'jointute'); /** * column heads */ $teams = array(); $sql = "SELECT team_id, team_name FROM teams ORDER BY team_name"; $res = $db->query($sql); while (list($hid, $hname) = $res->fetch_row()) { $teams[$hid] = $hname; } $output = '<tr><td> </td><td>' . join('</td><td>', $teams) . "</td></tr>\n"; $blank_scores = array_fill_keys(array_keys($teams),''); $now = 4; // current week $sql = "SELECT team_name, home_team, away_team, CASE WHEN date <= CURDATE() THEN CONCAT(home_goals,'-',away_goals) ELSE DATE_FORMAT(date, '%b %e') END as score FROM all_games INNER JOIN teams ON all_games.home_team = teams.team_id ORDER BY team_name"; $currTeam = ''; $res = $db->query($sql); while (list($teamname, $home, $away, $score) = $res->fetch_row()) { if ($currTeam != $teamname) { if ($currTeam) { $output .= "<tr><td>$currTeam</td><td>" . join('</td><td>', $teamscores) . "</td></tr>\n"; } $currTeam = $teamname; $teamscores = $blank_scores; } $teamscores[$home] = 'X'; $teamscores[$away] = $score; } $output .= "<tr><td>$currTeam</td><td>" . join('</td><td>', $teamscores) . "</td></tr>\n"; ?> <table border='1'> <?php echo $output ?> </table>

I thought he wanted the league table above that one I wouldn't bet on it

I had a similar table to yours (except week numbers instead of dates). These were the final results of the matches. For readability I substituted names for team ids: +--------+----------+-----------+----------+-----------+ | weekno | hometeam | homegoals | awayteam | awaygoals | +--------+----------+-----------+----------+-----------+ | 1 | Laker | 1 | Jardine | 1 | | 1 | Cowdrey | 1 | Grace | 0 | | 2 | Grace | 2 | Cowdrey | 2 | | 2 | Jardine | 1 | Laker | 3 | | 3 | Laker | 2 | Cowdrey | 4 | | 3 | Jardine | 4 | Grace | 4 | | 4 | Cowdrey | 4 | Laker | 4 | | 4 | Grace | 1 | Jardine | 2 | | 5 | Cowdrey | 2 | Jardine | 0 | | 5 | Grace | 0 | Laker | 3 | | 6 | Jardine | 1 | Cowdrey | 4 | | 6 | Laker | 4 | Grace | 1 | +--------+----------+-----------+----------+-----------+ The query I used was this (my house names are your teams) SELECT h.house_name as House, COUNT(*) as Played, SUM(won) as Won, SUM(drawn) as Drawn, SUM(lost) as Lost, SUM(gdiff) as GDiff, SUM(points) as Points FROM ( SELECT hometeam as houseid, CASE WHEN homegoals > awaygoals THEN 1 ELSE 0 END as won, CASE WHEN homegoals = awaygoals THEN 1 ELSE 0 END as drawn, CASE WHEN homegoals < awaygoals THEN 1 ELSE 0 END as lost, homegoals - awaygoals as gdiff, CASE WHEN homegoals > awaygoals THEN 3 WHEN homegoals = awaygoals THEN 1 ELSE 0 END as points FROM fixture UNION ALL SELECT awayteam as houseid, CASE WHEN homegoals < awaygoals THEN 1 ELSE 0 END as won, CASE WHEN homegoals = awaygoals THEN 1 ELSE 0 END as drawn, CASE WHEN homegoals > awaygoals THEN 1 ELSE 0 END as lost, awaygoals - homegoals as gdiff, CASE WHEN homegoals < awaygoals THEN 3 WHEN homegoals = awaygoals THEN 1 ELSE 0 END as points FROM fixture ) as X INNER JOIN house h USING (houseid) GROUP BY h.house_name ORDER BY Points DESC, GDiff DESC and the results were +---------+--------+------+-------+------+-------+--------+ | House | Played | Won | Drawn | Lost | GDiff | Points | +---------+--------+------+-------+------+-------+--------+ | Cowdrey | 6 | 4 | 2 | 0 | 8 | 14 | | Laker | 6 | 3 | 2 | 1 | 6 | 11 | | Jardine | 6 | 1 | 2 | 3 | -6 | 5 | | Grace | 6 | 0 | 2 | 4 | -8 | 2 | +---------+--------+------+-------+------+-------+--------+ I'm sure I have posted this before but searches on the forum seem to exclude code content.