Forums

Hi,

I am trying to get some data from the form before its submitted to the DB. In my form the user selects a name from a drop down menu. I want to use a custom html field to query another table and get the email address that matches the name from the form and then submit it as another field in the form. My custom html code looks like this:

<?php
$servername = "localhost";
$username = "*******";
$password = "********";
$dbname = "*********";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$buyer = $form->data['PurchaseRequest']['buyer'];

$qbmail = "SELECT `rev3_chronoforms_data_Buyers`.`buyer` 
	FROM `rev3_chronoforms_data_Buyers` 
	WHERE `rev3_chronoforms_data_Buyers`.`buyer` = $buyer";
$bmailv = $conn->query($qbmail);
while($ebmail = $bmailv->fetch_array())
$bmail = end($ebmail);

$conn->close();

echo "<input type=\"hidden\" name=\"bmail\" value=\"$bmail\">";
?>

Unfortunately this does not work. To try and figure out why, I simplified it and just tried to get the name to save to the bmail field by using this code:

<?php
$servername = "localhost";
$username = "xxxxxxx";
$password = "xxxxxxxxxx";
$dbname = "xxxxxxxxx";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$bmail = "$form->data['PurchaseRequest']['buyer']";

$conn->close();

echo "<input type=\"hidden\" name=\"bmail\" value=\"$bmail\">";
?>

In the debugger, I can see it is writting [bmail] => Array['PurchaseRequest']['buyer'] instead of the value.

Data Array:

Array
(
    [chronoform] => PurchaseRequest
    [event] => submit
    [Itemid] => 831
    [option] => com_chronoforms
    [view] => form
    [qty] => 9
    [itemn] => gjk
    [description] => bjk.
    [Vendor] => Wal-Mart
    [sup] => tom smith
    [buyer] => Barney Rubble
    [uname] => xxxx Admin
    [uemail] => admin@xxxx.ca
    [bmail] => Array['PurchaseRequest']['buyer']
    [input_submit_8] => Submit
    [xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx] => 1
    [chronoform_data] => Array
        (
            [cf_uid] => xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            [cf_created] => 2015-06-04 12:34:53
            [cf_created_by] => 42
            [cf_ipaddress] => 192.168.x.xx
            [cf_user_id] => 42
            [chronoform] => PurchaseRequest
            [event] => submit
            [Itemid] => 831
            [option] => com_chronoforms
            [view] => form
            [qty] => 9
            [itemn] => gjk
            [description] => bjk.
            [Vendor] => Wal-Mart
            [sup] => tom smith
            [buyer] => Barney Rubble
            [uname] => xxxx Admin
            [uemail] => admin@xxxx.ca
            [bmail] => Array['PurchaseRequest']['buyer']
            [input_submit_8] => Submit
            [xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx] => 1
            [cf_id] => 27
        )

    [chronoform_data_cf_id] => 27

Any help would be greatly appreciated.

Hi gamma1itman,

You have some problems with quotes here. PHP treats something in "" as a string, not as a variable that explains why this doesn't work

$bmail = "$form->data['PurchaseRequest']['buyer']";

it would need to be

$bmail = $form->data['PurchaseRequest']['buyer'];

And MySQL requires strings to be quotes (otherwise it assumes they are column names. That explains why this doesn't work

WHERE `rev3_chronoforms_data_Buyers`.`_chronoforms_data_Buyers` = $buyer";

it would need to be

WHERE `rev3_chronoforms_data_Buyers`.`buyer` = '".$buyer."' ;"

or

WHERE `rev3_chronoforms_data_Buyers`.`buyer` = '{$buyer}' ";

And SELECT . . . `buyer` WHERE `buyer` = '{$buyer}' makes no sense as you already know what the value of `buyer` is.

What do you actually need to look up?

Bob

Hi Bob,

Thanks for the response.

Here is what I am trying to do. I have two tables. The first table is a list of purchase requests, and the second table is a list of buyers. For the user to complete the form that populates the purchase request table, they must select the buyer from a drop down menu that looks up the list of buyers. In the buyer table there is a buyer name and email address. I want to get the buyers email address (from the record based on his name) written to the record so I can use the mailer to send the request to the applicable people including the buyer.

The WHERE statement buyer = buyer is to get the email field in the select part of the statement.

"SELECT `rev3_chronoforms_data_Buyers`.`buyer`
FROM `rev3_chronoforms_data_Buyers`
WHERE `rev3_chronoforms_data_Buyers`.`buyer` = $buyer";

Sorry, I managed to submit this before I was done and could not find a way to edit.

Hi Bob,

Thanks for the response.

Here is what I am trying to do. I have two tables. The first table is a list of purchase requests, and the second table is a list of buyers. For the user to complete the form that populates the purchase request table, they must select the buyer from a drop down menu that looks up the list of buyers. In the buyer table there is a buyer name and email address. I want to get the buyers email address (from the record based on his name) written to the purchase request record so I can use the mailer to send the request to the applicable people including the buyer.

The WHERE statement buyer = buyer is to get the email field in the select part of the statement.

"SELECT `rev3_chronoforms_data_Buyers`.`buyer`
FROM `rev3_chronoforms_data_Buyers`
WHERE `rev3_chronoforms_data_Buyers`.`buyer` = $buyer";

This should have read:

Hi Bob,

Thanks for the response.

Here is what I am trying to do. I have two tables. The first table is a list of purchase requests, and the second table is a list of buyers. For the user to complete the form that populates the purchase request table, they must select the buyer from a drop down menu that looks up the list of buyers. In the buyer table there is a buyer name and email address. I want to get the buyers email address (from the record based on his name) written to the record so I can use the mailer to send the request to the applicable people including the buyer.

The WHERE statement buyer = buyer is to get the email field in the select part of the statement.

"SELECT `rev3_chronoforms_data_Buyers`.`bemail` FROM `rev3_chronoforms_data_Buyers` WHERE `rev3_chronoforms_data_Buyers`.`buyer` = '".$buyer."' ";

I tried the changes you noted but still no go. To see what the result is I tried to just echo the value of $buyer and $bmail and its empty. I also echoed one that I knew is working, $username, and it shows.

Here is the code I used to test the echo

<?php
$servername = "****";
$username = "****";
$password = "****";
$dbname = "****";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$buyer = $form->data['PurchaseRequest']['buyer'];
$qbmail = "SELECT `rev3_chronoforms_data_Buyers`.`bemail` FROM `rev3_chronoforms_data_Buyers` WHERE `rev3_chronoforms_data_Buyers`.`buyer` = '".$buyer."' ";
$bmailv = $conn->query($qbmail);
while($ebmail = $bmailv->fetch_array())
$bmail = end($ebmail);
$conn->close();
echo "$buyer";
echo "$bmail";
echo "$username";
?>]

With this code, my form outputs the username from

$username = "****";

but not the $buyer or the $bmail values

With this code, my form outputs the username from

$username = "****"; but not the $buyer or the $bmail values

I placed the echos in the thank you message as well as the form, the form can't guess what I will select but still no buyer or bmail value. I don't think its getting the bmail value from the sql lookup because it can't figure out the buyer value

$bmail = $form->data['PurchaseRequest']['buyer'];

......I think this line is givng me problems.
This is in CFV4.

I am using:

echo "<input type=\"hidden\" name=\"bmail\" value=\"$bmail\">";

at the end of the form to write the result to the DB

At this time here is what my custom field looks like

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$buyer = $form->data['PurchaseRequest']['buyer'];
$qbmail = "SELECT `rev3_chronoforms_data_Buyers`.`bemail` FROM `rev3_chronoforms_data_Buyers` WHERE `rev3_chronoforms_data_Buyers`.`buyer` = '".$buyer."' ";

$bmailv = $conn->query($qbmail);
while($ebmail = $bmailv->fetch_array())
$bmail = end($ebmail);
$conn->close();
echo "$buyer";
echo "$bmail";
echo "$username";
echo "<input type=\"hidden\" name=\"bmail\" value=\"$bmail\">";
?>

Sorry for the many posts, I seem to be unable to edit my posts.

Hi gamma1itman,

Your MySQL statement is only asking for one value so you can't expect it to return three . . .

SELECT `rev3_chronoforms_data_Buyers`.`buyer`

Bob

Hi gamma1itman,

Your MySQL statement is only asking for one value so you can't expect it to return three . . .

SELECT `rev3_chronoforms_data_Buyers`.`buyer`

Bob

HI Bob,

My SQL statement is only asking for one value because the other values were declared in the PHP.

$username = "xxx";

The above $username value is declared in PHP and is only there to confirm the echo works

$buyer = $form->data['PurchaseRequest']['buyer'];

The above $buyer value is declared in PHP to come from the form value selection but does not return the value - no echo

$qbmail = "SELECT `rev3_chronoforms_data_Buyers`.`bemail` FROM `rev3_chronoforms_data_Buyers` WHERE `rev3_chronoforms_data_Buyers`.`buyer` = '".$buyer."' ";
$bmailv = $conn->query($qbmail);
while($ebmail = $bmailv->fetch_array())
$bmail = end($ebmail);

The above $bmail value is declared in PHP using MySql to calculate it based on the $buyer value from the form

I am certain that

$buyer = $form->data['PurchaseRequest']['buyer'];

is not returning the buyer name from the unsubmitted form. Is there a form event that I need to use like data to session?

Thank you for all you help.

Hi gamma1itman,

Sorry, I was led array by the array() in your custom code :-(

Where are you putting this code?

Have added some debug code or a Debugger action to see what value is in $form->data['PurchaseRequest']['buyer']

What do you expect the hidden input to do? It should work if this in in the On Load event but then the buyer won't have been selected at that point?

Bob

Where are you putting this code?

I am placing the code in a custom html field called bmail. the $username and $buyer are only being echoed for the purpose of testing. My end goal is to look up the buyers email address,since we already know the name which is the same in the buyer table, from the buyer table table and submit it with this form.

Have added some debug code or a Debugger action to see what value is in $form->data['PurchaseRequest']['buyer']

I have added a debugger it does show the PurchaseRequest buyer value from the drop down selection but it shows an empty result for the bmail field (needs $buyer to calculate value) it is not getting the $buyer from $buyer = $form->data['PurchaseRequest']['buyer'];

What do you expect the hidden input to do? It should work if this in in the On Load event but then the buyer won't have been selected at that point?

I expect the hidden input (custom html) to lookup the buyers name from the form and match it to the buyers table to return the buyers email address to be saved when the form is submitted.

Attached is a backup of the form with xxxx for credentials.

Thanks again Bob.

Hi gamma1itman,

If you want to look up something using values submitted in the form you need to do that after the form is submitted. Using a Custom HTML element it will run when the form is loaded and at that point there is no selection to use.

Bob

PS There are a couple of exceptions to this:

+ you can use Ajax in the form to look up a value when a selection is made;
+ you can look up a value if is is already available e.g. included in the URL used to load the form

Hi Bob,

Thanks for the follow up. Do you know of any good examples of how I might achieve my goal using Ajax.?

Warmest regards

Hi gamma1itman,

You can do that with some fairly simple jQuery.

I'm not clear why you need to do that though - isn't it good enough to get the email after the form is submitted?

Bob

Hi gamma1itman,

You can do that with some fairly simple jQuery.

I'm not clear why you need to do that though - isn't it good enough to get the email after the form is submitted?

Bob

How do I get the email address after the form is submitted, the email address is in a different table and I need to look it up from the name being entered in the purchase request form. Do you low where I might find a good example using jquery.

Thanks.

Hi gamma1itman,

When do you need to use the email address?

Bob

Hi gamma1itman,

When do you need to use the email address?

Bob

Hi Bob,

I need to use the email address to send a message after the form is submitted successfully. A message will go out to the buyer and supervisor (may not be a Joomla user, hence the separate table with the email address) and also out to the user, the user info I am able to get no problem like this:

$user =& JFactory::getUser();
$uname = $user->name;
$userid = $user->id;
$uemail = $user->email;

Thanks again Bob.

Hi gamma1itman,

Thank you, that helps a lot. We can for get the jQuery here.

In the form On Submit event please add a Custom Code action with this code:

<?php
$db = JFactory::getDBO();
$query = "
    SELECT `bemail`
        FROM `#__chronoforms_data_Buyers`
        WHERE `buyer` = '{$form->data['PurchaseRequest']['buyer']}' ;
";
$db->setQuery($query);
$form->data['bemail ']= $db->loadResult();
?>

Thus should put the email into bemail in the $form->data['array'] so you can use bemail in the Dynamic To Email box.

Bob