Retrieve values from 2 tables with minimal queries

[sully02]

I am trying to figure out how do a query where I would use one of thevalues from one query to access a value in another table with as littlequeries as possible. Just to make things simple, I'll use actualtable/field names.

I plan to run a query that fetches all values from the table'lmd_draftpicks' The main variable is pickid, which is the number ofthe draft pick involved. In that table I've got a variable namedteamid, which is the # correlating to an NFL team.

The first way that popped into my head is to assign the value of theteamid to a random variable such as x, then run another query to fetchthe name of the team where teamid = x.

That results in 2 queries per draft pick. Is there a way to do this with 1 query per pick, or so less overall queries are used?

Finally, to see if I'm doing this right, once I have the team nameselected, could I then replace the teamid variable in the first querywith the teamname?

(I'm sure some of this is simple to you guys, but I am still trying to learn PHP/MySQL).

Thanks in advance.

[sabret00the]

can't you just use a left join?

[Marco van Herwaarden]

PHP Code:

"SELECT draftpicks.*, teams.*
FROM " . TABLE_PREFIX . "draftpicks AS draftpicks
LEFT JOIN " . TABLE_PREFIX . "teams AS teams ON (teams.teamid = draftpicks.teamid)
WHERE draftpicks.pickid = $selecteddraft" 


Something like that?

[sully02]

So left join basically allows you to append a value to the query from another table using the value from the initial query? Do I understand this right?

[Marco van Herwaarden]

It lets you join (read from 2 tables which share something in common) the columns from 2 tables.

[sully02]

OK, so if I understand this right, I'd use a query like this:

SELECT *
FROM lmd_draftpicks AS draftpicks
LEFT JOIN lmd_teams AS teams ON (teams.teamid = draftpicks.teamid)
WHERE draftpicks.pickid = $selecteddraft
LEFT JOIN lmd_players AS players on (players.playerid = draftpicks.playerid)
WHERE draftpicks.playerid = $selected draft

Notes:
1 - I want to select all values from the draftpicks table
2 - The tables aren't the same prefix as my vBulletin tables, thus I don't plan to use the TABLE_PREFIX option
3 - Eventually I will be adding another table that has a list of prospects, so I'd need to use LEFT JOIN for 2 separate tables
4 - I will use the teamid field to look up the teamname field and put that in on my template
5 - I will use the playerid field to look up the playername and school fields to put those on my template

Did I do this right?

[Marco van Herwaarden]

Originally Posted by sully02

1 - I want to select all values from the draftpicks table
4 - I will use the teamid field to look up the teamname field and put that in on my template
5 - I will use the playerid field to look up the playername and school fields to put those on my template

So you don't want to select only the columns from the draftpicks table, but also want the playername and teamname.

The WHERE clause you only add once on the end.

I added a $selectedpick field as an example of how to select all info for just 1 draft. This will first (with the WHERE clause) select all rows from your primary table (draftpicks) with pickid = $selectedpick, then it will find all corresponding rows from both the teams and the players tables, based on the selection following the "ON" in their respective LEFT JOIN clauses.

You query would look like this:
[sql]SELECT draftpicks.*, teams.teamname, players.playername
FROM lmd_draftpicks AS draftpicks
LEFT JOIN lmd_teams AS teams ON (teams.teamid = draftpicks.teamid)
LEFT JOIN lmd_players AS players on (players.playerid = draftpicks.playerid)
WHERE draftpicks.pickid = $selectedpick[/sql]

[sully02]

I think that should work out good. The final result will look something like this:

http://sports-boards.net/forums/2004.php

This file (From last year) is saved as one giant HTML template.

For this year, I'm putting it in the database. The teamid wouldreference the team name and post it on there, then the playerid wouldreference the player's position, name, and school.

As I examine this further, I dont know how complex I want to makeit,but I suppose it wouldn't hurt to have a table for positions andschools, or would that make the query too large. I want to make it easyto get the information, but at the same time, I don't want a querythat'll be a page long, you know?

[sully02]

Now that I've made a little progress on it, I can give more insight to perfect the query I have:

Here is the list of prospects I have. They're sorted in ascending order by playerid.

http://sports-boards.net/draft/prospects.php?styleid=7

For this query, I want to use the given playerid tofindtheplayer'sname,position, and school (playername,position,schoolfieldsrespectively) andappend them to the array. Here'sthecurrentquery Ihave:

$picks=$DB_site->query("
	SELECT draftpicks.*, teams.teamname, players.*
	FROM lmd_draftpicks AS draftpicks
	LEFT JOIN lmd_teams AS teams ON (teams.teamid =	draftpicks.teamid)
	LEFT JOIN lmd_players AS players ON (players.playerid = draftpicks.playerid)
	WHERE draftpicks.pickid = $selectedpick
	ORDER BY pickid ASC");

Is that right? And finally, when I get to the template,howwouldIaccessthe player information? The variable I have to fetchthedatais$info,but the variable given to me in the querybyMarcois$selectedpick. Dothose variables have to be the same name?

I'm going to assume that since there are no conflictingfieldnames,Icanput the default field names in thetemplate($info[school],forexample).

*Crosses fingers, hoping he's doing this right*

(On a side note, I have to give a thumbs down to the"Automergeddoublepost" feature. I'd rather it show up as a new replybecauseotherwise the post gets ignored, and it is messing up thespacing onthe already existing post, but I digress...)

[sully02]

Originally Posted by sully02

Now that I've made a little progress on it, I can give more insight to perfect the query I have:

Here is the list of prospects I have. They're sorted in ascending order by playerid.

http://sports-boards.net/draft/prospects.php?styleid=7

For this query, I want to use the given playerid tofindtheplayer'sname,position, and school (playername,position,schoolfieldsrespectively) andappend them to the array. Here'sthecurrentquery Ihave:

$picks=$DB_site->query("
	SELECT draftpicks.*, teams.teamname, players.*
	FROM lmd_draftpicks AS draftpicks
	LEFT JOIN lmd_teams AS teams ON (teams.teamid =	draftpicks.teamid)
	LEFT JOIN lmd_players AS players ON (players.playerid = draftpicks.playerid)
	WHERE draftpicks.pickid = $selectedpick
	ORDER BY pickid ASC");

Is that right? And finally, when I get to the template,howwouldIaccessthe player information? The variable I have to fetchthedatais$info,but the variable given to me in the querybyMarcois$selectedpick. Dothose variables have to be the same name?

I'm going to assume that since there are no conflictingfieldnames,Icanput the default field names in thetemplate($info[school],forexample).

*Crosses fingers, hoping he's doing this right*

(On a side note, I have to give a thumbs down to the"Automergeddoublepost" feature. I'd rather it show up as a new replybecauseotherwise the post gets ignored, and it is messing up thespacing onthe already existing post, but I digress...)

A little update: I got the latter query to work without the where, which allows for the displaying of the entire draft:

http://sports-boards.net/draft/draft.php

Now I need to make it so you can view an individual round (draft.php?r=1, for example)

Would the WHERE statement at the end then be "WHERE draftpicks.roundid = r.roundid" ?