Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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;

share|improve this question
2  
You can't give multiple queries in a call to mysql_query(). – Barmar Apr 21 at 6:10

2 Answers

up vote 0 down vote accepted

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
share|improve this answer
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 2 days ago
any ideas what I can do to change this? – topedge yesterday
I decided since this is in addition to what I was doing it needed it's own question: stackoverflow.com/questions/16173444/… – topedge 23 hours ago
up vote 0 down vote

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.