I am relatively new to php but i am learning slowly. I am building a sports database website / cms, which is getting there very nicely.
I am having an issue joining 3 tables together, if i use then as separate queries they do ‘exactly’ what i want them to do, however I am at a loss as to how to join them together.
As far as i am aware i do not need to change anything in the database itself i just need to get the query correct.
Here is what i have done that works.
$get_players = mysql_query("
SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPositionID AS position,
P.PlayerNumber AS number,
P.PlayerPublish,
P.PlayerNationalityID AS nationality,
SN.SeasonID,
SN.SeasonPublish
FROM idihc_seasons S
LEFT OUTER JOIN idihc_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN idihc_seasonnames SN ON SN.SeasonID = S.SeasonID
WHERE SN.SeasonPublish = 1 and P.PlayerPublish = 1
GROUP BY id
ORDER BY number
",$connection)
or die(mysql_error());
I am now trying to join data which is held in a second column on a new table, which is linked via the id between them. I can currently get the query to display the ‘id’ which is the unique filed but it wont change it to the new value.
What i am trying to add: -
I have the ‘idihc_players’ table which has in it ‘PlayerPositionID’ (Which is already being used in the above query)
I also have ‘idihc_playersposition’ which has ‘PlayerPositionID’ and ‘PlayerPositionName’
what i wish to display is the ‘PlayerPositionName’ from the ‘id’ it finds, however i just get a blank and no data populated.
On the site only ONE season will ever be displayed, this is all controlled by the CMS, therefore a admin user will know what players are being displayed in which season as I have made that available there.
Everything that gets passed through to the website for all to view will only ever be one season and everything that connects to that season.
Hopefully I have explained myself a little better.
Question I have, does the GROUP BY really have an impact on the JOIN that I am trying to achieve? I didn’t think this would have an impact.
I have just removed GROUP BY and this had no effect on the results that I am achieving. Are you saying this is what was causing the problem when trying to join the other table?
As you can see under the heading ‘position’ I am only getting the actual ‘id’, hence why I wish to join the existing table to ‘idihc_playerpositions’ to enable me to get the actual name from idihc_PlayerPositionName’ for it to be displayed.
I will point out I do change the ‘P.PlayerPositionID AS position,’ field from the original query to ‘P.PlayerPositionID’, and amend accordingly in the third join.
no, i am not – i hadn’t got as far as your problem of joining any other tables, i was trying to understand your original query
by the way, regarding this part of your query –
FROM idihc_seasons S
LEFT OUTER JOIN idihc_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN idihc_seasonnames SN ON SN.SeasonID = S.SeasonID
WHERE SN.SeasonPublish = 1 and P.PlayerPublish = 1
the conditions in the WHERE clause negate the outer joins, so you might as well code them as INNER JOINs, they might run faster
okay, so your original query will return all publishable players in a single season
could you please now describe the additional tables, and indicate the one-to-many relationships they have with the tables in your original query
Many thanks for your reply, I am not sure what I was thinking about when I done OUTER JOINS, as you rightly point out, the OUTER JOINS are negated by the WHERE clause. DUH!!
I have since re-written the script using inner joins and this works perfectly.