count multivalue field

4 posts by 2 authors in: Forums > CMS Builder
Last Post: September 12, 2022   (RSS)

By mbareara - September 11, 2022

Thank you in advance for your help!

I have two tables: cms_player and cms_matches

in cms_matches i have a multivalue  field  called players 

Now i want in my website i would create a list o player and count the number of matches played for each player. 

PLAYERS | MATCHES PLAYED 

Any suggestions about that?

Orazio

By mbareara - September 11, 2022 

       <?php 
	  // mysql query
global $TABLE_PREFIX;
$query  = "select surname as surname, name as name, presenze_player as player,
count(presenze_player) as presenze
from cms_player LEFT JOIN cms_matches ON ( cms_player.num = cms_matches.presenze_player) group by surname order by presenze desc LIMIT 20";
$result = @mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
$rows   = array();
while ($row = mysql_fetch_assoc($result)):
?>
<tr class="teams-match">
			<td class="hometeam">

			<?php echo $row['surname'] ?> &nbsp; <?php echo $row['name'] ?> </a><!-- // .badge-wrapper -->
		</td>
        <td class="result">><?php echo $row['presenze'] ?></td>
             <td class="result"></td>
                  <td class="result"></td>

		
		</tr><!-- // .teams-match -->
							</tbody>
						</table><!-- // .team-results-fixtures -->
					</div><!-- // .module-content -->
				</div><!-- // .module-border -->
			</div>
                 <?php endwhile ?>

This is the code i have implemented. Of course it is wrong because it is a multivalue field... 

By daniel - September 12, 2022

Hi Orazio,

Multi-select field values are stored separated by tabs ("\t"), so in order to join on this you'll need to use the condition "cms_matches.players LIKE CONCAT('%\t', cms_player.num, '\t%')" to match on it. I'd also change the GROUP BY to use the player num, so it does not group players that share a surname. I believe something like this should work:

$query  = "select surname as surname, name as name, presenze_player as player,
count(presenze_player) as presenze
from cms_player LEFT JOIN cms_matches ON (cms_matches.players LIKE CONCAT('%\t', cms_player.num, '\t%')) group by cms_player.num order by presenze desc LIMIT 20"

Let me know if that helps!

Thanks,

Daniel
Technical Lead
interactivetools.com