Codeigniter: Display multiple columns from database as one

Question

I wanted to display multiple column values from my database.

Using the query from model

$this->db->select('*');
$this->db->from('projectskillslist ps');
$this->db->join('empskillslist s', 's.skillsID = ps.skillsID', 'left');
$this->db->join('projects p', 'p.projectID = ps.projectID', 'left');
$this->db->where('ps.projectID = 1');

$query = $this->db->get();
$result = $query->result_array();
if ($query->num_rows() > 0) {
   return $result;
}
   return false;

And in my controller

$data['pSkills'] = $this->emp_model->view_projskills();

The query returns perfectly as expected:

Now in my view,

I wanted to call

Title: Project 1
SkillName: JAVA, PHP

So far, I have done this:

    foreach ($pSkills as $data) {
       echo $data['title'];
       echo $data['skillName'];
    }

And the result I am getting is

 Title: Project 1
    Skills: PHP
 Title: Project 2
    Skills: JAVA

It is a silly question indeed, I have already looked up and search for the same problem but I still no luck. I hope you can help me. Thank you so much!!

Have you checked print_r(data['pSkills']) is these return you the same result as PhpMyAdmin ? — Akshay Bajpei
– Akshay Bajpei, Commented Feb 23, 2017 at 12:27
@AkshayBajpei I have edited the result, my bad. And yes it was printing the same — blakcat7
– blakcat7, Commented Feb 23, 2017 at 12:30
@blakcat7 do you only have a formatting question, then? Because in your original question you seemed to be missing data. — Shadow
– Shadow, Commented Feb 23, 2017 at 12:38
Create one function or procedure for GetAllSkillbyProjectID and pass the projectID u wants to, ana call this function to SkillName: — Jyoti mishra
– Jyoti mishra, Commented Feb 23, 2017 at 12:40
@Shadow I guess it is formatting. Thanks for pointing that out — blakcat7
– blakcat7, Commented Feb 23, 2017 at 13:38

ourmandave · Accepted Answer · 2017-02-23 14:26:31Z

1

Try this, but first order the result set by Project ID and Skill ID.

$this->db->order_by('projectID, skillID');

view

$lastProjectID = 0;
$p_skills = '';
$p_title = '';

foreach ($pSkills as $data) {

  if ($data['projectID'] !== $lastProjectID) {

    // if there's a title, printIt()
    if ($p_title !== '') {
      printIt($p_title, $p_skills);
    }

    // set new title and skill list
    $p_title = $data['title'];
    $p_skills = $data['skillName'];

    // remember last project 
    $lastProjectID = $data['projectID'];

  } else {

    // append skill name to skills
    $p_skills .= ", " . $data['skillName'];

  }
}

// end of foreach, if there's a title, printIt()
if ($p_title !== '') {
  printIt($p_title, $p_skills);
}

function printIt($title, $skills) {
  echo "Title: $title<br>";
  echo "SkillName: $skills<br>";
}

edited Feb 23, 2017 at 14:26

answered Feb 23, 2017 at 13:07

ourmandave

1,5852 gold badges16 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

blakcat7 Over a year ago

Thank you for replying. I tried the code itself, didn't change anything it prints the Project Title, but the Skills just print out 0.

ourmandave Over a year ago

@blakcat7 My bad! Appending the skillname should be $p_skills .= ", " . $data['skillName'];. I used += so it treated it like math addition.

blakcat7 Over a year ago

Great! Worked well for now however it's printing the first skill twice. Does it have to do with appending? Project 1: Java, Java, PHP When supposedly only Java, PHP

ourmandave Over a year ago

@blakcat7 Yes, it's appending the first skill after it sets it when the $lastProjectID changes. It should have an } else { in there. I'll edit the answer.

Khan Afzal · Accepted Answer · 2017-02-23 13:51:14Z

0

Follow these step if it would can help..

Step 1: You have to concatenate projectID where GROUP_CONCAT will help.

Step 2. Perform your SQL query like below

SELECT projectID, skillName, GROUP_CONCAT(projectID) AS projid FROM projectskillslist GROUP BY projid Step 3: To display it in a view , you can use PHP's explode() and iterate over that, like this if it would help..

    foreach ($pSkills as $row) {
    echo $row->title;
    echo explode(',', $row->skillName);
    /*
        or add below line of code  if it doesn't works

    */ 
     //echo str_replace(',', '<br />', $row->skillName);
}

answered Feb 23, 2017 at 13:51

Khan Afzal

1671 silver badge8 bronze badges

Collectives™ on Stack Overflow

Codeigniter: Display multiple columns from database as one

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related