TOPIC:

How to use MySQL query with BF records 6 years 5 months ago #128428

  • Topic Author
  • wcd
  • Offline
  • Junior Breezer
  • Junior Breezer
  • Posts: 28
  • Thanks: 0
Hi - I am trying to figure out the best way to query the BF records which are saved when the form is submitted.
We are migrating to BF from an older version of jforms. Previously in jforms each form record was stored in
a new row of the database table and each form field was written to a unique column of the table. This made querying the
database to retrieve specific records relatively easy with MySQL statements of the form:

SELECT <Table.Column> AS {name} FROM <TABLE> WHERE {name="Joe"} .

This is an oversimplified MySQL query example of course, but there are many places throughout the site that require this type
of query to identify records based on unique criteria for generating reports and checking user records, etc

Since BF table structure stores all form fields as values in a single column of the subrecords table, what is the proper way to search through all of the records and match ones with specific criteria using a similar MySql query statement?

Thanks.

Please Log in or Create an account to join the conversation.

How to use MySQL query with BF records 6 years 5 months ago #128487

  • pedro81's Avatar
  • pedro81
  • Offline
  • Ultimate Breezer
  • Ultimate Breezer
  • Posts: 15273
  • Karma: 287
  • Thanks: 1077
Hello,

please, take a look at this link:
crosstec.org/support/index.php?pid=knowl...=viewentclient&id=51

there is a good example to implement it.

Let us know if you have doubts with it.

Please Log in or Create an account to join the conversation.

How to use MySQL query with BF records 6 years 5 months ago #128489

  • TheMuffinMan's Avatar
  • TheMuffinMan
  • Offline
  • Developer
  • Developer
  • Posts: 10064
  • Karma: 167
  • Thanks: 808
Hi,

you can do it like this
// edit this to match your form id (scriptID in manage records)

$form_id = 1;
$db = JFactory::getDBO();

$db->setQuery( "select * from #__facileforms_records where form = ".$db->Quote($form_id)." order by submitted Desc");

$recs = $db->loadObjectList();
                
$recsSize = count($recs);
for($r = 0; $r < $recsSize; $r++) {
   $rec = $recs[$r];
   $db->setQuery("select Distinct * from #__facileforms_subrecords where record = $rec->id order by id");
   $subs = $db->loadObjectList();

   // printing structure
   print_r( $subs );
}

From there you can use $subs as they contain the values for each field and implement your requirement.

Regards,
Markus

Please Log in or Create an account to join the conversation.

Last edit: Post by TheMuffinMan.

How to use MySQL query with BF records 6 years 5 months ago #128533

  • Topic Author
  • wcd
  • Offline
  • Junior Breezer
  • Junior Breezer
  • Posts: 28
  • Thanks: 0
Hi Pedro,

Thanks for your reply, however I am not looking to populate the BF fields from another database table, but rather parse through each of the BF records for purposes of finding a specific record which matches some criteria or for pulling multiple records with a common criteria and then generating a report for printing. I have used SQL2EXCEL previously for this purpose when using another form component but BF stores the form records in a different format and I do not know how/if I can write SQL queries to accomplish the same.

Please Log in or Create an account to join the conversation.

How to use MySQL query with BF records 6 years 5 months ago #128534

  • Topic Author
  • wcd
  • Offline
  • Junior Breezer
  • Junior Breezer
  • Posts: 28
  • Thanks: 0
Hi Marcus,

Thanks....I was hoping to be able to accomplish this directly with only SQL statements as I am using another component SQL2EXCEL to generate reports from the data gathered from the BF submissions. I will give your approach a try and then see how else I may be able to generate the reports I need.

Please Log in or Create an account to join the conversation.

How to use MySQL query with BF records 6 years 5 months ago #128629

  • TheMuffinMan's Avatar
  • TheMuffinMan
  • Offline
  • Developer
  • Developer
  • Posts: 10064
  • Karma: 167
  • Thanks: 808
Hi,

you can also safe the extra query by using group_concat() over the fields (swapping the key/value pairs to a regular table) but that will bloat the sql query. I can extract an example from the record manager if you like.

Regards,
Markus

Please Log in or Create an account to join the conversation.

Moderators: ForumSupport
Time to create page: 0.052 seconds

BreezingForms Pro 1.4.7 for WordPress Released!

Available in the membership section.

September Discount!

Massive discounts on all subscriptions!

Get Your Subscription Here

Quick Links

Downloads

BreezingForms

ContentBuilder

BreezingCommerce

Templates

Documentation

BreezingForms

ContentBuilder

BreezingCommerce

Apprendre BreezingForms (French Community)

Apprendre et maîtriser BreezingForms par des tutoriels et exemples, le tout en français

breezingforms.eddy-vh.com

Questions et réponses sur les forums de l'AFUJ

AFUJ

Special Offer

Summer Sale! All subscriptions at a special price!

Includes prio support, all of our current and future Joomla!® extensions and Joomla!® templates for the duration of your membership.

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!