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