Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : need mysql help ASAP

[mikey1090]

I have a game, and in that game players are in teams. 2 queries are not working. 1 query to show the score rankings on the whole game, and one to show people land and score for just people in your team.

I have the following tables

A table called game, storing username pass etc

a table called res, storing players land and resources

a table called score to hold their score

a table called team to hold their teamid (if they are not in a team, teamid is 1)

a table called teams to hold details about each team (leader, temname team bank etc)

I ran query to select those fields from the table, i did not get a mysql_error() from it, but its showing the wrong data

Before i added the whole team aspect to it, it worked fine.

The where clause is WHERE game.id=res.id AND res.id=score.id AND team.id=game.id AND teams.teamid=team.teamid


But it shows the incorrect teamname


can anyone help me please?

Mike

[mitchlrm]

It is fairly impossible to help without a listing of your database definitions that shows primary and foreign keys.

[zacharooni]

Put your code in tags, and put it here so we can help you. Obviously take out passwords and such.

[mikey1090]

The following are all primary keys, and are based on the users Userid (like a foreighn key in each table)

game.id
score.id
res.id
team.id

They are INT (11)

The WHERE clause of the query is intended to display user accounts order by score, and display their team name based on the teamid in the team table.

PHP Code:

$result=mysql_query("SELECT
game.id,game.username,res.id,res.mineone,res.minetwo,score.score,team.id,
team.teamid,teams.teamname,teams.teamid
WHERE game.id=res.id AND res.id=score.id AND team.id=game.id AND teams.teamid=team.teamid AND game.id=score.id AND score.id=team.id") or die(mysql_error()); 


better?

[jimpoz]

You're missing the FROM clause in your query.

PHP Code:

$result=mysql_query("SELECT
game.id,game.username,res.id,res.mineone,res.minetwo,score.score,team.id,
team.teamid,teams.teamname,teams.teamid
FROM game, res, score, team, teams
WHERE game.id=res.id AND res.id=score.id AND team.id=game.id AND teams.teamid=team.teamid AND game.id=score.id AND score.id=team.id") or die(mysql_error()); 


[mikey1090]

that wasnt the actual query, that was me writing quickly to show an example

i managed to fix it, there was some data missing from a table so it messed up which records where shown

mike