mysql help please?

Hello there to everyone on the page i am fairly new to mysql coding and need help adding to this code below that i have made so that it checks if available amount in a table before it can level up (on this game i have made from scratch), as you can see at present it can go into the minus because theres not in place a check to see if available like an if statement and i dont know the correct syntax and struggling with this now for ages and im sure experts here will know what small bit i need to add, as i would nee it to be all on one line to keep it simple if possible that is.

 $data = mysql_query("UPDATE `users` SET `balance` = `balance` - 1000 WHERE `users`.`id` =$id") 

Code (markup):

anyone that has any ideas what i can do here no matter how simple i will test it and publish the whole lot to anyone that wants it its a part that allows a user to level up upon using there points gained by visiting the site, this is not for real money or anything but i surpose it could be used for similer uses in the futer but currently upon visiting a page on my site it gives 1000 (free) points) that can be used to level up etc, and later on the plan is to create more things they can spend them on but not got that far yet as its only free points (or free money) it will take some thinking hope to have a reply to this thread and as i said will publish the code i use in its entirety and also if you want i can show you a working version of this and also supply if needed a sql file for the table layout, reply asking for anything you may need or inbox me for a working demo of it aswel if required or just interested.


i see visiters visiting, can you sign up and respond if you think you can help, it will be much apreciated!

 

While I would recommend performing the calculation outside of the SQL statement, you can indeed use flow control functionality within SQL:

$data = mysql_query('UPDATE users SET balance = IF(SIGN(balance - 1000) <= 0, 0, balance - 1000) WHERE id = "' . mysql_real_escape_string($id) . '"');

Code (markup):

The IF() function works like this: IF(CONDITION, TRUE, FALSE)
In the example, for the condition I am using "SIGN(balance - 1000) <= 0" - this will check what the sign of the equation is; i.e. -1 means a negative number, 0 means 0 and 1 means a positive number; once we know the sign of the equation we check if the value is less or equal to 0.
If this is the case (i.e. the condition is true), we want to set the value to '0', if the condition is false, then we perform the equation.
This ensures that the value will always be 0 or greater. This does not however return a remainder if for example the available balance was 900, since it would set the value now to 0 without considering the remaining 100 that was missing.

So back to my original suggestion - perform the checking outside of SQL using PHP and determine if they have enough points to deduct the balance.

 

Simply use MYSQL for simple things.
if something needs to be added up, subtracted, etc.

get the data from mysql and then process it via your choice of
programming language such as python, php, c++, etc...

and then update the mysql row. ( DATA )

it may seem like this makes things extra-long but, not touching
the MYSQL database ensures the mysql_database is smoothly
running for the web site's visitors.

also it ensures clarity.

it's kind of like using 2 different plates for sliced watermelon
and chocolate ice cream.

rather than placing the both into the same plate.

it's possible but, why do it?

DON'T DO IT!

- Sumer Kolcak

 

Hi all,

I am very much new to this forum. Just wanna ask that my blog is having a problem with "Error establishing database connection" day and day out.

I checked everything. All seem okay. When I go into MySQL database via my cPanel, it shows " MySQL server is running offline. I do not see any reasons on my blog that can corrupt anything. I contacted my web host but they seem to have no answer to this. My blog goes up and down thruout the day. Any help on this will be highly appreciative.

Thanks

Craig

 

There's nothing much you can do about it, since it's the host's fault. I suggest you move to a different host.