Populating Dropdown from DB - concatenated fields in Option Text

Further to this thread: http://chronoengine.com/forums/posts/f2/t94320/preventing-an-email-from-being-sent-if-a-value-is-not-found.html I am attempting to reconstruct a form which contains a dropdown.

<?php
$db =& JFactory::getDBO();
$query = 'SELECT * FROM breeds  ORDER BY species, breed ';
$db->setQuery( $query );
$breeds = $db->loadAssocList();

foreach($breeds as $option) {
	echo '<option value="'.$option['breed'].'" >'.$option['species'].': '.
		$option['breed'].'</option>';
}
?>

I have followed the instructions here and have set up the dropdown so that it can import the 'breed' field from a database table and display the text as follows:

<select size="1" id="breed" class="" title="" container_id="0" name="breed">
<option value="Abyssinian">Abyssinian</option>
<option value="American Bobtail">American Bobtail</option>
<option value="American Curl">American Curl</option>
...

The original form displays the text as follows:

<select size="1" id="breed" class="" title="" container_id="0" name="breed">
<option value="Abyssinian">Cat: Abyssinian</option>
<option value="American Bobtail">Cat: American Bobtail</option>
<option value="American Curl">Cat: American Curl</option>
...

So basically I need to configure the Dropdown configuration so that, under the 'Dynamic Data' tab I place the concatenated result of the 'species' and 'breed' fields separated by a colon.

How do I achieve this?

Many thanks,

Dave

Is there no way of achieving this?

Hi Dave,

I think you can do it in the MySQL query:

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT `breed` as value, CONCAT(`species`, ': ', `breed`) AS text
    FROM `breeds`
    ORDER BY `species`, `breed` ";
$db->setQuery( $query );
$form->data['breeds'] = $db->loadAssocList();
?>

should let you use 'breeds', 'value' and 'text' in the Dynamic Data settings.

Bob

Hi Bob,

I did try that but the problem is that if I edit the code by hand I get a WhiteScreen.

Hi Dave,

A white screen is usually a sign of a PHP Error, if you temporarily set Site Error Reporting to Maximum you can usually see the error report and then fix the bug.

Bob

PS I don't see any obvious errors in the code I posted but it wasn't tested to there may well be the odd typo.

Sorry, Bob, I think we may be at cross-purposes.

Putting breed in the 'value' and 'text' boxes in the Dynamic Data settings sets the Dropdown box as I indicated, namely with both the 'Value' and the "Text' portions of the dropdown holding the 'breed' text.

That's not what I want. The 'Value' portion should hold the breed, but the 'Text' portion should hold the concatenated result of the 'Species' and the 'Breed' separated by a colon. So that the user may see that the dropdown contains "Cat: Abbysinian", while the 'Value' contains 'Abysinian', or "Dog: Afghan Hound" with the corresponding 'Value' being 'Afghan Hound'.

It should be noted that I'm merely trying to replicate the behaviour of the Dropdown in the previous form as I have to construct the form from scratch in order to insert the hidden input type "send_email" to allow the email to be aborted as per the other thread.

Putting code to directly query the database under the 'Code' tab of the form is what is generating the whitescreen.

Hi Dave,

I think that is what my query does.

Bob

Hi Bob,

I've tracked down the cause of the whitescreen.

I've created a simple form with a textbox and a hidden input type.

The textbox is named "email" and the hidden input is "send_email".

I have the Email[GH] with condition: {send_email}::1 and To: {email}

If the Hidden Input "Field default value" box is set to 1, the whitescreen occurs. If I remove the value the debugger reports

Debug Data
Email info
Conditional Email cancelled: send_email was not 1

Placing

<?php
$form->data['send_email']=1;
?>

in the Email[GH] configuration box under the "Template" tab has no impact. The debugger reports that "send_email was not 1". There is no indication of what error is causing the whitescreen. Joomla seems happy that no error has occurred.

Hi Dave,

Please see this FAQ to see if we can see exactly where the error is arising.

Bob

Ah, OK.

Error is

Fatal error: Call to undefined function checkdnsrr() in C:\Inetpub\vhosts\royalcaninbreedersclub.co.za\httpdocs\administrator\components\com_chronoforms\form_actions\email_gh\email_gh.php on line 420

Hi Dave,

It should be available in all PHP releases from 5.3.0 on (earlier if you aren't running on Windows). See the PHP manual here

What version of PHP do you have installed?

Bob

PHP 5.2.17.

:(

Unchecking the "Get Submitter's IP" doesn't help.

What is the checkdnsrr() needed for anyway?

Hi Bob,

The link you gave me to the php documentation contained a code section in the comments as follows:

<?php
function myCheckDNSRR($hostName, $recType = '') 
{ 
 if(!empty($hostName)) { 
   if( $recType == '' ) $recType = "MX"; 
   exec("nslookup -type=$recType $hostName", $result); 
   // check each line to find the one that starts with the host 
   // name. If it exists then the function succeeded. 
   foreach ($result as $line) { 
     if(eregi("^$hostName",$line)) { 
       return true; 
     } 
   } 
   // otherwise there was no mail handler for the domain 
   return false; 
 } 
 return false; 
} ?>

I added this to the email_gh.php file, replaced checkdnsrr with myCheckDNSRR and it works.

Now to see if the client can be convinced that the three weeks I've spent on this is not a barrier to giving me more work.

😟

Dave

Hi Dave,

Probably a PHP Error that needs to be debugged. Please see this FAQ

Bob