Jim R Posted October 2, 2018 Share Posted 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... Link to comment Share on other sites More sharing options...
requinix Posted October 2, 2018 Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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'; Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2018 Share Posted 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 Link to comment Share on other sites More sharing options...
requinix Posted October 2, 2018 Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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; Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2018 Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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; Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2018 Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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; Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2018 Share Posted 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" Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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'; Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2018 Share Posted October 2, 2018 Well, are you going to enlighten us about the outcome? Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted October 2, 2018 Still no output. Link to comment Share on other sites More sharing options...
requinix Posted October 2, 2018 Share Posted October 2, 2018 Does that mean an error message or that you don't see any results? Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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. Link to comment Share on other sites More sharing options...
kicken Posted October 2, 2018 Share Posted 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. Link to comment Share on other sites More sharing options...
Jim R Posted October 2, 2018 Author Share Posted 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. Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2018 Share Posted October 2, 2018 try SELECT * FROM fallLeague_coaches as c left join fallLeague2018 as p ON p.team = c.team AND p.confirm = 1 Link to comment Share on other sites More sharing options...
Jim R Posted October 3, 2018 Author Share Posted October 3, 2018 It looks like the rows are starting to show. Link to comment Share on other sites More sharing options...
Jim R Posted October 3, 2018 Author Share Posted 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.