在php中分组函数的结果

Question

Hi there I have a query result that gives me 2 elements starting the same but ending diferently, this way:

• btdeucat_de
• btdeucat_ca

By means of a php function I can eliminate the difference, but the echo still gives me 2 elements, how can I group them to form just one element:

• btdeucat

(I have tried GROUP BY at the query but that does not help because at the db names are different)

Here's the code I have:

<?php
$result0 = mysqli_query($dbiac, "SELECT * FROM corpus_info WHERE corpus IN (SELECT corpus FROM corpus_alignments)") or die(mysqli_error($dbiac));
while($cpsblg = mysqli_fetch_array($result0)){

// eliminate the difference and echo it to the page
$cpsblg = preg_replace('"_(de|en|ca|es|fr|it|pt)$"', '', $cpsblg['corpus']);
echo $cpsblg."<br />";
?>

Results in:

• btdeucat
• btdeucat

Answer 1

Can't you just do this?

SELECT c.corpus_initial
FROM (SELECT SUBSTRING_INDEX(corpus, '_', 1) AS corpus_initial
      FROM corpus_info 
      WHERE corpus IN (SELECT corpus FROM corpus_alignments)) c
GROUP BY c.corpus_initial