lukep11a

Thanks, I have tried it with and without the backticks and still get the same result..

The code below is part of some validation code that I have, it is supposed to count the number of entries in the table 'test_selections' where 'userid' equals the user that is logged in and where the 'transfer_in' date is a date within the current month. I am getting an error saying "Error running query Unknown column '10' in 'where clause' ". Does anybody know why this could be? I am hoping that it is something to do with the snippet of code below so I don't need to post the whole lot!! Any help would be much appreciated. $current_month = date("m"); $query = "SELECT COUNT(`userid`) as `transfers_count` FROM `test_selections` WHERE `userid` = '{$_SESSION['userid']}' AND Month(`transfer_in`) = `$current_month` GROUP BY `userid`";

<form name="form2" action="test-transfer-submit.php" method="get"> <table width="550" border="0" cellpadding="0" cellspacing="0"> <tr class="title"> <td width="50"> </td> <td width="50">id</td> <td width="250">Team Name</td> <td width="100">Total Points</td> <td width="100">last 5 games</td> </tr> <?php $query = "SELECT team, sum(points1.points) as total_points, points2.last_five FROM test_teams LEFT JOIN test_team_points AS points1 ON test_teams.teamid = points1.teamid LEFT JOIN (SELECT teamid, SUM(points) as last_five FROM test_team_points JOIN (SELECT t1.teamid, t1.points, t1.date, COUNT(t2.teamid) AS rank FROM test_team_points t1 LEFT JOIN test_team_points t2 ON t1.teamid = t2.teamid AND t1.date < t2.date GROUP BY t1.teamid, t1.date HAVING rank < 5) AS dt USING (teamid, date) GROUP BY teamid) as points2 ON test_teams.teamid = points2.teamid GROUP BY test_teams.teamid"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { $team = $row['team']; ?> <tr class="teams"> <td width="50"><input type="radio" name="<?php echo $row['teamid']; ?>" value="<?php echo $row['teamid']; ?>"></td> <td width="50"><?php echo $row['teamid']; ?></td> <td width="250"><?php echo "<a href='data/teams.php?team=$team' title='$team Team Data' />"; echo $team; echo "</a>"; ?></td> <td width="100"><?php echo $row['total_points']; ?></td> <td width="100"><?php echo $row['last_five']; ?></td> </tr> <?php } ?> </table> <input type="submit" value="Submit Transfers"> </form>

Thankyou for your time and help, I have applied the code but it is coming up with an error 'Column 'points' in field list is ambiguous', do you know why this could be?

Hi, sorry sometimes I find it hard to explain what I am trying to do, but yes mjdamato that is exactly what I am after. You put it in much better words than I did, if you could point me in the right direction that would be much appreciated.

thanks for that, will have a look into it!

Thanks for your reply, but I am trying to get the overall total (the total number of points of every entry for each team in test_team_points table) in one column (which I already have, variable set as total) and in the other column I want to place the total of the last 5 entries in the test_team_points table.

Hi, I am trying to get two SUM's into one query, one to show the overall total, and the other to show the total of the 5 most recent. Is it possible to put it into one query? I have tried but so far can't seem to get it to work, below I have the code that currently displays only the overall total, in the last column is where I am trying to put the total from the last 5 games. Any help would be very much appreciated. Thanks <table width="500" border="0" cellpadding="0" cellspacing="0"> <tr class="title"> <td width="250">Team Name</td> <td width="100">Total Points</td> <td width="100">last 5 games</td> </tr> <?php $query = "SELECT test_teams.team, SUM(test_team_points.points) AS total FROM test_teams, test_team_points WHERE test_teams.selectiongroup = '$group' AND test_teams.teamid = test_team_points.teamid GROUP BY test_teams.teamid ORDER BY test_teams.teamid ASC"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { ?> <tr class="teams"> <td width="250"><?php echo $row['team']; ?></td> <td width="100"><?php echo $row['total']; ?></td> <td width="100"></td> </tr> <?php } ?> </table>

This is the full set of code for the original query: <table width="635" border="0"> <?php $query = "SELECT tr.competition, tr.date, tr.htp, tr.hts, tr.ats, tr.atp, tr.et, tr.htpts, tr.atpts, tth.team as hometeam, tta.team as awayteam FROM test_selections ts LEFT JOIN (test_results tr, test_teams tth, test_teams tta) ON (tth.teamid = tr.hometeam AND tta.teamid = tr.awayteam) WHERE ts.userid = '{$_SESSION['userid']}' AND (tr.hometeam = ts.teamid OR tr.awayteam = ts.teamid) GROUP BY tr.resultid ORDER BY tr.resultid DESC"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { $team = $row['team']; ?> <tr> <td width="85" class="fixtures_date"><?php echo $row['date']; ?></td> <td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td> <td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['htp']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['hts']; ?></td> <td width="25" class="fixtures_center">-</td> <td width="25" class="fixtures_center"><?php echo $row['ats']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['atp']; ?></td> <td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['et']; ?></td> <td width="50" class="fixtures_center"><?php echo $row['htpts']; ?></td> <td width="50" class="fixtures_center"><?php echo $row['atpts']; ?></td> </tr> <?php } ?> </table>

I am using the selected columns later on, just didnt think I need to show it all but actually, I'm not sure how to call the array, this is the code I had before that doesn't seem to be working with the new code: <tr> <td width="85" class="fixtures_date"><?php echo $row['date']; ?></td> <td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td> <td width="135" class="fixtures_home_teams"><?php echo "<a href='data/teams.php?team=$team' title='$team Team Data' />"; echo $team; echo "</a>"; ?></td> <td width="25" class="fixtures_center"><?php echo $row['htp']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['hts']; ?></td> <td width="25" class="fixtures_center">-</td> <td width="25" class="fixtures_center"><?php echo $row['ats']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['atp']; ?></td> <td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td> <td width="25" class="fixtures_center"><?php echo $row['et']; ?></td> <td width="50" class="fixtures_center"><?php echo $row['htpts']; ?></td> <td width="50" class="fixtures_center"><?php echo $row['atpts']; ?></td> </tr>

Hi, I am trying to specify a new variable that points to two columns from a table, I think I need to use an array but not sure how to code it. The reason I want one variable instead of two is because it will be used as a dynamic link for home teams and away teams to link to the team page, hope that makes sense. Here is the code I currently have: <?php $query = "SELECT tr.competition, tr.date, tr.htp, tr.hts, tr.ats, tr.atp, tr.et, tr.htpts, tr.atpts, tth.team as hometeam, tta.team as awayteam FROM test_selections ts LEFT JOIN (test_results tr, test_teams tth, test_teams tta) ON (tth.teamid = tr.hometeam AND tta.teamid = tr.awayteam) WHERE ts.userid = '{$_SESSION['userid']}' AND (tr.hometeam = ts.teamid OR tr.awayteam = ts.teamid) GROUP BY tr.resultid ORDER BY tr.resultid DESC"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { $team = array($row['hometeam'], $row['awayteam']); ?> Any help would be very much appreciated.

Thankyou both for your help, it now works, went with gizmola's in the end because the other one was coming up with an error sayin query is empty! Thankyou both anyway though, really appreciate your help. Yes ideally though it would be one query but I'm just happy it's working. The reason I wasn't using teamid to begin with is because the page this code was going in was a link on each team name from the overall team table so had to use a $_GET on the team name, if that makes sense.

I got this from what you said but something is not quite right, it's not giving me any errors now but the result is 0 when it should be 9... <?php $hometotal = "SELECT SUM(test_results.hts) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.hometeam"; $result = mysql_query($hometotal) or die(mysql_error()); $row = mysql_fetch_assoc($result); $awaytotal = "SELECT SUM(test_results.ats) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.awayteam"; $result = mysql_query($awaytotal) or die(mysql_error()); $row = mysql_fetch_assoc($result); $total = $hometotal + $awaytotal; ?> <td width="100"><?php echo $total; ?></td>

But I need to get the SUM of both SUM(test_results.hts) and SUM(test_results.ats)

Hi, I am trying to get the sum of two queries and echo the result, both $hometotal and $awaytotal work on their own but I don't know how to put them together or whether it can be put into one query. I have tried a few different variations but keep getting SQL syntax errors. Does anybody know how I can write this code? Thanks in advance <?php $hometotal = "SELECT SUM(test_results.hts) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.hometeam"; $awaytotal = "SELECT SUM(test_results.ats) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.awayteam"; $total = $hometotal + $awaytotal; $result = mysql_query($total) or die(mysql_error()); $row = mysql_fetch_assoc($result); echo $total; ?>

Thanks for that, it explains joins really well. I am still struggling with mine though, because I am trying to join the test_fixtures table which contains 2 columns that need to be referenced to the test_teams table?

Hi, I have a fantasy football website, and on a user account page I want to display fixtures that are coming up that include teams that the current user has chosen. My test_teams table stores all the team names and their teamid. The test_selections table is where each users team selections are stored, it has two columns, userid and teamid. The test_fixtures table has two columns, hometeam and awayteam, these two cloumns hold the teamid of the teams that are playing. The code below correctly displays the fixtures that contain any of the current users team selections. However, it is only displaying the teamid of the teams that are playing as they have not been matched to the test_teams table to get the team name. Does anybody now how I can do this? I believe it can be done using a left join but so far I just keep getting errors when i try to write the code. Any help would be very much appreciated. <table width="380" border="0"> <?php $query = "SELECT test_fixtures.competition, test_fixtures.date, test_fixtures.hometeam, test_fixtures.awayteam FROM test_fixtures, test_selections WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam)"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { ?> <tr> <td width="85" class="fixtures_date"><?php echo $row['date']; ?></td> <td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td> <td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td> <td width="25" class="fixtures_center">v</td> <td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td> </tr> <?php } ?> </table>

I am completely for working things out for myself, It is definitely the best way to learn, however the code that you have provided me is so different to the one I originally had that I am not sure where to start. I mean, for a start what is tth.team? i get that tf.competition is team_fixtures.competition but where does tth come from?

thankyou for your reply, that doesn't seem to be working correctly though, it is displaying all the selected teams with every possible combination of opponents, here is some sample data if it helps: test_fixtures competition hometeamawayteamdate PL 32040770 CH 373240771 CH 412640771 CH 304340771 CH 213140771 CH 362540771 CH 342340771 CH 403840771 test_teams teamidteamselectiongroup 21Birmingham 4 22Blackpool 4 23West Ham 4 24Cardiff 4 25Reading 4 26Nott'm Forest 4 27Leeds 4 28Burnley 4 29Millwall 5 test_selections userid teamid 11 15 114 123 130 143 146 159

ok thankyou all for your help

Hi, I have three tables, one called 'test_selections' which store each registers users team selections, one called 'test_teams' which stores all teams that can possibly be selected, and one called 'test_fixtures' which stores games to be played. The code below selects all fixtures involving teams that the user who is currently logged has selected. The 'test_fixtures' table doesn't contain the actual team names it only holds the teamid (in test_fixtures.hometeam and test_fixtures.awayteam) so I link to 'test_teams' to get the team name (which is stored in test_teams.team). The only problem with this code is that on each output loop, the same team name is being displayed twice rather than the home team once and the away team once. I realise this is because <?php echo $row['team']; ?> is in there twice but I am thinking that maybe I am doing this the wrong way. Does anyone know how I can fix this or am I better off just putting the team names straight into the mysql table rather than the team id? I hope this makes sense. <table width="380" border="0"> <?php $query = "SELECT test_fixtures.competition, test_fixtures.date, test_teams.team FROM test_selections, test_fixtures, test_teams WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam) AND (test_selections.teamid = test_teams.teamid)"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { ?> <tr> <td width="85" class="fixtures_date"><?php echo $row['date']; ?></td> <td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td> <td width="135" class="fixtures_home_teams"><?php echo $row['team']; ?></td> <td width="25" class="fixtures_center">v</td> <td width="135" class="fixtures_away_teams"><?php echo $row['team']; ?></td> </tr> <?php } ?> </table>

would I have to redo that every time a new user registers though?

i am in the UK

thanks for your reply, do you know how I can apply it to my code? sorry I am still relatively new to php

Hi, the date column of my login table is updated with the current time when a user registers, can anyone tell me how I can change the query below so that login.date is converted to GMT+0100? <?php $query = "SELECT test_teams.team, test_teams.selectiongroup, SUM(test_team_points.points) AS total FROM test_teams, test_team_points, login, test_selections WHERE login.userid = '{$_SESSION['userid']}' AND login.userid = test_selections.userid AND test_selections.teamid = test_teams.teamid AND test_teams.teamid = test_team_points.teamid AND login.date < test_team_points.date GROUP BY test_teams.teamid ORDER BY test_teams.selectiongroup"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { ?>