Forums

Need step by step to display data from a second database

Permalink
I have already added my second database to the /config/database.php.

What I am unsure about is where/how to get the data into a page. Do I need a controller and single page? I've not done this in version 8 yet.

Can someone please give me a short step by step on how to do this please?
 
ConcreteOwl replied on at Permalink Reply
ConcreteOwl
I would ask this question in the concrete5 slack page at:
http://concrete5.slack.com/
GrizzlyAdams replied on at Permalink Reply
I've been trying but their slack page seems to be down. Any suggestions?
hutman replied on at Permalink Reply
hutman
Assuming that you added your new database and credentials into the database.php like in the documentation https://documentation.concrete5.org/developers/database-management/c...

You should be able to use either a Single Page Controller or a Block Controller to get your database information with something like this

$db = \Database::connection('CONNECTION_NAME');
$results = $db->executeQuery('SELECT * FROM table_name');

Query documentation is here https://documentation.concrete5.org/developers/appendix/concrete5-ve...
GrizzlyAdams replied on at Permalink Reply
Thanks for the reply Hutman! I am still piecing this together. Can I add the code you suggested to the formify_form/controller? Can I add it to an existing function there?

Also, the STATE dropdown was being populated from the default U.S. States option group. I changed that to "Specify Manually" and added a field class of "dynamic_state"
hutman replied on at Permalink Reply
hutman
Technically yes, you can add it there, but if you do you will need to make sure if you ever upgrade Formify that you redo the changes as they will get wiped out on upgrade.
GrizzlyAdams replied on at Permalink Reply
Ok, I'm not too worried as I probably won't upgrade for a while. So I can add the database connection to "public function view() {" and the data will be accessible from the view correct?
hutman replied on at Permalink Reply
hutman
You will need to get whatever data you want and then do a 

$this->set('variableName', $variableName);

And then it will be available in the view.php
GrizzlyAdams replied on at Permalink Reply
So I added this to the end of the View function..
$db = \Database::connection('esco_web_connection');
        $results = $db->executeQuery('SELECT * FROM Products_Premiums');
        $this->set('dynamicState', $dynamicState);


And I get this error on the page now.

SQLSTATE[HY000] [2019] Can't initialize character set utf8mb4_unicode_ci (path: /usr/share/mysql/charsets/)


Here is my DB connection..

'esco_web_connection' => [
         'driver'   => 'c5_pdo_mysql',
         'server'   => 'localhost',
         'database' => 'DATABASE',
         'username' => 'USERNAME',
         'password' => 'PASSWORD',
         'charset'  => 'utf8mb4_unicode_ci',
      ],


I am assuming it has something to do with the "Type" in the database? It is currently "InnoDB".
GrizzlyAdams replied on at Permalink Reply
I see that the collation should be utf8mb4_unicode_ci but it's currently "latin1_swedish_ci".

I'll change that and see....
GrizzlyAdams replied on at Permalink Reply
Ok, got that figured out but still no data in view.

I have this in the Formify controller...

$db = \Database::connection('my_web_connection');
$results = $db->executeQuery('SELECT * FROM Products_Premiums');
$this->set('dynamicState', $dynamicState);

And this in the view...

<?php echo $dynamicState ?>

I should be seeing all of the contents of that table. But I see nothing :(
hutman replied on at Permalink Reply
hutman
Do you have a variable $dynamicState in your code? I see a variable $results that isn't used but no definition of $dynamicState so that's going to be null.
GrizzlyAdams replied on at Permalink Reply
Good point, I NEED MORE COFFEE!!!!

Now I have

$db = \Database::connection('my_web_connection');
$dynamicState= $db->executeQuery('SELECT * FROM Products_Premiums');
$this->set('dynamicState', $dynamicState);

In the view I get the following error...

Concrete\Core\Database\Driver\PDOStatement could not be converted to string

Thank you so much for helping on this!
hutman replied on at Permalink Reply
hutman
You are trying to echo an array.
GrizzlyAdams replied on at Permalink Reply
Hi Hutman, circling back to this again.

I have modified my query to grab just the columns I need. (code below) I can see the array when doing var_dump in the view. Could you show how to separate and echo the values for use in the form?

In controller...
$db = \Database::connection('esco_web_connection');
        $quoteData = $db->executeQuery('SELECT wpp_MfrName, wpp_Model, wpp_StateDefaultAnnual, wpp_StateDefaultMonthly FROM Products_Premiums');
        $this->set('quoteData', $quoteData);
hutman replied on at Permalink Reply
hutman
Not with the information that has been provided.

Can you post a link to the form you are working with and also the output of this query (just a few rows should be enough)?

I don't have a clue how any of this data goes together so giving you code to make it work is impossible.
GrizzlyAdams replied on at Permalink Reply
Hi Hutman, I have got my form populating the dropdowns correctly now. I have taken my Quote form and Enrollment form into external forms now. This is in my Quote form controller...
function action_mfr_data(){
      $db = \Database::connection('esco_web_connection');
      $quoteData = $db->GetAll('SELECT DISTINCT wpp_MfrName FROM Products_Premiums ORDER BY wpp_MfrName');
      echo '<option value="">Select Manufacturer</option>';
      foreach($quoteData as $data){
         echo '<option value="'.$data['wpp_MfrName'].'">'.$data['wpp_MfrName'].'</option>';
      }
      die();
   }
   function action_mdl_data(){
      $db = \Database::connection('esco_web_connection');
      $quoteData = $db->GetAll('SELECT DISTINCT wpp_Model FROM Products_Premiums where wpp_MfrName= ?  ORDER BY wpp_Model', array($_POST['manufacture']));
      echo '<option value="">Select Model</option>';
      foreach($quoteData as $data){
         echo '<option value="'.$data['wpp_Model'].'">'.$data['wpp_Model'].'</option>';
Viewing 15 lines of 27 lines. View entire code block.


And this is in my Quote From view...
<form autocomplete="off" class="formify-form with-style" id="formify-form-1-562" data-bid="562" data-fid="1" data-rid="0" data-context="" enctype="multipart/form-data" method="post" action="/index.php/formify/go/1">
  <input type="hidden" name="rID" value="">
  <input type="hidden" name="token" value="">
  <input type="hidden" name="source" value="/">
  <input type="hidden" name="referrer" value="">
  <input type="hidden" name="timestamp" value="02dbf90f5f">
  <span style="display:block;height:0;width:0;overflow:hidden"><input type="text" name="02dbf90f5f" tabindex="-1" autocomplete="new-password"></span>
  <input type="hidden" name="ccm_token" value="1570129836:9df90112b57cb59d9333b7f21ac19bad">     
  <div class="formify-section" data-formify-section-index="1">
    <div class="formify-field-container select_state" id="formify-field-container-1" data-ffid="1" data-field-type="select" data-rule-count="0" data-unmet-rule-count="0" data-rule-action="" data-rule-requirement="">
      <div class="formify-field-label">
        <label>
          Select State <span style="color:#ff0000">*</span>     
          <div class="formify-field-description"></div>
        </label>
Viewing 15 lines of 175 lines. View entire code block.


Instead of submitting the form, I need it to work like an "add to cart". After selecting State, Manufacturer, Model it needs to pull "wpp_StateDefaultAnnual" and "wpp_StateDefaultMonthly" for that model/manufacturer and display onscreen.
I am unable however to get the numbers to show on screen.

If the user likes the quote and wishes to proceed, they would click "Enroll now". In doing that, I need to carry the options selected over to the Enrollment form (External form on another page) and populate the same fields.
GrizzlyAdams replied on at Permalink Reply
Hi Hutman, I had someone else take a crack at this and they vanished after getting it partially done. Would this be something you could look at for me?