Thread

Hi,

I’ve made a custom query trying to get the amount of products sold:

{exp:query sql="SELECT SUM(field_id_111) AS total_sold FROM exp_weblog_data WHERE field_id_110 = '{embed:emb_entry_id}' AND weblog_id = 17"}
    {if no_results}
        0
    {/if}
    {total_sold}
{/exp:query}

field_id_111 - quantity field
field_id_110 - item id field

this query works fine, as soon as there is field_id_111 or field_id_110… but if they are empty it will display {total_sold} instead of “0”

here’s the corresponding thread:
http://cartthrob.com/forums/viewthread/2175/P0/

does anybody knows why it’s displaying {total_sold} instead of “0”?

cheers
stefan

My guess would be that if a query doesn’t bring back any results then none of the variables that you have explicitly set up such as your {total_sold} variable will work as ExpressionEngine doesn’t by itself know what these are and so will return a NULL result much like in MySQL itself.

I’m not sure if there’s a way of telling a SQL query to make a default variable though. Hopefully someone can help with that if it is possible.

Best wishes,

Mark

My guess would be that if a query doesn’t bring back any results then none of the variables that you have explicitly set up such as your {total_sold} variable will work as ExpressionEngine doesn’t by itself know what these are and so will return a NULL result much like in MySQL itself.

I’m not sure if there’s a way of telling a SQL query to make a default variable though. Hopefully someone can help with that if it is possible.

Best wishes,

Mark

I am surprised that the {if no_results} is not working though…
maybe sql is generating an error, and therefore it’s not returning NULL but something else that the {if no_results} cannot handle?
but - I am very new to queries… it would be nice if somebody could shed some light!

Have you tried running the query with some known values in something like PHPMyAdmin or a query application and seeing what is sent back?

Might help narrow it down a bit. You could also try putting on the Output Profiler to make sure the query is being set correctly also.

Best wishes,

Mark

Hi Stefan,

Mark’s suggestions are excellent. Please do make sure that you are testing this with fixed values initially to verify that your code work as you expect. You should also be able to verify that it is working correctly with the output profiler. If your query is working correctly the if no_results conditional should also be working properly. Let us know what happens when you hardcode some existing values into the SQL.

Cheers

Greg

@Mark, thanks very much for the help.

Hi Stefan,

Mark’s suggestions are excellent. Please do make sure that you are testing this with fixed values initially to verify that your code work as you expect. You should also be able to verify that it is working correctly with the output profiler. If your query is working correctly the if no_results conditional should also be working properly. Let us know what happens when you hardcode some existing values into the SQL.

Cheers

Greg

@Mark, thanks very much for the help.

hi greg and mark,

I also found marks tipps very valuable…

so I’ve made a few queries with entries that work and entries that don’t…
so the ones that spell out {total_sold} in expression engine are of value “NULL” in MySQL…

that lead me to the following statement:
{if "{total_sold}” != “NULL”}{total_sold}{/if}
and: to my surprise - this works!

so the {no_results} thing is not really working as it is supposed to?
I am very new to queries though!

thanks mark! thanks greg…

@greg maybe you could post this in the bug tracker….

Hey outline, now that you have a solution to the initial problem, let’s have you post the other problem in a new thread, just to keep it separate. Closing this one in the meantime.