PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'on' for column

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
richarddunnebsc
New php-forum User
New php-forum User
Posts: 6
Joined: Thu Aug 31, 2017 3:48 am

Tue Sep 12, 2017 7:09 am

I have a checkbox in a form named Relocate. The value is being inserted into a Boolean(tinyint) field in a table (MySQL). I have Relocate as a field in my prepared insert query and :Relocate as a value. In the execute array statement I have

Code: Select all

"Relocate" => $Relocate
. I dont't understand why I'm getting
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'on' for column 'Relocate'
Any help/guidance on this appreciated.

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 526
Joined: Mon Feb 22, 2016 5:52 pm

Tue Sep 12, 2017 11:51 am

It would be helpful if you posted more code including the sql

richarddunnebsc
New php-forum User
New php-forum User
Posts: 6
Joined: Thu Aug 31, 2017 3:48 am

Tue Sep 12, 2017 1:03 pm

This is the php code I have (new to pdo), I now getting undefined variables for each BindValue.

Code: Select all

if ($_SERVER['REQUEST_METHOD']=='POST') 
	{  
	if(isset($_POST['Submit']))
		{
		require_once 'host.php';
		$err = array();
			//performing all validations and raising corresponding errors
		  if (empty($_POST['Name'])) $err[] = "Firstname is required";  
		  if (empty($_POST['County']))  $err[] = "County is required";
		  if (empty($_POST['Sector']))  $err[] = "Sector is required";
		  if (empty($_POST['CV']))  $err[] = "CV is required";
		  if (!filter_var($_POST['Email'],FILTER_VALIDATE_EMAIL)) 
			{
			 $err[] = "Wrong email format";
			if (!$err) 
				{ 
				$Name = htmlspecialchars($_POST['Name']);
				$County = htmlspecialchars($_POST['County']);
				$Degrees = htmlspecialchars($_POST['Degrees']);
				$Sector = htmlspecialchars($_POST['Sector']);
				$DrivingLicences = htmlspecialchars($_POST['DrivingLicences']);
				$Relocate = htmlspecialchars($_POST['Relocate']);
				$Visa = htmlspecialchars($_POST['Visa']);
				$CV = htmlspecialchars($_POST['CV']);
				$Email = htmlspecialchars($_POST['Email']);
				}
			}
		}
	
	try
		{
		$Con = new PDO('mysql:host=localhost;dbname=mydb', $User, $Password);
		$Con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		$InsertIntoCandidatesQuery = "insert into candidates (Name, County, Sector, Degree, DrivingLicences, Relocate, Visa, CV, Email) values (:Name, :County, :Sector, :Degree, :DrivingLicences, :Relocate, :Visa, :CV, :Email)";
		$InsertIntoCandidatesPrepare = $Con->prepare($InsertIntoCandidatesQuery);
		$InsertIntoCandidatesPrepare->BindValue(':Name', $Name);
		$InsertIntoCandidatesPrepare->BindValue(':County', $County);
		$InsertIntoCandidatesPrepare->BindValue(':Sector', $Sector);
		$InsertIntoCandidatesPrepare->BindValue(':Degree', $Degree);
		$InsertIntoCandidatesPrepare->BindValue(':DrivingLicences', $DrivingLicences);
		$InsertIntoCandidatesPrepare->BindValue(':Relocate', $Relocate);
		$InsertIntoCandidatesPrepare->BindValue(':Visa', $Visa);
		$InsertIntoCandidatesPrepare->BindValue(':CV', $CV);
		$InsertIntoCandidatesPrepare->BindValue(':Email', $Email);
		$InsertIntoCandidatesPrepare->execute();
		}
	catch (Excepion $err)
		{
		echo $err;
		
		}

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 526
Joined: Mon Feb 22, 2016 5:52 pm

Tue Sep 12, 2017 3:34 pm

You have logic errors:

Code: Select all

if (!filter_var($_POST['Email'],FILTER_VALIDATE_EMAIL)) 
			{
			 $err[] = "Wrong email format";
			if (!$err) 
				{ 
				$Name = htmlspecialchars($_POST['Name']);
				$County = htmlspecialchars($_POST['County']);
				$Degrees = htmlspecialchars($_POST['Degrees']);
				$Sector = htmlspecialchars($_POST['Sector']);
				$DrivingLicences = htmlspecialchars($_POST['DrivingLicences']);
				$Relocate = htmlspecialchars($_POST['Relocate']);
				$Visa = htmlspecialchars($_POST['Visa']);
				$CV = htmlspecialchars($_POST['CV']);
				$Email = htmlspecialchars($_POST['Email']);
				}
			}
The variables are only set if the e-mail is in the wrong format.

Your code then goes on to open the database and tries to process the data, the correctness or existence of the data does not matter.

richarddunnebsc
New php-forum User
New php-forum User
Posts: 6
Joined: Thu Aug 31, 2017 3:48 am

Wed Sep 13, 2017 12:40 am

What do you mean by
The variables are only set if the e-mail is in the wrong format.
?

richarddunnebsc
New php-forum User
New php-forum User
Posts: 6
Joined: Thu Aug 31, 2017 3:48 am

Wed Sep 13, 2017 6:16 am

Theses are the inputs of undefined variables

Code: Select all

<select name="Sector" style="width: 100%; height: 40px; font-size: 20px;">
						<option value="" selected="selected">Preferred Sector</option> 
						<option value="">Accountancy & Finance</option>
						<option value="">Banking, Financial services & Insurance</option>
						<option value="">Beauty</option>
						<option value="">Construction, Architecture & Property</option>
						<option value="">Customer Service, Call Centres & Languages</option>
						<option value="">Education, Childcare & Training</option>
						<option value="">Engineering & Utilities</option>
						<option value="">Environmental, Health & Safety</option>
						<option value="">Food</option>
						<option value="">Hotel & Catering</option>
						<option value="">HR & Recruitment</option>
						<option value="">IT</option>
						<option value="">Legal</option>
						<option value="">Leisure & Sport</option>
						<option value="">Management</option>
						<option value="">Marketing</option>
						<option value="">Medical Professionals & Healthcare</option>
						<option value="">Science & Pharmaceutical</option>
						<option value="">Production, Manufacturing & Materials</option>
						<option value="">Public Sector</option>
						<option value="">Publishing, Media & Creative Arts</option>
						<option value="">Retailing, Wholesaling & Purchasing</option>
					</select>
<input type="checkbox" name="Passport">&nbsp;Require Visa?
<span style="font-size: 20px;">Upload CV </span> &nbsp; <input type="file" name="CV" style="font-size: 20px;">
<input type="email" name="Email" placeholder="Email" style="width: 50%; height: 40px; font-size: 20px;">
And the php (WIP) undefined variables are Passport on the line before try. Email,Sector, Visa and CV in the execute. I'm also getting a Integrity constraint violation: 1048 Column 'Email' cannot be null' on this line as well, even though Email has a valid email.

Code: Select all

require_once 'host.php';
if ($_SERVER['REQUEST_METHOD']=='POST') 
	{  
	$error = [];
	if(!empty($_POST['Name'])) {$Name = htmlspecialchars($_POST['Name']);}else{$error = "Name is required";}
	if(!empty($_POST['County'])) {$County = htmlspecialchars($_POST['County']);}else{$error = "County is required";}
	if(!empty($_POST['Sector'])) {$Sector = htmlspecialchars($_POST['Sector']);}else{$error = "Sector is required";}
	if(!empty($_POST['CV'])) {$CV = htmlspecialchars($_POST['CV']);}else{$error = "CV is required";}
	if(!empty($_POST['Email']) && !filter_var($_POST['Email'], FILTER_VALIDATE_EMAIL)) {$Email = htmlspecialchars($_POST['Email']);}else{$error = "No email or email not formatted properly";}
	$Degrees = htmlspecialchars($_POST['Degrees']);
	$DrivingLicences = htmlspecialchars($_POST['DrivingLicences']);
	$Relocate = $_POST['Relocate'];
	$Passport = $_POST['Passport'];
	
		
	try
		{
	
		$Con = new PDO('mysql:host=localhost;dbname=mydb', $User, $Password);
		$Con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		$InsertIntoCandidates = $Con->prepare("INSERT INTO candidates(Email,Name,County,Sector,Degrees,DrivingLicences,Relocate,Visa,CV) VALUES(:Email,:Name,:County,:Sector,:Degrees,:DrivingLicences,:Relocate,:Visa,:CV)");
		$InsertIntoCandidates->execute(array(':Email' => $Email, ':Name' => $Name, ':County' => $County, ':Sector' => $Sector, ':Degrees' => $Degrees, ':DrivingLicences' => $DrivingLicences, ':Relocate' => $Relocate, ':Visa' => $Visa, ':CV' => $CV));
		}
	catch (Exception $error)
		{
		echo $error;
		
		}
	}

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 526
Joined: Mon Feb 22, 2016 5:52 pm

Wed Sep 13, 2017 8:58 am

Sorry, I was using this for my choice of words:

Code: Select all

$err[] = "Wrong email format";
You have logic problems with your code, by this I mean your if statements, specifically at the lines already noted.

----------------------------------------------------------------------

Set and not-set variables:

Code: Select all

$this_is_a_set_variable = 'something';

if ($this_is_a_set_variable) {
  echo $this_is_a_set_variable, '<br>';
} else {
  echo 'This should not be executed since the variable is set';
}


/*
 *  This should produce an error if you have error reporting on because the variable is NOT set
*/

if ($this_is_not_set) {
  echo 'It is set, so no error';
} else {
  echo 'This should produce an error';
}
Look at the following code, do you think that $third_set_variable will be echoed?

Code: Select all

$set_variable = 1;
$set_variable2 = 2;

if ($set_variable) {
  if ($set_variable2) {
    echo $set_variable;
  } else {
    $third_set_variable = 3;
  }
}
echo $third_set_variable;

Post Reply