Jump to content

Code Help Please


patsman77

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.