PHP MYSQL SET gives error in while loop

Question

I have this query:

$result2 = mysql_query("SET @total=0;
SELECT *,
@total:= @total+ `companyearned` AS `total`
FROM `recordedhours` WHERE `group` = '$uid'
ORDER BY `unixdate` DESC, `idnum` DESC
LIMIT $from, $max_results");

while ($rowb = mysql_fetch_array($result2)) {
//DO STUFF
}

But the SET @total=0; makes the while line give me an error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in The query works fine in phpmyadmin and the while works fine without the SET @total=0;

You can't give multiple queries in a call to `mysql_query()`. — Barmar, Apr 21 '13 at 06:10

score 0 · Accepted Answer · answered Apr 21 '13 at 06:11

0

As you can not use more than one queries in mysql_query(), But you can combine both your query into a single one.

Try this query..

SELECT *,
@total:= @total+ `companyearned` AS `total`
FROM `recordedhours`, (SELECT @total:=0) r WHERE `group` = '$uid'
ORDER BY `unixdate` DESC, `idnum` DESC
LIMIT $from, $max_results

answered Apr 21 '13 at 06:11

Ashwini Agarwal

4,828
2
42
59

Thanks for your help that worked for that issue but it is not giving the proper results when listed. I am trying to list two numbers. The companyearned and the total as it goes. And it should have the oldest results on the bottom. That is working fine for the companyearned, but total is going in the reverse order. This this is what I am getting: `$-23.22 $-23.22; $-20.22 $-43.44; $300.00 $256.56; $-6.00 $250.56;` and this is what I want: `$-23.22 $181.20; $-20.22 $204.42; $300.00 $224.64; $-6.00 -75.36; (which has brought in the total from all pages up until this point)` – topedge Apr 22 '13 at 15:15
any ideas what I can do to change this? – topedge Apr 22 '13 at 22:29
I decided since this is in addition to what I was doing it needed it's own question: http://stackoverflow.com/questions/16173444/php-mysql-sum-total-but-show-rows – topedge Apr 23 '13 at 15:33

score 0 · Answer 2 · answered Apr 21 '13 at 06:16

0

Use two calls to mysql_query():

mysql_query("SET @total=0");

$result2 = mysql_query("SELECT *,
@total:= @total+ `companyearned` AS `total`
FROM `recordedhours` WHERE `group` = '$uid'
ORDER BY `unixdate` DESC, `idnum` DESC
LIMIT $from, $max_results");

I think the variable should persist since it's the same database connection.

answered Apr 21 '13 at 06:16

Barmar

741,623
53
500
612

it should be `mysql_query("SET GLOBAL @total=0");` – Amir Apr 21 '13 at 06:20
@Amir This is just a session variable. Only users with the SUPER permission can set global variables. – Barmar Apr 21 '13 at 06:22

PHP MYSQL SET gives error in while loop

2 Answers2

Linked