mysql database query in element (select from list)

  • mlangamer
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 24
  • 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: 3128
  • Karma: 105
  • Thank you received: 413

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: 24
  • 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: 24
  • 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: 3128
  • Karma: 105
  • Thank you received: 413

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: 24
  • 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: ForumSupport
Time to create page: 0.106 seconds

We Are Doing Our Part

Massive discounts on all of our extension subscription plans to help you and your business in these times.

Get Your Subscription Here

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!

News and Updates

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

All Extensions Subscription

We are doing our part and support your business with extremely low prices -- For example 6-Months-Subscription for only $9 USD. For as long as necessary.

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!