creating dropdowns in cakephp using the data returned by using mysql query

Question

app/Controller/Actionscontroller.php

<?php
App::uses('AppController', 'Controller');

class ActionsController extends AppController {
  public Function index(){
    App::import('Model', 'ConnectionManager');
    $con = new ConnectionManager;
    $cn = $con->getDataSource('default');

    $tablequery="SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='cakephp_db' AND TABLE_NAME != 'report'";
    $this->set('table',$cn->query($tablequery));
  }
}
?>

app/View/Actions/index.ctp

<fieldset>
  <table>
    <tr><th><td>OR</td></th></tr>
    <tr>
      <th>CHOOSE EXISTING DATA</th>
      <td>
      <?php  echo $this->Form->create('table',array('type' => 'get'));
            echo $this->Form->select('table ',  $table,['empty' => 'choose one']);?>          
      <?php  echo $this->Form->end('submit'); ?>
  </td>
    </tr>
    </table>
  </fieldset>

app/model/action.php

<?php
App::uses('AppModel', 'Model');

class Action extends AppModel {
  public $validate = array(
        'table'=> array(
          'required' => array(
            'rule'=> 'notBlank',
            'message' => 'atleast select one table'
          )
        )
      );
}
?>

I need the result in this format. a sample output i have written

<select name="field">
    <option value="">(choose one)</option>
    <option value="0">1</option>
    <option value="1">2</option>
    <option value="2">3</option>
    <option value="3">4</option>
    <option value="4">5</option>
</select>

But i am getting the output in this format;By running this code.Help me to solve the issue.

<select name="table " id="tableTable">
<option value="">choose one</option>
</optgroup>
<option value="TABLE_NAME">Employee</option>
<optgroup label="TABLES">
<optgroup label="1">
</optgroup>
<option value="TABLE_NAME">House_Price</option>
<optgroup label="TABLES">
</optgroup>
<optgroup label="2">
</optgroup>
<option value="TABLE_NAME">insurence</option>
<optgroup label="TABLES">
</optgroup>
<optgroup label="3">
</optgroup>
<option value="TABLE_NAME">posts</option>
<optgroup label="TABLES">
</optgroup>
<optgroup label="4">
</optgroup>
<option value="TABLE_NAME">topics</option>
<optgroup label="TABLES">
</optgroup>
<optgroup label="5">
</optgroup>
<option value="TABLE_NAME">users</option>
<optgroup label="TABLES">
</optgroup>
<optgroup label="6">
</optgroup>
<option value="TABLE_NAME">visualizations</option>
<optgroup label="TABLES">
</optgroup>
</select>

Why using a raw query in your code. Use find('list') instead book.cakephp.org/2.0/en/models/… — Aman Rawat
– Aman Rawat, Commented Apr 18, 2017 at 5:50
here I am fetching the content from Information schema not from the table.so i have written query. — Manasa
– Manasa, Commented Apr 18, 2017 at 6:06

Aman Rawat · Accepted Answer · 2017-04-18 06:25:40Z

1

If you have gone througn the doc of cakephp

https://book.cakephp.org/2.0/en/core-libraries/helpers/form.html#FormHelper::select

If you give structure like this

$options = array(
   'Group 1' => array(
      'Value 1' => 'Label 1',
      'Value 2' => 'Label 2'
   ),
   'Group 2' => array(
      'Value 3' => 'Label 3'
   )
);
echo $this->Form->select('field', $options);

Output will be:

<select name="data[User][field]" id="UserField">
    <optgroup label="Group 1">
        <option value="Value 1">Label 1</option>
        <option value="Value 2">Label 2</option>
    </optgroup>
    <optgroup label="Group 2">
        <option value="Value 3">Label 3</option>
    </optgroup>
</select>

That's what in your case is happening .

You have to change the structure of the response you are getting like this

echo $this->Form->select('field', array(
    'Value 1' => 'Label 1',
    'Value 2' => 'Label 2',
    'Value 3' => 'Label 3'
));
Output:

<select name="data[User][field]" id="UserField">
    <option value=""></option>
    <option value="Value 1">Label 1</option>
    <option value="Value 2">Label 2</option>
    <option value="Value 3">Label 3</option>
</select>

So you have to make changes to your code as bellow

    $tablequery="SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='cakephp_db' AND 
    TABLE_NAME != 'report'";
    $rows = $cn->query($tablequery)->fetchAll('assoc');

    $dataToSend = [];
    foreach ($rows as $row) {
        $dataToSend[$row['TABLE_NAME']] = $row['TABLE_NAME'];
    }

    $this->set('table',$dataToSend);

edited Apr 18, 2017 at 6:25

answered Apr 18, 2017 at 6:13

Aman Rawat

2,6251 gold badge27 silver badges40 bronze badges

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

4 Comments

Manasa Over a year ago

on excecuting the above mentioned code it results in internal error says -Call to a member function fetchAll() on array

Aman Rawat Over a year ago

remove the fetchAll() . It's already in array. It's cakephp 3.x syntax ;)

Manasa Over a year ago

If i remove the fetchAll(assoc) it will results in undefined index : TABLE_NAME

Manasa Over a year ago

I made the changes as you have said.It works for me.Thank you

Collectives™ on Stack Overflow

creating dropdowns in cakephp using the data returned by using mysql query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related