lukep11a

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?

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.

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.

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

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.

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

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?

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.

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

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

have u tried removing the $ sign from the session variable in sessioncheck.php so instead of echo $_Session['$test']; use echo $_Session['test'];

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.

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

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

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

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.

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?

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.

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;

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!

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

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.

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

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

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