mysql database query in element (select from list)

  • mlangamer
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 23
  • Thank you received: 0

mlangamer created the topic: mysql database query in element (select from list)

Hello,
I need help!
I have a form in Breezingforms that needs to query in a database (mysql) called (vagas) and bring in an element (select from list) the result. Once I have selected a vacancy I can get through this form to reduce the number of vacancies. Can someone help me? Sorry but I do not understand English, I'm Brazilian and there is no Portuguese version for Breezingforms.
My table name = vagas
The fields are = id ,escola, serie, descrição_vaga, quantidade_de_vagas.
I have to reduce a vacancy in (quantidade_de_vagas) in sending the form
#231695
  • Posts: 1437
  • Karma: 48
  • Thank you received: 188

mihaela replied the topic: mysql database query in element (select from list)

Hello,

First of all, I will ask to check, you have table "vagas" that is not in Joomla database, but in some other database, is that correct?


Here you can find explanation how to fill in a select list element from database crosstec.org/en/support/online-documenta...ct-list-from-db.html .

The code looks a bit different if you want to fetch data from different database.
So, in order to achieve what you want, navigate to form's Advanced properties> Form pieces > Before Form, set Type to Custom and in the code area below put code similar to this:
$this->execPieceByName('ff_InitLib'); //Include BreezingForms Library
$test = "";

// enter your data here
$servername = "yourservername";
$username = "yourusername";
$password = "yourpasword";
$databasee = "vagas";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$databasee", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // echo "Connected successfully"; 
  // select list will be filled in with escola column form vagas, adjust to what you want 
    foreach($conn->query('SELECT escola from vagas') as $row) {
        $test .= "0;".$row['escola'].";".$row['escola']."\n";
        }
    }

function ff_setSelectList($name, $value)
{
     global $ff_processor;
     for ($r = 0; $r < $ff_processor->rowcount; $r++)
     {
          $row =& $ff_processor->rows[$r];
          if ($row->name==$name)
          $row->data2 = $value;
          unset($row);
     } // for
} // ff_setSelectList
 
ff_setSelectList('selectlistname', $test);
NOTE: In code above replace selectlistname with the Name of select list element that you've created for this in your form.

That code should fill the select list with "escola" column from table, you can set this to what suits you.

Furthermore, here are instructions on how to decrease "quantidade_de_vagas" by one based on selected "escola".

navigate to form's Advanced properties> Submit pieces > End Submit, set Type to Custom and in the code area below put code similar to this:
$this->execPieceByName('ff_InitLib'); //Include BreezingForms Library

// enter your data here
$servername = "yourservername";
$username = "yourusername";
$password = "yourpasword";
$databasee = "vagas";

$vac = ff_getSubmit('selectlistname');
try {
    $conn = new PDO("mysql:host=$servername;dbname=$databasee", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // echo "Connected successfully"; 
    $conn->query("update vagas SET quantidade_de_vagas = quantidade_de_vagas -1 WHERE  escola='$vac' ");
   
    }
NOTE: Once again, in code above replace selectlistname with the Name of select list element that you've created for this in your form.

Let me know whether you've managed to set this for your form and whether it works as wanted.

Regards,

Mihaela
#231700
  • mlangamer
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 23
  • Thank you received: 0

mlangamer replied the topic: mysql database query in element (select from list)

First of all, thank you for the help!

The table that has the name of "VAGAS" is in the same database of joomla.

How do I retrieve or call the function? Where do I put the code? Is it in the properties of my element called "ESOLALIST"?
#231704
Attachments:
  • mlangamer
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 23
  • Thank you received: 0

mlangamer replied the topic: mysql database query in element (select from list)

First of all, thank you for the help!
The table that has the name of "VAGAS" is in the same database of joomla.
How do I retrieve or call the function? Where do I put the code? Is it in the properties of my element called "ESOLALIST"?
#231705
  • Posts: 1437
  • Karma: 48
  • Thank you received: 188

mihaela replied the topic: mysql database query in element (select from list)

Hello,

I'm glad to help you.

Ok, since table is in Joomla database, you should do as follows.

You've asked where to put the code.
First, you have to navigate to Advanced properties of your form > More options > Form pieces > Before Form, set Type to Custom and in the code area below put code similar to this:
$this->execPieceByName('ff_InitLib'); //Include BreezingForms Library
$db = JFactory::getDBO();//Get Database Object
  
//Create your own query here
$db->setQuery('SELECT escola from vagas'); 
  
$result = $db->loadColumn(); //load the result from the query
$test = "0;Choose option; \n";
for ($i = 0; $i < count($result); $i++)
{
     $test .= "0;".$result[$i].";".$result[$i]."\n";
}
 
function ff_setSelectList($name, $value)
{
     global $ff_processor;
     for ($r = 0; $r < $ff_processor->rowcount; $r++)
     {
          $row =& $ff_processor->rows[$r];
          if ($row->name==$name)
          $row->data2 = $value;
          unset($row);
     } // for
} // ff_setSelectList
 
ff_setSelectList('selectlistname', $test);
NOTE: In the code above you have to replace selectlistname with the Name of select list that should display the data from "escola" table column.
That will fill in the select list.

What you have circled red in your form, List textarea in Properties of select list should be left empty.

Furthermore, to reduce the number of vacancies (quantidade_de_vagas) after certain "escola" is selected in select list do as follows.

Navigate to form's Advanced properties > More options > Submit pieces > End Submit, set Type to Custom and in the code area below put code similar to this:
$this->execPieceByName('ff_InitLib'); //Include BreezingForms Library
$db = JFactory::getDBO();//Get Database Object
  
// grab selected value from select list
$vac = ff_getSubmit('selectlistname');

//Create your own query here
$db->setQuery("update vagas SET quantidade_de_vagas = quantidade_de_vagas -1 WHERE  escola='$vac' ");
$db->execute();
NOTE: In the code above you have to replace selectlistname with the Name of select list that should display the data from "escola" table column.

Let me know whether you've managed to set this for your form.

Regards,

Mihaela
#231735
  • mlangamer
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 23
  • Thank you received: 0

mlangamer replied the topic: mysql database query in element (select from list)

is not bringing the result to the list.
I think I'm doing something wrong or not mentioning something in the function. Forgive me for giving so much work.
#231736
Attachments:
Moderators: ForumSupporttomeperica
Time to create page: 0.185 seconds

New Support System

Dear Customers,

Users with active subscription have support priority which will be dona via ServiceDesk (My Account > Raise a ticket).
FREE Support topics will be answered when AND if there is time.

You can check support guidelines here : here

Regards,
Crosstec 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

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