lukep11a

March 28, 2013

I am trying to get the rank of each team direct from a select statement.

My ultimate goal is to select the rank of a team depending on the number of total points, and also the number of total points minus the last 7 days in the same select statement as to get the change in rank.

I am currently doing this using a select query inside a while loop which is loading very slowly as there are currently about 100 rows and think if it were in one statement it would load much quicker.

But I can't even get the rank for the total points. I thought if I did this first then I could use a join to also select the rank of the total points minus the last 7 days.

I was thinking something like this would work:

SELECT t1.team_id, SUM(t1.points) AS total1, COUNT(t2.team_id) AS rank
FROM team_points t1
LEFT JOIN
   (SELECT team_id, SUM(points) AS total2 
   FROM team_points 
   GROUP BY team_id) AS t2
ON t1.total1 < t2.total2
GROUP BY team_id
ORDER BY total DESC

But that's not right. It gives the error Unknown column 't1.total1' in 'on clause'. I am struggling to get my head around this. I have tried many other ways without success. Does anybody have any ideas? Or am I better off sticking to my old way?

March 10, 2013

I am trying to determine the rank of a team using a single select statement. The rank is determined based on total_points.

SELECT COUNT(*) + 1 AS rank, SUM(CASE WHEN team_selections.transfer_in < fixtures.date AND team_selections.transfer_out > fixtures.date THEN tpts.points ELSE 0 END) as total
FROM user_teams
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points tpts
		ON team_selections.team_id = tpts.team_id
		LEFT JOIN fixtures
		ON tpts.fixture_id = fixtures.fixture_id
LEFT OUTER JOIN
(SELECT SUM(CASE WHEN team_selections.transfer_in < fixtures.date AND team_selections.transfer_out > fixtures.date THEN tpts.points ELSE 0 END) as total
FROM user_teams
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points tpts
		ON team_selections.team_id = tpts.team_id
		LEFT JOIN fixtures
		ON tpts.fixture_id = fixtures.fixture_id
		GROUP BY user_teams.user_team_id
) AS t2
     ON total < t2.total
		GROUP BY user_teams.user_team_id

The first part joins the required tables to calculate each teams points, which it does successfully. I then thought by doing a left outer join on the same select statement again I could count the number of teams that have a total that is lower, therefore returning the rank. But it isn't working, it is returning a value of rank for each team, anything between 300 and 1200 but there are only 122 teams.

Does anybody know if what I am trying to do is possible, or am I way off track? Any help would be greatly appreciated.

March 3, 2013

Oh yeah, I couldn't get it to work originally so I was trying it out inside the loop, but you obviously only need to specify that once which I didn't realise at first! Thanks for pointing it out, I've moved it back now.

March 3, 2013

Thanks to you both for your help, got it working with the code below so thanks for pointing me in the right direction:

$apointsAry = array();
$apointsAry[] = array($id, $aid, $at_points);

$stmt = $mysqli->prepare("INSERT INTO team_points (fixture_id, team_id, points)
VALUES (?, ?, ?)");
				
foreach ($apointsAry as $values) {
$stmt->bind_param("iii", $id, $aid, $at_points);
$id = $values[0];
$aid = $values[1];
$at_points = $values[2];
$stmt->execute();
}
$stmt->close();

March 3, 2013

I am currently going through the process of converting all mysql code to mysqli and having some trouble with inserting an array from a prepared statement. This is how it used to work:

$apointsAry = array();
$apointsAry[] = "($id, $aid, $at_points)";

$query = "INSERT INTO team_points (fixture_id, team_id, points)
VALUES ".implode(', ', $apointsAry);
$result = mysql_query($query) or die("Query: {$query}<br>Error: ".mysql_error());

And this is what I have got so far after converting it:

$stmt = $mysqli->prepare("INSERT INTO team_points (fixture_id, team_id, points)
VALUES (?, ?, ?)");
			
$stmt->bind_param("iii", $id, $aid, $at_points);
$stmt->execute();
$stmt->close();

Obviously the above code only enters one row. I have searched around but can't find how to incorporate an array with bind_param or is it not possible? Any help would be greatly appreciated.

December 12, 2012

Hi, I hadn't correctly named the fields so I guess they were all overwriting each other like you said, i had labelled them like this

<input name="home_pens[$i]"

and as I was using php to echo the form i needed to concatenate the $i variable, if that makes sense, like this:

<input name="home_pens['.$i.']"

It now works, thanks for all your help, you've been very patient with me!! Next thing is to work out how to ignore rows that have no scores entered for them

December 12, 2012

Thanks, the code now executes but it is only inserting the very last row into the table, do you know how I can insert every row?

December 11, 2012

Thank you for your advice regarding manually creating indexes, i will implement this into my code. Sorry for not providing you with more information sooner. I guess I was trying to just post what i thought was causing the problem so there wasn't loads of code to go through.

This is the code I am using on the page to either show the form or if submit button has been pressed, call the submitResults function:

<?php
if (isset($_POST['submit_results'])){

if (submitResults($_POST['fixture_id'], $_POST['home_score'], $_POST['away_score'], $_POST['aet'], $_POST['home_pens'], $_POST['away_pens'], $_POST['postponed'])){

echo 'Confirmation Message';

}else {

show_enter_results();
}

} else {
// has not pressed the submit button
show_enter_results();
}
echo $query;
?>

The function below is used to show the form, the form successfully displays any football fixtures that have been played but not had results entered yet.

function show_enter_results()
{
$self= htmlspecialchars($_SERVER['PHP_SELF']);
echo '<form action="'.$self.'" method="post">
<table>
<tr>
<td>Date</td>
<td>Comp</td>
<td align="right">Home Team</td>
<td>P</td>
<td></td>
<td></td>
<td></td>
<td>P</td>
<td>AET</td>
<td>Away Team</td>
<td>Postponed?</td>
</tr>';

$query="SELECT home.team_id AS home_id, home.team_name AS home_team, away.team_id AS away_id, away.team_name AS away_team, fixtures.fixture_id, fixtures.date, competitions.short_name
FROM fixtures
LEFT JOIN teams home
ON fixtures.ht_id = home.team_id
LEFT JOIN teams away
ON fixtures.at_id = away.team_id
LEFT JOIN results
ON fixtures.fixture_id = results.fixture_id
LEFT JOIN competitions
ON fixtures.comp_id = competitions.comp_id
WHERE results.fixture_id IS NULL AND fixtures.date <= CONVERT_TZ(NOW(), '-08:00', '+00:00')
GROUP BY fixtures.fixture_id
ORDER BY DATE(fixtures.date) ASC, competitions.comp_id ASC, fixtures.date ASC, home_team ASC";
$numresults = mysql_query($query);
$numrows = mysql_num_rows($numresults);
if ($numrows == 0) {
echo "<p class='normal'>All results are up to date.</p>";
}
$result = mysql_query($query) or die(mysql_error());
$i = 0;
while($row = mysql_fetch_assoc($result)) {
$search = array(' & ', ' ');
$replace = array('-and-', '-');

echo '<tr>
<input name="fixture_id[$i]" value="'.$row['fixture_id'].'" type="hidden" />
<td>'.date("d/m/y H:i", strtotime($row['date'])).'</td>
<td>'.$row['short_name'].'</td>
 <td align="right">';
 if ($row['home_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['home_team'])."' title='".$row['home_team']."Team Data'>".$row['home_team']."</a>";
 else echo $row['home_team'];
 echo '</td>
<td><input name="home_pens[$i]" type="checkbox" value="'.$row['home_id'].'" /></td>
<td><input name="home_score[$i]" type="text" size="2" maxlength="2" /></td>
<td>-</td>
<td><input name="away_score[$i]" type="text" size="2" maxlength="2" /></td>
<td><input name="away_pens[$i]" type="checkbox" value="'.$row['away_id'].'" /></td>
<td><input name="aet[$i]" type="checkbox" value="1" /></td>
<td>';
 if ($row['away_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['away_team'])."' title='".$row['away_team']."Team Data'>".$row['away_team']."</a>";
 else echo $row['away_team'];
 echo '</td>
<td><input name="postponed[$i]" type="checkbox" value="1" /></td>';
$i++;
}
echo '</table>
<input name="submit_results" type="submit" value="Submit" />
</form>';


}

If I haven't properly explained how I am creating the variables from the POST data, or where are I am creating the variables that are sent to the function then I apologise. I get a bit lost with the technical terms used as I am still relatively new to PHP.

December 11, 2012

Each row in the form as below:

echo '<tr>
	 <input name="fixture_id[]" value="'.$row['fixture_id'].'" type="hidden" />
	 <td>'.date("d/m/y H:i", strtotime($row['date'])).'</td>
	 <td>'.$row['short_name'].'</td>
	 <td align="right">';
if ($row['home_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['home_team'])."' title='".$row['home_team']."Team Data'>".$row['home_team']."</a>";
else echo $row['home_team'];
echo '</td>
	 <td><input name="home_pens[]" type="checkbox" value="'.$row['home_id'].'" /></td>
	 <td><input name="home_score[]" type="text" size="2" maxlength="2" /></td>
	 <td>-</td>
	 <td><input name="away_score[]" type="text" size="2" maxlength="2" /></td>
	 <td><input name="away_pens[]" type="checkbox" value="'.$row['away_id'].'" /></td>
	 <td><input name="aet[]" type="checkbox" value="1" /></td>
	 <td>';
if ($row['away_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['away_team'])."' title='".$row['away_team']."Team Data'>".$row['away_team']."</a>";
else echo $row['away_team'];
echo '</td>
	 <td><input name="postponed[]" type="checkbox" value="1" /></td>';

December 10, 2012

echo $query; doesn't output anything different than the error message I was getting before

December 10, 2012

have u tried removing the $ sign from the session variable in sessioncheck.php

so instead of echo $_Session['$test']; use echo $_Session['test'];

December 10, 2012

Hi, this is the first time I have used arrays in php. I have searched around many other posts but can't seem to get my head around it.

I am trying to process multiple arrays entered on a form. The number of entries could be different each time.

This is the code I have so far:

<?php
function submitResults($fixture_id, $ht_goals, $at_goals, $et, $h_pens, $a_pens, $postponed)
{
//Put group selections into arry in MySQL INSERT value format
$valuesAry = array();
foreach(array_keys($fixture_id) as $n)
{
$valuesAry[] = "($fixture_id[$n], $ht_goals[$n], $at_goals[$n], $et[$n], $h_pens[$n], $postponed[$n])";
}
$query = "INSERT INTO test_results (fixture_id, ht_goals, at_goals, et, pens, postponed)
VALUES ".implode(', ', $valuesAry);
$result = mysql_query($query) or die("Query: {$query}<br>Error: ".mysql_error());
return $result;
}
?>

It currently displays an error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , ), (109, , , , , ), (162, , , , , ), (316, , , , , ), (563, , , , , ), (6' at line 2

Any help anyone can give me would be greatly appreciated.

September 6, 2012

Ok thanks for your help, I have created FOREIGN KEYS on the two columns you suggested and it now seems to be loading the page faster

September 5, 2012

Sorry, could you explain what that means please? is that what's causing it to load slowly?

September 2, 2012

I get this for the first query:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE user_teams ALL NULL NULL NULL NULL 115 Using temporary; Using filesort

1 SIMPLE users eq_ref PRIMARY PRIMARY 3 vouche7_tff2012.user_teams.user_id 1

1 SIMPLE team_selections ref user_team_id user_team_id 3 vouche7_tff2012.user_teams.user_team_id 14

1 SIMPLE team_points ALL NULL NULL NULL NULL 462

1 SIMPLE payments ALL NULL NULL NULL NULL 29

And this for the second:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE user_teams ALL NULL NULL NULL NULL 115 Using temporary; Using filesort

1 SIMPLE users eq_ref PRIMARY PRIMARY 3 vouche7_tff2012.user_teams.user_id 1 Using index

1 SIMPLE team_selections ref user_team_id user_team_id 3 vouche7_tff2012.user_teams.user_team_id 14

1 SIMPLE team_points ALL NULL NULL NULL NULL 462

1 SIMPLE payments ALL NULL NULL NULL NULL 29

August 27, 2012

I am trying to display the change in rank for each team on the leaderboard, the first query selects the required information for the leaderboard. The second query calculates the position each team was in 7 days ago so that I can display the change in rank. The code I posted does work it just takes a long time to load the page so I am looking for a better way to get the same result.

August 23, 2012

Thanks, my first thought was to put it all in one join statement. But I order by total points to determine the rank so how can I determine the rank of the total points from 7 days ago in the same statement?

August 23, 2012

Sorry I tried to minimise the amount of code I posted but missed some vital parts out, here is the full code:

$query = "SELECT users.user_id, user_teams.user_team_id, user_team_name, first_name, last_name, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date 
		AND team_selections.transfer_out > team_points.date THEN team_points.points ELSE 0 END),0) as total, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < '2012-08-15' THEN team_points.points ELSE 0 END),0) as previous_total, txnid
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY total DESC, user_team_name ASC";
		$result = mysql_query($query) or die(mysql_error());
		?>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr class="tables-header">
                <td>Pos</td>
                <td>Change</td>
                <td>Team Name</td>
                <td>Chairman</td>
                <td>Points</td>
                <td>Paid</td>
              </tr>
            <?php
		$counter = 1;
		while($row = mysql_fetch_assoc($result))
        	{
		if ($row['user_id'] == $_SESSION['user_id']) {
			$link = '../my-account/teams';
			$class = 'tables-myteams'; }
		else {
			$link = '../stats/user-team-data';
			$class = 'tables-teams'; }
		if ($row['txnid'] == "") {
			$paid = 'No'; }
		else {
			$paid = 'Yes'; }

		$user_team_id = $row['user_team_id'];
		$query1 = "SELECT user_teams.user_team_id, user_teams.user_team_name, user_teams.date, COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < DATE_ADD(NOW(), INTERVAL -7 DAY) THEN team_points.points ELSE 0 END),0) as previous_total
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY previous_total DESC, user_teams.date ASC";
		$result1 = mysql_query($query1) or die(mysql_error());

		$change_counter = 1;

		$rank = 1; // rank pointer


			$userID=$user_team_id;

					while($row1 = mysql_fetch_assoc($result1)) 
					{
			// check for a match
			if($userID==$row1['user_team_id']){
			$rank=$change_counter; 
			}
			$change_counter++; //increase the value of the counter with 1 
			} // end of loop
			$change = $rank - $counter;

		?>
            <a name="<?php echo $row['user_team_id']; ?>" id="<?php echo $row['user_team_id']; ?>"></a>
              <tr class="<?php echo $class; ?>">
                <td><?php echo $counter; ?></td>
                <td><span class="<?php echo $mov_img; ?>"></span><?php echo $change; ?></td>
                <td><a href='<?php echo $link; ?>/<?php echo $row['user_team_id']; ?>' title='<?php echo $row['user_team_name']; ?> Team Data'><?php echo $row['user_team_name']; ?></a></td>
                <td><?php echo $row['first_name']." ".$row['last_name']; ?></td>
                <td><?php echo $row['total']; ?></td>
                <td><?php echo $paid; ?></td>
              </tr>
              <?php
		  $counter++; //increase the value of the counter with 1
		  }
		  ?>
            </table>

So, the first query selects all the parts needed to fill in the table and orders by the total points in descending order. The second query is within the while statement of the first to calculate the points each team had 7 days before today. This is so I can display how each team's rank has changed from 7 days ago. Ideally I would like to show each team's rank change since the last MySQL update if this is possible.

The query works in acheiving what I am trying to do but I am looking for a better solution if there is one as this way is very slow in loading.

August 22, 2012

Hi, I have a leaderboard which ranks each team in descending order of points. I want to get the rank change from an earlier time, I have some code that currently works but it is taking a long time to load the page. Is there a better way to apply the code below? I currently have it set to check the rank against 7 days ago but ideally would like it to show the rank change since the last table update.

$query = "SELECT users.user_id, user_teams.user_team_id, user_team_name, first_name, last_name, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date 
		AND team_selections.transfer_out > team_points.date THEN team_points.points ELSE 0 END),0) as total, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < '2012-08-15' THEN team_points.points ELSE 0 END),0) as previous_total, txnid
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY total DESC, user_team_name ASC";
		$result = mysql_query($query) or die(mysql_error());

		$counter = 1;
$user_team_id = $row['user_team_id'];
		$query1 = "SELECT user_teams.user_team_id, user_teams.user_team_name, user_teams.date, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < DATE_ADD(NOW(), INTERVAL -7 DAY) THEN team_points.points ELSE 0 END),0) as previous_total
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY previous_total DESC, user_teams.date ASC";
		$result1 = mysql_query($query1) or die(mysql_error());

		$change_counter = 1;

		$rank = 1; // rank pointer


			$userID=$user_team_id;

					while($row1 = mysql_fetch_assoc($result1)) 
					{
			// check for a match
			if($userID==$row1['user_team_id']){
			$rank=$change_counter; 
			}
			$change_counter++; //increase the value of the counter with 1 
			} // end of loop
			$change = $rank - $counter;

July 28, 2012

I'm really sorry Barand. I didn't read your first reply properly. DATE(fixtures.date) was exactly what I was looking for, it's now working. I didn't expect it to be that simple especially after reading up on SQL date/time functions. There are so many I didn't really know where to start!!

Thanks for your help... again!

July 28, 2012

Do you know a way that I can make it work?

I've converted $date to $date = date('Y-m-d', strtotime($row['date']));

then I though something like this in the WHERE statement to convert fixtures.date into yyyy-mm-dd, WHERE CONVERT(CHAR(10),fixtures.date,120) = '$date' but can't seem to get it working

July 28, 2012

Thanks Barand, $date is defined by $row['date'] which is from the fixtures table where the row is set as datetime format, it is set in this format so that it includes the date of the fixture and the kick off time. As not all fixtures kick off at the same time I need to find a way to show all fixtures from any day that there are games scheduled.

July 27, 2012

$date is selected from fixtures.date column and could contain

2012-07-27 15:00:00

so i want to select all fixtures that are on 27 July, ie:

2012-07-27 15:00:00

2012-07-27 18:00:00

2012-07-27 21:00:00

and not just the ones that are exactly equal to 2012-07-27 15:00:00 as some fixtures have different kick off times, if that makes sense?

July 27, 2012

Does anybody know how I can select the day, month and year from the variable $date which is stored as datetime. I am trying to display all fixtures for each day that there are fixtures for. I am doing it like this as some fixtures have the same date but different kick off times.

So, I was thinking something like WHERE Month(fixtures.date) = '$date' AND Day(fixtures.date) = '$date' but it's not returning any results. Here is my code:

$query1="SELECT home.team_name AS home_team, away.team_name AS away_team, competitions.comp_name
			FROM fixtures
			LEFT JOIN teams home
			ON fixtures.ht_id = home.team_id
			LEFT JOIN teams away
			ON fixtures.at_id = away.team_id
			LEFT JOIN competitions
			ON fixtures.comp_id = competitions.comp_id
			WHERE Month(fixtures.date) = '$date' AND Day(fixtures.date) = '$date'
			GROUP BY fixtures.fixture_id
			ORDER BY fixtures.date ASC";

July 22, 2012

Thanks Barand, done that now and it all works great, cheers for you help

Sign In

lukep11a

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by lukep11a

SELECT rank

how to SELECT rank

how to insert insert array

how to insert insert array

how to insert insert array

Help Inserting Multiple Arrays

Help Inserting Multiple Arrays

Help Inserting Multiple Arrays

Help Inserting Multiple Arrays

Help Inserting Multiple Arrays

Session Variables Not Passing

Help Inserting Multiple Arrays

Rank change since last update

Rank change since last update

Rank change since last update

Rank change since last update

Rank change since last update

Rank change since last update

Rank change since last update

WHERE day, month, year from datetime column

WHERE day, month, year from datetime column

WHERE day, month, year from datetime column

WHERE day, month, year from datetime column

WHERE day, month, year from datetime column

League Standings Table

Browse

Activity

Important Information