BF as query to MySQL

  • GalanopD
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 22
  • 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: 22
  • 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
  • GalanopD
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 22
  • Thank you received: 1

GalanopD replied the topic: BF as query to MySQL

Hoping that someone will find time to reply at the end... In the meantime, I am still trying to work this out...

Here is the Actionscript of the "Search" button
 function ff_bfQuickMode6825273_action(element, action)
{
    switch (action) {
        case 'click':
let var1 = ff_getElementByName('category').value;
let var2 = ff_getElementByName('subcategory').value;
let var3 = ff_getElementByName('CselCountry').value;

// call the .post
// jQuery.ajaxSetup({async:false});
jQuery.post(
 '<?php return JUri::root(); ?>/index.php', {
    option: 'com_breezingforms',
    ff_form: ff_processor.form,
    format: 'html',
    category: var1,
    subcategory: var2,
    country: var3
},
//  success: function(data) {
    function(data) {
    jQuery('#frmSrchResults').html(data);
  }
);
            break;
        default:;
    } // switch
} // ff_bfQuickMode6825273_action

Here is the Before Fom piece
$jinput = JFactory::getApplication()->input;
$this->execPieceByName('ff_InitLib'); //Include BreezingForms Library
$var1 = $jinput->get->post('category', '', 'WORD');
$var2 = $jinput->get->post('subcategory', '', 'WORD');
$var3 = $jinput->get->post('country', '', 'WORD');
if ($var1 && $var2 && $var3) {
// clean output buffer
while (@ob_get_level() > 0) {
@ob_end_clean();
} // End clean output buffer
$db = JFactory::getDbo(); //Get Database Object
$query = $db->getQuery(true)
    ->select([
        "MAX("
        . "CASE WHEN element = " . $db->q("245")
        . " THEN value ELSE NULL END"
        . ") AS " . $db->qn("Company Name"),
        "MAX("
        . "CASE WHEN element = " . $db->q("343")
        . " THEN value ELSE NULL END"
        . ") AS " . $db->qn("Company Address"),
        "MAX("
        . "CASE WHEN element = " . $db->q("248")
        . " THEN value ELSE NULL END"
        . ") AS " . $db->qn("Company Email"),
    ]) // End select
    ->from($db->qn("#__facileforms_subrecords"))
    ->group("record")
    ->having([
        "MAX("
        . "CASE WHEN element = " . $db->q("3142")
        . " THEN value ELSE NULL END"
        . ") = " . $db->q($var1),
        "MAX("
        . "CASE WHEN element = " . $db->q("7775")
        . " THEN value ELSE NULL END"
        . ") = " . $db->q($var2),
        "MAX("
        . "CASE WHEN element = " . $db->q("2064")
        . " THEN value ELSE NULL END"
        . ") = " . $db->q($var3)
    ]) // End having
    ->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();
} // End catch (Exception $e)
} // End if ($category && $subcategory && $country)

It doesn't work... It simple reproduces the index.php page within the custom module which is
<div id="frmSrchResults"></div>

Help!
#234318
Moderators: ForumSupporttomeperica
Time to create page: 0.173 seconds

New Icon Packs Category!

Crosstec is now offering icon packs.

If you are a paying subscriber, icon packs are automatically added to your account.

Check out our icon packs page!

Live Support Chat Opened!

Join our Discord chat here to receive live support and talk directly to the team!

Summer Sale!

50% discount on all of our extension subscription plans, templates and icon packs!

Get Your Subscription 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 for just 49! (Summer Sale)

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!

Live Support Chat Opened!

Join our Discord chat here to receive live support and talk directly to the team!

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