php - Mysql with variables and count method -

first sorry if pushed in wrong place little confused first time?

i trying create sql query retrieve count of id , set in variable got strange results.

here code.

set @order_count = 0,@refund =''; select @order_count := count(id) , @order_count @refund := (subquery) refunds, if(@refund > 0,( @order_count - @refund ), @order_count ) sold, wp_shop_orders o 

here screenshot of got.

also when run command through php using mysqli returns second column empty value.

thanks

this problem addressed in mysql manual section on user-defined variables. in particular, here:

as general rule, other in set statements, should never assign value user variable , read value within same statement. example, increment variable, okay:

set @a = @a + 1;

for other statements, such select, might results expect, not guaranteed. in following statement, might think mysql evaluate @a first , assignment second:

select @a, @a:=@a+1, ...;

however, order of evaluation expressions involving user variables undefined.

in other words, despite setting value of @order_count "first" in select statement, variable returning original value (an empty string) when selected second column.

the manual continues (emphasis mine):

another issue assigning value variable , reading value within same non-set statement the default result type of variable based on type @ start of statement. following example illustrates this:

mysql> set @a='test'; mysql> select @a,(@a:=20) tbl_name;

for select statement, mysql reports client column 1 string , converts accesses of @a strings, though @a set number second row. after select statement executes, @a regarded number next statement.

to avoid problems behavior, either not assign value , read value of same variable within single statement, or else set variable 0, 0.0, or '' define type before use it.

in other words, since declare variable assigning empty string, operations on variable interpreted using string values, hence mysqli reporting first column string '125068543' instead of integer.

now, haven't told else you're doing value, scenario you've presented, can eliminate expected results setting (and referencing) variable once, so:

select @order_count := count(id) wp_shop_orders o