BF as query to MySQL

  • GalanopD
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 21
  • Thank you received: 1

GalanopD replied the topic: BF as query to MySQL

Hello,

Here is what I am trying to do, any help would be highly appreciated.

Joomla module to appear on webpage
<div id="frmSrchResults"></div>

"Search" button on the form with user choices to pull data from db
function ff_Search_action(element, action)
{
    switch (action) {
        case 'click':
let var1 = ff_getElementByName('category').value;
let var2 = ff_getElementByName('subcategory').value;

// call the .post
jQuery.post(
  '<?php return JUri::root(); ?>index.php', {
    option: 'com_breezingforms',
    ff_form: ff_processor.form,
    format: 'html',
    par1: var1,
    par2: var2
  },
  function(data) {
    jQuery('#frmSrchResults').html(data);
  }
);
            break;
        default:;
    } // switch
} // ff_Search_action

In Form Pieces-Before Form
$this->execPieceByName('ff_InitLib');

// fetch .post() parameters
$var1 = JRequest::getVar('par1');
$var2 = JRequest::getVar('par2');

if ($var1 && $var2 && $var1 !== '' && $var2 !== '') {
  $db = JFactory::getDBO();
  $db->setQuery("Query I need to complete");
  $result = $db->loadAssocList();

// clean output buffer
  while (@ob_get_level() > 0) { @ob_end_clean(); }

  echo $result;
  exit;
}

This is an example of the database structure
         id  title                       name                     value
         4   Company Name     companyname       Microsoft
         4   Company Address  companyaddress    someaddress
         4   Country                  country                 USA
         4   Category                category                Computer
         4   Sub-category         subcategory          Software
         5   Company Name      companyname      Apple
         5   Company Address   companyaddress   someaddress2
         5   Country                  country                 CANADA
         5   Category                category                Business
         5   Sub-category         subcategory          Executive
        6   Company Name      companyname       Ollivetti
        6   Company Address   companyaddress   someaddress3
        6   Country                  country                 CANADA
        6   Category                category                Business
        6   Sub-category         subcategory           Executive

Example of user input in form elements select list:
Category=Business
Sub-category=Executive
Country=CANADA

Now I need to: SELECT value (according to user choice on the form. Each form element is a select list) FROM table etc. So in my example the result is expected to be something like this:
Company Name        Company Address 
Apple               someaddress2    
Ollivetti           someaddress3

I hope that now it makes a bit more sense... Thanks in advance

#233951
Attachments:
  • GalanopD
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 21
  • Thank you received: 1

GalanopD replied the topic: BF as query to MySQL

Foolowing this article deprecated and this deprecated 2 came up to this. But I still cannot make it work...
$jinput = JFactory::getApplication()->input;
$category = $jinput->get->post('name', '', 'WORD');
$subcategory = $jinput->get->post('name', '', 'WORD');

if ($category && $subcategory) {
$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select([
        "MAX("
        . "CASE WHEN name = " . $db->q("companyname")
        . " THEN value ELSE NULL END"
        . ") AS " . $db->qn("Company Name"),
        "MAX("
        . "CASE WHEN name = " . $db->q("companyaddress")
        . " THEN value ELSE NULL END"
        . ") AS " . $db->qn("Company Address")
    ])
    ->from($db->qn("#__facileforms_subrecords"))
    ->group("id")
    ->having([
        "MAX("
        . "CASE WHEN name = " . $db->q("category")
        . " THEN value ELSE NULL END"
        . ") = " . $db->q($category),
        "MAX("
        . "CASE WHEN name = " . $db->q("subcategory")
        . " THEN value ELSE NULL END"
        . ") = " . $db->q($subcategory)
    ])
    ->order($db->qn("Company Name"));

    try
    {
        $db->setQuery($query);
        if (!$results = $db->loadAssocList())
        {
            echo "No matches found";
        }
        else
        {
            echo "<table>";
            echo "<tr><th>", implode("</th><th>", array_keys($results[0])), "</th></tr>";
            foreach ($results as $row)
            {
                echo "<tr><td>", implode("</td><td>", $row), "</td></tr>";
            }
            echo "</table>";
        }
    }
    catch (Exception $e)
    {
        JFactory::getApplication()->enqueueMessage("<div>Query Syntax Error, ask dev to run diagnostics</div>", 'error');
        // Don't show the following details to the public:
        //echo $query->dump();
        //echo $e->getMessage();
    }
#233952
Moderators: ForumSupporttomeperica
Time to create page: 0.138 seconds

Community Reward

Help us to create new extensions and plugins! With only $5 you help us a lot and get unlimited download access to all of our products, professional support and even more. Get your reward now!

Read More Here

News and Updates

Get informed about new downloads, updates and more in our News and Updates newsletter.

All Extensions Subscription

Get 1 year access to all of our current and future products and 1 year of professional support -- 99€

No support per domain or website installation limits! Includes all of our current and future Joomla!® extensions, Joomla!® templates for the duration of your membership. This means, by purchasing an All Extensions Subscription you'll have it all covered!

Get it from here

3rd Party Discount - 25% Off

We help you to keep your costs under control. If you are a new member and purchased a form building tool from a different form vendor, then you'll get a 25% discount on our subscription plans.

How to receive the discount:

Send us a quick email to sales@crosstec.org with a proof of purchase (for example a paypal receipt), await payment instructions and enjoy your membership!

Community Reward

Help us to create new extensions and plugins! With only $5 you help us a lot and get unlimited download access to all of our products, professional support and even more. Get your reward now!

Read More Here