Passing a Mysql variable through a mysql query

Databases
1

Dear all,

I’m not sure if the MySQL forum or the PHP is the best place for this query, but have plumbed for here.

I have SQL code that results in the correct output when run in phpMyAdmin

set @csum := 0;
Select Week, Competitor, (@csum := @csum + Points) as 'Cum points'
from(
SELECT weekRefTest.week_no as Week, users.user_name as Competitor, SUM(data14.points) as Points
 FROM weekRefTest
INNER
 JOIN data14
   ON data14.player_id = weekRefTest.player_id
  AND data14.week = weekRefTest.week_no
INNER JOIN users on weekRefTest.user_id = users.user_id
where users.user_name = 'Mike'
GROUP
   BY weekRefTest.week_no
   order by Week)b

However, when I try to push this through a mysql_query:


$mike = "set @csum := 0;
Select Week, Competitor, (@csum := @csum + Points) as 'Cum points'
from(
SELECT weekRefTest.week_no as Week, users.user_name as Competitor, SUM(data14.points) as Points
 FROM weekRefTest
INNER
 JOIN data14
   ON data14.player_id = weekRefTest.player_id
  AND data14.week = weekRefTest.week_no
INNER JOIN users on weekRefTest.user_id = users.user_id
where users.user_name = 'Mike'
GROUP
   BY weekRefTest.week_no
   order by Week)b";

it results in the following “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Select Week, Competitor, (@csum := @csum + Points) as ‘Cum points’ from( SELEC’ at line 2”.

Can you use MySql variables using mysql_query or do you have to use a different method?

I’d be grateful for any help you could offer.

Mike

2

the error message points to the exact spot where it barfed – right after the semi-colon ending the first statement

apparently you cannot submit more than one statement that way

3

Hi,

Thanks for the reply. Yes, digging around online last night it became clear that I needed to break the query into two to get it to work (i.e. run mysql_query on set @csum := 0; and then again on the main query), this appears to have resolved it.

Cheers

Mike