databasejoin advanced php

[henk]

I have a form in a module on a web page where the user can enter his name and e-mail when he wants to join an event.
This data is then stored in a submission table.
I use a databasejoin with a radio button to select an event from the events table.
The event table contains an event name, date and price. There can be several different events and one event can have several dates.
This table is updated remotely from a different server. (I use an form with the upsert plugin for that)
Every event has its own form (and web pages), so only a date needs to be selected.
In the databasejoin where box I added the event name as a string. The radio button simply shows some dates the user can select from.
After the user has saved the form an e-mail is send, informing him that he has just selected this date and some more info. So far, so good.

But what I am looking for is to add more data to the e-mail: the price and the event name from the event table.
Now I need to put the price and event name hard coded in the e-mail, but I want it automatically inserted from the event table.
Since I have the selected ID I could use a query to look it up; tried that, but I can not get that to work.
Using a php variable for the ID in the where query does not work a fixed number does work however.
It then also takes quite some time to send the e-mail.

I use some php in the advanced box of the radio button to format the date, that works great (like in the example $opt->text = $date->format('l j F Y').
So I tried to use php in the advanced box to assign a value to a variable that I can use later in the e-mail, but I could not figure that out. I changed the where clause to concat a second value, so I have date and price. Then in the advanced box I split date and price to seperate variables, format date as before and changed $opt->text to show date only. This works and now I have a new vaiable that contains the price, but how to insert this new variable in the e-mail?

Second what I would like to have is that I can have the event name in the module or link.
If I need to add another event, I need to create another form (make a copy) and change the where clause in the databasejoin element for that form and also create another e-mail. Of course I need to make another module, but I would like to have an option in the module to enter the event name. This event name would than be used in the databasejoin where clause and in the e-mail. In that way I can easely use the same form for another event.

Maybe I should follow a quite different strategy on this, but for now I could not think of any other approach.

Some idea's how to fix this are appreciated.

Henk

 

[troester]

In the email form plugin you can use php templates where you can php to collect data.
http://fabrikar.com/forums/index.php?wiki/email-form-plugin/

See /plugins/fabrik_form/email/tmpl for examples.
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/ for accessing database data

 

[henk]

Thanks for pointing me there and yes, I read this and tried several.

A query does not work if I use a php variable in the where clause. It should, but it only works with a fixed number. A php variable is not interpreted (null). I know about the correct quote's; tried several of those and also does not work.

Example:
where('fieldC = '.$myDb->quote('value'));
If I set $var = '34', then for the 'value' I cannot use $var, but I can use '34'.

Second is that a query takes too much time before an e-mail is send.

I cannot figure out how to use a variable in the email php template that was previously set in the databasejoin advanced box.

Henk

 

[cheesegrits]

Can you quote the entire code you use when tying to use a variable in the query.

$foo = 123;
$query->select('whatever')->from('sometable')->where('somefield = ' . $db->quote($foo));

... will definitely work (unless you put single quotes around $foo when quoting it). But of course it depends how you are trying to get the value for $foo.

I'm assuming you are doing this with a PHP template in the email plugin?

-- hugh

 

[henk]

Thanks Hugh for this tip; this one works.
But for $foo I now want to use the id that was selected.

$foo = '{clzpb_shop_submission___shop_date_raw}';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('id','datum','price','shop'));
$query->from('clzpb_shop_dates');
$query->where('id = ' . $db->quote($foo));
$db->setQuery($query);
$result = $db->loadRow();
echo $result[2];

Now $foo does get the value of the id (52 in this case) and the type is a string.
I did some experimenting and I found that an integer seems to be required for the query to work.
So I tried to convert $foo into an interger, but that does not work for some strange reason.
I tried (int), settype(), intval(), adding a zero, but no way I am able to get it work.
If I simply type $foo = 52; that works.
If I echo $foo = '{clzpb_shop_submission___shop_date_raw}'; this also displays 52.

I am lost here.

Henk

 

[Bauer]

Before your ->setQuery insert the line
echo $query->__toString();
or
echo $db->replacePrefix((string) $query);
if you want to cut and paste directly into PhpMyAdmin for testing.
If you're getting a fatal error - include an exit; after you echo the query.

Once you have a visible readout of the exact query being used it's easier to find and debug the exact location of any syntax errors.

Looking at what you posted here
$query->where('id='.$foo) ;
should do the trick.
(id is an integer so no quotes are needed)

Another thing is try changing
$foo = '{clzpb_shop_submission___shop_date_raw}';
to
$foo = (int)'{clzpb_shop_submission___shop_date_raw}';

That will assure that $foo is always an integer - even if the placeholder is invalid or an empty string (in which case it would be zero - but that's still better than the code blowing up on you).

 

[henk]

I just tried $query->where('id='.$foo) , but that does not work.
It makes no difference if I use this or $query->where('id = ' . $db->quote($foo));

When $foo = 52; this works. When $foo = '{clzpb_shop_submission___shop_date_raw}'; is does not.

$foo = 52; this is an interger
$foo = '{clzpb_shop_submission___shop_date_raw}'; this is a string '52'
But there is no way I can change $foo = '{clzpb_shop_submission___shop_date_raw}'; to an integer

Also adding echo $query->__toString(); does not work.
The email is not send because of a error, not even if I add exit.

Still confused ...

 

[Bauer]

I just tried $query->where('id='.$foo) , but that does not work.
It makes no difference if I use this or $query->where('id = ' . $db->quote($foo));

When $foo = 52; this works. When $foo = '{clzpb_shop_submission___shop_date_raw}'; is does not.

$foo = 52; this is an interger
$foo = '{clzpb_shop_submission___shop_date_raw}'; this is a string '52'
But there is no way I can change $foo = '{clzpb_shop_submission___shop_date_raw}'; to an integer

Also adding echo $query->__toString(); does not work.
The email is not send because of a error, not even if I add exit.

Still confused ...

Yeah I'm confused too.
did you try $foo = (int)'{clzpb_shop_submission___shop_date_raw}'; ?

I forgot you are doing this in the email plugin.
You will have to echo that to the php error log instead.
So
Before your ->setQuery insert the line try
error_log($query->__toString());
or
error_log($db->replacePrefix((string) $query));

Then check the php error log and see what you get

 

[cheesegrits]

Try ...

var_dump($foo);exit;

... after setting it, and make sure the string length that reports is 2, that there aren't any non printing characters in it.

You'll need to do this in a non AJAX context (so from a standard component / menu view) so the output goes to the browser.

-- hugh

 

[henk]

Yes I tried casting to int as several other functions I wrote before.
No way I am able to convert to an integer.

$foo = 52;
gettype($foo) gives integer 52, which is what works

$foo = '{clzpb_shop_submission___shop_date_raw}';
gettype($foo) gives string '52', which does not work

I don't see anything coming up in the php error log.
If that's the error log I can find on the server side (I use wamp server).
I set error reporting to maximum and still nothing reported.
I never worked with php error reporting though, so maybe I need to set something or look somewhere else?

Any idea's?...

 

[cheesegrits]

Did you try my suggestion of var_dump()?

-- hugh

 

[henk]

Hugh, I don't know how to get that in somewhere.
I tried to create an article with sourcerer code and made a menu item for it.
I get an undefine variable error.

Anyway, I already used checked $foo to see what type and value is gets.
For that I set $query->where('id = 52'); to make sure the email runs.

Henk

 

[Bauer]

[...]
I don't see anything coming up in the php error log.
If that's the error log I can find on the server side (I use wamp server).
I set error reporting to maximum and still nothing reported.
I never worked with php error reporting though, so maybe I need to set something or look somewhere else?

Any idea's?...

From the Joomla admin menu under 'System' 'System Information' 'PHP Information' in the 'Core' section ('Local value'' column) you will see where the php error_log is located.

If you explicitly write something to the php error log via code it will be there (unless your code had a syntax error).

 

[troester]

I tried to create an article with sourcerer code

Sourcerer doesn't know anything about Fabrik. The code examples are for Fabrik code fields or Fabrik php script/template files.
I didn't try but I'm pretty sure that a Fabrik placeholder {abc} inside Sourcerer will create a syntax error.

 

[Bauer]

I have had weird problems using the email form plugin in the past myself.
Especially when using certain placeholders that I thought would be text but were an array.
It can get really confusing with the way all the different element types store their values.

What I ended up doing was dumping the values of $this->data to the php error_log and then printing it out for reference as I wrote the code.

If you want to try that just start your code with...
error_log(print_r($this->data,1));
Then print or cut and paste that section from the error log for reference.

Then, in your php code, use the values referenced from $this->data instead of placeholders.

Now I'm just really curious as to if you find the cause and/or solution. This is a weird one.

 

[henk]

@Bauer
Yes I was looking at the correct php error log.
I dropped some text in the erro log in order to find and yes, now I see the query.

SELECT id,datum,price,shop
FROM clzpb_shop_dates
WHERE id = {clzpb_shop_submission___shop_date_raw}

Which is not working of course.
I did $foo = '{clzpb_shop_submission___shop_date_raw}';
And then if I echo $foo I get '52'. ??

Next question is how to get this working.

 

[troester]

This is why Hugh suggested the var_dump.

Try ...
var_dump($foo);exit;
... after setting it, and make sure the string length that reports is 2, that there aren't any non printing characters in it.

It seems the placeholder is only replaced in the echoed text AFTER the php code is run.
So var_dump will show something like
string(39) 52

 

[cheesegrits]

Hang on ... my brain has been in neutral ... are we talking about a PHP email template?

In which case, placeholders won't work. Placeholders can only be used in "inline" code, entered in the backend, where eval() the code directly. We don't do replacement on files, we just include them. You'll have to use $this->data[] directly, and make sure you test to see if it's an array (which a join should be):

$foo = $this->data['clzpb_shop_submission___shop_date_raw'];
$foo = is_array($foo) ? $foo[0] : $foo;

-- hugh

 

[henk]

Yes, that's what I find out now too.
Very locical too (afterwords) that placeholders don't work in the PHP email template.
I could have read that in the wiki, but somehow it seemd so easy to use the placeholder (spoiled as a am)
Anyway I learned more about how to debug.
Will try your code, Hugh. This wil probably work.

Henk

 

[henk]

Just implemented your code, Hugh. It works perfect!

Henk