mysql left join repeating rows

I am working on a database of dictionary with 4 tables designed like

words
wordid | lemma

senses
wordid | synsetid

synsets
synsetid | definition

samples
synsetid | sample

开发者_StackOverflow社区

So I use query below to get all sense definitions and samples

if(isset($searchterm)){ 

            echo "<b>".$searchterm."</b><hr>"; 

            // QUERY TO FIND SENSES 
            $senses_query="SELECT   
                words.lemma,words.wordid,  
                senses.wordid,senses.synsetid, 
                synsets.synsetid, synsets.definition, 
                samples.synsetid, samples.sample 
            FROM 
                words  
            LEFT JOIN 
                senses ON words.wordid=senses.wordid 
            LEFT JOIN 
                synsets ON senses.synsetid=synsets.synsetid 
            LEFT JOIN 
                samples ON senses.synsetid=samples.synsetid 
            WHERE 
                words.lemma REGEXP '^$searchterm$'"; 

            $senses_query_result = mysql_query($senses_query) OR die("sense alamadım.<br>".mysql_error()); 
            $num=mysql_num_rows($senses_query_result); 


            while($sensesFound = mysql_fetch_array($senses_query_result )){ 

                echo "&nbsp; "     . $sensesFound['lemma'] .    ""; 
                echo "&nbsp; "     . $sensesFound['definition'] . ""; 
                echo "&nbsp; "     . $sensesFound['sample'] .     ""; 
                echo "<br>"; 

            } 

    }  

The problem is: if there are more than one sample of a definition it repeats like this.

definition 1 sample 1
definition 1 sample 2
definition 2 sample 1
definition 2 sample 2
definition 2 sample 3
e.t.c

I like to have rows like

definition 1 sample 1 sample 2
definition 2 sample 1 sample 2 sample 2

I tried group_concat on select but it concats all samples of all definitions into single.

Is it possible with mysql query or should I use php to handle this kind of work.

Thanks in advance

---

You can use GROUP_CONCAT and then in php use explode on the column to split up the individual samples.

Also, with GROUP_CONCAT make sure to use a delimiter not used in the sample text.

---

You want all samples (in one row) for every definition.

Therefore, use GROUP_CONCAT(samples.sample) and also GROUP BY senses.wordid, senses.synsetid:

(I guess senses.wordid, senses.synsetid is the primary key of table senses)

        $senses_query="

        SELECT   
            words.lemma, words.wordid,  
            senses.wordid, senses.synsetid, 
            synsets.synsetid, synsets.definition, 
            COUNT(samples.synsetid) AS numberOfSamples
            GROUP_CONCAT(samples.sample SEPARATOR ', ') AS samples
        FROM 
            words  
        LEFT JOIN 
            senses ON words.wordid=senses.wordid 
        LEFT JOIN 
            synsets ON senses.synsetid=synsets.synsetid 
        LEFT JOIN 
            samples ON senses.synsetid=samples.synsetid 
        WHERE 
            words.lemma REGEXP '^$searchterm$'

        GROUP BY senses.wordid, senses.synsetid
        "; 

---

No, you cannot get the way you want from a single query, you have to use GROUP_CONCAT to fetch all the rows (repeating in the join query) as single row.

Use GROUP_CONCAT separated by , and like @Dan said, you can explode (",", $result) to get it in an array.

For ex:

SELECT GROUP_CONCAT(samples.sample SEPERATOR ',' ) .....

The above query will give the results like

definition 1 sample-1,sample-2
definition 2 sample-1,sample-2,sample-3
.....