How to fetch values from database table and output array for use in Formify form in Concrete5 5.8

Question

I don't have a lot of experience with arrays that are to be populated from a database.

In my controller I have

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

And in my view I have...

foreach($dynamicStates as $dynamicState){
  echo $dynamicState . "<br>";
}

But, all I get is ....

Array
Array
Array
Array
etc....

I am at a loss here. I need all the data to setup conditional data in form dropdowns. But, I can't get any data to display.

When I print_r I see...

Array ( [wpp_UID] => 738452 [wpp_UpdateDate] => 2019-09-16 [wpp_ProductNumber] => 2-48 [wpp_MfrNumber] => 2 [wpp_MfrName] => Widex Hearing Aid Company [wpp_Model] => Inteo- ITC [wpp_Model2] => [wpp_State] => [wpp_PricingName] => Protection Plus [wpp_PricingStyle] => ITC [wpp_StateDefaultAnnual] => 201 [wpp_StateDefaultMonthly] => 18 [wpp_Channel] => Earserv ) Array ( [wpp_UID] => 738453 [wpp_UpdateDate] => 2019-09-16 [wpp_ProductNumber] => 2-47 [wpp_MfrNumber] => 2 [wpp_MfrName] => Widex Hearing Aid Company [wpp_Model] => Inteo- CIC [wpp_Model2] => [wpp_State] => [wpp_PricingName] => Protection Plus [wpp_PricingStyle] => CIC [wpp_StateDefaultAnnual] => 223 [wpp_StateDefaultMonthly] => 20 [wpp_Channel] => Earserv ) Array ( [wpp_UID] => 738454 [wpp_UpdateDate] => 2019-09-16 [wpp_ProductNumber] => 2-50 [wpp_MfrNumber] => 2 [wpp_MfrName] =>

Your are printing an array with echo. Try to use `print_r` to see the structure of the array, then `echo` the array indexes you want. — fonini, Sep 25 '19 at 18:02
I did that and I can see all of the info. Will each row from the table be a new array then? Could you show me an example please? — Aaron Marple, Sep 25 '19 at 18:07
Try instead `echo` do `var_dump`, and ye, see the structure, if there is another array, than you need do another foreach, or how fonini says, array indexes you want. Show there, what you see, after var_dump; — Arturs, Sep 25 '19 at 18:12
Updated your question. `$dynamicStates` is an multidimensional array (matrix). Inside the foreach, the `$dynamicState` is an array, when every table column becomes an array index. — fonini, Sep 25 '19 at 18:12
Use, for example, `echo $dynamicStates['wpp_UID'];` to display individual values within the array. — Dave, Sep 25 '19 at 18:25
I guess I don't really have to "echo" all the data, I just wasn't able to get anything to show. Now I know it's there. So, what I need for this to accomplish is as follows, I have 3 select inputs in my form "State", "Manufacturer" and "Model". So if the user selects Arizona, then populate the 2nd SELECT input with all Manufacturers in that state, etc. — Aaron Marple, Sep 25 '19 at 18:31

Rafeeq Mohamed · Answer 1 · 2019-09-25T20:59:50.703

Try this

    <?php 
    $db = \Database::connection('esco_web_connection');
    $dynamicState = $db->executeQuery('SELECT * FROM Products_Premiums');
    $this->set('dynamicState', $dynamicState);
    ?>
    <html>
        <head>
            <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
        </head>
    <body>
    <label for="state">State</label>
    <select name="state" id="state">
        <option value="" selected="selected">Please Select</option>
      <?
        foreach($dynamicState as $dynamicStates) { ?>
        <option value="<?= $dynamicStates['wpp_State'] ?>"><?= $dynamicStates['wpp_State'] ?></option>
      <?
        } ?>
    </select> 

    <label for="manufacturer">Manufacturer</label>
    <select name="manufacturer" id="manufacturer">
        <option value="" selected="selected">Please Select</option>
    </select>

    <script type="text/javascript">
        $(document).ready(function () {
            $('#state').change(function() {
                var sel_state = $(this).val();
                .ajax({
                    type: "POST",
                    url: "fetch_mfrName.php",
                    data: 'state=' + sel_state,
                    success: function(result-array) {
var result = JSON.parse(result-array);
                        result.each(function(){
                            $('#manufacturer').append('<option value="'+result['wpp_MfrName']+'">'+result['wpp_MfrName']+'</option>');
                        });
                        $('#manufacturer').change(function() {
                            ////...next ajax if you need
                        });
                    } 
                });
            }); 
        }); 
    </script>
    </body>
    </html>

Create a php file, file name as 'fetch_mfrName.php'

<?php    
include 'connect_to_db.php';
    $state = mysql_real_escape_string($_POST['state']);
    $query = "SELECT `wpp_MfrName` FROM `Products_Premiums` WHERE `wpp_State`='$state'";
    $mfr_names = mysql_query($query);

    echo mysql_fetch_assoc($mfr_names);
?>

I have to rework it now. Will it work in PHP 7.1? Just found out that the business rules changed for this. So the new flow is as follows... All states shown by default, not from the database. Wondering if I should let Formify U.S. States option group populate it or hardcode them? I think the logic you supplied would still work for populating the "Manufacturer" dropdown on change of "State". Then selecting a "Manufacturer" it would display their "Models" in the 3rd dropdown. On "Submit", I need it to do the math using other DB values and display the TOTAL on page. — Aaron Marple, Sep 26 '19 at 18:05

How to fetch values from database table and output array for use in Formify form in Concrete5 5.8

1 Answers1