Code Help Please

[patsman77]

<?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

 

 

[ginerjm]

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?

[patsman77]

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.

[ginerjm]

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

[patsman77]

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?

[patsman77]

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.

[Barand]

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?

[patsman77]

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.

[patsman77]

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

[Barand]

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.

[patsman77]

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?

[Barand]

Yes thanks.

 

Another question - how are the points calculated and assigned?

[patsman77]

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

[Barand]

 

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?

[patsman77]

I agree. I did not build it I am just trying to make it work

[ginerjm]

Table 3 is unnecessary . You should merge it with Table 1

[patsman77]

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.

[Barand]

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>

[gizmola]

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.

 

[patsman77]

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?

[Barand]

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;

[patsman77]

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.

[patsman77]

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.

[Barand]

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.

[Barand]

I do not have error reporting turned on.

 

Then turn it on!

 

When developing you need

• error_reporting = E_ALL
• display_errors = ON