SQL Query in Page - issue

[tajhay]

Hi guys,
following on from this thread https://xenforo.com/community/threa...te-to-display-data-from-a-mysql-query.211452/ i am trying to create a page to display the contents of a SQL Query. When i put the following code in the page, i get the error message when trying to save the page :
Oops! We ran into some problems.
Line 3: Expected valid expression. - Template name: public:_page_node.80:

<table border=1 cellspacing=1 cellpadding=1>
<tr><th>ID</th><th>Warrior</th><th>Warrior #</th></tr>
<xf:foreach loop="$xf.app.db.fetchAll('SELECT
    xf_xa_sc_item.item_id,
    xf_xa_sc_item.title,
    xf_xa_sc_item_field_value.field_value
FROM
    xf_xa_sc_item
JOIN xf_xa_sc_item_field_value ON xf_xa_sc_item.item_id = xf_xa_sc_item_field_value.item_id
WHERE xf_xa_sc_item.category_id in (2,4)
and  xf_xa_sc_item_field_value.field_id = 'Warrior_No'
ORDER BY
    LENGTH(
        xf_xa_sc_item_field_value.field_value
    ),
    xf_xa_sc_item_field_value.field_value;')" value="$row">
<tr>
<td>{$row.item_id}</td>
<td>{$row.title}</td>
<td>{$row.field_value}</td>
</tr>
</xf:foreach>
</table>

As soon as i remove the line

and xf_xa_sc_item_field_value.field_id = 'Warrior_No'

it saves successfully - however without giving the results i want. Any assistance would be greatly appreciated.

 

[pattycake2]

grab the exact sql of the broken one, and stick it into phpmyadmin and see what it does.
I'm guessing it has to do with too many single quotes.

Change this:
and xf_xa_sc_item_field_value.field_value = 'Warrior_No'

to this:
and xf_xa_sc_item_field_value.field_value = ''Warrior_No''

Those are not double quotes... they are two single quotes around Warrior No

Or, maybe escape the single quote like this:
and xf_xa_sc_item_field_value.field_value = \'Warrior_No\'

Will that work?

if not, go here:

How to escape apostrophe (') in MySql?

The MySQL documentation says that it should be \'. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

stackoverflow.com

 

[tajhay]

to this:
and xf_xa_sc_item_field_value.field_value = ''Warrior_No''

tried that with single quotes as well as the escape single quotes. no good.

phpmyadmin gives results using my query


However for some reason it says its not a valid expression when in xenforo pages.

 

[Kirby]

<table border=1 cellspacing=1 cellpadding=1>
<tr><th>ID</th><th>Warrior</th><th>Warrior #</th></tr>
<xf:foreach loop="{{ $xf.app.db.fetchAll('
    SELECT xf_xa_sc_item.item_id, xf_xa_sc_item.title, xf_xa_sc_item_field_value.field_value
    FROM xf_xa_sc_item
    JOIN xf_xa_sc_item_field_value ON (xf_xa_sc_item.item_id = xf_xa_sc_item_field_value.item_id)
    WHERE xf_xa_sc_item.category_id IN (2, 4)
        AND xf_xa_sc_item_field_value.field_id = \'Warrior_No\'
    ORDER BY LENGTH(xf_xa_sc_item_field_value.field_value), xf_xa_sc_item_field_value.field_value
') }}" value="$row">
<tr>
<td>{$row.item_id}</td>
<td>{$row.title}</td>
<td>{$row.field_value}</td>
</tr>
</xf:foreach>
</table>

 

[tajhay]

<table border=1 cellspacing=1 cellpadding=1>
<tr><th>ID</th><th>Warrior</th><th>Warrior #</th></tr>
<xf:foreach loop="{{ $xf.app.db.fetchAll('
    SELECT xf_xa_sc_item.item_id, xf_xa_sc_item.title, xf_xa_sc_item_field_value.field_value
    FROM xf_xa_sc_item
    JOIN xf_xa_sc_item_field_value ON (xf_xa_sc_item.item_id = xf_xa_sc_item_field_value.item_id)
    WHERE xf_xa_sc_item.category_id IN (2, 4)
        AND xf_xa_sc_item_field_value.field_id = \'Warrior_No\'
    ORDER BY LENGTH(xf_xa_sc_item_field_value.field_value), xf_xa_sc_item_field_value.field_value
') }}" value="$row">
<tr>
<td>{$row.item_id}</td>
<td>{$row.title}</td>
<td>{$row.field_value}</td>
</tr>
</xf:foreach>
</table>

you champion! It worked! Is the only difference the quote escape? thankyou so much Kirby!

 

[pattycake2]

you champion! It worked! Is the only difference the quote escape? thankyou so much Kirby!

I thought you said you had already tried to escape the single quote and it didn't work?
btw, the reason it worked in phpadmin but not in xf is because the sql in phpadmin did not have a single quote at the beginning of your SELECT statement where your script does.

<xf:foreach loop="{{ $xf.app.db.fetchAll('SELECT xf_.....

 

[tajhay]

I thought you said you had already tried to escape the single quote and it didn't work?
btw, the reason it worked in phpadmin but not in xf is because the sql in phpadmin did not have a single quote at the beginning of your SELECT statement where your script does.

<xf:foreach loop="{{ $xf.app.db.fetchAll('SELECT xf_.....

I did try it. Kirby's sql also had the join condition ON in brackets as well.

 

[pattycake2]

gotcha