how to edit and delete each on data on group_concat() using codeigniter

Question

I need to edit or delete each data on GROUP_CONCAT(). The major issue concerning me is how to pass the id of each data.

I need if I will add cursor to those subjects on "kiswahili" or "physics", we can expect the mouse cursor over the subject will move with link of an id of that subject on edit page or delete page.

This is my subject model code:

public function set_subject_get($idSchool){
    $this->db->select('standards_name, GROUP_CONCAT(subject_name SEPARATOR " || ") as subject, 
    GROUP_CONCAT(subject_id) as subjectid');
    $this->db->from('subjects');
    $this->db->join('standards', 'standards_id = subject_standard_id');
    $this->db->where('subject_school_id', $idSchool);
    $this->db->group_by('subject_standard_id');
    $query = $this->db->get();
    return $query->result();
}

on the view page I have code like this:

if($subjects > 0){
    $n = 1;
    foreach($subjects as $data){
        ?>
        <tr>
            <th scope="row"><?php echo $n;?></th>
            <td><?php echo $data->standards_name; ?></td>
            <td><?php echo $data->subject; ?></td>
        </tr>
        <?php
        $n++;
    }
}else{
    //...

And the subject table contain those informations

please you can help me to move on this part thank you for all

Answer 1

Your code can be

$this->db->select('standards_name, GROUP_CONCAT(`subject_id`,',', subject_name ORDER BY `subject_id` SEPARATOR " || ") as subject, 

that would look like

SELECT 
GROUP_CONCAT(`subject_id`,',', `subject_name` ORDER BY `subject_id` SEPARATOR " || ")
FROM
Options
GROUP BY `subject_standard_id`

| GROUP_CONCAT(`subject_id`,',', `subject_name` ORDER BY `subject_id` SEPARATOR " || ") |
| :------------------------------------------------------------------------------------ |
| 1,kiswahili || 2,physies || 11,aas                                                    |
| 3,history || 4,english || 5,geography || 6,ada || 7,ada || 8,ada || 9,s || 10,s       |
| 17,kiswahili || 18,english || 19,history || 20,civics                                 |
| 12,as || 13,asa || 14,asa || 15,maarifa yajamii || 16,sayansi                         |

db<>fiddle here

If you want more complex, you have to loose the GROUP BY and detect the change in subject_standard_id

Another posibility you split $data->standards_name(which i provided) first by || and then by , and have so both in separate variables, so you can buold more elaborate table structure, this has also the advantage, that you have an id for every delete or edit button

Your phpcode would look like

$str = '3,history || 4,english || 5,geography || 6,ada || 7,ada || 8,ada || 9,s || 10,s ';
$combine = explode("||", $str);
foreach ($combine as $tex) {
    $id_split = explode(",", trim($tex));
    echo "<a href='www.webpage.de?id=".$id_split[0]."'>".$id_split[1]."</a></br>";
}

And the result

<a href='www.webpage.de?id=3'>history</a></br>
<a href='www.webpage.de?id=4'>english</a></br>
<a href='www.webpage.de?id=5'>geography</a></br>
<a href='www.webpage.de?id=6'>ada</a></br>
<a href='www.webpage.de?id=7'>ada</a></br>
<a href='www.webpage.de?id=8'>ada</a></br>
<a href='www.webpage.de?id=9'>s</a></br>
<a href='www.webpage.de?id=10'>s</a></br>

This is of course only example how top do it.

The final result for this answer will