Dropdown Built from mySQL count

Hello,

I have been searching the forum, FAQ and the CFv6 Manual, but I am not seeing an outline to what I am needing. I am also not the strongest coder, so I am hoping someone here can point me in the right direction.

I need a dropdown built off of another database table within the same joomla install.

Here is a brief explanation of what I am looking for:

I have a site where people login, and select whether they are available to work that day. Within the xxxx_areyouavailable there is a field entitled `available`, which is either 1 = available or 0 = not available. This field changes daily.

I need a dropdown in the ChronoForm to look at that `available` field and count how many 1's there are, but display the dropdown list as a list of numbers up to that count.

If there are 6, 1's in `available`, then the dropdown would show, 1, 2, 3, 4, 5, 6.

Is this possible?

How would I go about setting it up, or finding a tutorial on CF to do this.

Thanks,
Melvins

Hi Melvins,

I think I would probably do this with Custom Code:

First, a database query to get the total available

Then a few lines of a PHP 'for' loop to create an array with that number of entries in the form data that can be used as the option set for the drop-down.

Bob

Hi Bob,

I get the PHP "for" loop to crate an array. A quick google/stackexchange search explained that. And I have a working version (using a year), that I am sure I can hack once I figure out the mysql query.

I'm less clear on setting up the database query within Chronoform. I know query would be something like COUNT(*) FROM `xxxx_areyouavailable` WHERE `available` = 1;

But how do I put that into a Custom Code? Do I add a "Read Data" on the "Load" and put the query in that? Then how do I get that to connect to the custom code?

I hate being so obtuse, and any help is greatly appreciated.

Thanks,
Melvins

Hi Melvins,

personally I'd probably include the DB Query in the Custom Code - but you can use a DB Read with Select Type set to 'Return the count of records matching the filtering conditions.'

The result of the query will be added to the form data (a Debugger action should show you the name of the new variable).

Bob

Hi Bob,

Thank you so much for your help. I am on the cusp of getting this figured out.

I followed this FAQ (Dynamic dropdown options) and successfully got the dropdown to populate from `available` field. So far so good. (Dow the only selection I have is 2, which is the number of "1's" in the `available`, but it's progress)

I added a debugger that give me this array:

Array
(
    [read_data6] => Array
        (
            [log] => Array
                (
                    [0] => SELECT COUNT(`Data6`.`id`) AS `Data6.count` FROM `xxxx_areyouavailable` AS `Data6` WHERE `Data6`.`available ` = '1' LIMIT 100;
                )
            [var] => 2
        )
)

I have a Custom Code with a simple PHP 'for' loop set up like this (generates a list 1 through 50.):

<select>
<?php
 for ($num=1; $num<50; $num++)
{
 echo '<option>' .$num. '</option>'; 
}
?>
</select> 

How do get the array information into the PHP 'for' loop?

I have tried a wild variety of inputs like $num=$form->data[ 'read_data6' ] and $num=$form->data[ 'Data6' ] and $num={var:Data6.count} and $num={var:read_data6} and $num={var:Data6} and even $num={var:Array} Along with variations with {data: } and [var: ] or [data: ]

What am I missing?

Thanks,
Melvins

Hi Bob,

Just wanted to follow up on this. Any direction you can point me in is appreciated.

Thanks,
Melvins

Hi Bob,

I have made some changes to my form, after reading some FAQ and forum posts. But still is not quite right.

MySQL
Table: #__comprofiler
Field: cb_iamavailabletodrivetoday
Count of "1": 4 out of 7

Here is what I have
Setup Load Tab:
1) Read Data:
Name: read_data8
Designer Label: Read Database
Model name: Data8
Database table: #__comprofiler
Filtering settings -> Where conditions: cb_iamavailabletodrivetoday:1
Data settings -> Select type: Return the count of records matching the filtering conditions.
Paging for multiple results: Enabled | Page Limit: 100
Fields to retrieve: Data8.cb_iamavailabletodrivetoday

2) Display Section (default settings)

3) Debugger (default settings)

Designer Tab: (Custom HTML)

<?php
$num = ($this->data['read_data8']); 
echo 'Total number of elements in the $read_data array is - ' . count($num);
?>
<p>The variable is {var:read_data8}</p>

<select>
<?php
$i = ($this->data['read_data8']); 
for ($num=0; $num<(count($i)+1); $num++)
{
echo '<option>' .$num. '</option>'; 
}
?>
</select> 

When I load the form I get this result:

Total number of elements in the $read_data array is - 0
The variable is 4
dropdown select with only "0" as option.

I'm confused why {var:read_data8} shows the proper count results, but $this->data[ 'read_data8' ] does not.

I have tried this in CFv5, with the same setup (swapping out $this->data with $form->data AND swapping out {var:read_data8} with {read_data8}) and it works.

Any direction on how to call in the database array is appreciated.

Lastly, here is the complete debugger:

Array
(
[option] => com_chronoforms6
[cont] => manager
[chronoform] => map
[g5_files_mode] => "list"
[__utma] => 260802190.15699250.1479912015.1505925402.1505932990.12
[__utmz] => 260802190.1500413359.3.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)
[f145a32cb0e6a317448632ebd23af09a] => uhtuab9urdl28ahnq4mt33rbs7
[dff7e3cd512fe05b773d230eb32bfcf6] => mj4j62lrrvdqp9pj8cquvu3if6
[ed11542ccd3e3fff771e207f4e44782a] => c8d1qeimi97p7rl9pim0e83sv5
[joomla_remember_me_2963edd711433f781a01e071b4afe93f] => IYXeMOu9NUi0USFK.EvZZW46HTV7FtnlQ7Wi3
[SPro_ssid] => 151325966672.19.12
[_ga] => GA1.2.15699250.1479912015
[f607ee09cee5cd7ccc914dba51d03945] => 3m5nk2pac9sqkk890ffu403195
[38670f5984adf347900965a31ab9a949] => n58a2n757182kg2a04i1e8cs66
[cbtabsCB] => cbtabnav27
[69de98fcc6327aaf85b41830d362ec61] => 127ltumppcne563kmnpm2rplm6
[joomla_user_state] => logged_in
)
Array
(
[read_data8] => Array
(
[log] => Array
(
[0] => SELECT COUNT(`Data8`.`id`) AS `Data8.count` FROM `deal_comprofiler` AS `Data8` WHERE `Data8`.`cb_iamavailabletodrivetoday` = '1';
[1] => SELECT COUNT(`Data8`.`id`) AS `Data8.count` FROM `deal_comprofiler` AS `Data8` WHERE `Data8`.`cb_iamavailabletodrivetoday` = '1' LIMIT 100;
)

[var] => 4
)

)

Thank you Max!!!! This was a lifesaver.

Melvins