calling MySQL stored procedure in PHP script on form submission

[Wisdom Hunter]

Hugh,
I understand that on form submission I can run a PHP script. Is it possible to call a MySQL stored procedure from this PHP script ? In certain cases - if a new row has been added - I want to add a row to another table as well. If this is possible can you point me to where I would find the appropriate syntax ?
Thanks !
- Peter.

 

[Wisdom Hunter]

Related to this - where can I see the structure of various globals (such as $my, $database, etc) ... so I don't have to ask so many syntax questions ? Are they defined in PHP scripts that I can see in my file system ?

 

[cheesegrits]

Sure, you can do whatever you want in PHP submit scripts. And looking at the Fabrik code itself will give you plenty of examples of how to execute and process queries.

One thing I find makes it easier is instead of writing all your PHP in the tiny box on the Fabrik form admin page, create a file in the ./libs dir (mysubmit.php, or whatever) and in the PHP form on the admin page just do:

require($mosConfig_absolute_path . "/components/com_fabrik/libs/mysubmit.php");

Then you can edit your actual code the file using your favorite code editor, and not have to keep saving the Form.

A good place to start looking is in ./fabrik.class.php, in the processForm() function. That's where we eval your form submit script. So you can see what's in scope, anything else you'll have to global in.

Oh, and set your script up in Fabrik as "As soon as form submitted (simple eval)". No CURL involved here.

-- hugh

 

[Wisdom Hunter]

OK, I'll play around with that and let you know if I encounter any issues that I can't solve myself.

 

[Wisdom Hunter]

Hugh,
One thing I am a bit unclear on :

is << global $database >> a class ? It seems to have methods associated with it, judging by code blocks like this (taken from fabrik.class.php) :

global $database;
$sql = "SELECT id FROM #__fabrik_tables WHERE form_id = '$this->id'";
$database->setQuery( $sql );
$this->table_id = $database->loadResult( );

If $database is a class, where is it defined - is it a Fabrik thing, or is it a PHP built-in ? I can't see where it's defined in the Fabrik code base so I am guessing it's more generic than that ... I can go to a PHP bulletin board if you tell me it's a generic PHP built-in .... It would be really helpful to see the definition so I can know which method is appropriate to calling a stored procedure. I'm assuming that calling a stored proc is not the same as executing a query (at least they're not treated as equivalent in the Oracle world I come from).

 

[Wisdom Hunter]

Never mind, found the definition for database.php. Thanks !
BTW the new interface looks good but the forum behaves really strangely now.

 

[cheesegrits]

As you seem to have discovered, $database is the class instance created by J! during global init, which components then use.

Can you be a little more specific about 'behaves really strange now'? I'm fully expecting some teething issues with the new J!/vB wrapping technique we're using ... but it all looks OK to me ...

-- hugh

 

[Wisdom Hunter]

please tell me if there is a problem with my syntax

Hugh,

I am calling a php script to call a Stored Proc when my People form is submitted ( see below double dashed line). It is not generating any php error messages anymore (I had some at first) but it also isn't doing what I expect - i.e. the stored proc is either not being called correctly or there is an error in the stored proc itself. I'm not that familiar with MySQL error handling yet, but before I go digging into that - can you just have a quick look at the below and see if you can tell me any obvious problems that you can see in my php code that would cause the query not to successfully call the stored proc ?

Thanks.
==================== here is the php code ============================
<?php

global $my;
global $database;

//
// initialize param to be passed to the stored proc
//
$user_id=$my->id ;

//
// call the stored procedure
//
$sql = "CALL cc_add_new_mbr_to_grp($user_id)";
$database->setQuery( $sql );
$database->query();

?>

 

[Wisdom Hunter]

Oh yeah, forgot to mention - I told Fabrik to process the script after the form has been processed (simple eval). I went with "after the form has been processed" because my stored proc relies on a MySQL function called LAST_INSERT_ID() to get the ID of the row that was created when the form is submitted - and of course that wouldn't be available yet if the script is processed as soon as the form is submitted ... make sense ?

 

[cheesegrits]

Yup, makes sense. Although if you have any joined data, the last insert id may not be what you expect.

Try adding some error reporting on the query, like this:

              if (!$database->query( )) {
    $err_msg = $database->getErrorMsg( ) . "\n";
    echo $err_msg;
            }

-- hugh

 

[Wisdom Hunter]

I tried what you suggested ; there was still no error message. The stored proc didn't return an error but also didn't do what I expected it to do. I then wrote a PHP script to do the same thing without going to a stored proc. I've attached it for reference. Again, it didn't do what I expected and I got no error message.
So I decided to start with a simple test script. I uploaded the attached script as <test.php> to the libs directory and used

require($mosConfig_absolute_path . "/components/com_fabrik/libs/CCSmallGroups/test.php");

... again nothing happened, no message was displayed when I submitted the form. I then placed the contents of the test.php script inside the "PHP code to execute on form submission" text box. I tried this with both "As soon as form submitted (simple eval)" and "After Fabrik has processed the form (simple eval)" - although I think I need it to execute after the form is submitted, because of the last_insert_id ... but I wanted to try all possibilities before bugging you again. I now think there may well be nothing wrong with my stored proc or the PHP script that I wrote to do the insert ... there is just some glitch with submitting PHP scripts or possibly displaying messages. Or at least, until I can display messages from a PHP script that I run on form submit, I can't evaluate where the problem lies. So : any read on what might cause a PHP script not to work on form submit ?

I can create an account for you again if you like ...

 

[Wisdom Hunter]

Below is the contents of "test.php" which still gives me no results on form submit. Any idea why ?

<?php

$user_id=$my->id ;
$per_id=$this->_lastInsertId ;
$test_msg = "User ID is $user_id". "\n";
$test_msg .= "Person ID is $per_id". "\n";
echo $test_msg;

?>

 

[Wisdom Hunter]

Can display a message from menu item, not from php script on form submit

Further to this : I tried the test.php script as a menu item of type Submit URL. I had to remove the reference to $this because it generated an error message about not being in an object context ; but I managed to get a message to display. So why can I not get a message to display from a php script that runs on form submit ? Any ideas ?
I've attached a screenshot of the current settings for on-form-submit

 

[Wisdom Hunter]

Here is the screenshot

 

[cheesegrits]

I'll log in and take a look. If I haven't replied again by the time you read this, PM me your login details again - I periodically delete all my PM's, to reduce the risk to you of someone compromising the Fabrikar server and grabbing my PM inbox. So I may not have your details any more.

-- hugh

 

[cheesegrits]

OK, the problem was that you had a "jump page" specified, which means you get redirected to that page, so the actual form submit results page doesn't get shown.

I removed the jump pages from your forms, so if you add a row to the Authentication table, you now see your SQL errors.

-- hugh

 

[Wisdom Hunter]

Hugh,
That helped - thanks. My underlying problem is not solved but at least I know what I am dealing with. Can I leave the ticket open for a bit ? Thanks.

 

[cheesegrits]

Sure.

-- hugh

 

[Wisdom Hunter]

OK, I solved it - but thought you might be interested in the issues I encountered along the way, for future support purposes.

#1 - calling a stored proc is not working, basically due to a permissions issue. The hosting package that I am working with does not give me SUPER privilege on the MySQL database, and without this, I apparently cannot specify the DEFINER as being someone other than my "root" admin user (I'll just call him "xxx" for this post). But when I install Joomla it creates a different user - xxx_joom3 in this case. So, my database connection is to the xxx_joom3 database and it is trying to call a stored proc that PHPMyAdmin created as owned by xxx. Have tried askig for help on this one with my hosting company but in the meantime decided to try something else ... see "#2"

#2 - I worked out how to do the same thing from a PHP script using database.query. It all works beautifully except for one little problem. I need the ID of the PERSON record that was just created. When I call $this->_lastInsertId in my PHP script, even though the script is supposed to be submitted after Fabrik has processed the form, it comes back NULL. If I try to run a query to return the value of the MySQL function LAST_INSERT_ID() it returns 0. I finally had to run a query to SELECT the ID of the PERSON record that had just been created by the form submission. This works but it seems odd to me that using $this->_lastInsertId or running "SELECT LAST_INSERT_ID ()" didn't work. Any comments ?

 

[Wisdom Hunter]

One more thing ... handling an array with database.query

Hugh,
So, I got the basic functionality working, but for my PHP script to do everything that my stored proc was intended to do, I actually need to do some massaging on the newly-created PERSON record in some cases. So here is my question. How do I SELECT into an array of values. I looked for examples in the Fabrik packages but so far, my attempts to do this have not worked. Anyway, I'll figure it out eventually if you don't have time for this - it's just syntax - but if you have time for a quick example I'd appreciate it.