exp:query Question - SQL is returning a value using the DB Query Form, but nothing on front end...
Development and Programming
I need to pull a number from the database. This used to work as expected, and all other numbers are being pulled correctly.
I realized I needed to restrict this query result, so I added an INNER JOIN - which also returned the correct value in the DB Query Form - and it started returning a zero.
Since then, no matter what I do, even restoring the query to its original state, all I get from it is zero. Even when I copy/paste a working query into the tag, it still returns zero. Here is the select statement I’m trying:
SELECT count(*) AS games FROM exp_channel_data WHERE field_id_8 = '{last_segment}'When I test this in the DB Query Form, I substitute ‘April’ for ‘{last_segment}’ and I get results. On the HTML page, zero.
I actually just hard-coded the EXACT sql statement that returns results in the Query Form into the template, and I’m still getting a zero.
Here is the surrounding code:
{exp:channel:form channel="live_point_totals" status="open" return="battle_reports/show_scores/{last_segment}"}
<input name="title" type="hidden" value='{current_time}'>
<input name="url_title" type="hidden" value='url_title{current_time}'>
<table border="0" valign="top" align="right" cellpadding="1">
<!--standard games played-->
<tr>
<td align="right">Standard Battles:</td>
<td width="10" align="right">
{exp:query sql="SELECT count(*) AS games FROM exp_channel_data WHERE field_id_8 = '{last_segment}'"}
{games}
{if games==0}
<font color="#0000A0">{games}</font>
{if:else}
<font color="#FFFF00">{games}</font>
{/if}
<input name="01_standard_games" id="01_standard_games" type="hidden" value='{games}'>
{/exp:query}
</td>
</tr>
<!--epic game played-->
<tr>
<td align="right">Epic Battle Bonus:</td>
<td align="right">
{exp:query sql="SELECT count(*) AS epic FROM exp_channel_data WHERE field_id_39 = '{last_segment}'"}
{if epic>0}<font color="#FFFF00">3</font>
<input name="02_epic_game_bonus" id="02_epic_game_bonus" type="hidden" value='3'>
{if:else}<font color="#0000A0">0</font>
<input name="02_epic_game_bonus" id="02_epic_game_bonus" type="hidden" value='0'>
{/if}
{/exp:query}
</td>
</tr>The ‘games’ query returns zero, no matter what. The ‘epic’ query returns the correct non-zero number. I went ahead and used my actual code, to not muddy the waters.
I’m absolutely stumped and convinced I’m missing some stupid technical detail. But I’ve been staring at it 2 hrs last night and 1 today after a break. No idea what’s happening, but this breaks the entire site…
Standard vs. Epic Battles? Games? Sounds like there’s some fun going on! 😊 I’m inclined to think tabletop wargaming. Am I close?
Is it possible that there is a variable collision with {games} (custom field name, template partial name, template variable name, etc.)? Simply changing it from games to standard_games in your query and your template tags might do the trick. If that doesn’t resolve it I’d suggest creating a new template with just the query tag in it. I’d start with hardcoding the value of field_id_8. When that works, then I’d return it to {last_segment} to ensure that part is working. If all of that works then we’ll have to take another look at this template.