patsman77 Posted August 19, 2015 Share Posted August 19, 2015 <?php require('/home/swammisp/public_html/swammi/swammiconf.php'); $datenow=time()+7200; // When does the first game of the week start? $sql="select min(gametime) as t FROM ((select gametime from phpfb_schedule WHERE week=$week) as x)"; $tresult = mysql_query($sql); $tres = mysql_fetch_object($tresult); $mintime = $tres->t; $alllocked = ($mintime < $datenow); $base = "select DISTINCT username FROM allpoints"; $resultOrig = mysql_query($base); $base1 = "select DISTINCT username FROM allpoints"; $resultOrig1 = mysql_query($base1); $currentnum = 0; $numrows = mysql_num_rows($resultOrig1); $i=0; while ($baseRes=mysql_fetch_object($resultOrig)) { $user = $baseRes->username; $SumWeely = 0; $sumTotal = 0; $i++; for ($currWeek=1; $currWeek<=2; $currWeek++) { $sql = " SELECT allpoints.username,phpfb_picks.pick,allpoints.value,phpfb_schedule.result,phpfb_schedule.gameid FROM `allpoints` left join phpfb_picks on allpoints.gameid = phpfb_picks.gameid left join phpfb_schedule on phpfb_picks.gameid = phpfb_schedule.gameid WHERE phpfb_schedule.week = '".$currWeek."' and allpoints.username ='". $user ."' and phpfb_picks.user ='". $user ."'"; $result = mysql_query($sql) or die (mysql_error()); while($row = mysql_fetch_object($result)) { if($row->pick == $row->result ) { $SumWeely = $row->value; } } $sumTotal+= $SumWeely+$SumWeely; } } ?> <tr><td><?php echo $i; ?></td><td><?php echo $user; ?></td><td><?php echo $sumTotal; ?></td><td><?php echo $SumWeely; ?></td></tr> </table> Hello All, I am a beginner trying to make things happen. My skills are definitely low, but I am trying to improve. I am working on a standings page that will do the following: I am joining 3 tables to gather information. Username, Week, Winning Result, User Pick, and Assigned Points I am then looking at Winning Result, and if that is equal to User Pick, then Assign points I am looking for an out some to do the following columns: 1.) Rank (counter of 1 through how many distinct users in a specific table) 2.) Username 3.) Total points for all combined weeks 4. - 21) Week by week results. Basically, calculate how many points each user gets for each week and a total of all weeks combined. Here is the code I am working on (I can get one row to display, but and some of the data looks correct) Can anyone help with this? Thanks, Patsman77 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 19, 2015 Share Posted August 19, 2015 Way too complex code to wade into without any help from you. Don't you ever use comments to describe what all you are doing? And what is an "out some" that you say you are looking for? Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 sorry for the typo, out some = outcome..... I am basically pulling fields from 3 tables. table 1 has the username, gameID, and pick for the user table 2 has game result table 3 has username, gameid and assigned points What I am trying to do is pull those fields, then if pick from table 1 = result from table 2 then add assigned points that to a variable, and run a total for the week. So user select 16 games and assigns points for each game, 1- number of games for the week. And then there would be a variable that holds a total of all weeks. The display would be: Rank (counter), Username, Total of all weeks, Wk 1, Wk2, Wk3.....Wk17 Not sure if this helps, the code I am using was written my someone else and I am trying to complete it. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 19, 2015 Share Posted August 19, 2015 Your pool runs for multiple weeks, no? So how will you handle the data for each week for each user for each pick? Does the gameId have any indication of the week? The best way is to do a query that pulls all the data together for you. Such as the data from table 1 and the date from table2 in one query result. Of course I am assuming that table 23 does have the gameId in it. I am confused as to what 'assigned points is in table 3 Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 OK, non technically, this is a football pool (Confidence) Where you rank your picks for each week, 1 through number of games (usually 16) Table 1 has the username, pick, gameID, week Table 2 has the winner of the game, gameID, week Table 3 username, pick, and assigned confidence value, gameID, week I am pulling all these into 1 query. so my results show user name, week, pick, winner, assigned confidence value I want to look at this week by week.... and show the results for each week, and a total of all weeks. The user gets awarded the points they assigned if pick = winner. Make any sense? Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 I am getting 1 row displaying in my table. Rank = 3, Username is last one It found, a number I am not sure what it is, and a number that makes sense for the total, but not for the user it is displaying. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2015 Share Posted August 19, 2015 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? Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 Here is a link to the output format I am looking to get: http://swammisport.com/standings/confidenceJHTEST.php Based on my query: Select from allpoints (holds the username, week, assigned points, gameID) join phpfb_picks (hold username, gameID, pick, week) join phpfb_schedule (holds gameID, week, result (winner)) Pick is the team the user selects to win the game confidence value is the value you give your pick to win the game. 16 games usually, so you rank your picks 1-16 (or number of games for the week) getting all the data into the tables works fine, it is trying to display them based on the link above where I am struggling. Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 I switched Total and week 1.. .so now total is correct for that user..... but there should be 3 users there so it would be more like... 1.) the.swammi - 150 - 70 - 80 2.) Guest - 148 - 72-76 3.) Blindside 141 - 66 - 75 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2015 Share Posted August 19, 2015 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. 1 Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 Sorry, here is some data: Table 1 (allpoints) Week, gameID, username, value 1, 01, guest, 7 1, 02, guest, 3 1, 03 guest 15 2, 04, guest 6 2, 05, guest, 11 2, 06, guest, 7 Table 2 (picks) Week, gameID, username, pick 1, 01, guest, NE 1, 02, guest, GB 1, 03, guest, MIA 2, 04, guest, CHI 2, 05, guest, PHI 2, 06, guest, DAL Table 3 Schedule Week, gameID, result 1, 01, NE 1, 02, ATL 1, 03, MIA 2, 04, CHI 2, 05, SF 2, 06, DAL based on this user guest would have 22 points for week 1 and 13 points for week 2, total of 35 points. Does this help? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2015 Share Posted August 19, 2015 Yes thanks. Another question - how are the points calculated and assigned? Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 there is an entry form that the user selects who they think will win the game and then assigns a value of how confident they are that the team they chose will win. If there are 16 games for the week, they select all 16 games and assign a value 1-16 for those games. They cannot use the same number more than once for a week, so all numbers 1-16 would be assigned to different games. If they win the game, they are awarded the points they assigned. so if they pick Pittsburgh and assign 12 points, and Pittsburgh wins, they get 12 points. This is the same for all the games for the week. If they win the game they get the assigned value. Then there would be a weekly total, and a season total. (adding all weeks together) Thanks for looking into this for me! Much appreciated. Patsman77 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2015 Share Posted August 19, 2015 Table 1 (allpoints) Week, gameID, username, value 1, 01, guest, 7 1, 02, guest, 3 1, 03 guest 15 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? Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 I agree. I did not build it I am just trying to make it work Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 19, 2015 Share Posted August 19, 2015 Table 3 is unnecessary . You should merge it with Table 1 Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 19, 2015 Author Share Posted August 19, 2015 Yes agreed.... But for now I am running out of time as season starts soon. I can look to fix it next season.... Right now my main goal is producing the standings page if it's even possible this way. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2015 Share Posted August 20, 2015 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> Quote Link to comment Share on other sites More sharing options...
gizmola Posted August 20, 2015 Share Posted August 20, 2015 There are times when I question whether Barand is an actual human or a SQL generating Cyborg sent from the future to help the human race combat it's lack of relational database understanding. Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 20, 2015 Author Share Posted August 20, 2015 Sorry, but I am just gettinga blank white page? I assume it has to do with the DB connection? db_inc.php? I tried to replace $db = new mysqli(HOST,USERNAME,PASSWORD,'patsman'); with $db = new mysqlli("localhost", "my username", "my password", "my database name"); I tried to leave it as you have it and include a db_inc.php that has the above information and that did not work either. Where am I going wrong... besides being a nOOb? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2015 Share Posted August 20, 2015 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; Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 20, 2015 Author Share Posted August 20, 2015 Ok, I made the db_inc.php file as you showed, putting in my values. Then I changed the table references on the inner joins to reflect my table names (picks, schedule, which are phpfb_picks, phpfb_schedule). allpoints is correct table name. I am getting page cannot be displayed. problem with website or programming - error 500. I do not have error reporting turned on. Quote Link to comment Share on other sites More sharing options...
patsman77 Posted August 20, 2015 Author Share Posted August 20, 2015 I did notice an error on my part. I told you that week was a field on picks table, which it is not. I removed that from the inner join, but no joy. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2015 Share Posted August 20, 2015 There are times when I question whether Barand is an actual human or a SQL generating Cyborg sent from the future to help the human race combat it's lack of relational database understanding. My cover's blown. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2015 Share Posted August 20, 2015 I do not have error reporting turned on. Then turn it on! When developing you need error_reporting = E_ALL display_errors = ON Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.