Jump to content

Problem with Mysql Query


zyklon
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...