Update Joomla Table - using PHP script on Form Submission

F

festus

New Member
I have been using Fabrik for more than a year, but this is the first time I am trying to make use of the feature to run PHP script at the end of the Form submission. I have searched the Forum for some examples to update the 'users table' field, but could not find similar example. May be someone can help me find the way...

I have a new field added to the joomla users table that keeps track of a 'credit unit' the user has. While submitting the Form I have a form field to amend this value. On submission of the form I want to amend the value in the 'jos_users.credit' field with the new value submitted in the form.

Assuming:
Field Name to update = jos_users.credit
Form Field name for credit unit adjustment = credit_adjust

I have done the following steps:

a) Display the name of the elements in $aData

<pre><?php print_r($this?_data); ?><pre>

b) The name of the elements i want to use are:

[form_id] => 4
[jos_users___id] => 66
[jos_transactions___credits] => 10 ... Field name = 'Credit Adjustment'

jos_transactions is a new Fabrik table I have created to keep track of transactions of the users
and the credit value to be adjusted as credit/debit.
For credit I will use the value as -10 and debit will be just 10

On Form submission, I want to sum up (add with the sign) the existing value in jos_users.credit field
with the value entered in the Form 'jos_transactions_credits' and update the value in the jos_users.credit
field.

For example, if the existing value in jos_users.credit field = 100
If I enter in the Form Field 'Credit Adjustment' = 30
After the form is submitted, the value in jos_user.credit field = 70 ...(100-30)

If I enter in the Form Field 'Credit Adjustment' = -30
After the form is submitted, the value in jos_user.credit field = 130 ...(100-(-30))

c) sample code:

Selected the option form dropdown = As soon as form submitted (simple eval)

Following examples I found in several postings, I created the following code to be entered into the
'PHP code to execute on Form submission':

$credit = mosGetParam($_POST, 'jos_transactions___credits');
$uid = mosGetParam($_POST, 'jos_users___id');

"UPDATE jos_users SET credit = credit - $credit WHERE id=$uid";

On submission of the form, i do not see any errors, but the jos_user.credit field is NOT getting updated!
I guess I am making some mistakes in my code.

Would appreciate some advise on this..

Festus

P.S. I have installed Fabrik_v1.0.4 SVN_520
 
Hey Thomas

first of all - thanks for such a concise and well thought out post - makes it really easy to follow and comprehend what you are trying to
do :)

I'm guessing that your full code is this, if it isnt then maybe thats the problem:

Code: 
global $database;
$credit = mosGetParam($_POST, 'jos_transactions___credits');
$uid = mosGetParam($_POST, 'jos_users___id');
$sql = "UPDATE jos_users SET credit = credit - $credit WHERE id=$uid";
$database->setQuery($sql);
$database->query();

if your code is that then you can dubug it by doing something like this:

Code: 
global $database;
$credit = mosGetParam($_POST, 'jos_transactions___credits');
$uid = mosGetParam($_POST, 'jos_users___id');
$sql = "UPDATE jos_users SET credit = credit - $credit WHERE id=$uid";
$database->setQuery($sql);
$database->query();
echo $database->getQuery();
echo $database->getErrorMsg();
exit;

this will print out the full query, and any error messages generated by the query which you can then use to debug the code you have

hop that helps
Rob
 
Hi Rob,

Thanks for the excellent support you are providing to the community.
Your time (& others like you in the forum) is extremely valuable and so I need to be disciplined and systematic in my posting, in order not to waste your time. Really appreciate the prompt response and clear directions.

a) I did not use the full code as given by you, so that was one mistake I corrected.
b) The debug 'echo' lines were very useful, so I included that in my code.
c) In my post I have given the second field name as 'jos_transactions__credits'. The actual name is in the format
'jos_new_transactions__credits'. I had used the shorter name is the posting, but thought of the possibility that
having the table name as 'jos_new_transactions' could be not compatible with the code processing the Form.

Following is the code I pasted in the PHP script submit:
Code: 
echo "<pre>";print_r($aData);echo "</pre>";
global $database;
$credit = mosGetParam($_POST, 'jos_new_transactions___credits');
$uid = mosGetParam($_POST, 'jos_users___id');
$sql = "UPDATE jos_users SET credit = credit - $credit WHERE id=$uid";
$database->setQuery($sql);
$database->query();
echo $database->getQuery();
echo $database->getErrorMsg();
exit;

Following is the screen output I got after the Form was submitted:

Code: 
Array
(
  [jos_users___id] => 66
  [jos_users___name] => plapuser1
  [jos_users___username] => plapuser1
  [jos_users___email] => [email]plapuser1@domain.com[/email]
  [jos_users___credit] => 34
  [jos_users___userhide] => 0
  [jos_users___id_online] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 66
          [jos_new_transactions___userid] => 66
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => 6
          [jos_new_transactions___date] => 
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Test Top up-23
        )

    )

UPDATE jos_users SET credit = credit - WHERE id=66

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=66' at line 1 SQL=UPDATE jos_users SET credit = credit - WHERE id=66

It seems, for some reason the value of $credit is 'missing' in the sql command.
I am puzzled, since the value I entered in the Form is captured as:

Code: 
[jos_new_transactions___credits] => 6

However the value '6' does not seem to go into the variable $credit.

I have tried so many variations of the code, but without success.

I am wondering if the name for the table should be of the format [jos_transactions___credits]
and not [jos_new_transactions___credits], which has extra 'new_'.

Anyway I AM making progress with my understanding of what is going on and would appreciate some advise on what next I should do in order to resolve this problem. I would like this posting also to be a useful reference for others in the community who may have similar application needs.

Best Regards,
Festus
 
Hi Thomas

looks like you have two credit elements [jos_users___credit] => 34 and => 6
Im not sure which one you need, I'm guessing the latter, in which case you can get that "6" value with

Code: 
$credit = $aData['join']['2']['jos_new_transactions___credits'];

hth
Rob
 
Dear Rob,

I am able to successfully retrive the $credit value now and the sql is updating the user field correctly.

Thanks again for timely advise and the time allocated to help me out on this issue.

Best Regards,
Festus
 
Dear Rob,

I have to bring up a request for advise one one more step I need to take to complete the processing of the form. I have made some attempts on my side but without success. The following sql command successfully updates the 'credit' field in the jos_user table.

$sql = "UPDATE jos_users SET credit = credit - $credit WHERE id=$uid";

After this I need to also update the field 'jos_new_transactions.crbal' with the updated credit value.

The new updated credit value needs to be available in the two tables- one in the user record for the particular
userID and the second on the new transaction record created by the Fabrik form in the 'jos_new_transaction' table.

Following is the code I tried out:

Code: 
(As soon as form submitted - simple eval)

echo "<pre>";print_r($aData);echo "</pre>";
global $database;
$credit = $aData['join']['2']['jos_new_transactions___credits'];
$uid = mosGetParam($_POST, 'jos_users___id');
$crbal = $aData['jos_users___credit'] - $credit;
$sql = "UPDATE jos_users SET credit = credit - $credit WHERE id=$uid";
$database->setQuery($sql);
$database->query();
echo $database->getQuery();
echo $database->getErrorMsg();
$aData['join']['2']['jos_new_transactions___crbal'] = $crbal;
echo "<pre>";print_r($aData);echo "</pre>";
// exit;

Following is the array dump:

Code: 
Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [email]plapuser2@domain.com[/email]
  [jos_users___credit] => 71
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => -9
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 80
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 9
        )

    )


)

UPDATE jos_users SET credit = credit - -9 WHERE id=67

As you can see in the last line above, the sql seems to update the users table, but when I check the field directly
in the database, there is no update. The crbal field in the new_transactions table is however updated as per my requirement.

Would appreciate your advise on the amendments I need to make to the code to achieve the update of the 2 fields.

Best Regards,

Festus
 
hmm in theory what you are doing looks right.
I've tested a similar set up and it appears to work for me. Im using PHP5.2.5 - can I ask you which version of php you are using? You can find this in your joomla admin "system->system info under the sytem info tab.

Your code should also have printed out $aData twice, the second being after the sql statement is written out, could you let me know if this isnt the case?

Finally just so you are aware, which I think you are judging by what you are doing, that if you modify the data stored in $aData with the "(As soon as form submitted - simple eval)" option on then this modified data should be what is written into the fabrik tables.

Cheers
Rob
 
Live server app versions:

Code: 
Database Version: 5.0.45-community-log
PHP Version: 	5.2.5
Fabrik_v1.0.4 SVN_520

The full screen dump:
Code: 
Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [email]plapuser2@domain.com[/email]
  [jos_users___credit] => 71
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => -4
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 80
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 10
        )

    )

  [tableid] => 4
  [fabrik] => 4
  [task] => processForm
  [rowid] => 67
  [Itemid] => 190
  [option] => com_fabrik
  [form_id] => 4
  [fabrik_frommodule] => 
  [fabrik_cursor] => 0
  [fabrik_total] => 37
  [returntoform] => 0
  [fabrik_referrer] => [url]http://www.domain.com/index.php?option=com_fabrik&Itemid=190[/url]
  [layout] => bluesky
  [********************************] => 1
  [fabrik_repeat_group_7_counter] => 1
  [fabrik_repeat_group_8_counter] => 1
  [Submit] => Submit
)

UPDATE jos_users SET credit = credit - -4 WHERE id=67

Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [email]plapuser2@domain.com[/email]
  [jos_users___credit] => 71
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => -4
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 75
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 10
        )

    )

  [tableid] => 4
  [fabrik] => 4
  [task] => processForm
  [rowid] => 67
  [Itemid] => 190
  [option] => com_fabrik
  [form_id] => 4
  [fabrik_frommodule] => 
  [fabrik_cursor] => 0
  [fabrik_total] => 37
  [returntoform] => 0
  [fabrik_referrer] => [url]http://www.domain.com/index.php?option=com_fabrik&Itemid=190[/url]
  [layout] => bluesky
  [*************************************] => 1
  [fabrik_repeat_group_7_counter] => 1
  [fabrik_repeat_group_8_counter] => 1
  [Submit] => Submit
)

Transact Update

The customer Trasaction Table has been updated.

Should I try updating the Fabrik to: fabrik_1.04_svn_793 ?
I do have this downloaded and ready to test. But wanted to debug to code to make sure I am doing it right.

What is the safest way to go from svn520 to svn793 so I keep the configs and table intact ?

What would you advise me to take as the next step ?

Best Regards,
Festus
 
An update of fabrik might well be the solution.

To do this get the latest version of the SVN as per my sig - currently rev 863

There are no database changes etc just the files to upload.
As a precaution make a copy of your existing fabrik files before ftp'ing up the new files from the SVN.
thats all :)

Rob
 
Following is the update:

a) I have upgraded Fabrik to the latest svn 866, but this upgrade did not fix the problem.

b) I ran the following code under 2 scenarios - the full code & second time by commenting out the
last line 'exit;'. I have given below the screen print and the result in both cases.

Code: 
echo "<pre>";print_r($aData);echo "</pre>";
global $database;
$credit = $aData['join']['2']['jos_new_transactions___credits'];
$uid = mosGetParam($_POST, 'jos_users___id');
$crbal = $aData['jos_users___credit'] - $credit;
$aData['join']['2']['jos_new_transactions___crbal'] = $crbal;
$sql = "UPDATE jos_users SET credit = $crbal WHERE id=$uid";
$database->setQuery($sql);
$database->query();
echo $database->getQuery();
echo $database->getErrorMsg();
echo "<pre>";print_r($aData);echo "</pre>";
exit;

I tested under following 2 conditions:

Code: 
Drop down option = as soon as form is submitted (simple eval)
 
Case 1: PHP Code as above including the last line 'exit;'
 
New record in 'jos_new_transaction' table is NOT created.
But the 'jos_users' table credit field IS updated.
 
Case 2: Last line 'exit;' Commented out.
New record created in 'new_transactions' table & crbal field is updated.
But the 'jos_users' table credit field is NOT updated.

-------------------------------------------------------------------------------------------------
Following are the screen print out for the 2 cases:

Case 1: PHP Code as above including the last line 'exit;'

New record in 'jos_new_transaction' table is NOT created.
But the 'jos_users' table credit field IS updated.

Code: 
Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [EMAIL="plapuser2@domain.com"]plapuser2@domain.com[/EMAIL]
  [jos_users___credit] => 102
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => 12
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 100
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 18
        )
 
    )
 
  [tableid] => 4
  [fabrik] => 4
  [task] => processForm
  [rowid] => 67
  [Itemid] => 190
  [option] => com_fabrik
  [form_id] => 4
  [fabrik_frommodule] => 
  [fabrik__cursor] => 0
  [fabrik__total] => 45
  [returntoform] => 0
  [fabrik_referrer] => [URL]http://www.domain.com/index.php?option=com_fabrik&Itemid=190[/URL]
  [layout] => bluesky
  [*********************************] => 1
  [fabrik_repeat_group_7_counter] => 1
  [fabrik_repeat_group_8_counter] => 17
  [Submit] => Submit
)
 
UPDATE jos_users SET credit = 90 WHERE id=67
 
Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [EMAIL="plapuser2@domain.com"]plapuser2@domain.com[/EMAIL]
  [jos_users___credit] => 102
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => 12
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 90
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 18
        )
 
    )
 
  [tableid] => 4
  [fabrik] => 4
  [task] => processForm
  [rowid] => 67
  [Itemid] => 190
  [option] => com_fabrik
  [form_id] => 4
  [fabrik_frommodule] => 
  [fabrik__cursor] => 0
  [fabrik__total] => 45
  [returntoform] => 0
  [fabrik_referrer] => [URL]http://www.domain.com/index.php?option=com_fabrik&Itemid=190[/URL]
  [layout] => bluesky
  [********************************] => 1
  [fabrik_repeat_group_7_counter] => 1
  [fabrik_repeat_group_8_counter] => 17
  [Submit] => Submit
)
---------------------------------------------------------------------------------------------------
Case 2: Last line 'exit;' Commented out.

New record created in 'new_transactions' table & crbal field is updated.
But the 'jos_users' table credit field is NOT updated.

Code: 
Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [EMAIL="plapuser2@domain.com"]plapuser2@domain.com[/EMAIL]
  [jos_users___credit] => 102
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => 2
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 90
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 17
        )
 
    )
 
  [tableid] => 4
  [fabrik] => 4
  [task] => processForm
  [rowid] => 67
  [Itemid] => 190
  [option] => com_fabrik
  [form_id] => 4
  [fabrik_frommodule] => 
  [fabrik__cursor] => 0
  [fabrik__total] => 44
  [returntoform] => 0
  [fabrik_referrer] => [URL]http://www.domain.com/index.php?option=com_fabrik&Itemid=190[/URL]
  [layout] => bluesky
  [*******************************] => 1
  [fabrik_repeat_group_7_counter] => 1
  [fabrik_repeat_group_8_counter] => 16
  [Submit] => Submit
)
 
UPDATE jos_users SET credit = 100 WHERE id=67
 
Array
(
  [jos_users___id] => 67
  [jos_users___name] => plapuser2
  [jos_users___username] => plapuser2
  [jos_users___email] => [EMAIL="plapuser2@domain.com"]plapuser2@domain.com[/EMAIL]
  [jos_users___credit] => 102
  [jos_users___userhide] => 0
  [jos_users___dtupdt] => 
  [join] => Array
    (
      [2] => Array
        (
          [rowid] => 67
          [jos_new_transactions___userid] => 67
          [jos_new_transactions___item_id] => 0
          [jos_new_transactions___title] => 
          [jos_new_transactions___credits] => 2
          [jos_new_transactions___date] => 
          [jos_new_transactions___crbal] => 100
          [jos_new_transactions___service] => Content Access
          [jos_new_transactions___comments] => Top up 17
        )
 
    )
 
  [tableid] => 4
  [fabrik] => 4
  [task] => processForm
  [rowid] => 67
  [Itemid] => 190
  [option] => com_fabrik
  [form_id] => 4
  [fabrik_frommodule] => 
  [fabrik__cursor] => 0
  [fabrik__total] => 44
  [returntoform] => 0
  [fabrik_referrer] => [URL]http://www.domain.com/index.php?option=com_fabrik&Itemid=190[/URL]
  [layout] => bluesky
  [*********************************] => 1
  [fabrik_repeat_group_7_counter] => 1
  [fabrik_repeat_group_8_counter] => 16
  [Submit] => Submit
)
 
Transact Update
 
The customer Trasaction Table has been updated.
-----------------------------------------------------------------------------------------------
I seem to be making either some fundamental mistake with my approach or
some simple syntax error..

Would appreciate further advise for action.

Best Regards,
Festus
 
Well, with the 'exit' in there, the entire script (ie the Fabrik form processing) is going to abort, so that explains why the ___crbal doesn't get updated when you have the 'exit'.

Not quite sure why the jos_users update isn't happening without it tho ... strange ...

Rob?

-- hugh
 
no idea either - it all looks good to me

Thomas can you pm me a super admin account to your site - and if its ok with you, allow me to debug whats going on?

Cheers
Rob
 
Sure Rob. I will PM soon.
Appreciate your time to get to the bottom of this issue and help to resolve.

Regards,
Festus
 
hi

i took a look on your site.
its a bit hard to know what you want to do to be honest -

can you explain what these fields are for and their relationship to each other:

plap1_users___credit
plap1_paypal_access___credits
plap1_paypal_access___crbal

cheers
Rob
 
Hi Rob,

Glad you found the time to take look at the install.
Following is the logical sequence of how the 3 fields are updated:

plap1_users___credit (value A)
>The value is updated to reflect the credit balance units available to the user.

plap1_paypal_access___credits (value B)
>The value of credit adjusted for a particular transaction. This can be positive or negative.

plap1_paypal_access___crbal (value C)
>'crbal' will be in synch with value A in the 'user table'.

As an example:

a) Customer has a credit unit of 100
value A =100 and
value C =100

b) Customer uses 50 credits to pay for certain service at the portal.
value B=50

The new record 'plap1_paypal_access' will have the details
of the transaction and the particular transaction 'credits' =50
This new record is created when the Fabrik form is submitted.

c) The PHP script in the Fabrik Form will update as follows:
> plap1_users___credit (value A) = 50 (100-50)
> plap1_paypal_access___crbal (value C) = 50 (same as value A)

Hope that clarifies the flow and the logic.
Let me know if you need additional info.

Appreciate again your time.

Regards,
Festus
 
Hi Rob and Friends of Fabrik,

I assume the clarifications on the field I posted above are clear. I really hope someone can help me to get over this hurdle since I am simply stuck not being able to make full use of Fabrik. I will be happy to pay a fee for someone to spend some time to resolve this for me and I will be happy to post the solution to the community as well. I am sure there are many others in the community who probably have similar application needs.

Best Regards,
Festus
 
Should I repost this item to the 'Subscriber Section' ?

Dear Rob, Hugh,

Will this problem get a bit more attention, if I re-post it to the
'subscriber's section', since I am a subscriber and I VERY BADLY need to get over the hurdle I am facing with this simple but crucial task of running the php script in the form submission. If yes, may I request that this topic be moved to the 'subscriber section' ? Or else, should I repost under that section as a new entry?

Bes Regards,
Festus
 
I've moved the thread to Subscriber Support, so it'll now get priority. I'll see if I can find some time later today to look at it.

-- hugh
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    No members online now.
    Total: 126 (members: 1, guests: 125)
    Back
    Top