CAKEPHP Mysql query data format

Question

I have built this query in one of my controller methods.
$students = $this->Student->query("SELECT id,name FROM students WHERE id IN (SELECT student_id FROM groups_students WHERE group_id = '$id') ");

I have tested it and I know the SQL returns the correct data. In my view, it is being displayed with $this->Form->input('Student')

I simply want it to print out the names of the possible students that can be selected, which is why I am selecting the name and id...however, within my multiple select box, it is printing out both the name and the id of the student, as well as the word "students" for some reason.

I want the data to be returned from SQL in this format....

Array ( //[id] => 'displayValue',

[1] => 'displayValue1',
[2] => 'displayValue2',
[4] => 'displayValue4',
[5] => 'displayValue5',
[6] => 'displayValue6',
[3] => 'displayValue3',

)

This is how it returns data from $this->Student->find('list').

This is currently how the data is being formatted upon retrieval, according to the CakePHP documentation.

array(
(int) 0 => array(
    'students' => array(
        'id' => '2',
        'name' => 'evan teague',
        'lesson_cost' => null,
        'paid_lessons' => null,
        'notes' => '',
        'created' => '2013-11-21 13:35:17',
        'modified' => '2014-01-13 22:31:14'
    )
),
(int) 1 => array(
    'students' => array(
        'id' => '3',
        'name' => 'Andrew',
        'lesson_cost' => null,
        'paid_lessons' => null,
        'notes' => '',
        'created' => '2013-11-22 10:08:01',
        'modified' => '2014-01-13 22:22:50'
    )
),
(int) 2 => array(
    'students' => array(
        'id' => '5',
        'name' => 'evan2',
        'lesson_cost' => null,
        'paid_lessons' => null,
        'notes' => '',
        'created' => '2013-11-22 14:17:57',
        'modified' => '2013-11-22 14:17:57'
    )
)

)

Any help on this would be appreciated.

cornelb · Accepted Answer · 2014-01-18 20:05:20Z

1

If you are getting the correct data, but you have a problem displaying it, try this:

In the controller

$students = data from mysql query
$students = Hash::combine($students, '{n}.students.id', '{n}.students.name');
$this->set('students', $students);

in the view:

echo $this->Form->input('Student', array(
   'options' => $students,
));

edited Jan 18, 2014 at 20:05

answered Jan 18, 2014 at 18:45

cornelb

6,0663 gold badges22 silver badges30 bronze badges

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

3 Comments

john smith Over a year ago

See my edit above but the issue is the data format, I believe. This resulted in no change.

cornelb Over a year ago

What does the array you pasted refer to? Can you paste the $students variable? Can you add a screenshot/html paste of how the <select> looks like?

cornelb Over a year ago

I updated my answer. Added $students = Hash::combine($students, '{n}.students.id', '{n}.students.name');, which takes the data you need from the array

Anubhav · Accepted Answer · 2014-01-18 19:31:29Z

0

   $options = array(
        'conditions'=>array('GroupsStudent.group_id'=>$id)
        'joins' => array(
            array(
                'alias' => 'GroupsStudent',  
                'table' => 'groups_students',
                'type' => 'LEFT',
                'conditions' => array(
                    'GroupsStudent.student_id'='Student.id',
                ),
            )
        ),
        'fields' => array('Student.id','Student.name')
    );
   $students = $this->Student->find('list',$options);

the above query will return list of id, name pair

answered Jan 18, 2014 at 19:31

Anubhav

1,6234 gold badges18 silver badges32 bronze badges

1 Comment

john smith Over a year ago

This is returning every student from the student list, for some reason.

Collectives™ on Stack Overflow

CAKEPHP Mysql query data format

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related