Help to search and display table data

Hi,

Im new to Joomla and Chronoforms. I have a table with 5 fields: id, method, age, num, calc
I have created a form containing the following elements: method(text box), age(text box), num(text box), and submit button.

I want to query the table using the 3 fields (method, age, num) which will be input in the form then display the calc value after submit button is clicked. This is actually simulating a calculator but instead of performing the math functions, a lookup table is used instead.

Any help will be much appreciated.

Thank you in advance.

Aron

Hi Aron,

This is a pretty standard form: How far have you got? Where do you need help?

You can do the calculations in the form ON Submit using a DB Record Loader to get the data and PHP in a Custom Code action to do any further processing needed.

Bob

Hi Bob,

Thanks for your quick response. I do know that the form data should be submitted and processed by a Php script. Is this where the custom code should be added: Form Wizard -> Others tab -> Custom PHP Code? I have very little PHP coding skills so can you point to me a similar example on how to achieve this. I also installed ChronoConnectivity (is this needed?). Btw, Im using Joomla 3.

Aron

Hi Aron,

You don't need ChronoConnectivity (that's for creating lists of records).

Yes your code can go into that box. There are hundreds of examples of database queries in the forums and FAQs here - though none will be exactly the same as yours.

Bob

Hi Bob,

So I already got the form created. Then on the box,

- I need to put the generated code for the form.

- add these codes to connect to db and query the table

$db =& JFactory::getDBO();
$query = "SELECT `calc` FROM `prob_tbl` WHERE `method` = '$method_frm' AND 'age'='$age_frm' AND 'num'='$num_frm'";
$db->setQuery($query);

- display the query result

Am I on the right track?

Thanks,

Aron

Hi Bob,

Ah just read and followed the tutorials in creating the basic form and the db record loader. So I created my form with the 3 input elements and a submit button.
method
age
num
submit

Then in the Events -> On Load, I added Show html.

And in On Submit, I added the DB Record Loader with the ff configuration:
DB Field is blank
Table is my table_name
Request Param is var_success
WHERE statement is `method` = '$method' AND 'age'='$age' AND 'num'='$embryos'

I know I'm still missing something because when I test the form, as shown in the attached, the screen becomes blank after I click the Calculate button. Btw, my table has the following column names: id, method, age, embryos, LBTOHB

Thanks,

Ron

Hi,

I already got up to this point where my debug data is:
Debug Data

db_record_loader
SELECT * FROM `eggfreezing_calculator` AS `calcid` WHERE `method` LIKE '%%' AND 'age' LIKE '%%' AND 'embryos'LIKE '%%'

From the above, it looks like the form is not passing the post data so I'm not getting any results.

I have the ff settings:
model id - calcid
and my WHERE box contains
`method` LIKE '%<?php echo $form->data['method']; ?>%' AND 'age' LIKE '%<?php echo $form->data['age']; ?>%' AND 'embryos'LIKE '%<?php echo $form->data['num']; ?>%'

I'm getting stucked in here.

Aron

Hi,

Now I'm getting the form inputs passed to the DB record loader. But still I cannot get to display the results. Here is my debug data:
Debug Data

db_record_loader
SELECT * FROM `eggfreezing_calculator` AS `calcid` WHERE `method` LIKE '%1%' AND 'age' LIKE '%25%' AND 'embryos' LIKE '%3%'

I added a Custom Code (pls see the attached image) to display a record field from my table:

<?php
$db->setQuery($query);
$options = $db->loadAssocList(); ?>
Probability: <?php echo $options['LB_TO_HB'];
?>

Help please.

Thanks,

Aron

Hi Aron,

There are some incorrect quotes in the WHERE clause, column names use back-ticks `` so the clause needs to be

 `method` LIKE '%1%' AND `age` LIKE '%25%' AND `embryos` LIKE '%3%'

Bob

Hi Aron,

As in the other thread, column names need back-ticks round them, not single quotes. And you have missed the code to execute the query and get a result:

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT `calc` 
    FROM `prob_tbl` 
    WHERE `method` = '$method_frm' AND `age` = '$age_frm' AND `num` = '$num_frm' ;
";
$db->setQuery($query);
$calc = $db->addResult(); // << add this line (or something similar)
?>

The actual method you use to get the result sill depend on whether it it a single result or an array of results.

Bob

Hi Bob,

This is now what I have in my Custom Code:

<?php
$db =& JFactory::getDBO();
$query = "
    SELECT *
        FROM `eggfreezing_calculator WHERE `method` LIKE '%<?php echo $form->data['input_method']; ?>%' AND `age` LIKE '%<?php echo $form->data['input_age']; ?>%' AND `embryos` LIKE  '%<?php echo $form->data['input_num']; ?>%' `;
";
$db->setQuery($query);
$calc = $db->addResult();
foreach ( $calc as $d ) {
  echo 'Probability: '.$d['LB_TO_HB'];
}
?>

I only want to display one result row based on my query.

I still do not see the result displayed, which should be this:
Probability: the value of my table field LB_TO_HB

Am I still missing something?

Thanks, Aron

Hi,

This is my latest config for the form (please refer to attach):
1. I have the Show HTML action in the On Load
2. DBRL is in On Submit
3. In DBRL, model id is set to calcid and Load under model id is NO
4. DBRL WHERE statement is

`method` LIKE '%<?php echo $form->data
['input_method']; ?>%' AND `age` LIKE '%<?php echo 
$form->data['input_age']; ?>%' AND `embryos` LIKE  
'%<?php echo $form->data['input_num']; ?>%' 

5. I have this in Custom Code to display just one row of my results (also in the attach):

$db->setQuery($query);
$calc = $db->addResult();
  echo 'Probability: '.$calc['LB_TO_HB'];

But it is just showing this:

$db->setQuery($query); $calc = $db->addResult(); echo 'Probability: '.$calc['LB_TO_HB']; Data Array:

I need help in the above code to display my result and also where to place it.

Thanks,

Aron

Hi,

In addition to the above post, attached is the debug view. It is showing the result of the SELECT query so my problem now is how to display the result field [LB_TO_HB]. Please help in the code and where I need to add it.

Thanks,

Aron

Hi Aron,

a) You need to add <?php ?> tags to your Custom Code action.

b) You can display data from the $form->data[''] array either with {input_name} or with <?php echo $form->data['input_name']; ?> depending on the context.

Bob

Thanks. My form is now working. I actually used the table_column_name instead of the input_name because the data I wanted to display is not in my form inputs.

Aron