Jump to content
Jim R

Inner Join help...

Recommended Posts

Posted (edited)

(please forgive I haven't switched over mysqli yet)

My two data tables:  

One has 400+ players who are on teams.  The other has about 40 coaches who coach these teams.  

I'm trying output rosters of players who play for a coach, with the coach's name heading each roster.

  • Team Name - Coach LAST NAME
    • Player 1
    • Player 2
    • Player 3
    • etc

(except I'm using a table)

I haven't assigned teams yet to the players, so I'm anticipating my output will just be a list of coaches, but I'm getting this error...

 

Quote

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home4/#####/#####/metroindybasketball.com/resources/league2018/rosters.php on line 35
 

$query = 'SELECT * FROM fallLeague2018 as p
inner join fallLeague_coaches as c

WHERE p.confirm="1" 
and p.team is not null 
and p.team = c.team

ORDER BY p.team,p.triple,p.number,p.nameLast';
$results = mysql_query($query) //or trigger_error('MySQL error: ' . mysql_error())
;

$currentTeam = '';
$currentCoach = '';

while($line = mysql_fetch_assoc($results)) {
	if($line['p.team'] != $currentTeam) {
		$currentTeam = $line['p.team'];
		$currentCoach = $line['c.team'];		
		echo '<tr><td colspan="6"><hr></td></tr>
		<tr><td colspan="6"><span class="coach">' . $currentTeam . ' - Coach' . $line['c.coachLast'] . '</td></tr>';
	}
	echo '<tr>
            <td>' . $line['p.triple'] . $line['p.number']. '</td>
			<td><b>' . $line['p.nameFirst'] . ' ' . $line['p.nameLast'] . '</b></td>
			<td><center>'. $line['p.feet'] . '\'' . $line['p.inches'] . '"</center></td>
			<td><center>'. $line['p.grade'] . '</center></td>		
			<td>'. $line['p.school'] . '</td>
			<td><b>'. $line['p.college'].'</b></td>
		</tr>';
}

	        echo '</table></div>';

 

 

Below are snapshots of the tables...

 

 

 

 

Screen Shot 2018-10-02 at 3.23.35 PM.png

Screen Shot 2018-10-02 at 3.25.23 PM.png

Edited by Jim R

Share this post


Link to post
Share on other sites

The syntax for an inner JOIN is "JOIN <table> ON <conditions>" (with or without the INNER, same thing).

That p.team = c.team you have now should go up into the ON.

Share this post


Link to post
Share on other sites

I'm still getting the same error, but I changed the query. 

 

$query = 'SELECT * FROM fallLeague2018 as p
inner join fallLeague_coaches as c

ON p.team = c.team

WHERE p.confirm="1" 
and p.team is not null 


ORDER BY p.team,p.triple,p.number,p.nameLast';

 

Share this post


Link to post
Share on other sites

However, without "TEAM" data in the player table you won't get many matches.

If you want to show coaches even though there are no matching players you need a LEFT JOIN

FROM coach c LEFT JOIN player p ON c.team = p.team

 

Share this post


Link to post
Share on other sites

If you're still getting the same error message then there's something else wrong with your query. One that would prevent it from working at all.

Post the schema for both of those tables.

Share this post


Link to post
Share on other sites

Still the same error, but here's the query...

$query = 'SELECT * FROM fallLeague2018 as p
left join fallLeague_coaches as c

ON p.team = c.team

WHERE p.confirm="1" 
and p.team is not null 


ORDER BY p.team,p.triple,p.number,p.nameLast';

$results = mysql_query($query) //or trigger_error('MySQL error: ' . mysql_error())
;

$currentTeam = '';
$currentCoach = '';

while($line = mysql_fetch_assoc($results)) {
	if($line['p.team'] != $currentTeam) {
		$currentTeam = $line['p.team'];
		$currentCoach = $line['c.team'];		
		echo '<tr><td colspan="6"><hr></td></tr>
		<tr><td colspan="6"><span class="coach">' . $currentTeam . ' - Coach' . $line['c.coachLast'] . '</td></tr>';
	}
	echo '<tr>
            <td>' . $line['p.triple'] . $line['p.number']. '</td>
			<td><b>' . $line['p.nameFirst'] . ' ' . $line['p.nameLast'] . '</b></td>
			<td><center>'. $line['p.feet'] . '\'' . $line['p.inches'] . '"</center></td>
			<td><center>'. $line['p.grade'] . '</center></td>		
			<td>'. $line['p.school'] . '</td>
			<td><b>'. $line['p.college'].'</b></td>
		</tr>';
}

	        echo '</table></div>';

 

The error is coming from the WHILE clause. 

Share this post


Link to post
Share on other sites
5 minutes ago, requinix said:

If you're still getting the same error message then there's something else wrong with your query. One that would prevent it from working at all.

Post the schema for both of those tables.

`fallLeague2018` (
 `id` int(4) NOT NULL AUTO_INCREMENT,
  `confirm` int(1) DEFAULT NULL,
   `team` varchar(3) DEFAULT NULL,
  `nameFirst` varchar(255) DEFAULT NULL,
  `nameLast` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=188 DEFAULT CHARSET=latin1;

 

`fallLeague_coaches` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `team` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `coachFirst` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `coachLast` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Share this post


Link to post
Share on other sites
18 minutes ago, Jim R said:

ORDER BY p.team,p.triple,p.number,p.nameLast';

You can only order by columns that exist.

Stop using "SELECT star" - specify the columns you want.

URGENT! Stop using mysql_ functions. These have been deprecated for years and disappeared completely in 2015 (PHP 7.0). Change to PDO functions.

Share this post


Link to post
Share on other sites

I know...I guess I was just focusing on the matching columns.  

 

CREATE TABLE `fallLeague2018` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `confirm` int(1) DEFAULT NULL,
  `paid` varchar(10) DEFAULT NULL,
  `player_coach` varchar(10) DEFAULT NULL,
  `team` varchar(3) DEFAULT NULL,
  `nameFirst` varchar(255) DEFAULT NULL,
  `nameLast` varchar(255) DEFAULT NULL,
  `adultEmail` varchar(255) DEFAULT NULL,
  `adultFirst` varchar(255) DEFAULT NULL,
  `adultLast` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `feet` int(1) DEFAULT NULL,
  `inches` int(2) DEFAULT NULL,
  `aau` varchar(255) DEFAULT NULL,
  `grade` varchar(10) DEFAULT NULL,
  `phoneMobile` varchar(14) DEFAULT NULL,
  `adultMobile` varchar(14) DEFAULT NULL,
  `video` varchar(1) DEFAULT NULL,
  `instagram` varchar(30) DEFAULT NULL,
  `twitter` varchar(30) DEFAULT NULL,
  `toggle` int(1) DEFAULT NULL,
  `number` int(3) DEFAULT NULL,
  `teamName` varchar(255) DEFAULT NULL,
  `color` varchar(11) DEFAULT NULL,
  `leagueCoach` varchar(255) DEFAULT NULL,
  `teamNumber` varchar(255) DEFAULT NULL,
  `college` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=189 DEFAULT CHARSET=latin1;

Share this post


Link to post
Share on other sites
Posted (edited)

Still no sign of "triple"

Where does your trigger_error() function put the errors? Have you tried looking at the reported errors?

 

edit: PS that table needs normalizing. team names and coach names etc should not be repeated in every record. They should be stored once in the team or coach tables.

Edited by Barand

Share this post


Link to post
Share on other sites
35 minutes ago, Barand said:

Still no sign of "triple"

I changed Toggle to Triple.  I forgot I had done that.  Triple exists now, and the boolean error went away.  Nothing is output though on the page. 

 

35 minutes ago, Barand said:

edit: PS that table needs normalizing. team names and coach names etc should not be repeated in every record. They should be stored once in the team or coach tables.

teamName, leagueCoach and teamNumber are old values I don't use anymore.  I just haven't deleted the columns as I've duplicated the schema to a new table each year. 

 

Share this post


Link to post
Share on other sites

Current Schema with triple in it:

CREATE TABLE `fallLeague2018` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `confirm` int(1) DEFAULT NULL,
  `paid` varchar(10) DEFAULT NULL,
  `player_coach` varchar(10) DEFAULT NULL,
  `team` varchar(3) DEFAULT NULL,
  `nameFirst` varchar(255) DEFAULT NULL,
  `nameLast` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `feet` int(1) DEFAULT NULL,
  `inches` int(2) DEFAULT NULL,
  `adultEmail` varchar(255) DEFAULT NULL,
  `adultFirst` varchar(255) DEFAULT NULL,
  `adultLast` varchar(255) DEFAULT NULL,
  `aau` varchar(255) DEFAULT NULL,
  `grade` varchar(10) DEFAULT NULL,
  `phoneMobile` varchar(14) DEFAULT NULL,
  `adultMobile` varchar(14) DEFAULT NULL,
  `video` varchar(1) DEFAULT NULL,
  `instagram` varchar(30) DEFAULT NULL,
  `twitter` varchar(30) DEFAULT NULL,
  `triple` int(1) DEFAULT NULL,
  `number` int(3) DEFAULT NULL,
  `color` varchar(11) DEFAULT NULL,
  `college` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=193 DEFAULT CHARSET=latin1;

Share this post


Link to post
Share on other sites

Compare your join

1 hour ago, Jim R said:

SELECT * FROM fallLeague2018 as p left join fallLeague_coaches as c ON p.team = c.team

with the one I suggested

1 hour ago, Barand said:

FROM coach c LEFT JOIN player p ON c.team = p.team

noting the positions of the table names relative to "LEFT JOIN"

Share this post


Link to post
Share on other sites
$query = 'SELECT * FROM fallLeague_coaches as c
left join fallLeague2018 as p

ON p.team = c.team

WHERE p.confirm="1" 
and p.team is not null 


ORDER BY p.team,p.triple,p.number,p.nameLast';

 

Share this post


Link to post
Share on other sites
Posted (edited)

Well, are you going to enlighten us about the outcome?

Edited by Barand

Share this post


Link to post
Share on other sites

Does that mean an error message or that you don't see any results?

Share this post


Link to post
Share on other sites

The error message went away when I changed toggle to triple.  No results are shown.  

 

As of now there are four coaches in that table. 

Share this post


Link to post
Share on other sites

If you have no matching player data then all your fields in the fallLeague2018 table will be NULL, meaning none of your WHERE conditions will match which results in no rows being returned.  Any conditions you want to enforce on the fallLeague2018 table have to be part of the join's ON clause.

Share this post


Link to post
Share on other sites

Before I opted for a separate table for the coaches, had them listed as an array to work from.  It's much easier to change a data table than peck through code to make changes each time.  However, the array version worked quite well for five years.  

 

5 minutes ago, kicken said:

If you have no matching player data then all your fields in the fallLeague2018 table will be NULL, meaning none of your WHERE conditions will match which results in no rows being returned.  Any conditions you want to enforce on the fallLeague2018 table have to be part of the join's ON clause.

I have one row of data for each coach now.  Nothing is being output.  

 

 

Share this post


Link to post
Share on other sites

try

SELECT * 
	FROM fallLeague_coaches as c
		left join 
	fallLeague2018 as p 
		ON p.team = c.team
		AND p.confirm = 1

 

Share this post


Link to post
Share on other sites

It looks like the rows are starting to show.  

Share this post


Link to post
Share on other sites

http://metroindybasketball.com/mibfl/2018-mibfl-rosters/

 

When I was working with the array instead of the a separate data table, the $currentTeam triggered the change.  I added the $currentCoach thinking I need to trigger that too.  

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.