Barand

I did suggest how you might do it.

Because there is no record in course table where the course.id matches the user.course for Emma in the user table

you are missing the comma before SUM and you have too many "WHERE"s. PS It is more useful to tell us what you are trying to do instead of what you are not trying to do

yes, but neither will prevent you adding (2,3) and (3,2)

You could set a rule that the lower id is the user and higher id is the friend when adding the record and put a unique constraint on (user_id, friend_id)

A schema like this here http://forums.phpfreaks.com/topic/297801-how-can-i-write-a-query-select-from-table-where-a-pair-of-record-is-not-there/?do=findComment&comment=1518909 would fit that requrement

this line $str = file_get_contents("templates/staff.html", true); needs to be before the loop otherwise you reset the content each time

If you want to apply that rule then create a subquery to get both sets (user/friend and friend/user) SELECT user_id , friend_id FROM friend WHERE user_id = 2 UNION SELECT friend_id , user_id FROM friend WHERE friend_id = 2 +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 2 | 3 | | 2 | 4 | | 2 | 1 | +---------+-----------+ then left join with that instead of the friend table SELECT u.id , u.name as non_friend FROM user u LEFT JOIN ( SELECT user_id , friend_id FROM friend WHERE user_id = 2 UNION SELECT friend_id , user_id FROM friend WHERE friend_id = 2 ) f ON u.id = f.friend_id WHERE u.id <> 2 AND f.friend_id IS NULL ORDER BY id; +----+------------+ | id | non_friend | +----+------------+ | 5 | user 5 | +----+------------+

All arrays have keys $result = [ ['string'=>'replacement 1', 'template'=>'template1'], ['string'=>'replacement 2', 'template'=>'template2'], ]; echo '<pre>',print_r($result, true),'</pre>'; gives Array ( [0] => Array ( [string] => replacement 1 [template] => template1 ) [1] => Array ( [string] => replacement 2 [template] => template2 ) )

<?php $paths = [ 'B' => 'C', 'A' => 'B', 'C' => 'D' ]; $orig = 'A'; $dest = 'D'; $start = $orig; $end = ''; while ($end != $dest) { $end = $paths[$start]; echo "$start —> $end<br>"; $start = $end; } ?>

isset() is for checking if PHP variables exist, not for external physical entities such as a database. If the database didn't exist your connection code would fail EG $myconn = new mysqli($servername, $username, $password, $dbname);

$user_query only gets a value if this condition is true if(isset($u992092914_trial)) If it is not true you attempt the query anyway on an empty string. Where is $u992092914_trial being set?

I have changed my tables and query, which is now $sql = "SELECT username , week , SUM(points) FROM ( SELECT a.username , a.week , CASE WHEN pick=result THEN value ELSE 0 END as points FROM allpoints a INNER JOIN phpfb_picks p USING (username,gameid) INNER JOIN phpfb_schedule s USING (week,gameid) ) wktots GROUP BY username, week"; The results are the same

Then turn it on! When developing you need error_reporting = E_ALL display_errors = ON

My cover's blown.

try putting the replacement code in a separate function then call it for each row // array to simulate db results $result = [ ['string'=>'replacement 1', 'template'=>'template1'], ['string'=>'replacement 2', 'template'=>'template2'], ]; $str = '<div class="col-xs-3 text-center"> <h4><mofish id="template1" type="text" label="Title" /></h4> <p><mofish id="template2" type="text" label="Description" /></p> </div>'; // for each row in database foreach($result as $row){ // set replacement to be the string from db $replace = $row['string']; $srch = $row['template']; $str = replace_mofish($srch, $replace, $str); } echo '<pre>',htmlentities($str),'</pre>'; // check output function replace_mofish($srch, $replace, $str) { $p2=0; $found = 0; while(($p1 = strpos($str, '<mofish', $p2)) !== false) { $p2 = strpos($str, '/>', $p1); $x = (substr($str,$p1,$p2-$p1+2)); // template contains template1 or template2 if (strpos($x, "id=\"$srch\"")) { $found = 1; break; } } if ($found) { return str_replace($x, $replace, $str); } else return $str; } gives <div class="col-xs-3 text-center"> <h4>replacement 1</h4> <p>replacement 2</p> </div>

the data mysql> SELECT id, name FROM user; +----+--------+ | id | name | +----+--------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | | 4 | user 4 | | 5 | user 5 | +----+--------+ mysql> SELECT id, user_id, friend_id FROM friend; +----+---------+-----------+ | id | user_id | friend_id | +----+---------+-----------+ | 1 | 1 | 2 | | 2 | 1 | 3 | | 3 | 2 | 3 | | 4 | 2 | 4 | | 5 | 4 | 5 | +----+---------+-----------+ Suppose I am logged in as user #2. The main part of the query finds all my possible friends (IE all users that are not me). So leaving out the LEFT JOIN we have SELECT u.id , u.name FROM user u WHERE u.id <> 2 ORDER BY id; +----+--------+ | id | name | +----+--------+ | 1 | user1 | | 3 | user3 | | 4 | user 4 | | 5 | user 5 | +----+--------+ So having got the possible friends we want to compare that list agianst the friends I already have to see which ones are missing. SELECT u.id SELECT user_id , u.name , friend_id FROM FROM user u friend WHERE u.id <> 2 WHERE user_id = 2 ORDER BY id +----+--------+ +---------+-----------+ | id | name | | user_id | friend_id | +----+--------+ +---------+-----------+ | 1 | user1 | | 2 | 3 | | 3 | user3 | | 2 | 4 | | 4 | user 4 | +---------+-----------+ | 5 | user 5 | | +----+--------+ | | | | | | | +-------------------LEFT JOIN---------------------+ The way to do this is to use a LEFT JOIN which will get the data from users and friends. Where there is no matching friend record the fields from friend are null. To do this we use FROM user u LEFT JOIN friend f ON u.id = f.friend_id AND f.user_id = 2 which takes those friend records for user #2 and matches the possible user ids against the existing friend ids The query is now SELECT u.id , u.name , f.friend_id FROM user u LEFT JOIN friend f ON u.id = f.friend_id AND f.user_id = 2 WHERE u.id <> 2 ORDER BY id; +----+--------+-----------+ | id | name | friend_id | +----+--------+-----------+ | 1 | user1 | NULL | | 3 | user3 | 3 | | 4 | user 4 | 4 | | 5 | user 5 | NULL | +----+--------+-----------+ Nearly there! We label the name column with an alias "non_friend" (as that is what they are). We only wanted the friend_id column to show the nulls, so that can go. Lastly, we are only interested in those with NULL values (non-matching) so we add an extra condition to the the WHERE clause AND f.friend_id IS NULL which gives the final version of the query SELECT u.id , u.name as non_friend FROM user u LEFT JOIN friend f ON u.id = f.friend_id AND f.user_id = 2 WHERE u.id <> 2 AND f.friend_id IS NULL ORDER BY id; +----+------------+ | id | non_friend | +----+------------+ | 1 | user1 | | 5 | user 5 | +----+------------+ I hope that helps.

Have you got error reporting turned on in your php.ini file? db_inc.php <?php define("HOST",'localhost'); define("USERNAME",'*******'); define("PASSWORD",'*******'); define("DATABASE", '*******'); $mysqli_driver = new mysqli_driver(); $mysqli_driver->report_mode = MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT;

this should do it <?php include("db_inc.php"); // define HOST, USERNAME etc $db = new mysqli(HOST,USERNAME,PASSWORD,'patsman'); $sql = "SELECT username , week , SUM(points) FROM ( SELECT a.username , a.week , CASE WHEN pick=result THEN value ELSE 0 END as points FROM allpoints a INNER JOIN picks p USING (username,week,gameid) INNER JOIN schedule s USING (week,gameid) ) wktots GROUP BY username, week"; $data = []; $weeks = range(1,17); // create initial array elements for each user $newarray = array_fill_keys($weeks,''); $newarray['total'] = 0; // store results in $data array by user $curruser=''; $res = $db->query($sql); while (list($user,$wk,$pts) = $res->fetch_row()) { if ($user != $curruser) { $data[$user] = $newarray; $curruser = $user; } $data[$user]['total'] += $pts; $data[$user][$wk] = $pts; } // sort data array by total pts desc uasort($data, function($a,$b) { return $b['total'] - $a['total']; }); // table headings $thead = "<tr><th colspan='3'>Points YTD</th><th colspan='17'>Points by week</th></tr>\n"; $thead .= "<tr><th>Rank</th><th>Name</th><th>Total</th><th>" . join('</th><th>', $weeks) . "</th></tr>\n"; // build table data $tdata = ''; $count = $rank = 1; $prevtot = 0; foreach ($data as $user => $udata) { $rank = ($udata['total']==$prevtot) ? $rank : $count; ++$count; $prevtot = $udata['total']; $tdata .= "<tr><td>$rank</td><td>$user</td><td>{$udata['total']}</td><td>" . join('</td><td>', array_slice($udata, 0, 17)) . "</td></tr>\n"; } ?> <html> <head> <title>Confidence Pool</title> </head> <body> <table border='1'> <?=$thead?> <?=$tdata?> </table> </body> </html>

Is the "value" in that table the "confidence value"? Why have "allpoints" and "picks" tables, they are almost identical. Why not put the pick and value for the week in the same table?

Yes thanks. Another question - how are the points calculated and assigned?

OK, you didn't take the hint about providing some data. I'll just set up my own tables and several week's test data before I can start on your problem. Don't expect an instant reply.

what is table1? what is table2? what is table3? what is a "pick"? what is a "confidence value"? Perhaps you show some sample data for each table?

Which bit are you having a problem with? Show your current code

here's a replacement "combine indexes" section of the code // // combine indexes // uasort($filtered, function($a,$b) {return count($b) - count($a);}); foreach ($filtered as $i=>$a) { foreach ($filtered as $j=>$b) { if ($i==$j) continue; if (count($a)<2 || count($b)<2) continue; if (array_intersect($a, $b)) { $filtered[$j] = array_unique(array_merge($a,$b)); } } } foreach ($filtered as $k => $kwarr) { if (count($kwarr) == 0) { $otheritems[] = $k; } elseif (count($kwarr) > 1) { $newkw = join(' & ', $kwarr); $occurs = []; foreach ($kwarr as $kw) { if (isset($kwindex[$kw])) { $occurs = array_merge($occurs, $kwindex[$kw]); // combine individual lists unset($kwindex[$kw]); // then remove them } } sort($occurs); $newkw = join(' & ', $kwarr); $kwindex[$newkw] = array_unique($occurs); // add the combined index } } $kwindex = array_filter($kwindex);