PHP checkbox input not updating database correctly

Question

Very new to writing code, so apologize in advance if some of it a little strange looking. I have managed to display the checkboxes and they update my database but assign a value of zero when it should be a 1, so there is obviously an error in how it treats the input. Any help is greatly appreciated. MySQL database has a username column and playdate 1,2,3,4,5. Username is VARCHAR and others are ENUM with values of 0 and 1.

<?php
$host="localhost";
$username="******";
$password="*******";
$db_name="signuplist";
$tbl_name="signupbydate";
$myusername=$_SESSION['logname'];
?>

<html>
    <head>
        <title>Checkbox</title>
    <head>
         <title>HTML Checkbox</title>
    </head>
    <body>
    <div style='margin-left:6.0in; margin-top:0.75in'>
    <p style='font-weight:bold'>
        When would you like to play golf?</p>
    <p>Choose the dates you wish to play</p>
    <p>Make sure you click 'Update' when you are done</p>
           <form name="requesttime" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
           <input type="checkbox" name="playdate[]" value="playdate1"> September 3, 2014 <br>
           <input type="checkbox" name="playdate[]" value="playdate2"> September 6, 2014 <br>
           <input type="checkbox" name="playdate[]" value="playdate3"> September 10, 2014 <br>
           <input type="checkbox" name="playdate[]" value="playdate4"> September 13, 2014 <br>
           <input type="checkbox" name="playdate[]" value="playdate5"> September 17, 2014 <br>
           <br>
           <input type="submit" value="Update" name="submit">
<?php

$cxn=mysqli_connect($host,$username,$password,$db_name)
     or die ("Couldn't Connect to Server");
$chkbox = array('playdate1', 'playdate2', 'playdate3', 'playdate4', 'playdate5');

     if(isset($_POST['submit']))
     {   $playdate = $_POST['playdate'];
         $values = array();
           foreach($chkbox as $selection )
           {     if(in_array($selection, $playdate))
                   { $values[ $selection ] = 1;  }
                 else
                   { $values[ $selection ] = 0;  }
     } 

    // MySQL statement. Don't forget to strip to avoid sql injection 

     $sql1="UPDATE $tbl_name 
            SET playdate1=$values[playdate1], playdate2=$values[playdate2], playdate3=$values  [playdate3], playdate4=$values[playdate4], playdate5=$values[playdate5]
            WHERE username='$myusername'";

           // MySQL statement to execute the UPDATE statement above.           
      mysqli_query($cxn, $sql1) or die('<br/>Error reading database: '.mysqli_error($cxn));
      mysqli_close($cxn);
     }  // End of, if statement from the button check
?>
</form>
</body>
</html>

Thanks in advance for your patience and kind assistance!

Try SET playdate1=$values['playdate1'], playdate2=$values['playdate2'], playdate3=$values ['playdate3'], playdate4=$values['playdate4'], playdate5=$values['playdate5'] — Funk Forty Niner
– Funk Forty Niner, Commented Jul 19, 2014 at 20:35
Thanks, but no luck-I get a syntax error when I add the brackets. — user3830570
– user3830570, Commented Jul 19, 2014 at 20:38
Yes, it is. I think it is something around the foreach loop as it updates the database but assigns a value of zero when it should be a 1... — user3830570
– user3830570, Commented Jul 19, 2014 at 20:42
Instead of { $values[ $selection ] = 1; } can you try { $values=1; } - Otherwise, I'd have to setup a DB and I won't have time to do it. Supper's almost ready on the BBQ lol — Funk Forty Niner
– Funk Forty Niner, Commented Jul 19, 2014 at 20:45

Dave Morrissey · Accepted Answer · 2014-07-19 21:52:05Z

So we've been round the houses a bit but here's the explanation. Your code is in fact pretty much fine, and generates a SQL query that looks correct when you print it. The problem is a consequence of using the ENUM type. When you have an enum that consists of numbers, and you update the value in the column using an integer, MySQL interprets the integer you send as the index of the enum value you want to set, and not the actual value. Thus 1 is the index of the enum value 0, and 0 is the index of the implicit enum value '' (empty string, inserted when an invalid/index is supplied).

The moral of this story is, don't try to use an ENUM column when your values are integers because of the confusion it will cause. Stick to using it for strings (or not at all). In this case, your value is a true/false and can best be represented with a BIT(1) column.

See this page for details: http://dev.mysql.com/doc/refman/5.0/en/enum.html

Collectives™ on Stack Overflow

PHP checkbox input not updating database correctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related