Inner Join help...

Jim R · October 2, 2018

(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...

Edited October 2, 2018 by Jim R

requinix · October 2, 2018

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.

Jim R · October 2, 2018

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';

Barand · October 2, 2018

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

requinix · October 2, 2018

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.

Jim R · October 2, 2018

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.

Jim R · October 2, 2018

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;

Barand · October 2, 2018

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.

Jim R · October 2, 2018

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;

Barand · October 2, 2018

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 October 2, 2018 by Barand

Jim R · October 2, 2018

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.

Jim R · October 2, 2018

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;

Barand · October 2, 2018

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"

Jim R · October 2, 2018

$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';

Barand · October 2, 2018

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

Edited October 2, 2018 by Barand

Jim R · October 2, 2018

Still no output.

requinix · October 2, 2018

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

Jim R · October 2, 2018

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.

kicken · October 2, 2018

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.

Jim R · October 2, 2018

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.

Barand · October 2, 2018

try

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

Jim R · October 3, 2018

It looks like the rows are starting to show.

Jim R · October 3, 2018

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.

Sign In

Inner Join help...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information