Cannot query the DB with PHP using an ID in the Where clause

[Revolution10]

Dear all,

something that I cannot see and I hope it can be solved easily.

I have this query that doesn't work:

// If group is Delivery List, then query for the Address data
if ($group->title =='Delivery List'):
$IDinput = '{From_raw}';

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query
->select('address')
->from('package_location')
->where('id = ' . $db->getEscaped($IDinput));

echo $query;
$db->setQuery($query);
$fieldA = $db->loadResult();
echo $fieldA;

endif;

I echoed the query and it is correct.

SELECT address FROM package_location WHERE id = 3

If I change $IDinput value for a value inserted manually it works:

->where('id = ' . $db->getEscaped(3));

or

->where('id = 3' );

I thought then that was a format problem with the $IDinput value. So I tried to force it to INT:
$IDinput =(int) '{From}';

but it returned always 0:
SELECT address FROM package_location WHERE id = 0

By the way, From_raw is an join field. I tested with {rowID} (only to test) and the same problem...

So, I'm here blocked without knowing what to do...

Thank you very much for your help!

 

[Sophist]

{From} / {From_raw} does not seem like a placeholder to me - normally these are of the form {table___field} (3 underscores) and this seems to be a field only and not have a table.

So what is probably happening is that you are getting a "WHERE id = ``" or "WHERE id = `{From_raw}`" and then when you do (int) '{From}' this becomes (int) '' or (int) '{From}' after placeholder substitution and (int) returns a 0.

Just a guess though...

S

 

[Revolution10]

Hi Sophist,

Just tested with:
$IDinput = (int)'{deliveries___From_raw}';
echo ($IDinput);

and the echo returns 0. (without converting returns 3, that is the value cointained in the field).

thank you!

 

[Sophist]

What do you get with:

$IDinput = '{deliveries___From_raw}';
echo $IDinput;

 

[Revolution10]

It returns 3, that is the correct value. But if I use this variable (string) for the query, it doesn't returns nothing.

thanks

 

[Sophist]

Well, I have no idea why '{deliveries___From_raw}' returns 3 (or more likely '3') but (int)'{deliveries___From_raw}' returns 0. Sorry, but it doesn't look like I can help further.

 

[Revolution10]

Hi,

thanks for the answer. I hope somebody can help me!

 

[troester]

"3" may be a HTML formatted one eg <span>3</span>
use var_dump($IDinput) instead of echo, this will show the string length.

This would explain why (int)$IDinput is 0 and id=$IDinput is not matching.

Which exact fabrik version are you running?

 

[Revolution10]

Hi,

I've tested with var_dump($IDinput) and this is the result:

string(23) "2"

the Fabrik version is 3.1rc1

thanks!

 

[Sophist]

Hmm. string appears to be length 1 but is apparently length 23.

So we know why you are getting (int) = 0 but not why the string is something other than a number.

What we don't know is what the other characters are. I think that jDump will escape any html and show it to you BTW, so that could be one way to find out what the string really is.

Otherwise, I guess we need to look at the definition of the deliveries___From element. What can you tell us about this?

S

 

[Revolution10]

Hi,

this is a database join element, rendered as drop-down that links with the ID of another list. But, to discard that the problem is with this element, I've checked with the ID element, that is an internal ID element:
And this is the result
string(21) "1", and getting (int) = 0 too...

thanks

 

[troester]

I'm pretty sure string(21) is containing the link to details.

The question is, why this seems to be the same with deliveries___From_raw, which should really contain the value only (same with id).
I think this may be a bug with element/element_raw, there are some more post about this.

 

[Revolution10]

Hi,

well, this would explain it. Do you know if there is any workaround to get the value as integer?

thanks!

 

[troester]

You can try
http://php.net/manual/en/function.strip-tags.php

 

[Revolution10]

Hi,
Thanks for helping!

I've tried with it:

$IDinput= strip_tags($IDinput);
var_dump ($IDinput);

And still getting:
string(21) "1"

And forcing to (int) still giving 0.

May be doing something wrong?

Thank you!

 

[troester]

strip_tags should do in case of HTML tags.
Did you try Sophist's jDump?

Maybe it's not containing the link to details but the placeholder string itself and then later the echoed/var_dumped string is parsed through placeholder - I can remember such an issue, but I can't remember in which context.
This could be proved with str_replace("{","x",$IDinput)

Where do you use your code?

 

[Revolution10]

Hi,

I try this code in a template for the detail view, I'm trying to create a customize view to create a printout for a delivery note. I'll find info about jdump (i've never used it) and try the str_replace.

Thank you!

 

[Revolution10]

Hi,

this is the result of jdump:


and using '{deliveries___id}' without _raw gives the same result...

But I dumped the compete element:



may be there is a way to extract the value from the propierty of the element directly... I mean, access to the array elements of the group and extract the raw value from From field....

 

[Revolution10]

Hi,

for the moment I've solved it accessing directly to:

$IDinput=$group->elements[From]->element_raw;

I suppose that really is a bug accessing to the placeholder!

Thank you very much for your help!

 

[Sophist]

All the jDump suggests to me is that you have not got the placeholder name correct.