Barand

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);

or $arr = [5,3,2]; // orig array $rem = [3,2]; // array of items to be removed $arr = array_diff($arr,$rem);

should be __construct() 2 underscores

+1. I always generate a pdf if printed output is required. (Has the advantage that it can be printed, saved, emailed or just viewed).

$p2=0; $found = 0; while(($p1 = strpos($str, '<mofish', $p2)) !== false) { $p2 = strpos($str, '/>', $p1); $x = (substr($str,$p1,$p2-$p1+2)); if (strpos($x, 'id="number2"')) { $found = 1; break; } } if ($found) { $newstr = str_replace($x, $replace, $str); }

try $str = '<div class="text"> <mofish id="number1" type="number1" title="true" /> </div> <div class="text"> <mofish id="number2" type="number2" title="true" /> </div>'; $replace = 'whatever'; $p2=0; while(($p1 = strpos($str, '<mofish', $p2)) !== false) { $p2 = strpos($str, '/>', $p1); $x = (substr($str,$p1,$p2-$p1+2)); if (strpos($x, 'id="number2"')) { break; } } $newstr = str_replace($x, $replace, $str);

by changing the code to meet the new requirements

what is wrong with number_format ?

Yes. Also note, in part answer to you other post, there is a NULL-safe equality operator http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to

The query I gave you in a previous post was to populate your dropdown menu. (http://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/?do=findComment&comment=1518346) It did NOT contain "WHERE b.book_id = $posted_id". You seem to be confusing issues. The WHERE clause is for selecting the selected book from you database on the subsequent page.

Dislike++ Not to mention unnecessarily creating them as (evil) GLOBALS

the query would be SELECT MONTH(due_date) as month , SUM(cust_order_total) AS order_total_sum FROM orders WHERE YEAR(due_date) = 2014 GROUP BY month