count multivalue field
4 posts by 2 authors in: Forums > CMS Builder
Last Post: September 12, 2022 (RSS)
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
<?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'] ?> <?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,
Technical Lead
interactivetools.com