smd_query: txp tags in queries

ironmangary · 2022-10-25 17:55:56

In the site I’m building, each article is an athlete. I want to create a table of the player’s salary for each year. I created a table in my MySQL database called “wma_salaries” with the columns, user_id, year, and salary. I want to pull the year and salary where the table’s user_id field equals the article ID of the current article being processed. In my article form, I have this code:

<txp:smd_query query="select year, salary from wma_salaries where user_id='<txp:article_id />'" label="Salary History" labeltag="h4" wraptag="table" break="tr">
<td>{year}</td><td>{salary}</td></txp:smd_query>

But I get errors when I run the code:

Tag error: <txp:smd_query query="select year, salary from wma_salaries where user_id='<txp:article_id />'" label="Salary History" labeltag="h4" wraptag="table" break="tr">
<td>{year}</td><td>{salary}</td></txp:smd_query> ->  Warning: count(): Parameter must be an array or an object that implements Countable while parsing form wrestler on page profile_wrestler

I was hoping escaping would work like default core txp tags, but I’m obviously either doing something wrong or this can’t be done. Is there a different/better way of doing this? Thanks!

EDIT: added bc. for code formatting.

Dragondz · 2022-10-25 21:22:04

Hi
It s a bug on smd_query with recent version of php (php > 7.3 if i am not mistaken)
If you can choose a php version 7.1 the error will disappear, you can also correct the plugin, look at github i raised a ticket and offered a correction but got no response from Stef (Stef is awsome i suspect he missed that).
Cheers

Bloke · 2022-10-25 21:42:09

Ack, I missed this, sorry Dragondz. I’ll have to try and fix it, though I need to support PHP <7.3 somehow.

In the meantime, ironmangary, I don’t think the parser is doing quite what you expect. I suspect it’s trying to process the tag-in-tag verbatim which then returns an empty set, which then triggers the error. Have you tried using the variable ?ID instead?

<txp:smd_query query="select year, salary from wma_salaries where user_id=?ID" ...>

That should match the current article ID if you’re in individual article context. So be careful if you’re not, because it’ll fail and throw an error. You can defend against that by wrapping the entire construct in a <txp:if_individual_article> container.

Hope that helps.

ironmangary · 2022-10-25 23:25:41

That fixed it. Slick. I love this plugin! Thanks!

ironmangary · 2022-10-31 18:30:31

Hi,

I’m revisiting this thread because I’m getting inconsistant results. After receiving the above advice from Stef, I created the below, which works great:

page: profile

<txp:output_form form="wma_header" />

<txp:if_individual_article>
  <txp:article form="profile_all" />
</txp:if_individual_article>

<txp:output_form form="wma_footer" />

form: profile_all

<h4>Personal Information</h4><br><br>
Real Name: <txp:custom_field name="profile_real_name" /><br><br>
<br><br>
<txp:smd_query query="select salary_year, salary_amount from wma_salaries where aid=?ID" label="Salary History" wraptag="table" break="tr">
<td>{salary_year}</td><td>{salary_amount}</td></txp:smd_query>

So, I tried reproducing the process on another page.

page: event

<txp:output_form form="wma_header" />

<txp:if_individual_article>
<txp:article form="event" />
</txp:if_individual_article>
<txp:output_form form="wma_footer" />

form: event

<center><h3><txp:title /></h3></center>
<br><br>
<h4>Details</h4>
<br><br><ul>
<li>Date: <txp:posted />
<li>Location: <txp:custom_field name="event_venue" />
</ul>
<br><br>
<h4>Card</h4><br><br>
<ul>
<txp:smd_query query="select header, participants from wma_matches where aid=?ID order by match_num desc">
{header}&nbsp:&nbsp{participants}</txp:smd_query></ul>
<br><br>
<h4>Event Summary</h4><br><br>
<txp:body />

The result is this error:

Tag error: <txp:smd_query query="select header, participants from wma_matches where aid=?ID order by match_num desc">
{header}&nbsp:&nbsp{participants}</txp:smd_query> ->  Textpattern Error: Unknown column 'ID' in 'where clause' while parsing form event on page event

I ran the sql query directly on the database, and it’s fine. It’s not parsing the ?ID properly in the “event” form, but it is in the “profile_all” form, but I can’t figure out why. I’m pretty confident it’s something I’m doing, but what?

(If I get this working, I do have one follow-up question.)

Thanks!

EDIT: added bc. for code formatting.

Bloke · 2022-10-31 18:45:53

Hmmm, assuming you have articles in your event section and your URL is definitely triggering article context (otherwise ID won’t be set) then it might be a byproduct of the way the plugin cheats and populates the article array. It might not be detecting it properly. You can try and force the plugin to treat your content as an article by supplying populate="article" as an attribute.

Failing that, I’d switch debug on. You can supply debug="2" or maybe 3 or 4 (I can’t remember how far I went) and it’ll splurge stuff to the screen. The highest level of debugging should output what the plugin thinks is in the article arrays so if they show up as NULL or empty then we can at least work back from there to see why it’s breaking.

ironmangary · 2022-10-31 18:59:44

The populate attribute made no difference, so I set debug=“5” and got this:

++ URL FILTERS ++


++ DEFAULTS ++
array (
)
select header, participants from wma_matches where aid=ID order by match_num desc
Tag error: <txp:smd_query query="select header, participants from wma_matches where aid=?ID order by match_num desc" debug="5">
{header}&nbsp:&nbsp{participants}</txp:smd_query> ->  Textpattern Error: Unknown column 'ID' in 'where clause' while parsing form event on page event
Tag error: <txp:smd_query query="select header, participants from wma_matches where aid=?ID order by match_num desc" debug="5">
{header}&nbsp:&nbsp{participants}</txp:smd_query> ->  Warning: count(): Parameter must be an array or an object that implements Countable while parsing form event on page event

For additional debugging, I added this right aboove the smd_query stuff:

<h5><txp:article_id /></h5><br>

…and that returned “57,” the number I was expecting.

I have no idea what is different between “profile” and “events.”

EDIT: added bc. for code formatting.

Bloke · 2022-10-31 19:34:22

Odd. How does the debug output differ from that of the profile page? It seems to know it’s in article context but is not populating the article content to use in the query, for some reason.

Presumably then, the thing works if you hard code an actual ID like 57 in the form?

ironmangary · 2022-10-31 20:08:44

Yes, if I hard code the article ID in the query, it gives me the expected results. As for the debug output on the profile page, it is correctly parsing the article ID…

array (
)
select salary_year, salary_amount from wma_salaries where aid=2
++ QUERY RESULT SET ++
1 ROWS

I’m at a loss.

EDIT: added bc. for code formatting.

Dragondz · 2022-11-01 07:01:00

Hi

When i use query i use simple quotes on value fields ‘?ID’

<txp:smd_query query=“select header, participants from wma_matches where aid='?ID' order by match_num desc”>

I dont know if that could help, but it s worth try.

Cheers.

ironmangary · 2022-11-01 14:35:11

Definitely worth a shot…the single quotes fixed it! Thank you!

Bloke · 2022-11-01 18:30:31

Okay now that’s strange because the only reason it would ‘fix’ it is if the value coming from the ID field is empty or non-numeric.

Essentially the error goes away because it turns this invalid construct:

SELECT stuff FROM something WHERE some_id=;

To:

SELECT stuff FROM something WHERE some_id='';

Which is valid and will pull out content from the database where the ID field is blank. Are you sure you’re getting the content in from the correct article?

If it works, great. Though I cannot fathom why it would make any difference when dealing with numeric fields. Maybe if it was a text field and you’re trying to treat as numeric, it’ll throw a wobbly without the quotes. Dunno.

Textpattern CMS

Textpattern CMS support forum

#1 2022-10-25 17:55:56

smd_query: txp tags in queries

#2 2022-10-25 21:22:04

Re: smd_query: txp tags in queries

#3 2022-10-25 21:42:09

Re: smd_query: txp tags in queries

#4 2022-10-25 23:25:41

Re: smd_query: txp tags in queries

#5 2022-10-31 18:30:31

Re: smd_query: txp tags in queries

#6 2022-10-31 18:45:53

Re: smd_query: txp tags in queries

#7 2022-10-31 18:59:44

Re: smd_query: txp tags in queries

#8 2022-10-31 19:34:22

Re: smd_query: txp tags in queries

#9 2022-10-31 20:08:44

Re: smd_query: txp tags in queries

#10 2022-11-01 07:01:00

Re: smd_query: txp tags in queries

#11 2022-11-01 14:35:11

Re: smd_query: txp tags in queries

#12 2022-11-01 18:30:31

Re: smd_query: txp tags in queries

Board footer