Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Update DB field to decrement its value

    Hi, I am just trying to use this as a test script to use on something larger, but I am having trouble on working out how to do this.

    Firstly the database connection script, is included in the file and it connects with no problems.

    In my database table, there is a field called credits. At the moment I have given a user 25 credits just to test this script.

    Here is the form that takes the users username and a field where for example I could type 4 which should change the value in the db to 21.

    Here is the form

    PHP Code:
    <?php 
    include 'core/init.php';
    protect_page();
    include 
    'includes/overall/header.php'?>

    <?php

    if (isset($_GET['success']) === true && empty($_GET['success']) === true) {
        echo 
    'Your account has now been debited.';
    } else {
        if (empty(
    $_POST) === false && empty($errors) === true) {
            
    $update_credits = array(
                
    'credits'         => $_POST['credits']
                );
            
            
    update_credits($update_credits$username);
            
    header('Location: sell.php?success');
            exit();
            
        } else if (empty(
    $errors) === false) {
            echo 
    output_errors($errors);
        }
        }
    ?>


          <h1>Sell</h1>
          <p></p>
          
        <form action="" method="post">
        <ul>
        <li>Username:
        <input type="text" value="<?php echo $user_data['username']; ?>">
        </li>
        <li>Sell:
        <input type="text" name="credits">
        </li>
        <li><input type="submit" value="submit">
        </ul>
        </form>
          

          
    <?php include 'includes/overall/footer.php'?>
    This calls the function update_credits and passes the values ($update_credits, $username); to my users.php file which should update the database, decrementing the credits, depending on the number entered.

    Code:
    function update_credits($username, $update_credits) {
    	$username = sanitize($username);
    
    mysql_query("UPDATE `users` SET `credits` = credits - '$credits' WHERE `username` = '$username'");
    }
    The script acts as if it has processed the form and it is successful, but the credits still remain the same.

    Any ideas on a solution are very much appreciated

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This is a PHP issue.
    Where are you providing the update_credits function with $credits variable? The only variables available to this function are $username and $update_credits.
    Also, don't treat it as a string. If its a number it should be handled as such in the query. Mysql is non-strict by default, but can be forced into strict mode where the datatypes will reject invalid types instead of converting them.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi, the $credits variable is provided here

    Code:
    $update_credits = array( 
                'credits'         => $_POST['credits'] 
                );
    As for

    'Also, don't treat it as a string. If its a number it should be handled as such in the query.'

    $credits cannot be a number in the query as it is a variable with different values each time. Therefore the query has to contain a variable and not a number.

    However I changed it to this

    Code:
    function update_credits($username, $update_credits) {
    	$username = sanitize($username);
    	$credits1 = $update_credits['credits'];
    
    mysql_query("UPDATE `users` SET `credits`='$credits'-'$credits1' WHERE `username` = '$username'");
    
    }
    But still end up with nothing happening.

    Also, can you tell me what you mean by

    'Mysql is non-strict by default, but can be forced into strict mode where the datatypes will reject invalid types instead of converting them.'

    I am not sure how or if I need to do this.

    Many Thanks

    Paul

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    $update_credits is an array; $credits still doesn't exist. An associative offset is not the same as a variable and cannot be referenced as such (unless its a referenced variable or object offset within the array in which case either are valid, but the variable still requires the correct scope).

    You cannot subtract strings, so therefore they cannot be strings. These are numbers.

    PHP Code:
    function update_credits($username$update_credits) {
        
    $username sanitize($username);
        
    $credits = (float)$update_credits['credits'];

    mysql_query("UPDATE `users` SET `credits`= `credits` - $credits WHERE `username` = '$username'");


    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi Fou-Lou, thanks for your reply. I chnaged the code to what you supplied but this does not appear to work either

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Add the or die(mysql_error()); to it and see if it fails. If it doesn't produce any errors, than the where condition isn't valid OR $_POST['credits'] isn't cast-able into a float.

    Edit:
    Just noticed here: your arguments are backwards as well.
    Last edited by Fou-Lu; 12-29-2013 at 06:29 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,059
    Thanks
    2
    Thanked 317 Times in 309 Posts
    Sorry to derail this thread, but you would NOT program a credit/debit script this way, where you add or subtract an amount directly with the total. You would have a transaction table (think of your bank or credit card account) where you insert a row for each credit (a + amount) and each debit (a - amount). Then to find the current balance you would simply sum up the rows for any particular user.

    By adding or subtracting an amount directly with the total, you no longer know if there were any multiple posting as you don't have any record of what transactions occurred to produce that total.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #8
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi CFMaBiSmAd, thanks for your reply. No need to apologise; finding the correct way to do this, would be very helpful.

    I kind of understand what you mean. Here is what I think, please tell me if I am correct

    1. Create a new table called transactions
    2. Create 2 columns - username, credits

    Now here I get confused, say a user used 2 credits, how would I write the query so that it deletes 2 credits or rows for that user?

    Also if a user purchased 10 credits, how would I make it so it adds 10 credits or rows for that user?

    I can work out how to count the numbers of credits for a selected username, but other than that I really am stuck.

    Your help is much appreciated, I have done a couple of courses but cant find anything that covers this specifically.

    Paul

  • #9
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,059
    Thanks
    2
    Thanked 317 Times in 309 Posts
    You would want your table to have a few more columns -

    table - id, user_id, posted_by, amount, datetime, ip, memo

    id - auto-increment id to reference the rows by

    user_id - who the row belongs to, ideally it is the user_id not the username

    posted_by - who caused the row to be inserted. this would normally be the user the row belongs to (i.e. he bought credits), but you could have a case where an administrator or similar adds or subtracts an amount. it would be the admin's id. for something like a game, this would be the user's id who you were playing and they lost the amount that got credited to your account.

    amount - the amount + for credit, - for debit

    datetime - the datetime the row was inserted

    ip - most web based information storage records the current ip address of the request

    memo - why the credit/debit row was added - game winnings, loss, why the admin (see the possibilities in the posted_by field above) added something...

    additional fields could include an account type to distinguish between different accounts for one user

    Code:
    // example query inserting a debit (- amount)
    
    // values gotten from wherever you are determining the amounts from now...
    $amount = -2;
    $memo = "game loss";
    
    $query = "INSERT INTO transactions (user_id, posted_by, amount, datetime, ip, memo) VALUES
    	({$_SESSION['user_id']}, {$_SESSION['user_id']}, $amount, NOW(), '{$_SERVER['REMOTE_ADDR']}','$memo')";
    Code:
    // to sum the current user's account balance -
    $query = "SELECT SUM(amount) as balance FROM transactions WHERE user_id = {$_SESSION['user_id']}";
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #10
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi CFMaBiSmAd, thanks for your help. Although I am having a bit of trouble making this work.

    Due to the way I have designed the site, I need this to be called as a function when the form is processed. I will post the new form below

    PHP Code:
    <?php 
    include 'core/init.php';
    protect_page();
    include 
    'includes/overall/header.php'?>

    <?php

    if (isset($_GET['success']) === true && empty($_GET['success']) === true) {
        echo 
    'Your account has now been debited.';
    } else {
        if (empty(
    $_POST) === false && empty($errors) === true) {
            
    $amount $_POST['amount'];
            
    update_credits($user_id$amount);
            
    header('Location: sell.php?success');
            exit();
            
        } else if (empty(
    $errors) === false) {
            echo 
    output_errors($errors);
        }
        }
    ?>


          <h1>Sell</h1>
          <p></p>
          
        <form action="" method="post">
        <ul>
        <li>Test Debit:
             <input type="submit" value="Debit">

        </ul>
        </form>
          

          
    <?php include 'includes/overall/footer.php'?>
    Now the bit for the function. I have slightly altered what you posted to try and make the function work, but am having no luck.

    Code:
    function update_credits($user_id) {
    	$user_id 	= (int)$user_id;
    	$amount = -1;
    	$memo = "used";
    
    mysql_query("INSERT INTO transactions (user_id, posted_by, amount, datetime, ip, memo) VALUES
    	({$_SESSION['user_id']}, {$_SESSION['user_id']}, $amount, NOW(), '{$_SERVER['REMOTE_ADDR']}','$memo')");
    }
    Please note I altered the form to just run the update_credits function so the amount should be -1 and nothing is being written to the db table.

    I am getting no errors when I click on the Debit button, and it says that it was successful but no rows are added to the table

    As I have changed this to a mysql_query("") instead of the $query that you used, could this be the problem? Is the coding different for this type of query?

    Many Thanks

    Paul

  • #11
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Exclamation

    Hi, I have altered the coding to include the query on one page as it was not working when I was trying to include it as a function. However the last piece of code you gave does not work

    Code:
    // to sum the current user's account balance -
    $query = "SELECT SUM(amount) as balance FROM transactions WHERE user_id = {$_SESSION['user_id']}";
    I think I may have misunderstood how this works. I have made the table with the fields you suggested. The script writes the info to the database which is great.

    I am unsure how the above query sums up the - and + values. Also what do you mean 'as balance'? This is not a field in the form and not sure of its use in the query.

    Happy New Year

    Paul

  • #12
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi, I have gone back to trying to display the account balance using a function

    Code:
    function amount() {
    $query = "SELECT SUM(amount) FROM transactions WHERE user_id = {$_SESSION['user_id']}"; 
    }
    The code for the widget that displays this info is here

    PHP Code:
    <div class="widget">
        <h2>Credits</h2>
        <div class="inner">
    <?php
    $amount 
    amount();
    $suffix = ($amount != 1) ? 's' '';
    ?>
    You currrently have <?php echo amount(); ?> credit<?php echo $suffix?> remaining
        <ul>
        <li><a href="purchase.php">Buy more credits</a></li>
        </ul>
        
        </div>
        </div>
    I get the You currently have [blank] credits remaining. I have tried messing about with it, but every thing I do throws back an error, apart from with the above code which just doesnt work

    Many Thanks

    Paul

  • #13
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi guys, can I have some help here please?

    Thanks

  • #14
    New Coder
    Join Date
    Oct 2012
    Posts
    99
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Ok, tried a different way, but cant seem to display the result of the $query.

    Here is what I have

    PHP Code:
    <div class="widget">
        <h2>Credits</h2>
        <div class="inner">
    <?php
    $amount 
    amount();
    $suffix = ($amount != 1) ? 's' '';

    $query "SELECT SUM(amount) as balance FROM transactions WHERE user_id = {$_SESSION['user_id']}";?>
    You currrently have <?php echo $amount$suffix?> remaining
        <ul>
        <li><a href="purchase.php">Buy more credits</a></li>
        </ul>
        
        </div>
        </div>
    Any help is always appreciated

  • #15
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,059
    Thanks
    2
    Thanked 317 Times in 309 Posts
    The examples I posted were the mysql query statements showing how you could perform the stated tasks. In fact, they are php assignment statements, forming and assigning the string on the right-hand side to the php variable, who's name is $query, on the left-hand side. Other than being a php assignment statement, they are not code, working or otherwise, that you can copy/paste.

    Putting the appropriate values that you have available into the query, running the query, testing if it ran without errors, and then testing if a row was actually inserted for the insert query or fetching the data from the select query are things you should have mastered and already know how to do in order to incorporate those examples into a script.

    Not sure if you got the INSERT query to work, but -

    1) In post #10, you have called your function with a $user_id and $amount as parameters. Does you code at that point have a $user_id variable available?

    2) In post #10, your function definition doesn't have two parameters defined.

    3) In post #10, you have not modified the example query statement I posted to use your $user_id variable.

    4) In post #10, you have not tested if the query ran without any errors or tested if the query actually inserted a row. You can only return a success status, which you aren't doing, nor using in the calling code, if you know that both of these conditions were true.

    In post #11, the as balance is assigning an alias name to the SUM(amount) expression. The SUM(amount) expression is doing what it infers, it is summing up all the amount columns from the rows that the query matched. This is pretty basic mysql and can be looked up in the mysql documentation.

    In post #12, to make a general purpose function, that could get the account balance for any user, wouldn't you define and pass it the user_id you want it to use in the query? You would also need to run the query, test for query errors, fetch the data from the query, and return that value from the function so that it would be available to the calling code.
    Last edited by CFMaBiSmAd; 01-09-2014 at 11:59 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    aquaman (01-11-2014)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •