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.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need help with UPDATE query

    I've been watching and reading tutorials for about a month, so needless to say I have a LOT to learn when it comes to both PHP and MySQL. I would appreciate any help that you can offer. Please keep in mind that I'm basically a complete noob with this stuff, so I would appreciate it if you used the easiest terminology as possible.


    I'm currently working on a website for my gaming community. Within our community, we have ranking systems for each game we feature. Up until now, I've had to keep track of everyone's points (how they rank up) through our ranking systems via Excel. It is so incredibly time consuming, so I decided I wanted to have forms that my members can fill out. I want to make use of a database table(s) to keep track of their points, and I want them to be able to update it themselves at any point.

    I've tried so many things to try and get this to work so I couldn't list everything that I've tried. I can, however, supply you with the code I've been fiddling with the last few hours (code at bottom of this original post).

    My forms consist of checkboxes only. I'm needing a bit of math (just addition) to go into this, so I will do the best I can in regard to explaining the entire process (of the form). The form is below. I am providing you with each checkbox's name and id, and I am also providing you with the number of points that a member would receive for each of the options.

    The form itself:
    [ ] text here (name="codbop" id="codbop") - member will receive 250 points
    [ ] text here (name="codmw3p" id="codmw3p") - member will receive 300 points
    [ ] text here (name="onekdr" id="onekdr") - member will receive 100 points
    [ ] text here (name="twokdr" id="twokdr") - member will receive 300 points
    [ ] text here (name="threekdr" id="threekdr") - member will receive 500 points
    Update! (submit button)

    Scenario: Lets say that one of my members needs to update their rank, so they come to this form. As of right now, they have 0 points in the "total" value/item in the table. They check "codbop" and "codmw3p" and they click "Update" to update their rank. This member should be receiving a total of 550 with this one form submission.

    I know the code needs to add the options the member chooses (while ignoring the options they don't check/choose). In this case, the member chose "codbop" and "codmw3p." So, the code (in this particular scenario) will add "codbop" and "codmw3p," which will end up being 550 as we mentioned earlier. This 550 will replace the 0 (the previous total) with 550 (the new total) in "total."


    This is basically it. I'm not sure if this is easy to understand or not, so if you have any questions just let me know.

    P.S. I'm not sure how many tables I need, but thus far I've tried using just one table, which is called "myMembers." I've tried reading through tons of tutorials, but I never found anything that helpful. I noticed that a few tutorials used two or three tables for similar things of this nature, but I feel as though I should reach out to a community such as this for help for my particular project.


    What I've managed to mess around with the last few hours using tutorials here and there is below in the code box. I only have "codbop" and "codmw3p" in there because I wanted to try and figure out the code to add just two options together before I added the other options (which would be more code, I wanted to keep it simple). I have no idea what to do -- please help! I'm so embarrassed by the code I currently have, I know it's probably way off the mark. I've run out of options, so I'm hoping you guys can help me.

    Edit: P.S.S. I have a functional line of code that connects this to my database, so I left it out.

    Edit #2: P.S.S.S. The "total" item in the table is set to INT(255) with "As defined:" set to 0. These same settings are set for "codbo" and "codmw3p" as well.


    Code:
    <?PHP
    $total = "";
    $codbop = "";
    $codmw3p = "";
    
    $codbop = 'unchecked';
    $codmw3p = 'unchecked';
    $onekdr = 'unchecked';
    $twokdr = 'unchecked';
    $threekdr = 'unchecked';
    
    if (isset($_POST['Submit1'])) {
    
    if (isset($_POST['codbop'])) {
    $codbop = $_POST['codbop'];
    
    if ($codbop = '250') {
    $codbop = 'checked';
    }
    }
    
    if (isset($_POST['codmw3p'])) {
    $codmw3p = $_POST['codmw3p'];
    
    if ($codmw3p = '300') {
    $codmw3p = 'checked';
    }
    }
    
    if (isset($_POST['onekdr'])) {
    $onekdr = $_POST['onekdr'];
    
    if ($onekdr = '100') {
    $onekdr = 'checked';
    }
    }
    
    if (isset($_POST['twokdr'])) {
    $twokdr = $_POST['twokdr'];
    
    if ($twokdr = '300') {
    $twokdr = 'checked';
    }
    }
    
    if (isset($_POST['threekdr'])) {
    $threekdr = $_POST['threekdr'];
    
    if ($threekdr = '500') {
    $threekdr = 'checked';
    }
    }
    }
    
    $sqlUpdate = mysql_query("UPDATE myMembers SET total='$cdbop' + '$codmw3p' WHERE id='$id' LIMIT 1");
    ?>
    Last edited by MommaFushigi; 11-26-2011 at 08:09 PM. Reason: forgot to add something

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,038
    Thanks
    23
    Thanked 589 Times in 588 Posts
    When working with a number of checkboxes they must be grouped together with a single name.
    When some of the checkboxes are not sent - well you have found out.
    SUBMIT does not work in all browsers. Use the hidden input to determine submittion.

    Here's the code for the HTML form:
    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
    </head>
    <body>
    <form id="myform" action="test.php" method="post"
    <input name="cbox[]" type="checkbox" value="codbop" />codbop<br />
    <input name="cbox[]" type="checkbox" value="codmw3p" />codmw3p<br />
    <input name="cbox[]" type="checkbox" value="onekdr" />onekdr<br />
    <input name="cbox[]" type="checkbox" value="twokdr" />twokdr<br />
    <input name="cbox[]" type="checkbox" value="threekdr" />threekdr<br />
    <input type="hidden" name="msoft" value="bad" />
    <br />
    <input name="submit" value="submit" type="submit" />
    </body>
    </html>
    and here's the test.php it calls:
    PHP Code:
    <?php
    $total 
    '0';
    function 
    IsChecked($chkname,$value)
    {
        if(!empty(
    $_POST[$chkname]))
        {
            foreach(
    $_POST[$chkname] as $chkval)
            {
                if(
    $chkval == $value)
                {
                    return 
    true;
                }
            }
        }
        return 
    false;
    }

    if (
    $_POST["msoft"] == 'bad')
    {
        
    $total += IsChecked('cbox','codbop') ? 250 0;
        
    $total += IsChecked('cbox','codmw3p') ? 300 0;
        
    $total += IsChecked('cbox','onekdr') ? 100 0;
        
    $total += IsChecked('cbox','twokdr') ? 300 0;
        
    $total += IsChecked('cbox','threekdr') ? 500 0;
    }
    echo 
    $total;
    //$sqlUpdate = mysql_query("UPDATE myMembers SET total='$total' WHERE id='$id' LIMIT 1");
    ?>
    I know this is a lot so if you have questions just ask here and don't start another thread.

  • #3
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you for your response!

    That definitely makes sense for each of them to be grouped together with the same name.

    I plugged in the code you provided but it's telling me "Notice: Undefined index: msoft". Am I supposed to use "$msoft = ""; for that as well?

    Edit: Nevermind, as "$msoft = ""; didn't seem to work either, haha.
    Last edited by MommaFushigi; 11-25-2011 at 10:54 PM.

  • #4
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Update:

    I've continued to piddle with it but that pesky undefined index notice won't go away. I've asked around while waiting for a response here but the only answer I got was to suppress/hide notices, but I'd rather solve the problem that is causing me to get the notice.

    I tried re-copying and pasting the code that sunfighter graciously supplied me with, but I still get the undefined index error. Any ideas?

  • #5
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,038
    Thanks
    23
    Thanked 589 Times in 588 Posts
    There are TWO programs! If you run the top one, the HTML code pick the boxes and hit select That will run the php code for you and you'll see the out put.

    The only way your getting an Undefined index: msoft is by just running the php code. DON'T Do that!

    Two programs Put both in the same folder and run the HTML.
    Last edited by sunfighter; 11-26-2011 at 04:59 PM.

  • #6
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm not sure what you mean. I put the php code you provided at the top of my page (which is a .php page), and then the table underneath the php (after the html, head and body tags).

  • #7
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,038
    Thanks
    23
    Thanked 589 Times in 588 Posts
    Yeah that's the problem. If you want things in the same page use something like this:

    PHP Code:
    <?php
    $total 
    '0';
    function 
    IsChecked($chkname,$value)
    {
        if(!empty(
    $_POST[$chkname]))
        {
            foreach(
    $_POST[$chkname] as $chkval)
            {
                if(
    $chkval == $value)
                {
                    return 
    true;
                }
            }
        }
        return 
    false;
    }

    if(isset(
    $_POST['msoft']))
    {
        if (
    $_POST["msoft"] == 'bad')
        {
            
    $total += IsChecked('cbox','codbop') ? 250 0;
            
    $total += IsChecked('cbox','codmw3p') ? 300 0;
            
    $total += IsChecked('cbox','onekdr') ? 100 0;
            
    $total += IsChecked('cbox','twokdr') ? 300 0;
            
    $total += IsChecked('cbox','threekdr') ? 500 0;
        }
        echo 
    "When this is removed and the query reinstalled.<br /> You will not see this, but FYI the total is: <span style=\"color:red;\"> $total</span><br /><br />";
        
    //$sqlUpdate = mysql_query("UPDATE myMembers SET total='$total' WHERE id='$id'");
    }
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
    </head>
    <body>
    <form id="myform" action="#" method="post"
    <input name="cbox[]" type="checkbox" value="codbop" />codbop<br />
    <input name="cbox[]" type="checkbox" value="codmw3p" />codmw3p<br />
    <input name="cbox[]" type="checkbox" value="onekdr" />onekdr<br />
    <input name="cbox[]" type="checkbox" value="twokdr" />twokdr<br />
    <input name="cbox[]" type="checkbox" value="threekdr" />threekdr<br />
    <input type="hidden" name="msoft" value="bad" />
    <br />
    <input name="submit" value="submit" type="submit" />
    </body>
    </html>
    Hope this helps a little better. Please notice the if(isset($_POST['msoft'])) line This makes sure the rest of the code does not execute until we have something in msoft. Without it you get the error you were getting.
    PS You need to define the $id in this.
    Last edited by sunfighter; 11-26-2011 at 05:23 PM.

  • Users who have thanked sunfighter for this post:

    MommaFushigi (11-26-2011)

  • #8
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I re-did it and it seems to work. I fill out the form and I see this on my page: When this is removed and the query reinstalled. You will not see this, but FYI the total is: 300."

    I had checked "codmw3p," which has a value of 300, so that's already progress, so thank you! Although, I eagerly went to check the database to see if it added the 300 to the value already in "total," but it did not put the 300 in the database.



    Edit: Nevermind! I got the form working! There's one issue, though. Every time I fill out the form, the most recent form submission replaces the previous ones. For example, I filled out the form once and checked "codbop," which gave me 250 points. I filled it out again, but this time I checked "codmw3p," which gave me 300 points. I need the 300 to be added onto the current total, which, in this case, is 250 (for a total of 550) -- it, instead, replaced the 250 with the 300. I can't imagine I'd need to change much within the code, but I'll play around with it until I hear back from you to see if I can figure it out.



    Edit #2: Nevermind again, I figured it out! Thank you so much for your help, as I have no idea how long it would've taken me if I didn't ask for help. You have no idea how much time you will be saving me. Thanks again!
    Last edited by MommaFushigi; 11-26-2011 at 08:08 PM.


  •  

    Posting Permissions

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