1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.
  2. Better Analytics for WordPress Get It Free

mysql help please?

Discussion in 'MySQL' started by jamied_uk, Feb 29, 2012.

  1. #1
    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!
     
    Last edited: Feb 29, 2012
    jamied_uk, Feb 29, 2012 IP
  2. trendint

    trendint Peon

    Messages:
    52
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    trendint, Feb 29, 2012 IP
  3. GodOfTerror

    GodOfTerror Peon

    Messages:
    41
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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
     
    GodOfTerror, Mar 11, 2012 IP
  4. craig-pfd

    craig-pfd Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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
     
    craig-pfd, Mar 17, 2012 IP
  5. mark2048

    mark2048 Peon

    Messages:
    12
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    0
    #5
    There's nothing much you can do about it, since it's the host's fault. I suggest you move to a different host.
     
    mark2048, Mar 22, 2012 IP