How to sort same mysql table value using by codeigniter?

Question

What I want to do is using codeigniter to select from mysql and sort the value

my database records:

Criteria | Question
   A     |    Q1
   B     |    Q2
   c     |    Q3
   A     |    Q4
   A     |    Q5

What I want to display in my interface:

A
--------
Q1
Q4
Q5

B
--------
Q2

C
--------
Q3

this is how my code look like

$this->db->select('*');
$this->db->from('qm_form');
$this->db->join('criteria', 'qm_form.criteria_id = criteria.criteria_id');
$this->db->join('question', 'criteria.criteria_id = question.criteria_id');
$this->db->group_by('criteria_title');
$this->db->order_by('criteria_title','asc','question_title','asc');
$query = $this->db->get();

return $query->result();

MYSQL TABLE
question table
question_id | criteria_id | question_title

criteria_table
criteria_id | criteria_title

qm_form table
qm_id | criteria_id | qm_title

any idea how to do it?

thanks

you could have attempted it yourself first and ask if you encounter any problem, asking without trying and showing your code is no good at all..! — Sudhir Bastakoti
– Sudhir Bastakoti, Commented May 8, 2013 at 4:48
thanks for Sudhir advice, because my code is very long , I scare I paste the code at here will be more messy — Oscar
– Oscar, Commented May 8, 2013 at 4:52

William Lawn Stewart · Accepted Answer · 2013-05-08 04:51:39Z

1

You can do this by using two order by commands:

$this->db->select('*');
$this->db->from('my_table');
$this->db->order_by("Criteria", "asc");
$this->db->order_by("Question", "asc"); 
$query = $this->db->get();
$result = $query->result();

answered May 8, 2013 at 4:51

William Lawn Stewart

1,2051 gold badge12 silver badges23 bronze badges

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

Comments

GautamD31 · Accepted Answer · 2013-05-08 05:14:43Z

1

Try with group by like

$this->db->select('*');
$this->db->from('my_table');
$this->db->group_by('Question');
$this->db->order_by('Critera','asc');
$query = $this->db->get();
$result = $query->result();

Then print like

foreach($result as $element)
{
    $hash = $element['Critera'];
    $unique_array[$hash] = $element
}

edited May 8, 2013 at 5:14

answered May 8, 2013 at 4:45

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

5 Comments

Oscar Over a year ago

Hi gautam3164, thanks for reply, I had try your method, but the problem now is criteria A only show one question which is only show Q1, and the Q4 and Q5 is missing

Oscar Over a year ago

Ya, I just update my code in the post, it's still same problem after I group by

GautamD31 Over a year ago

can you print the $result using my ans..??

GautamD31 Over a year ago

hey it is coming yaar...just change 'Question' to Question....remove single quotes like this sqlfiddle.com/#!2/7f2f5/5

Oscar Over a year ago

ya, but how to I display in html like A = Q1,Q4,Q5 B = Q2 C = Q3

peterm · Accepted Answer · 2013-05-08 05:28:04Z

1

IMHO you just need the equivalent of a query

SELECT *
  FROM tablename
ORDER BY `Criteria`, `Question`

SQLFiddle

I'm not an expert in CodeIgniter but you can try

$this->db->select('*');
$this->db->from('tablename');
$this->db->order_by('Criteria ASC, Question ASC');
...

UPDATE: Based on your comments you need an equivalent of a query

SELECT `Criteria`, GROUP_CONCAT(`Question`)
  FROM tablename
GROUP BY `Criteria`
ORDER BY `Criteria`

SQLFiddle

Which probably translates to

$this->db->select('Criteria, GROUP_CONCAT(Question)');
$this->db->from('tablename');
$this->db->group_by('Criteria');
$this->db->order_by('Criteria', 'asc');
...

Assuming that your joins are correct then try

$this->db->select('criteria.criteria_title, GROUP_CONCAT(question.question_title)');
$this->db->from('qm_form');
$this->db->join('criteria', 'qm_form.criteria_id = criteria.criteria_id');
$this->db->join('question', 'criteria.criteria_id = question.criteria_id');
$this->db->group_by('criteria.criteria_title');
$this->db->order_by('criteria.criteria_title', 'asc');

edited May 8, 2013 at 5:28

answered May 8, 2013 at 4:52

peterm

93k16 gold badges157 silver badges162 bronze badges

7 Comments

Oscar Over a year ago

Thanks peterm, but what I want is only show one criteria instead of show all criteria, such as A = Q1,Q4,Q5 B = Q2 C = Q3

peterm Over a year ago

@Oscar You do that in your code that builds user interface. You can't get shown output with just select. You either need to group_concat with group by and end up with comma delimited list of questions or you need to fetch result sets for each Criteria separately.

Oscar Over a year ago

Thanks peterm, I am newbie on codeigniter, any idea how to do the group_concat in codeigniter ? thanks again

Oscar Over a year ago

Thanks, it's almost there, but in qm_form table was join with my question and criteria table, you can view my code in the post, so how to I use the GROUP_CONCAT(Question) in my code? thanks a lot

peterm Over a year ago

@Oscar In order to be able to do that I need to see real field names from both your tables. Because your current code selects *. Post your tables structures in your original question.

|

Collectives™ on Stack Overflow

How to sort same mysql table value using by codeigniter?

3 Answers 3

Comments

5 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related