Problem with Mysql Query

[zyklon]

A part of my code which is supposed to do the ranking in the game i have been making isnt working Here is the code:

<?php	 include'config.php';	 $minutes = mysql_result(mysql_query("SELECT * FROM `cron` WHERE id = '1'"),0,"minutes");	 if($minutes)	 {	 $minutes--;	mysql_query("UPDATE `cron` SET `minutes`='$minutes' WHERE id = '1'") or die("MySQL ERROR: ".mysql_error());	 }	else	{	mysql_query("UPDATE `cron` SET `minutes`='4' WHERE `id` = '1'") or die("MySQL ERROR: ".mysql_error());	 mysql_query("UPDATE `user` SET `gold`=`gold`+ROUND(`land`/2),`turns`=`turns`+1, `power`=ROUND(`gold`/1000 + `troops`*100 + `land` + `generals`*20)") or die("MySQL ERROR: ".mysql_error());	mysql_query("SET @i:=0") or die("MySQL ERROR: ".mysql_error()); mysql_query("UPDATE `user` SET `rank`=(@i:=@i+1) ORDER BY `power` DESC") or die("MySQL ERROR: ".mysql_error()); }	?>

These are the line of the code that are seemingly failing/not working:

mysql_query("SET @i:=0") or die("MySQL ERROR: ".mysql_error()); mysql_query("UPDATE `user` SET `rank`=(@i:=@i+1) ORDER BY `power` DESC") or die("MySQL ERROR: ".mysql_error());

It also didnt work when it was like this:

mysql_query("SET @i=0") or die("MySQL ERROR: ".mysql_error()); mysql_query("UPDATE `user` SET `rank`=(@i:=@i+1) ORDER BY `power` DESC") or die("MySQL ERROR: ".mysql_error());

Anyways cant wait until this is figured out the game is going to be a Turn/tick-based mmorpg!

[CStrauss]

can you post what the error messeage is saying? Im not a mysql expert by far but I'm pretty good at figuring out my errors as the come along from what the mysql_error() reports back on my screen.

[zyklon]

can you post what the error messeage is saying?

nothing, thats the problem, it reports no errors at all even in errors.txt, it just doesnt work, it 's the weirdest thing, thats why i am looking for help.

[Jack McKalling]

When did this error like occurred then? Did this code ever work, or not yet ever?If the later is the case, I would suggest removing any quotes surrounding table or column names, which is not necessary But I don't claim that is the sollution to this error. :)I see you are using user defined variables don't you? I never used that before, I always use variables php-side.. Much more reliable in my opinion..

[zyklon]

i'm not sure if it ever worked...i thought it was once, and just to help... heres the structures of the table:so remove all of the ` out of the code

## Table structure for table `cron`#CREATE TABLE cron (  minutes int(2) NOT NULL default '0',  id int(1) NOT NULL default '0') TYPE=MyISAM;____________________________________## Table structure for table `user`#CREATE TABLE user (  username varchar(60) NOT NULL default '',  password varchar(32) NOT NULL default '',  email varchar(60) NOT NULL default '',  troops int(10) NOT NULL default '1',  generals int(10) NOT NULL default '0',  turns int(10) NOT NULL default '10',  gold int(10) NOT NULL default '100',  land int(10) NOT NULL default '100',  power int(10) NOT NULL default '0',  rank int(10) NOT NULL default '0',  id int(4) NOT NULL auto_increment,  new tinyint(1) NOT NULL default '1',  arm tinyint(1) NOT NULL default '1',  PRIMARY KEY  (id)) TYPE=MyISAM;

[justsomeguy]

mysql_query("SET @i:=0") or die("MySQL ERROR: ".mysql_error()); mysql_query("UPDATE `user` SET `rank`=(@i:=@i+1) ORDER BY `power` DESC") or die("MySQL ERROR: ".mysql_error());

What in the world are you trying to do with those two lines? What change are you trying to affect in the database? It looks like you set a variable to 0, and then set the rank to either 0 or 1. Does the second query increment the variable for each row that gets updated? You might want to check the manual for MySQL and make sure that the specific version you are using supports that syntax. You might also try running the query directly on MySQL, through something like PHPMyAdmin.

[zyklon]

that is ranking script, it is ranking the players and the rank is the only thing being changed for everyone, this is my cron script. i will run it on phpmyadmin, i am using phpdev423 to dev, and will either use an old linux machine or another computer with wamp on it.edit: i ran a test in phpmyadmin it said error in first line.

[justsomeguy]

edit: i ran a test in phpmyadmin it said error in first line.

That's the problem then, the MySQL server might not recognize the syntax you are using. You may have to put the queries in a loop, but that would put some minor stress on the database server.

[zyklon]

ya, and may cause the page to run very slow....

[reportingsjr]

Why not try removing the @'s so you get an error? Then tell us..

[zyklon]

i reran it in sql and it said it ran succesfully i had previously entered in wrong in phpmyadmin, bu the ranking part didnt work. it just gives them there numbeer depending on where they are in the table.

[Jack McKalling]

Why not try removing the @'s so you get an error? Then tell us..

No, the @'s over here do not have the same meaning as in occasional PHP Here, they have got something to do with (SQL) user variables Anywhere else in php, they prevent php form showing an error, but not in an sql string.

[zyklon]

I'm guessing my problem in the script resides in the second linee with my coding. Either i have to find a way to fix that or make a loop in php, which would stink.Do you think it might be all the ` and ' not in the right spots?

[justsomeguy]

The backticks are fine, you can use them to surround field names.You might try bringing this up on the MySQL forums and see if you have any luck there, there are probably more people there with knowledge of MySQL-specific issues then there are here.

[zyklon]

i'll try that, thank you.