checkbox to mysql blues

cybereclipse · Mar 23, 2012

Okay, I've a small issue with a database project. I'm creating a database using php from an html form. the database works but I have a checkbok that users can check using the html form. The other fields word but the check box does not. Here is the code to see what I'm doing wrong.

<input name="survey" type="checkbox" id="survey" value="yes" <?php echo ($_POST['survey'] == 'yes' ? 'checked' : '');?> />

phpmyadmin says it 0 whether i checked or not. i'm using a tinyint(1) as my data type. Thanks in advanced!

bradgrafelman · Mar 23, 2012

The code you've shown us isn't really relevant (other than to show us the name/value of the checkbox form entity, I suppose).

What we need to see is the PHP code that processes form submissions and inserts/updates data in the DB.

cybereclipse · Mar 23, 2012

if (isset($_POST['submitted'])){

$fields = array(
	'email',
	'state',
	'district',
	'gender',
	'age',
	'profession',

);

foreach($fields as $fieldName) {
	if(isset($_POST[$fieldName]) and trim($_POST[$fieldName]) !==''){
		$$fieldName = trim($_POST[$fieldName]);
	}else {

			$errors[] = "Please enter your". $fieldName .""; //code to validate fields
}
}
if(!isset($errors)){

require_once('Connections/encourage.php');

$query = "INSERT INTO participants (email, state, district, gender, age, profession, survey, registration_date) 
VALUES ('$email', '$state', '$district', '$gender', '$age', '$profession','$survey', NOW())"; //databasse connection

$result = mysql_query ($query); 	

if ($result){

echo '<h1 id="mainhead">Thanks for submitting</hl>
<p>You are now registered</p>';
exit();

}else{

echo '<h1 id="mainhead">System Error</hl>
<p>Your registration could not be completed due to a system error We apologize for any incovience</p>';//gives system error
echo 'p' . mysql_error(). '<br /><br />Query: ' . $query . '</p>';
exit();

}
mysql_close();

} else { 

	echo '<h1 id="mainhead">Error!</h1>
	<p class="error">The following error(s) occurred:<br />';
	foreach($errors as $msg) {
		echo " - $msg<br/>\n";
	}
	echo '</p><p>Please try again.</p><p><br/></p>';
}

}
?>
  <form id="form1" name="form1" method="post" action"registration.php">
  <fieldset class="first">
  <label class="lableone" for="email">Email:* </label>
  <input name="email" value="<?php if(isset($_POST['email'])) echo $_POST['name'];?>"/>

  <label for="state"/>State:* </label>
  <input name="state" value="<?php if(isset($_POST['state'])) echo $_POST['state'];?>"/>

  <label for="schooldistrict"/>School District:* </label>
  <input name="district" value="<?php if(isset($_POST['district'])) echo $_POST['district'];?>" />

  <label for="gender">Gender:* </label>
 <select name="gender">
   <option>Choose Your Gender</option>
   <option value="male" <?php echo ($form['gender'] == 'male' ? ' selected' : ''); ?>>Male</option>
   <option value="female"<?php echo ($form['gender'] == 'female' ? ' selected' : ''); ?>>Female</option>
 </select>

<label for="age"/>Your Age:* </label>
  <input name="age" type="text" class="age" maxlength="2" value="<?php if(isset($_POST['age'])) echo $_POST['age'];?>"  />

  <label for="profession"/>Profession:* </label>
  <input name="profession" value="<?php if(isset($_POST['profession'])) echo $_POST['profession'];?>" />

  <label for="survey"/>Willingness to participate in future surveys: </label>
  <input name="survey" type="checkbox" id="survey" value="yes" <?php echo ($_POST['survey'] == 'yes' ? 'checked = "checked"' : '');?> />

  </fieldset>
<fieldset>
  <input class="btn" name="submit" type="submit" value="Submit" />
<input class="btn" name="reset" type="reset" value="Clear Form" />
<input type="hidden" name="submitted" value="TRUE" />
</fieldset>

  </form>

bradgrafelman · Mar 23, 2012

And where do you convert the string "yes" (which is the value of the checkbox when checked) to a numeric value that would fit in a tinyint(1) column?

EDIT: In addition, where do you define the variable $survey ?

cybereclipse · Mar 23, 2012

That I do not know. I'm using tinyint(1) for a reason, my phpmyadmin at 1and1 does not support Boolean . I'm using mysql version 5.0. If i'm using tinyint, Since its coming zeros anyways, I would like to read "0" for no "1" for yes. It would be great if the database records when the user click the check box = "yes", not checked ="no"

I can prob use boolean for true or false but i'm open to suggestions, advice, and the correct php syntax. Thanks

traq · Mar 24, 2012

BOOLEAN is the same thing (i.e., an alias) for TINYINT.

One solution would be to change your inputs to use [font=monospace]value="1"[/font] rather than [font=monospace]value="yes"[/font].

The other would be to translate any 'yes' values into [font=monospace]1[/font] values before putting them into the query.

HOWEVER--

Your code is open to SQL injection. Your "validation" function makes sure that fields are not empty, but does not sanitize them. You need to be using [man]mysql_real_escape_string/man to prevent problems.

Also, what happens when the survey box is not checked? Your [font=monospace]$survey[/font] variable would not be set. You should be getting a warning about that when you try to use it even though it doesn't exist.

bradgrafelman · Mar 24, 2012

traq wrote:
BOOLEAN is the same thing (i.e., an alias) for TINYINT.

That's true in the latest version of MySQL, yes, but I still prefer using 'BOOL' (or 'BOOLEAN') when applicable since a) you never know if that might change in the future, and b) I find 'BOOL' to be more descriptive/transparent than 'TINYINT(1)'.

@: Also note that '1' is different than 1. Taking that into consideration, plus traq's observations above regarding security as well as the issue of a possibly undefined variable, one suggestion I might make would be to do something like:

$survey = isset($_POST['survey']) ? 1 : 0;

which is a) compact, and b) takes care of both sanitization and undefined variable errors.

Weedpacket · Mar 24, 2012

traq wrote:
Your code is open to SQL injection. Your "validation" function makes sure that fields are not empty, but does not sanitize them. You need to be using mysql_real_escape_string() to prevent problems.

Of course, for a checkbox this is easily done because its value is not as important as whether it's even there in the submitted data or not because that is what indicates whether or not it was checked (if it is present, insert 1; if it's not, insert 0).

Not to say that it's just as important to make sure all the other fields are sanitised. And it may also be a good idea to start moving away from the long-term deprecated MySQL extension (and perhaps into one with parameterised queries that do the necessary escaping automatically).

traq · Mar 24, 2012

bradgrafelman;10999778 wrote:
... I still prefer using 'BOOL' (or 'BOOLEAN') when applicable since a) you never know if that might change in the future, and b) I find 'BOOL' to be more descriptive/transparent than 'TINYINT(1)'.

agreed.

bradgrafelman;10999778 wrote:
@: Also note that '1' is different than 1...

In my experience, both [font=monospace]1[/font] and [font=monospace]'1'[/font] are evaluated (in BOOL columns) as [font=monospace]1[/font] by mysql.
...tested just now on 5.1.14

I've never thought about it before, and after a quick search I couldn't find documentation to indicate if this is intentional (and/or reliable). Do you know of an answer? Specifically, if this might be unreliable across versions or configurations?

bradgrafelman · Mar 24, 2012

traq;10999786 wrote:
Do you know of an answer?

Not really... I suppose it's more of a personal preference than anything else. MySQL, just like PHP, does know when to do some automatic type juggling when you give it something that doesn't make sense (e.g. give it a string of ASCII characters rather than numeric values). I blame it on my C/C++ background.

cybereclipse · Mar 25, 2012

Yes, awesome. I'm very aware that I need to secure my code but that's my next step. Not to familiar with that so could anyone help me out with that?

Bonesnap · Mar 25, 2012

cybereclipse;10999846 wrote:
Yes, awesome. I'm very aware that I need to secure my code but that's my next step. Not to familiar with that so could anyone help me out with that?

String data should be put through the mysql_real_escape_string() function and data that you're expecting to be an integer should be cast as an integer.

Weedpacket · Mar 25, 2012

cybereclipse wrote:
I'm very aware that I need to secure my code but that's my next step.

Security is not just something you can bolt on afterwards. It's something you have to keep in mind right from the start.

"Yes, I know anyone can just open the car door, get in, start it and drive away. Adding security is my next step."

Ashley_Sheridan · Mar 28, 2012

I'd probably go with an enum data type for the field for this. It's a yes/no value so it's unlikely to change unless you need that mysterious third value of 'maybe'. Internally it's stored as a tinyint, so there's no extra storage used (IIRC) and you use it in your queries as if it were a string, so it would work perfectly in the above code where it's being compared to the value of 'yes'

cybereclipse · Mar 28, 2012

Thanks for your response. I will try enum but I have a question, If I use that data type, would this code work

 <input name="survey" type="checkbox" value="<?php echo (isset($_POST['survey']))? 'checked' : 'unchecked';?>" />

||

$survey = isset($_POST['survey']) ? 1 : 0;

that bradgrafelman provided?

bradgrafelman · Mar 28, 2012

No, because a field defined as ENUM('no', 'yes') would have values of 1 and 2 for 'no' and 'yes', respectively. A value of 0 (or the empty string, '') is more like an error/undefined/NULL value. If you wanted to use an ENUM column, then you'd want to make sure that you're setting $survey to either 'yes' or 'no' - not a numeric value.

cybereclipse · Mar 28, 2012

Okay so I went to set ENUM ('1', '2')

and changed to 'yes' and 'no' in my php code

  <input name="survey" type="checkbox" value="<?php $survey = isset($_POST['survey']) ? 'yes' : 'no'; ?>" />

bradgrafelman · Mar 28, 2012

cybereclipse;11000256 wrote:
Okay so I went to set ENUM ('1', '2')

Why would you do that? The string '1' and the string '2' (note those are different than the numbers one and two) aren't very descriptive at all.

If you walked up to me and asked me if I liked the color green, and I responded with '2', would you understand what I meant?

cybereclipse;11000256 wrote:
and changed to 'yes' and 'no' in my php code

... which adds further confusion into the mix, because now your PHP code doesn't match what the DB is expecting.

cybereclipse · Mar 28, 2012

well show me how its done.

bradgrafelman · Mar 28, 2012

I already did that back in post #7 of this thread.

checkbox to mysql blues

Ccybereclipse

Bbradgrafelman

Ccybereclipse

Bbradgrafelman

Ccybereclipse

Ttraq

Bbradgrafelman

Weedpacket

Ttraq

Bbradgrafelman

Ccybereclipse

BBonesnap

Weedpacket

AAshley_Sheridan

Ccybereclipse

Bbradgrafelman

Ccybereclipse

Bbradgrafelman

Ccybereclipse

Bbradgrafelman