TOPIC:

Database queries 1 month 3 weeks ago #247274

  • Topic Author
  • ooc
  • Offline
  • Expert Breezer
  • Expert Breezer
  • Posts: 231
  • Thanks: 1
I had replaced the code with the one found in crosstec.org/en/support/online-documenta...ct-list-from-db.html . so have had to input it again. Now I get no syntax error with it so perhaps it is working now.
I tried to move on and populate the select list for eventdate : Still no error but I get nothing returned yet the sql returns 2 dates in phpMyAdmin.
I then tried to populate arrival but with it I get a syntax error. Here are all the code snippets:

Before form:
if( isset($_POST['eventdate']) && is_numeric( $_POST['eventdate'] ) ){

    if (ob_get_level()) ob_end_clean();
        while (ob_get_level()){
             ob_end_clean();
        }

        $db = JFactory::getDbo();
        $db->setQuery("Select * From h8be5_eventregistration  Where eventdate = " . $db->quote($_POST['eventdate']) . " Order By eventdate, arrival");

        header('Content-Type: application/json');
        echo json_encode($db->loadObjectList());
        exit;
}

Select list for eventdate
if( isset($_POST['eventdate']) && is_numeric( $_POST['eventdate'] ) ){

    if (ob_get_level()) ob_end_clean();
        while (ob_get_level()){
             ob_end_clean();
        }

        $db = JFactory::getDbo();
        $db->setQuery("Select * From h8be5_eventregistration  Where eventdate = " . $db->quote($_POST['eventdate']) . " Order By eventdate, arrival");

        header('Content-Type: application/json');
        echo json_encode($db->loadObjectList());
        exit;
}

Select list for arrival - gets syntax error so for now I have hard coded the list
<?php
$db = JFactory::getDbo();
$db->setQuery("Select arrival From h8be5_eventregistration  where `countnow` < `maximum` and `eventdate` = '20200913' ”);
$items = $db->loadObjectList();
$item_count = count($items);
$list = '';

for($i = 0; $i < $item_count; $i++){
    
     $list .= '0;'  .  $items[$i]->arrival  .  ';'  .  $items[$i]→arrival  .  '\n';
}

return $list;
?>

I am also attaching the form as it is now.

File Attachment:

File Name: eventregis...ion1.zip
File Size:22 KB


Please let me know if there is anything more I can provide for you.
Attachments:

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

Database queries 1 month 3 weeks ago #247344

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

the code for the event select list is PHP, it should be rather javascript on the field action as found in the example where you got it originally.

Also please remove this in the before form piece:
 && is_numeric( $_POST['eventdate'] ) 

You are sending a date, which is not numeric and thus it wouldn't enter the case and not executing your code.

Regards,
Markus

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

Database queries 1 month 3 weeks ago #247366

  • Topic Author
  • ooc
  • Offline
  • Expert Breezer
  • Expert Breezer
  • Posts: 231
  • Thanks: 1
Thank you Markus

I am beginning to understand better how the makemodelselection form works but perhaps this is not at all what I would need so perhaps what I am asking is not possible at all.
The sql which the Before form piece executes seems to be the one that is used throughout the form . I would need to be able to do a SELECT near the beginning and later an UPDATE based on the user input. The goal of this section of the form is to control the number of people in any one time slot (arrival).
So I will rephrase my initial request:
Is there a way to access a database table throughout a form to SELECT and later UPDATE a table? I am just using the table to control numbers between instances of the usage of the form - like selling objects and knowing when we have run out. Perhaps you have an example of something like that.

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

Database queries 1 month 3 weeks ago #247419

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

if you would be fine doing the update upon form submission, you could use a custom end submit piece to update the table row.

End submit pieces are using PHP, as well and you can access form submission data like this:
$this->execPieceByName('ff_InitLib');

$a_field_value = ff_getSubmit('FIELDNAME');

// code, including SQL to use the value here

So you can basically pass anything from the form submission to db queries and do whatever you please.

Regards,
Markus

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

Database queries 1 month 2 weeks ago #247485

  • Topic Author
  • ooc
  • Offline
  • Expert Breezer
  • Expert Breezer
  • Posts: 231
  • Thanks: 1
Thank you Marcus
That has helped to assure me this can be done.

Unfortunately I have a few more problems:

1: The Select from the database works quite well now from the SQL in the before form piece. However the sql says
$db->setQuery("Select arrival From h8be5_eventregistration  Where eventdate = " .  $db->quote($_POST['eventdate'])  . " and " . $db->quote($_POST['groupsize'])  .  "+ countnow   < maximum Order By  arrival");
What I find is it actually executes
$db->setQuery("Select arrival From h8be5_eventregistration  Where eventdate = " .  $db->quote($_POST['eventdate'])  . " and "  .  "+ countnow   < maximum Order By  arrival");
not using the
. $db->quote($_POST['groupsize'])
.
Groupsize is a numeric value while eventdate is Char. Do I need to code . $db->quote($_POST) differently?

2: For the after submit - I found it worked a charm as long as I put in constants but when I tried to put in the form variables I couldn't work out how to code them.
Here are the 2 sqls I used
$this->execPieceByName('ff_InitLib');

$a_field_value = ff_getSubmit(ELEMENT);

   $db = JFactory::getDbo();
    $db->setQuery("UPDATE `h8be5_eventregistration` SET `countnow`= 8 WHERE `eventdate`='20200913' and `arrival` = `10:15-10:30'  ")
This did the update correctly.
This version replacing one of the elements with a variable did not work. I see from ff_getSubmit I could pass a string of values for the 3 of them but thought I would try with a single variable first.
$this->execPieceByName('ff_InitLib');

$arrival = ff_getSubmit('arrival');

   $db = JFactory::getDbo();
    $db->setQuery("UPDATE `h8be5_eventregistration` SET `countnow`= 8 WHERE `eventdate`='20200913' and `arrival` = `@arrival` ")

For these first 2, I suspect I need more info on how to get Joomla/php/sql to interact. I have looked at the Joomla doc help files but they seem to be mostly for extension developers and I couldn't find anything that I could relate to your code snips. Is there any better documentation you could point me to? Trying to learn....

3: The form is mostly ready now except that strangely the field 'arrival' on page 2 does not pass to its summary element on page 3. It was doing so but something now is preventing it. I am attaching the current form.

4: I tried to see if the 'arrival' did at least pass on to the Google Spreadsheet - something that has always worked well for me but now it doesn't pass anything. I did get a warning from Google that they hadn't 'approved' Crosstec , I think that was the word, but I persisted and was able to make all the links between elements. Yet they didn't get passed on.???

5: Is there some way I can have my Paypal and my Captcha elements not have Labels? They look kind of dumb.

6: Last is that Paypal (on test) does not seem to work. I am using the same code I use on other forms but I haven't used (executed) the test criteria for some time - perhaps they have deleted my test account? But if you could have a look at other criteria I would appreciate it.

If you could help with any of the above I would greatly appreciate it. To see the form on line go to ottawaoc.ca/test/index.php/copyeventregistration . You can submit any tests - no problem.
Attachments:

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

Database queries 1 month 2 weeks ago #247505

  • Topic Author
  • ooc
  • Offline
  • Expert Breezer
  • Expert Breezer
  • Posts: 231
  • Thanks: 1
I should have mentioned - my Joomla and Breezingforms are both fully up to date:
Joomla - 3.9.21
Breezingforms 1.9.1 Stable (build 940).
php is 5.6.40 (I understand I should get them to upgrade this but it is still supported - just)
MySql - 5.6.47

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

Time to create page: 0.054 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!