[SOLVED] Trying to get the output right, but I'm so confused.

[harlequeen]

Hi

 

I have 2 tables called teams and contacts. 

In contacts are the following fields:

teamName

telnum

division

privatenum

 

and in teams:

teamName

team_id

Points

totalPoints

gamesWon

 

When asking to echo all the teams in the database, I get what I expect, 61 teams, but when I'm trying to limit it to only those teams in division 1, I get the complete list 15 times.

 

I am trying to get to the 15 teams in division 1 to print to screen and am doing it in the following manner.

 

"SELECT teams.teamName, teams.team_id, teams.Points, teams.totalPoints FROM contacts, teams WHERE contacts.division = 1";

 

Part of my problem is I'm trying to teach myself this, so I don't really know where I'm going wrong.

 

Would someone be kind enough to help me out. I update a darts website weekly and have been doing it manually for years.  My circumstances have changed and I had hoped that I would be able to devise a way of adding the scores for the teams into a form and update the website in that way.  Although, at this rate it won't be ready before the season starts and I'll have to manually do it in Excel and upload everything like that.

 

Please, any help would be appreciated

[ldougherty]

I'm not sure why you'd be trying to pull from both tables if all the information you want is in table #1

 

In contacts are the following fields:

teamName 

telnum 

division 

privatenum

 

So the query would be

 

select * from contacts where division = '1'

 

This would return all the information from contacts where the division = 1

[harlequeen]

The team ID is not in table 1. That info is stored in table2.  Sorry if I didn't make myself clear.

 

Harlequeen

[abazoskib]

"SELECT t.teamName, t.team_id, t.Points, t.totalPoints FROM contacts as c, teams as t WHERE c.division = 1 AND c.teamName=t.teamName";

 

You have to tell mysql how your join is related. In your case, your join of contacts and teams is related by teamName.

[harlequeen]

Hi

 

Thanks for the help although I eventually got it to work by using a left join as below.  I tried this befor seeing your post so I can't say whether the above works or not.

 

Here is the query I used:

 

$query="SELECT teams.teamName, teams.team_id, teams.Points from teams LEFT JOIN contacts ON teams.teamName =contacts.teamName where contacts.division=1";

 

Thanks for the help.

 

Harlequeen