FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


[MySQL (& php)] += in SQL?





Stubru Freak
For an online game I made I regularly do things like this:

Code:
$Results=mysql_query("SELECT * FROM `map` WHERE `X` = '" . $ToX . "' AND `Y` = '" . $ToY . "'");
mysql_query("UPDATE `map" . $TimeZone . "` SET `Archer` = '" . ($Results["Archer"]+$_POST["Archer"]) . "' WHERE `X` = '" . $ToX . "' AND `Y` = '" . $ToY . "'");
$Results2=mysql_query("SELECT * FROM `map` WHERE `X` = '" . $FromX . "' AND `Y` = '" . $FromY . "'");
mysql_query("UPDATE `map" . $TimeZone . "` SET `Archer` = '" . ($Results2["Archer"]-$_POST["Archer"]) . "' WHERE `X` = '" . $FromX . "' AND `Y` = '" . $FromY . "'");


This is to move archers from spot 1 to spot 2. But I know this is an insecure way, mainly because I did manage to duplicate my units by running 2 pages at the same time.*

Isn't there a way to tell mysql to add something to a database value, like I could tell UPDATE `table` SET `number` += 2 or something?
Or another way to make this more secure

Thanks in advance,
Frederik Vanderstraeten

*(Moved the units to two map spots at the same time, and both pages first got the amount of units out of the database and then inserted that amount on two different places, duplicating them)
dandelion
As far as I know MySQL doesn't support the += operator, but you can still use the column=column+N statement as shown below.
Code:
UPDATE table SET column=column+2
Stubru Freak
dandelion wrote:
As far as I know MySQL doesn't support the += operator, but you can still use the column=column+N statement as shown below.
Code:
UPDATE table SET column=column+2


Can you? Thank you very much, didn't know that Very Happy

And a second question, is there any way to do this in one query:
Code:
$date=mysql_query("SELECT `Value` FROM `gamevars` WHERE `Name` = 'date'");
$date=mysql_fetch_array($date);
$date=$date["Value"];
if($date!=gmdate("Y-m-d H:00:00")){
   mysql_query("UPDATE `gamevars` SET `Value` = '" . gmdate("Y-m-d H:00:00") . "' WHERE `Name` = 'date'");
}

Sorry, I AM a mysql noob Razz
Rhysige
Ones a select ones an update as far as I know you cant mix them.
Stubru Freak
Rhysige wrote:
Ones a select ones an update as far as I know you cant mix them.


But I don't need the select in fact, all I have to do is edit a value if it isn't the current date.
kv
Code:
"UPDATE `gamevars` SET `Value` = '" . gmdate("Y-m-d H:00:00") . "' WHERE `Name` = 'date' AND '".gmdate("Y-m-d H:00:00")." != SELECT `Value` FROM `gamevars` WHERE `Name` = 'date'"


you don't have to do this though. Anyway, since you are updating it to current date if it is not current, you don't even have to check. Just go ahead update it. It makes an extra update but avoids the extra query.
Stubru Freak
kv wrote:
Code:
"UPDATE `gamevars` SET `Value` = '" . gmdate("Y-m-d H:00:00") . "' WHERE `Name` = 'date' AND '".gmdate("Y-m-d H:00:00")." != SELECT `Value` FROM `gamevars` WHERE `Name` = 'date'"


you don't have to do this though. Anyway, since you are updating it to current date if it is not current, you don't even have to check. Just go ahead update it. It makes an extra update but avoids the extra query.


k, that's right Very Happy thanks

But I really need to know if it changed or not, it has to execute some code if it changed. I suppose I can do that with mysql_affected_rows()...

Edit: Nvm, php.net:
Quote:
When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.
Related topics
Mysql And PHP HELP PLZ
An Example to mySQL class
Separating contents of MySQL row and put them in variables?
A very good PHP MySQL Tutorial
protecting mysql databases from sql injection attacks
(solicitud) video de manejo de sql
PHP and MYSQL on the computer
BEGINNING MYSQL and PHP
Microsoft SQL & PHP blog
cannot add data to mysql from PHP form
Error with MySQL and PHP script in my CMS
[help]Mysql & php problem
PHP and Javascript Issue handling data from a mysql query
php sql import
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.